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 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语句里,双引号的里面套双引号,会有歧义

会报错应该在里面放单引号

execute()和executeUpdate()主要区别execute()返回一个boolean类型值，true表示第一个结果是ResultSet对象，false表示第一个结果是没有结果的更新语句（insert，delete，update）。

executeUpdate()返回一个int类型值，表示有几条数据受到了影响。

此外，execute()还可以通过getResultSet()获得执行语句后的结果；

以上为个人经验，希望能给大家一个参考，也希望大家多多支持脚本之家。

您可能感兴趣的文章:java中的executeQuery()方法使用