开发工具:
文件大小: 30kb
下载次数: 0
上传时间: 2015-11-12
详细说明： package com.wsy.dao; import java.sql.Connection; import java.sql.Date; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; import java.util.ArrayList; import java.util.List; import com.wsy.model.Back; import com.wsy.model.BookInfo; import com.wsy.model.BookType; import com.wsy.model.Borrow; import com.wsy.model.Operater; import com.wsy.model.Order; import com.wsy.model.OrderAndBookInfo; import com.wsy.model.Reader; import com.wsy.model.user; public class Dao { pr otected static String dbClassName = "com.mysql.jdbc.Driver"; protected static String dbUrl = "jdbc:mysql://localhost:3306/mysql"; protected static String dbUser = "root"; protected static String dbPwd = ""; protected static String second = null; private static Connection conn = null; private Dao() { try { if (conn == null) { Class.forName(dbClassName); conn = DriverManager.getConnection(dbUrl, dbUser, dbPwd); } else return; } catch (Exception ee) { ee.printStackTrace(); } } private static ResultSet executeQuery(String sql) { try { if(conn==null) new Dao(); return conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE).executeQuery(sql); } catch (SQLException e) { e.printStackTrace(); return null; } finally { } } private static int executeUpdate(String sql) { try { if(conn==null) new Dao(); return conn.createStatement().executeUpdate(sql); } catch (SQLException e) { System.out.println(e.getMessage()); //if(e.getMessage().equals("[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]DELETE 语句与 COLUMN REFERENCE 约束 'FK_TB_BORRO_REFERENCE_TB_BOOKI' 冲突。该冲突发生于数据库 'db_library'，表 'tb_borrow', column 'bookISBN'。")) return -1; } finally { } } public static void close() { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); }finally{ conn = null; } } /* * 管理员登录方法 */ public static Operater check(String name, String password) { int i = 0; Operater operater=new Operater(); String sql = "select * from tb_operator where name='" + name + "' and password='" + password + "'and admin=1"; ResultSet rs = Dao.executeQuery(sql); try { while (rs.next()) { String names = rs.getString(1); operater.setId(rs.getString("id")); operater.setName(rs.getString("name")); operater.setGrade(rs.getString("admin")); operater.setPassword(rs.getString("password")); if (names != null) { i = 1; } } } catch (Exception e) { e.printStackTrace(); } Dao.close(); return operater; } /* * 查询类别方法 */ public static List selectBookCategory() { List list=new ArrayList(); String sql = "select * from tb_bookType"; ResultSet rs = Dao.executeQuery(sql); try { while (rs.next()) { BookType bookType=new BookType(); bookType.setId(rs.getString("id")); bookType.setTypeName(rs.getString("typeName")); bookType.setDays(rs.getString("days")); bookType.setFk(rs.getString("fk")); list.add(bookType); } } catch (Exception e) { e.printStackTrace(); } Dao.close(); return list; } public static List selectBookCategory(String bookType) { List list=new ArrayList(); String sql = "select days from tb_bookType where typeName='"+bookType+"'"; ResultSet rs = Dao.executeQuery(sql); try { while (rs.next()) { BookType type=new BookType(); type.setDays(rs.getString("days")); list.add(type); } } catch (Exception e) { e.printStackTrace(); } Dao.close(); return list; } /* * 图书类别表相关操作 * */ public static int InsertBookType(String bookTypeName,String days,Double fk){ int i=0; try{ String sql="insert into tb_bookType(typeName,days,fk) values('"+bookTypeName+"','"+days+"',"+fk+")"; i=Dao.executeUpdate(sql); }catch(Exception e){ e.printStackTrace(); } return i; } public static int UpdatebookType(String id,String typeName,String days,String fk){ int i=0; try{ String sql="update tb_bookType set typeName='"+typeName+"',days='"+days+"',fk='"+fk+"' where id='"+id+"'"; //System.out.println(sql); i=Dao.executeUpdate(sql); }catch(Exception e){ e.printStackTrace(); } Dao.close(); return i; } // public static int DelbookType(String id){ // int i=0; // try{ // String sql="delete from tb_bookType where id='"+id+"'"; // //System.out.println(sql); // i=Dao.executeUpdate(sql); // }catch(Exception e){ // e.printStackTrace(); // } // Dao.close(); // return i; // } public static List selectBookTypeFk(String bookType) {//取每种书超过规定时间罚款金额 List list=new ArrayList(); String sql = "select * from tb_bookType where typeName='"+bookType+"'"; ResultSet rs = Dao.executeQuery(sql); try { while (rs.next()) { BookType type=new BookType(); type.setFk(rs.getString("fk")); type.setDays(rs.getString("days")); list.add(type); } } catch (Exception e) { e.printStackTrace(); } Dao.close(); return list; } /* * 图书信息表相关操作 */ /* * 插入图书信息方法 */ public static int Insertbook(String ISBN,String typeId,String bookname,String writer,String translator,String publisher,Date date,Double price){ int i=0; try{ String sql="insert into tb_bookInfo(ISBN,typeId,bookname,writer,translator,publisher,date,price) values('"+ISBN+"','"+typeId+"','"+bookname+"','"+writer+"','"+translator+"','"+publisher+"','"+date+"',"+price+")"; //System.out.println(sql); i=Dao.executeUpdate(sql); }catch(Exception e){ System.out.println(e.getMessage()); } Dao.close(); return i; } /* * 查询图书相关信息 * */ public static List selectBookInfo() { List list=new ArrayList(); String sql = "select * from tb_bookInfo"; ResultSet rs = Dao.executeQuery(sql); try { while (rs.next()) { BookInfo bookinfo=new BookInfo(); bookinfo.setISBN(rs.getString("ISBN")); bookinfo.setTypeid(rs.getString("typeid")); bookinfo.setBookname(rs.getString("bookname")); bookinfo.setWriter(rs.getString("writer")); bookinfo.setTranslator(rs.getString("translator")); bookinfo.setPublisher(rs.getString("publisher")); bookinfo.setDate(rs.getDate("date")); bookinfo.setPrice(rs.getDouble("price")); list.add(bookinfo); } } catch (Exception e) { e.printStackTrace(); } Dao.close(); return list; } public static List selectBookInfo(String ISBN) { List list=new ArrayList(); String sql = "select * from tb_bookInfo where ISBN='"+ISBN+"'"; ResultSet rs = Dao.executeQuery(sql); try { while (rs.next()) { BookInfo bookinfo=new BookInfo(); bookinfo.setISBN(rs.getString("ISBN")); bookinfo.setTypeid(rs.getString("typeid")); bookinfo.setBookname(rs.getString("bookname")); bookinfo.setWriter(rs.getString("writer")); bookinfo.setTranslator(rs.getString("translator")); bookinfo.setPublisher(rs.getString("publisher")); bookinfo.setDate(rs.getDate("date")); bookinfo.setPrice(rs.getDouble("price")); list.add(bookinfo); } } catch (Exception e) { e.printStackTrace(); } Dao.close(); return list; } /* * 修改图书信息方法 */ public static int Updatebook(String ISBN,String typeId,String bookname,String writer,String translator,String publisher,Date date,Double price){ int i=0; try{ String sql="update tb_bookInfo set ISBN='"+ISBN+"',typeId='"+typeId+"',bookname='"+bookname+"',writer='"+writer+"',translator='"+translator+"',publisher='"+publisher+"',date='"+date+"',price="+price+" where ISBN='"+ISBN+"'"; //System.out.println(sql); i=Dao.executeUpdate(sql); }catch(Exception e){ e.printStackTrace(); } Dao.close(); return i; } // /* // * 删除图书信息方法 // */ // public static int Delbook(String ISBN){ // int i=0; // try{ // String sql="delete from tb_bookInfo where ISBN='"+ISBN+"'"; // //System.out.println(sql); // i=Dao.executeUpdate(sql); // }catch(Exception e){ // e.printStackTrace(); // // } // Dao.close(); // return i; // } /* * 对读者信息表执行的相关操作 */ public static int InsertReader(String name,String sex,String age,String identityCard,Date date,String maxNum,String tel,Double keepMoney,String zj,String zy,Date bztime,String ISBN){ int i=0; try{ String sql="insert into tb_reader(name,sex,age,identityCard,date,maxNum,tel,keepMoney,zj,zy,bztime,ISBN) values('"+name+"','"+sex+"','"+age+"','"+identityCard+"','"+date+"','"+maxNum+"','"+tel+"',"+keepMoney+",'"+zj+"','"+zy+"','"+bztime+"','"+ISBN+"')"; System.out.println(sql); i=Dao.executeUpdate(sql); }catch(Exception e){ e.printStackTrace(); } Dao.close(); return i; } public static List selectReader() { List list=new ArrayList(); String sql = "select * from tb_reader"; ResultSet rs = Dao.executeQuery(sql); try { while (rs.next()) { Reader reader=new Reader(); //reader.setId(rs.getString("id")); reader.setName(rs.getString("name")); reader.setSex(rs.getString("sex")); reader.setAge(rs.getString("age")); reader.setIdentityCard(rs.getString("identityCard")); reader.setDate(rs.getDate("date")); reader.setMaxNum(rs.getString("maxNum")); reader.setTel(rs.getString("tel")); reader.setKeepMoney(rs.getDouble("keepMoney")); reader.setZj(rs.getInt("zj")); reader.setZy(rs.getString("zy")); reader.setISBN(rs.getString("ISBN")); reader.setBztime(rs.getDate("bztime")); list.add(reader); } } catch (Exception e) { e.printStackTrace(); } Dao.close(); return list; } public static List selectReader(String readerISBN) { List list=new ArrayList(); String sql = "select * from tb_reader where ISBN='"+readerISBN+"'"; ResultSet rs = Dao.executeQuery(sql); try { while (rs.next()) { Reader reader=new Reader(); reader.setName(rs.getString("name")); reader.setSex(rs.getString("sex")); reader.setAge(rs.getString("age")); reader.setIdentityCard(rs.getString("identityCard")); reader.setDate(rs.getDate("date")); reader.setMaxNum(rs.getString("maxNum")); reader.setTel(rs.getString("tel")); reader.setKeepMoney(rs.getDouble("keepMoney")); reader.setZj(rs.getInt("zj")); reader.setZy(rs.getString("zy")); reader.setISBN(rs.getString("ISBN")); reader.setBztime(rs.getDate("bztime")); list.add(reader); } } catch (Exception e) { e.printStackTrace(); } Dao.close(); return list; } public static int UpdateReader(String id,String name,String sex,String age,String identityCard,Date date,String maxNum,String tel,Double keepMoney,String zj,String zy,Date bztime,String ISBN){ int i=0; try{ String sql="update tb_reader set name='"+name+"',sex='"+sex+"',age='"+age+"',identityCard='"+identityCard+"',date='"+date+"',maxNum='"+maxNum+"',tel='"+tel+"',keepMoney="+keepMoney+",zj='"+zj+"',zy='"+zy+"',bztime='"+bztime+"'where ISBN='"+ISBN+"'"; i=Dao.executeUpdate(sql); }catch(Exception e){ e.printStackTrace(); } Dao.close(); return i; } public static int DelReader(String ISBN){ int i=0; try{ String sql="delete from tb_reader where ISBN='"+ISBN+"'"; //System.out.println(sql); i=Dao.executeUpdate(sql); }catch(Exception e){ e.printStackTrace(); } Dao.close(); return i; } /* * 对订购信息表操作 */ public static int InsertBookOrder(String ISBN,Date date,String number,String operator,String checkAndAccept,Double zk){ int i=0; try{ String sql="insert into tb_order(ISBN,date,number,operator,checkAndAccept,zk) values('"+ISBN+"','"+date+"','"+number+"','"+operator+"',"+checkAndAccept+",'"+zk+"')"; i=Dao.executeUpdate(sql); }catch(Exception e){ e.printStackTrace(); } Dao.close(); return i; } public static List selectBookOrder() { List list=new ArrayList(); String sql = "SELECT * FROM tb_order a INNER JOIN tb_bookInfo b ON a.ISBN = b.ISBN"; ResultSet rs = Dao.executeQuery(sql); try { while (rs.next()) { OrderAndBookInfo order=new OrderAndBookInfo(); order.setISBN(rs.getString(1)); order.setOrderdate(rs.getDate(2)); order.setNumber(rs.getString(3)); order.setOperator(rs.getString(4)); order.setCheckAndAccept(rs.getString(5)); order.setZk(rs.getDouble(6)); order.setTypeId(rs.getString(8)); order.setBookname(rs.getString(9)); order.setWriter(rs.getString(10)); order.setTraslator(rs.getString(11)); order.setPublisher(rs.getString(12)); order.setDate(rs.getDate(13)); order.setPrice(rs.getDouble(14)); list.add(order); } } catch (Exception e) { e.printStackTrace(); } Dao.close(); return list; } public static List selectBookOrder(String ISBN) { List list=new ArrayList(); String sql = "SELECT * FROM tb_order where ISBN='"+ISBN+"'"; ResultSet rs = Dao.executeQuery(sql); try { while (rs.next()) { Order order=new Order(); order.setISBN(rs.getString("ISBN")); order.setDate(rs.getDate("date")); order.setNumber(rs.getString("number")); order.setOperator(rs.getString("operator")); order.setZk("zk"); order.setCheckAndAccept("checkAndAccept"); list.add(order); } } catch (Exception e) { e.printStackTrace(); } Dao.close(); return list; } public static int UpdateCheckBookOrder(String ISBN){ int i=0; try{ String sql="update tb_order set checkAndAccept=0 where ISBN='"+ISBN+"'"; i=Dao.executeUpdate(sql); }catch(Exception e){ e.printStackTrace(); } Dao.close(); return i; } /* * 对借阅表进行操作 */ public static int InsertBookBorrow(String bookISBN,String readerISBN,String operatorId,Timestamp borrowDate,Timestamp backDate){ int i=0; try{ String sql="insert into tb_borrow(bookISBN,readerISBN,operatorId,borrowDate,backDate)values('"+bookISBN+"','"+readerISBN+"','"+operatorId+"','"+borrowDate+"','"+backDate+"')"; i=Dao.executeUpdate(sql); }catch(Exception e){ e.printStackTrace(); } Dao.close(); return i; } public static List selectBorrow(String readerISBN) { List list=new ArrayList(); String sql = "select * from tb_borrow where readerISBN='"+readerISBN+"'"; ResultSet rs = Dao.executeQuery(sql); try { while (rs.next()) { Borrow borrow=new Borrow(); borrow.setId(rs.getInt("id")); borrow.setBookISBN(rs.getString("bookISBN")); borrow.setReaderISBN(rs.getString("readerISBN")); borrow.setBorrowDate(rs.getString("borrowDate")); borrow.setBackDate(rs.getString("backDate")); borrow.setBookName(rs.getString("borrowBookName")); list.add(borrow); } } catch (Exception e) { e.printStackTrace(); } Dao.close(); return list; } /* * 查询还书内容，tb_bookinfo tb_reader tb_borrow之间的查询 */ public static List selectBookBack(String readerISBN) { List list=new ArrayList(); String sql = "SELECT a.ISBN AS bookISBN, a.bookname, a.typeId ,b.id,b.operatorId, b.borrowDate, b.backDate, c.name AS readerName, c.ISBN AS readerISBN FROM tb_bookInfo a INNER JOIN tb_borrow b ON a.ISBN = b.bookISBN INNER JOIN tb_reader c ON b.readerISBN = c.ISBN WHERE (c.ISBN = '"+readerISBN+"' and isback=1)"; System.out.println(sql); ResultSet rs = Dao.executeQuery(sql); try { while (rs.next()) { Back back=new Back(); back.setBookISBN(rs.getString("bookISBN")); back.setBookname(rs.getString("bookname")); back.setTypeId(rs.getInt("typeId")); back.setOperatorId(rs.getString("operatorId")); back.setBorrowDate(rs.getString("borrowDate")); back.setBackDate(rs.getString("backDate")); back.setReaderName(rs.getString("readerName")); back.setReaderISBN(rs.getString("readerISBN")); back.setId(rs.getInt("id")); list.add(back); } } catch (Exception e) { e.printStackTrace(); } Dao.close(); return list; } public static int UpdateBookBack(String bookISBN,String readerISBN,int id){//归还图书操作 int i=0; try{ String sql="update tb_borrow set isback=0 where bookISBN='"+bookISBN+"'and readerISBN='"+readerISBN+"' and id="+id+""; System.out.println(sql); i=Dao.executeUpdate(sql); }catch(Exception e){ e.printStackTrace(); } Dao.close(); return i; } //new public static List selectbookserch() { List list=new ArrayList(); String sql = "select * from tb_bookInfo"; ResultSet s = Dao.executeQuery(sql); try { while (s.next()) { BookInfo bookinfo=new BookInfo(); bookinfo.setISBN(s.getString(1)); bookinfo.setTypeid(s.getString(2)); bookinfo.setBookname(s.getString(3)); bookinfo.setWriter(s.getString(4)); bookinfo.setTranslator(s.getString(5)); bookinfo.setPublisher(s.getString(6)); bookinfo.setDate(s.getDate(7)); bookinfo.setPrice(s.getDouble(8)); list.add(bookinfo); } } catch (Exception e) { e.printStackTrace(); } Dao.close(); return list; } public static List selectbookmohu(String bookname){ List list=new ArrayList(); String sql="select * from tb_bookInfo where bookname like '%"+bookname+"%'"; System.out.print(sql); ResultSet s=Dao.executeQuery(sql); try { while(s.next()){ BookInfo bookinfo=new BookInfo(); bookinfo.setISBN(s.getString(1)); bookinfo.setTypeid(s.getString(2)); bookinfo.setBookname(s.getString(3)); bookinfo.setWriter(s.getString(4)); bookinfo.setTranslator(s.getString(5)); bookinfo.setPublisher(s.getString(6)); bookinfo.setDate(s.getDate(7)); bookinfo.setPrice(s.getDouble(8)); list.add(bookinfo); } } catch (SQLException e) { // TODO 自动生成 catch 块 e.printStackTrace(); } return list; } public static List selectbookmohuwriter(String writer){ List list=new ArrayList(); String sql="select * from tb_bookInfo where writer like '%"+writer+"%'"; System.out.print(sql); ResultSet s=Dao.executeQuery(sql); try { while(s.next()){ BookInfo bookinfo=new BookInfo(); bookinfo.setISBN(s.getString(1)); bookinfo.setTypeid(s.getString(2)); bookinfo.setBookname(s.getString(3)); bookinfo.setWriter(s.getString(4)); bookinfo.setTranslator(s.getString(5)); bookinfo.setPublisher(s.getString(6)); bookinfo.setDate(s.getDate(7)); bookinfo.setPrice(s.getDouble(8)); list.add(bookinfo); } } catch (SQLException e) { // TODO 自动生成 catch 块 e.printStackTrace(); } return list; } public static int Insertoperator(String name,String sex,int age,String identityCard,Date workdate,String tel,String password){ int i=0; try{ String sql="insert into tb_operator(name,sex,age,identityCard,workdate,tel,password) values('"+name+"','"+sex+"',"+age+",'"+identityCard+"','"+workdate+"','"+tel+"','"+password+"')"; System.out.println(sql); i=Dao.executeUpdate(sql); }catch(Exception e){ e.printStackTrace(); } Dao.close(); return i; } public static List selectuser() { List list=new ArrayList(); String sql = "select id,name,sex,age,identityCard,workdate,tel,password from tb_operator where admin=0"; ResultSet rs = Dao.executeQuery(sql); try { while (rs.next()) { user user=new user(); user.setId(rs.getInt(1)); user.setName(rs.getString(2)); user.setSex(rs.getString(3)); user.setAge(rs.getInt(4)); user.setIdentityCard(rs.getString(5)); user.setWorkdate(rs.getDate(6)); user.setTel(rs.getString(7)); user.setPassword(rs.getString(8)); list.add(user); } } catch (Exception e) { e.printStackTrace(); } Dao.close(); return list; } public static int Deluser(int id){ int i=0; try{ String sql="delete from tb_operator where id='"+id+"'"; //System.out.println(sql); i=Dao.executeUpdate(sql); }catch(Exception e){ e.printStackTrace(); } Dao.close(); return i; } public static int Updateuser(int id,String name,String sex,int age,String identityCard,Date workdate,String tel,String password){ int i=0; try{ String sql="update tb_operator set name='"+name+"',sex='"+sex+"',age="+age+",identityCard='"+identityCard+"',workdate='"+workdate+"',tel='"+tel+"',password='"+password+"' where id='"+id+"'"; i=Dao.executeUpdate(sql); }catch(Exception e){ e.printStackTrace(); } Dao.close(); return i; } public static int Updatepass(String password,String name){ int i=0; try{ String sql="update tb_operator set password='"+password+"' where name='"+name+"'"; i=Dao.executeUpdate(sql); }catch(Exception e){ e.printStackTrace(); } Dao.close(); return i; } } ...展开收缩
(系统自动生成,下载前可以参看下载内容)
下载文件列表
相关说明
- 本站资源为会员上传分享交流与学习,如有侵犯您的权益,请联系我们删除.
- 本站是交换下载平台，提供交流渠道，下载内容来自于网络，除下载问题外，其它问题请自行百度。
- 本站已设置防盗链，请勿用迅雷、QQ旋风等多线程下载软件下载资源，下载后用WinRAR最新版进行解压.
- 如果您发现内容无法下载，请稍后再次尝试；或者到消费记录里找到下载记录反馈给我们.
- 下载后发现下载的内容跟说明不相乎，请到消费记录里找到下载记录反馈给我们，经确认后退回积分.
- 如下载前有疑问，可以通过点击"提供者"的名字，查看对方的联系方式，联系对方咨询.
