JSP期末项目（图书馆管理系统附数据库）

package fjzzy.org; import java.sql.*; public class ConnDB { int i=0; Connection conn=null; PreparedStatement pstmt=null; ResultSet rs=null; static String url="jdbc:mysql://localhost:3306/work"; static String user="root"; static String password="123456"; //连接数据库的方法 public static Connection getConnection(){ Connection conn=null; try { Class.forName("com.mysql.jdbc.Driver");//注册驱动类 }catch(ClassNotFoundException e){//没有找到驱动类时 System.out.print("无法找到驱动类"); } try { conn=DriverManager.getConnection(url, user, password); } catch (SQLException e) { e.printStackTrace(); } if(conn==null){ System.out.print("数据库连接失败"); } return conn; } //用户名登陆 public ResultSet doQuery(String sql,String name,String password){ try{ conn=ConnDB.getConnection(); pstmt=conn.prepareStatement(sql); pstmt.setString(1, name); pstmt.setString(2, password); rs=pstmt.executeQuery(); }catch (SQLException e) { e.printStackTrace(); } return rs; } //手机号登陆 public ResultSet doQuery2(String sql,String name,String telphone){ try{ conn=ConnDB.getConnection(); pstmt=conn.prepareStatement(sql); pstmt.setString(1, name); pstmt.setString(2, telphone); rs=pstmt.executeQuery(); }catch (SQLException e) { e.printStackTrace(); } return rs; } //注册 public int doUpdate(String sql,String name,String password,String telphone){ try{ conn=ConnDB.getConnection(); pstmt=conn.prepareStatement(sql); pstmt.setString(1, name); pstmt.setString(2, password); pstmt.setString(3, telphone); i=pstmt.executeUpdate(); }catch (SQLException e) { e.printStackTrace(); } return i; } //用户名验证 public ResultSet checkname(String sql,String name){ try{ conn=ConnDB.getConnection(); pstmt=conn.prepareStatement(sql); pstmt.setString(1, name); rs=pstmt.executeQuery(); }catch (SQLException e) { e.printStackTrace(); } return rs; } //手机号验证 public ResultSet checktelphone(String sql,String telphone){ try{ conn=ConnDB.getConnection(); pstmt=conn.prepareStatement(sql); pstmt.setString(1, telphone); rs=pstmt.executeQuery(); }catch (SQLException e) { e.printStackTrace(); } return rs; } //数据库模糊查询 public ResultSet selectsql(String sql,String bookname){ try { conn=ConnDB.getConnection(); pstmt=conn.prepareStatement(sql); pstmt.setString(1, "%" + bookname + "%"); rs=pstmt.executeQuery(); } catch (Exception e) { // TODO: handle exception } return rs; } //书籍录入 public int insert(String sql,String bookid, String bookname,String booktype,String bookwriter,String bookdetail,String bookprice,String bookstock,String bookimgsrc){ try{ conn=ConnDB.getConnection(); pstmt=conn.prepareStatement(sql); pstmt.setString(1, bookid); pstmt.setString(2, bookname); pstmt.setString(3, booktype); pstmt.setString(4, bookwriter); pstmt.setString(5, bookdetail); pstmt.setString(6, bookprice); pstmt.setString(7, bookstock); pstmt.setString(8, bookimgsrc); i=pstmt.executeUpdate(); }catch (SQLException e) { e.printStackTrace(); } return i; } //书籍修改 public int updata(String sql,String bookid, String bookname,String booktype,String bookwriter,String bookdetail,String bookprice,String bookstock,String bookimgsrc){ try{ conn=ConnDB.getConnection(); pstmt=conn.prepareStatement(sql); pstmt.setString(1, booktype); pstmt.setString(2, bookwriter); pstmt.setString(3, bookdetail); pstmt.setString(4, bookprice); pstmt.setString(5, bookstock); pstmt.setString(6, bookimgsrc); pstmt.setString(7, bookname); pstmt.setString(8, bookid); i=pstmt.executeUpdate(); }catch (SQLException e) { e.printStackTrace(); } return i; } //删除书籍 public int delete(String sql,String bookid, String bookname,String booktype){ try{ conn=ConnDB.getConnection(); pstmt=conn.prepareStatement(sql); pstmt.setString(1, bookid); pstmt.setString(2, bookname); pstmt.setString(3, booktype); i=pstmt.executeUpdate(); }catch (SQLException e) { e.printStackTrace(); } return i; } //查找类型 public ResultSet selecttype(String sql){ try { conn=ConnDB.getConnection(); pstmt=conn.prepareStatement(sql); rs=pstmt.executeQuery(); } catch (Exception e) { // TODO: handle exception } return rs; } //删除用户 public int delectuser(String sql,String name){ try{ conn=ConnDB.getConnection(); pstmt=conn.prepareStatement(sql); pstmt.setString(1, name); i=pstmt.executeUpdate(); }catch (SQLException e) { e.printStackTrace(); } return i; } //book删除库存 public int updatebook(String sql,int bookstock, String bookid){ try{ conn=ConnDB.getConnection(); pstmt=conn.prepareStatement(sql); pstmt.setInt(1,bookstock); pstmt.setString(2, bookid); i=pstmt.executeUpdate(); }catch (SQLException e) { e.printStackTrace(); } return i; } //book删除lingshi public int deletelingshi(String sql, String name){ try{ conn=ConnDB.getConnection(); pstmt=conn.prepareStatement(sql); pstmt.setString(1,name); i=pstmt.executeUpdate(); }catch (SQLException e) { e.printStackTrace(); } return i; } //借阅管理 public ResultSet selectbook(String sql,String bookid){ try { conn=ConnDB.getConnection(); pstmt=conn.prepareStatement(sql); pstmt.setString(1, bookid); rs=pstmt.executeQuery(); } catch (Exception e) { // TODO: handle exception } return rs; } public ResultSet selectuser(String sql,String name){ try { conn=ConnDB.getConnection(); pstmt=conn.prepareStatement(sql); pstmt.setString(1, name); rs=pstmt.executeQuery(); } catch (Exception e) { // TODO: handle exception } return rs; } public int insertborrow(String sql,String bookid,String bookname,String booktype,int booknumber,String bookdate,String bookrule_date,String name){ try{ conn=ConnDB.getConnection(); pstmt=conn.prepareStatement(sql); pstmt.setString(1, bookid); pstmt.setString(2, bookname); pstmt.setString(3, booktype); pstmt.setInt(4, booknumber); pstmt.setString(5, bookdate); pstmt.setString(6, bookrule_date); pstmt.setString(7, name); i=pstmt.executeUpdate(); }catch (SQLException e) { e.printStackTrace(); } return i; } //加入临时表 public int lingshi(String sql,String bookid,String name,String bookname){ try{ conn=ConnDB.getConnection(); pstmt=conn.prepareStatement(sql); pstmt.setString(1, bookid); pstmt.setString(2, name); pstmt.setString(3, "1"); pstmt.setString(4, bookname); i=pstmt.executeUpdate(); }catch (SQLException e) { e.printStackTrace(); } return i; } public ResultSet id(String sql,String name){ try{ conn=ConnDB.getConnection(); pstmt=conn.prepareStatement(sql); pstmt.setString(1, name); rs=pstmt.executeQuery(); }catch (SQLException e) { e.printStackTrace(); } return rs; } public ResultSet id(String sql){ try{ conn=ConnDB.getConnection(); pstmt=conn.prepareStatement(sql); rs=pstmt.executeQuery(); }catch (SQLException e) { e.printStackTrace(); } return rs; } //删除lingshi表 public int delete3(String sql,String id,String n