Jsp连接MySql,实现对数据的增删改查

package com.zl.model; import java.sql.*; import java.util.*; public class UserBeanCL { // 验证用户是否存在 private Connection conn = null; private PreparedStatement pstmt = null; private ResultSet rs = null; private Statement stmt = null; private int pageSize = 5; private int pageCount = 0;// 该值从rowCount和pageSize查询 private int rowCount = 0;// 该值从数据库查询 // 关闭资源函数 public boolean checkUser(String u, String p) { boolean b = false; try { ConnDB cd = new ConnDB(); conn = cd.getConn(); pstmt = conn.prepareStatement("select * from users where username = ? and password = ? "); pstmt.setString(1, u); pstmt.setString(2, p); rs = pstmt.executeQuery(); if (rs.next()) { b = true; } } catch (SQLException e) { e.printStackTrace(); } finally { this.close1(); } return b; } public void close1() { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } rs = null; } if (pstmt != null) { try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } pstmt = null; } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } conn = null; } } public void close2() { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } stmt = null; } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } conn = null; } } // 返回分页总页数 public int getPageCount() { try { conn = new ConnDB().getConn(); stmt = conn.createStatement(); rs = stmt.executeQuery("select count(*) from users"); if (rs.next()) { rowCount = rs.getInt(1); } // 计算pageCount的值 if (rowCount % pageSize == 0) { pageCount = rowCount / pageSize; } else { pageCount = (rowCount / pageSize) + 1; } } catch (SQLException e) { e.printStackTrace(); } finally { this.close2(); } return pageCount; } // 得到用户需要显示的信息 public ArrayList<UserBean> getUserPage(String PageNow) { ArrayList<UserBean> al = new ArrayList<UserBean>(); int Now = 1; try { if (PageNow != null) { Now = Integer.parseInt(PageNow); } conn = new ConnDB().getConn(); PreparedStatement pstmt = conn .prepareStatement("select * from users order by userid asc limit ?,? "); pstmt.setInt(1, (Now - 1) * pageSize); pstmt.setInt(2, pageSize); rs = pstmt.executeQuery(); while (rs.next()) { UserBean ub = new UserBean(); ub.setUserid(rs.getInt(1)); ub.setRoomNumber(rs.getString(2)); ub.setUsername(rs.getString(3)); ub.setPassword(rs.getString(4)); ub.setSex(rs.getString(5)); ub.setSchlNumber(rs.getString(6)); ub.setCollege(rs.getString(7)); ub.setGrade(rs.getString(8)); ub.setTime(rs.getString(9)); ub.setNote(rs.getString(10)); al.add(ub); } } catch (SQLException e) { e.printStackTrace(); } finally { this.close1(); } return al; } public int countx(String pageNow) { int size = 9; int x = 0; int temp = Integer.parseInt(pageNow); x = temp - size; if (x <= 0) { x = 1; } return x; } public int county(String pageNow) { int size = 9; int y = 0; int temp = Integer.parseInt(pageNow); y = temp + size; if (y > pageCount) { y = pageCount; } return y; } public boolean delUserById(String SuserId) { boolean b = false; try { conn = new ConnDB().getConn(); pstmt = conn.prepareStatement("delete from users where userid=?"); pstmt.setString(1, SuserId); int num = pstmt.executeUpdate(); if (num == 1) { b = true; } } catch (Exception e) { e.printStackTrace(); } finally { this.close1(); } return b; } /** * @author c3t(工位85) * @param用户信息封装的bean * @return boolean:如果为true,说明添加成功 */ public boolean addUser(UserBean user) { boolean b = false; try { UserBean ub = user; conn = new ConnDB().getConn(); pstmt = conn.prepareStatement("insert users values(null,?,?,?,?,?,?,?,?,?)"); pstmt.setString(1, ub.getRoomNumber()); pstmt.setString(2, ub.getUsername()); pstmt.setString(3, ub.getPassword()); pstmt.setString(4, ub.getSex()); pstmt.setString(5, ub.getSchlNumber()); pstmt.setString(6, ub.getCollege()); pstmt.setString(7, ub.getGrade()); pstmt.setString(8, ub.getTime()); pstmt.setString(9, ub.getNote()); int num = pstmt.executeUpdate(); if (num == 1) { b = true; } } catch (Exception e) { e.printStackTrace(); } finally { this.close1(); } return b; } public boolean updateUser(UserBean user) { boolean b = false; try { UserBean ub = user; conn = new ConnDB().getConn(); pstmt = conn.prepareStatement("update users set roomNumber=?," + "username=?,password=?,sex=?,schlNumber=?,college=?," + "grade=?,time=?,note=?where userid=?"); pstmt.setString(1, ub.getRoomNumber()); pstmt.setString(2, ub.getUsername()); pstmt.setString(3, ub.getPassword()); pstmt.setString(4, ub.getSex()); pstmt.setString(5, ub.getSchlNumber()); pstmt.setInt(10, ub.getUserid()); pstmt.setString(6, ub.getCollege()); pstmt.setString(7, ub.getGrade()); pstmt.setString(8, ub.getTime()); pstmt.setString(9, ub.getNote()); System.out.println(user.getUserid()+ub.getUserid()); int num = pstmt.executeUpdate(); System.out.println(num); if (num == 1) { b = true; } } catch (Exception e) { e.printStackTrace(); } finally { this.close1(); } return b; } public ArrayList<UserBean> findUser(String user){ ArrayList<UserBean> al=new ArrayList<UserBean>(); try{ conn=new ConnDB().getConn(); pstmt=conn.prepareStatement("select * from users where username=?"); pstmt.setString(1, user); rs=pstmt.executeQuery(); while(rs.next()){ UserBean ub=new UserBean(); ub.setUserid(rs.getInt(1)); ub.setRoomNumber(rs.getString(2)); ub.setUsername(rs.getString(3)); ub.setPassword(rs.getString(4)); ub.setSex(rs.getString(5)); ub.setSchlNumber(rs.getString(6)); ub.setCollege(rs.getString(7)); ub.setGrade(rs.getString(8)); ub.setTime(rs.getString(9)); ub.setNote(rs.getString(10)); al.add(ub); } }catch(Exception e){ e.printStackTrace(); }finally{ this.close1(); } return al; } //通过房号查询单个用户 public ArrayList<UserBean> findbyRoom(String user){ ArrayList<UserBean> al=new ArrayList<UserBean>(); try{ conn=new ConnDB().getConn(); pstmt=conn.prepareStatement("select * from users where roomNumber=?"); pstmt.setString(1, user); rs=pstmt.executeQuery(); while(rs.next()){ UserBean ub=new UserBean(); ub.setUserid(rs.getInt(1)); ub.setRoomNumber(rs.getString(2)); ub.setUsername(rs.getString(3)); ub.setPassword(rs.getString(4)); ub.setSex(rs.getString(5)); ub.setSchlNumber(rs.getString(6)); ub.setCollege(rs.getString(7)); ub.setGrade(rs.getString(8)); ub.setTime(rs.getString(9)); ub.setNote(rs.getString(10)); al.add(ub); } }catch(Exception e){ e.printStackTrace(); }finally{ this.close1(); } return al; } //通过用户名查询单个用户 public UserBean getOneUser(int userid){ UserBean ub = new UserBean(); try{ conn=new ConnDB().getConn(); pstmt=conn.prepareStatement("select * from users where userid=?"); pstmt.setInt(1,