java如何连接数据库executeUpdate()和executeQuery()

目录

  • Update
  • Insert
  • Delete
  • select
  • 需要注意的点
    • 1.字符串的拼接必须在双引号的基础上被单引号套住
    • 2.在Bean类,默认的构造方法还与参数顺序有关
    • 3.构造方法的方法名就是类名....
    • 4.system.out.println 里打印加不加toString的区别
    • 5.sql语句里,双引号的里面套双引号,会有歧义
  • execute()和executeUpdate()主要区别
    executeUpdate

    Update
    //没有返回值public void update(int count){conn=DBUtil.getConn(); String sql="update counter set count=?"; try {PreparedStatement ps = conn.prepareStatement(sql); //传进去的ps.setInt(1,count); ps.executeUpdate(); } catch (SQLException e) {e.printStackTrace(); }finally{DBUtil.closeConn(); }}


    Insert
    //没有返回值,参数是个字符串部门名称就ok了,因为id的话是自增 public void insert(String departmentname) {conn = ConnectionFactory.getConnection(); String sql = "insert into department (departmentname) values(?)"; try {PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setString(1, departmentname); pstmt.executeUpdate(); } catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace(); } finally {ConnectionFactory.closeConnection(); } }

    //因为employeeid自增,所以不用设置public void insert(Employee employee){conn=ConnectionFactory.getConnection(); String sql="insert into employee"+"(employeename,username,password,phone,email,departmentid,status,role)" +" values(?,?,?,?,?,?,?,?)"; try {PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setString(1,employee.getEmployeename()); pstmt.setString(2,employee.getUsername()); pstmt.setString(3,employee.getPassword() ); pstmt.setString(4,employee.getPhone() ); pstmt.setString(5,employee.getEmail()); pstmt.setInt(6,employee.getDepartmentid()); //注册成功后,默认为正在审核,status为0pstmt.setString(7,"0"); //注册时,默认为员工角色,role值为2pstmt.setString(8,"2"); pstmt.executeUpdate(); } catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace(); }finally{ConnectionFactory.closeConnection(); }}


    Delete
    //删除不用返回值 public void delete(int departmentid) {conn = ConnectionFactory.getConnection(); String sql = "delete from department where departmentid=?; "; try {PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt(1, departmentid); pstmt.executeUpdate(); } catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace(); } finally {ConnectionFactory.closeConnection(); } }


    select
    //返回int类型public int select(){ int count=0; conn=DBUtil.getConn(); String sql = "select * from counter"; try{PreparedStatement ps = conn.PreparedStatement(sql); ResultSet rs =ps.excuteQuery(); if(rs.next()){count=rs.getInt("visitcount"); } }catch{ }finally{DBUtil.closeConn(); } return count; }

    //返回部门集合 public List selectAll() {conn = ConnectionFactory.getConnection(); // 新建一个集合departmentsListList departmentsList = new ArrayList(); try {Statement st = null; String sql = "select * from department"; st = conn.createStatement(); ResultSet rs = st.executeQuery(sql); Department department; while (rs.next()) {// 新建一个department来接收数据库的信息department = new Department(); department.setDepartmentid(rs.getInt("departmentid")); department.setDepartmentname(rs.getString("departmentname")); departmentsList.add(department); }} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace(); } finally {ConnectionFactory.closeConnection(); }// 返回集合return departmentsList; } //返回员工public List selectAllEmployee(){conn=ConnectionFactory.getConnection(); List employeeslist=new ArrayList(); Employee employee=null; try {PreparedStatement st=null; //只查询已注册且未审批 且 角色是员工的String sql="select * from employee where role='2' and status='0'"; st = conn.prepareStatement(sql); ResultSet rs =st.executeQuery(sql); while(rs.next()){employee=new Employee(); employee.setEmployeeid(rs.getInt("employeeid")); employee.setEmployeename(rs.getString("employeename")); employee.setUsername(rs.getString("username")); employee.setPhone(rs.getString("phone")); employee.setEmail(rs.getString("email")); employee.setStatus(rs.getString("status")); employee.setDepartmentid(rs.getInt("departmentid")); employee.setPassword(rs.getString("password")); employee.setRole(rs.getString("role")); employeeslist.add(employee); }} catch (SQLException e) {e.printStackTrace(); }finally{//最后总要关闭连接ConnectionFactory.closeConnection(); }return employeeslist; }

    public Employee selectByNamePwd(String username, String pwd) {Employee employee = null; try {//创建PreparedStatement对象PreparedStatement st = null; //查询语句String sql = "select * from employee where username='" + username + "' andpassword='" + pwd + "'"; st = conn.prepareStatement(sql); ResultSet rs = st.executeQuery(sql); //判断结果集有无记录,如果有:则把内容取出来,变成一个employee对象,并且返回它if (rs.next() == true) {employee = new Employee(); employee.setEmployeeid(rs.getInt("employeeid")); employee.setEmployeename(rs.getString("employeename")); employee.setUsername(rs.getString("username")); employee.setPhone(rs.getString("phone")); employee.setEmail(rs.getString("email")); employee.setStatus(rs.getString("status")); employee.setDepartmentid(rs.getInt("status")); employee.setPassword(rs.getString("password")); employee.setRole(rs.getString("role")); }} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace(); } finally {ConnectionFactory.closeConnection(); }return employee; }

    public Employee selectByUsername(String username){conn=ConnectionFactory.getConnection(); Employee employee=null; try {PreparedStatement st=null; String sql="select * from employee where username='"+username+"'"; st = conn.prepareStatement(sql); ResultSet rs =st.executeQuery(sql); if(rs.next()==true){employee=new Employee(); employee.setEmployeeid(rs.getInt("employeeid")); employee.setEmployeename(rs.getString("employeename")); employee.setUsername(rs.getString("username")); employee.setPhone(rs.getString("phone")); employee.setEmail(rs.getString("email")); employee.setStatus(rs.getString("status")); employee.setDepartmentid(rs.getInt("status")); employee.setPassword(rs.getString("password")); employee.setRole(rs.getString("role")); }} catch (SQLException e) {e.printStackTrace(); }finally{ConnectionFactory.closeConnection(); }return employee; }


    需要注意的点
    1.字符串的拼接必须在双引号的基础上被单引号套住
    上面有个小陷阱
    如果加了
    java如何连接数据库executeUpdate()和executeQuery()
    文章图片

    会正常执行,如果没有加,会因为字段不是字符串而报错.
    java如何连接数据库executeUpdate()和executeQuery()
    文章图片

    结果集为空
    java如何连接数据库executeUpdate()和executeQuery()
    文章图片

    java如何连接数据库executeUpdate()和executeQuery()
    文章图片

    java如何连接数据库executeUpdate()和executeQuery()
    文章图片


    2.在Bean类,默认的构造方法还与参数顺序有关
    也就是说public Employee(String user,int id, String pwd){}
    和 public Employee(int id,String user,String pwd){}是不一样的构造方法
    测试main方法里,插入的数据的类型顺序决定了调用哪个构造方法.
    【java如何连接数据库executeUpdate()和executeQuery()】java如何连接数据库executeUpdate()和executeQuery()
    文章图片


    3.构造方法的方法名就是类名....
    java如何连接数据库executeUpdate()和executeQuery()
    文章图片


    4.system.out.println 里打印加不加toString的区别
    java如何连接数据库executeUpdate()和executeQuery()
    文章图片

    看起来没有区别(这个不敢肯定)

    5.sql语句里,双引号的里面套双引号,会有歧义
    java如何连接数据库executeUpdate()和executeQuery()
    文章图片

    会报错
    应该在里面放单引号
    java如何连接数据库executeUpdate()和executeQuery()
    文章图片


    execute()和executeUpdate()主要区别
    • execute()返回一个boolean类型值,true表示第一个结果是ResultSet对象,false表示第一个结果是没有结果的更新语句(insert,delete,update)。
    • executeUpdate()返回一个int类型值,表示有几条数据受到了影响。
    此外,execute()还可以通过getResultSet()获得执行语句后的结果;
    以上为个人经验,希望能给大家一个参考,也希望大家多多支持脚本之家。

      推荐阅读