java怎么连接数据库executeUpdate()和executeQuery()

这篇文章主要介绍了java怎么连接数据库executeUpdate()和executeQuery()的相关知识，内容详细易懂，操作简单快捷，具有一定借鉴价值，相信大家阅读完这篇java怎么连接数据库executeUpdate()和executeQuery()文章都会有所收获，下面我们一起来看看吧。

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 block                        e.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为0                        pstmt.setString(7,"0");                        //注册时，默认为员工角色，role值为2                        pstmt.setString(8,"2");                        pstmt.executeUpdate();                  } catch (SQLException e) {                        // TODO Auto-generated catch block                        e.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 block                        e.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();                // 新建一个集合departmentsList                List 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 block                        e.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 + "' and  password='" + 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 block                        e.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.字符串的拼接必须在双引号的基础上被单引号套住

上面有个小陷阱

如果加了

会正常执行，如果没有加，会因为字段不是字符串而报错.

结果集为空

2.在Bean类,默认的构造方法还与参数顺序有关

也就是说public Employee(String user,int id, String pwd){}

和 public Employee(int id,String user,String pwd){} 是不一样的构造方法

测试main方法里,插入的数据的类型顺序决定了调用哪个构造方法.

3.构造方法的方法名就是类名....

4.system.out.println 里打印加不加toString的区别

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

5.sql语句里,双引号的里面套双引号,会有歧义

会报错

应该在里面放单引号

关于"java怎么连接数据库executeUpdate()和executeQuery()"这篇文章的内容就介绍到这里，感谢各位的阅读！相信大家对"java怎么连接数据库executeUpdate()和executeQuery()"知识都有一定的了解，大家如果还想学习更多知识，欢迎关注行业资讯频道。