BookDAO.java~36~
上传用户:toby828
上传日期:2015-06-26
资源大小:8558k
文件大小:5k
源码类别:

Jsp/Servlet

开发平台:

Java

  1. package com.dao;
  2. import com.core.ConnDB;
  3. import java.sql.*;
  4. import java.util.*;
  5. import com.actionForm.BookForm;
  6. public class BookDAO {
  7.     private ConnDB conn=new ConnDB();
  8. //查询数据
  9. public Collection query(String strif){
  10. BookForm bookForm=null;
  11. Collection bookColl=new ArrayList();
  12. String sql="";
  13. if(strif!="all" && strif!=null && strif!=""){
  14.     sql="select b.*,c.name as bookcaseName,p.pubname as publishing,t.typename from tb_bookinfo b left join tb_bookcase c on b.bookcase=c.id join tb_publishing p on b.ISBN=p.ISBN join tb_booktype t on b.booktype=t.id where "+strif+"";
  15. }else{
  16.     sql="select b.*,c.name as bookcaseName,p.pubname as publishing,t.typename from tb_bookinfo b left join tb_bookcase c on b.bookcase=c.id join tb_publishing p on b.ISBN=p.ISBN join tb_booktype t on b.typeid=t.id";
  17. }
  18. System.out.println("图书查询时的SQL:"+sql);
  19. ResultSet rs=conn.executeQuery(sql);
  20. try {
  21.     while (rs.next()) {
  22.         bookForm=new BookForm();
  23.         bookForm.setBarcode(rs.getString(1));
  24.         bookForm.setBookName(rs.getString(2));
  25.         bookForm.setTypeId(rs.getInt(3));
  26.         bookForm.setAuthor(rs.getString(4));
  27.         bookForm.setTranslator(rs.getString(5));
  28.         bookForm.setIsbn(rs.getString(6));
  29.         bookForm.setPrice(Float.valueOf(rs.getFloat(7)));  //此处必须进行类型转换
  30.         bookForm.setPage(rs.getInt(8));
  31.         bookForm.setBookcaseid(rs.getInt(9));
  32.         bookForm.setStorage(rs.getInt(10));
  33.         bookForm.setInTime(rs.getString(11));
  34.         bookForm.setOperator(rs.getString(12));
  35.         bookForm.setDel(rs.getInt(13));
  36.         bookForm.setId(Integer.valueOf(rs.getString(14)));
  37.         bookForm.setBookcaseName(rs.getString(15));
  38.         bookForm.setPublishing(rs.getString(16));
  39.         bookForm.setTypeName(rs.getString(17));
  40.         bookColl.add(bookForm);
  41.         System.out.print(bookForm.getTypeName());
  42.     }
  43. } catch (SQLException ex) {
  44. }
  45. conn.close();
  46. return bookColl;
  47. }
  48. //用于修改的查询
  49. public BookForm queryM(BookForm bookForm){
  50. BookForm bookForm1=null;
  51. String sql="select b.*,c.name as bookcaseName,p.pubname as publishing,t.typename from tb_bookinfo b left join tb_bookcase c on b.bookcase=c.id join tb_publishing p on b.ISBN=p.ISBN join tb_booktype t on b.typeid=t.id where b.id="+bookForm.getId()+"";
  52. System.out.println("修改时的SQL:"+sql);
  53. ResultSet rs=conn.executeQuery(sql);
  54. try {
  55.     while (rs.next()) {
  56.         bookForm1=new BookForm();
  57.         bookForm1.setBarcode(rs.getString(1));
  58.         bookForm1.setBookName(rs.getString(2));
  59.         bookForm1.setTypeId(rs.getInt(3));
  60.         bookForm1.setAuthor(rs.getString(4));
  61.         bookForm1.setTranslator(rs.getString(5));
  62.         bookForm1.setIsbn(rs.getString(6));
  63.         bookForm1.setPrice(Float.valueOf(rs.getFloat(7)));  //此处必须进行类型转换
  64.         bookForm1.setPage(rs.getInt(8));
  65.         bookForm1.setBookcaseid(rs.getInt(9));
  66.         bookForm1.setStorage(rs.getInt(10));
  67.         bookForm1.setInTime(rs.getString(11));
  68.         bookForm1.setOperator(rs.getString(12));
  69.         bookForm1.setDel(rs.getInt(13));
  70.         bookForm1.setId(Integer.valueOf(rs.getString(14)));
  71.         bookForm1.setBookcaseName(rs.getString(15));
  72.         bookForm1.setPublishing(rs.getString(16));
  73.         bookForm1.setTypeName(rs.getString(17));
  74.     }
  75. } catch (SQLException ex) {
  76. }
  77. conn.close();
  78. return bookForm1;
  79. }
  80. //添加数据
  81. public int insert(BookForm bookForm){
  82. String sql1="SELECT * FROM tb_bookinfo WHERE barcode='"+bookForm.getBarcode()+"' or bookname='"+bookForm.getBookName()+"'";
  83. ResultSet rs = conn.executeQuery(sql1);
  84. String sql = "";
  85. int falg = 0;
  86. try {
  87. if (rs.next()) {
  88.     falg = 2;
  89. } else {
  90.     sql ="Insert into tb_bookinfo (barcode,bookname,typeid,author,translator,isbn,price,page,bookcase,storage,inTime,operator) values('"+bookForm.getBarcode()+"','"+bookForm.getBookName()+"',"+bookForm.getTypeId()+",'"+bookForm.getAuthor()+"','"+bookForm.getTranslator()+"','"+bookForm.getIsbn()+"',"+bookForm.getPrice()+","+bookForm.getPage()+","+bookForm.getBookcaseid()+","+bookForm.getStorage()+",'"+bookForm.getInTime()+"','"+bookForm.getOperator()+"')";
  91.     falg = conn.executeUpdate(sql);
  92.     System.out.println("添加图书的SQL:" + sql);
  93.     conn.close();
  94. }
  95. } catch (SQLException ex) {
  96. falg = 0;
  97. }
  98. System.out.println("falg:"+falg);
  99. return falg;
  100. }
  101. //修改数据
  102. public int update(BookForm bookForm){
  103. String sql="Update tb_bookinfo set typeid="+bookForm.getTypeId()+",author='"+bookForm.getAuthor()+"',translator='"+bookForm.getTranslator()+"',isbn='"+bookForm.getIsbn()+"',price="+bookForm.getPrice()+",page="+bookForm.getPage()+",bookcase="+bookForm.getBookcaseid()+",storage="+bookForm.getStorage()+" where id="+bookForm.getId()+"";
  104. int falg=conn.executeUpdate(sql);
  105. System.out.println("修改数据时的SQL:"+sql);
  106. conn.close();
  107. return falg;
  108. }
  109. //删除数据
  110. public int delete(BookForm bookForm){
  111. String sql="UPDATE tb_bookinfo SET del=1 where id="+bookForm.getId()+"";
  112. int falg=conn.executeUpdate(sql);
  113. System.out.println("删除时的SQL:"+sql);
  114. return falg;
  115. }
  116. }