bookinfodao.java

网上书店 源代码和数据库网上书店 源代码和数据库

package com.shop.model.dao.addbook;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.shop.model.utils.DataBaseConn;
import com.shop.model.utils.PageVO;
import com.shop.vo.addbook.BookInfoVo;


public class BookInfoDAO {

	public List<BookInfoVo> findAllBookInfoVO() {
		Connection conn = null;
		Statement st = null;
		ResultSet rst=null;
		
		String select = "select isbn,BOOKNUM,author,sellprice,bookimage from BookInfo";
		List<BookInfoVo> booklist=new ArrayList<BookInfoVo>();
		
		DataBaseConn dbconn = new DataBaseConn();
		conn = dbconn.getConnection();
		try {
			st = conn.createStatement();
			rst=st.executeQuery(select);
			while(rst.next()){
				BookInfoVo book=new BookInfoVo();
				book.setIsbn(rst.getString("ISBN"));
				book.setAuthor(rst.getString("BOOKNUM"));
				book.setBookName(rst.getString("AUTHOR"));
				book.setSellPrice(rst.getFloat("SELLPRICE"));
				book.setBookImage(rst.getBytes("BOOKIMAGE"));
				booklist.add(book);
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			if(rst!=null){
				try {
					rst.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(st!=null){
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return booklist;
		
	}
	
    /*=========================================验证一种书是否存在于库中=============================================
	 *
	 *
	 *
	 */
	public boolean isExsistBook(String isbn){
		
	    String select = "select * from bookinfo where isbn='"+isbn+"'";	
	    Connection conn = null;
		Statement st = null;
		ResultSet rst=null;
		DataBaseConn dbconn = new DataBaseConn();
		conn = dbconn.getConnection();
		
		try {
			st=conn.createStatement();
			rst=st.executeQuery(select);
			if(rst.next()){
				return true;
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			if(rst!=null){
				try {
					rst.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(st!=null){
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return false;
	}
	//----------------------------------------------------------------------------------------------------------
	
	

    /*=======================================================得到单条记录==============================================
     * 参数:isbn
     * 类型:String  
	 * 返回:BookInfoVo
	 */
     public BookInfoVo getBookByIsbn(String isbn){
    	 String select="select isbn,BOOKNUM,author,printaddress,price,sellprice,inbooknumber,remainnumber,printdate,bookimage,bookdescrption,booktype from bookinfo where isbn='"+isbn+"'";
    	 BookInfoVo book=new BookInfoVo();
    	 Connection conn = null;
 		 Statement st = null;
 		 ResultSet rst=null;
 		 DataBaseConn dbconn = new DataBaseConn();
		 conn = dbconn.getConnection();
		 
		 try {
			 st=conn.createStatement();
			 rst=st.executeQuery(select);
			 if(rst.next()){
				 book.setIsbn(rst.getString("isbn"));
				 book.setBookName(rst.getString("BOOKNUM"));
				 book.setAuthor(rst.getString("author"));
				 book.setPrintAddr(rst.getString("printaddress"));
				 book.setPrice(rst.getFloat("price"));
				 book.setSellPrice(rst.getFloat("sellprice"));
				 book.setInBookNumber(rst.getString("inbooknumber"));
				 book.setRemainNumber(rst.getString("remainnumber"));
				 book.setPrintDate(rst.getDate("printdate"));
				 book.setBookImage(rst.getBytes("bookimage"));
				 book.setBookDescrption(rst.getString("bookdescrption"));
				 book.setBookType(rst.getString("booktype"));
				 return book;
			 }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				if(rst!=null){
					rst.close();
				}
				if(st!=null){
					st.close();
				}
				if(conn!=null){
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
    	 return book;
     }
   //------------------------------------------------------------------------------------------------------------------	
	
	
	
	
	
	
	
//-----------------------------------------------写分页dao------------------------------------------------------------------
	public List<BookInfoVo> findBookInfoVO(PageVO page) {
		Connection conn = null;
		Statement st = null;
		ResultSet rst=null;
		
		String select = "select isbn,remainNumber,BOOKNUM,author,sellprice,bookimage from BookInfo";
		List<BookInfoVo> booklist=new ArrayList<BookInfoVo>();
		Integer firstArg=0;
		Integer secondArg=0;
		if(page.getCurrentPageNo()<page.getMaxPageNo()){
			firstArg =page.getPerPageNumber();
			secondArg=page.getPerPageNumber()*page.getCurrentPageNo();
		}else{
			firstArg = page.getMaxRowNo() - page.getPerPageNumber()*(page.getCurrentPageNo()-1);
			secondArg =page.getMaxRowNo();
		}
		
		
		StringBuffer selectBuffer =new StringBuffer();
		selectBuffer.append("select isbn,remainNumber,BOOKNUM,author,sellprice,bookimage from (select top "+firstArg+" isbn,remainNumber,BOOKNUM,author,sellprice,bookimage from ")
		.append("(select top "+secondArg+" isbn,remainNumber,BOOKNUM,author,sellprice,bookimage from ( ") 
		.append(select)
		.append(") as t1 order by t1.isbn asc ")
		.append(" ) as t2 order by t2.isbn desc ")
		.append(" ) as t3 order by t3.isbn asc ");
		
		
		
		
		DataBaseConn dbconn = new DataBaseConn();
		conn = dbconn.getConnection();
		try {
			st = conn.createStatement();
			rst=st.executeQuery(selectBuffer.toString());
			while(rst.next()){
				BookInfoVo book=new BookInfoVo();
				book.setIsbn(rst.getString("ISBN"));
				book.setRemainNumber(rst.getString("REMAINNUMBER"));
				book.setBookName(rst.getString("BOOKNUM"));
				book.setAuthor(rst.getString("AUTHOR"));
				book.setSellPrice(rst.getFloat("SELLPRICE"));
				book.setBookImage(rst.getBytes("BOOKIMAGE"));
			
				booklist.add(book);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(rst!=null){
				try {
					rst.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(st!=null){
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return booklist;
		
	}
	
	
	
	public List<BookInfoVo> findAllUserInfoVO(String isbn) {
		return null;
	}
	
	
	public int getCount(){
		String select = "select count(*) from bookinfo";
		Connection conn = null;
		Statement st = null;
		ResultSet rst=null;
		DataBaseConn dbconn = new DataBaseConn();
		conn = dbconn.getConnection();
		try {
			st=conn.createStatement();
			rst=st.executeQuery(select);
			if(rst.next()){
				return rst.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(rst!=null){
				try {
					rst.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(st!=null){
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return 0;
	}
	
	
	
	
	
	
	
	
	public byte[] getBookImageById(String isbn){
		
		byte[] image;
		Connection conn = null;
		Statement st = null;
		ResultSet rst=null;
		
		String select = "select bookimage from BookInfo where isbn='"+isbn+"'";
		DataBaseConn dbconn = new DataBaseConn();
		conn = dbconn.getConnection();
			try {
				st = conn.createStatement();
				rst=st.executeQuery(select);
				if(rst.next()){
					image=rst.getBytes("bookimage");
					return image;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				if(rst!=null){
					try {
						rst.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if(st!=null){
					try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if(conn!=null){
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		
		
		
		return null;
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	//----------------------------------------插入一条信息----------------------------------------------------
	public void addBookInfo(BookInfoVo book){
		
		String insert="insert into BookInfo(isbn,BOOKNUM,author,printaddress,price,sellprice,printdate,inbooknumber,remainnumber,inbookdate,bookimage,bookdescrption,booktype) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
		//String insert="insert into BookInfo(isbn,BOOKNUM,author,printaddress,price,sellprice,inbooknumber,remainnumber,bookimage,bookdescrption,booktype) values(?,?,?,?,?,?,?,?,?,?,?)";
		
       
        
        
        Connection conn=null;
        PreparedStatement pst=null;
        DataBaseConn dbconn = new DataBaseConn();
		conn = dbconn.getConnection();
		
		
		try {
			pst=conn.prepareStatement(insert);
			pst.setString(1, book.getIsbn());
			pst.setString(2, book.getBookName());
			pst.setString(3, book.getAuthor());
			pst.setString(4, book.getPrintAddr());
			pst.setFloat(5, book.getPrice());
			pst.setFloat(6, book.getSellPrice());
			pst.setDate(7, new java.sql.Date(book.getPrintDate().getTime()));
			
			pst.setInt(8,Integer.parseInt(book.getInBookNumber()));
			pst.setInt(9, Integer.parseInt(book.getRemainNumber()));
			pst.setDate(10, new java.sql.Date(book.getInBookDate().getTime()));
			
			pst.setBytes(11, book.getBookImage());
			pst.setString(12, book.getBookDescrption());
			pst.setString(13, book.getBookType());
			pst.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(pst!=null){
				try {
					pst.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
        
	}
	//----------------------------------------********---------------------------------------------------
	
	
	
}