bookdao.java

图书管理系统

/**
 * 图书的操作类
 */
package fuguo.yy1.dao;

/**
 * @author cankongyun
 *
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import fuguo.yy1.base.BaseDAO;
import fuguo.yy1.db.DBConn;
import fuguo.yy1.dto.Book;
import fuguo.yy1.util.SQLConstants;

public class BookDAO implements BaseDAO {

	private static Connection con=DBConn.getConnection();
	private static PreparedStatement pstmt=null;
	private static ResultSet rs=null;
	
	
	/**
	 * 显示所有图书信息
	 * @return ArrayList
	 */
	public static ArrayList books(){
		ArrayList al=new ArrayList();
		Book book=null;
		try{
			pstmt=con.prepareStatement(SQLConstants.SELECT_BOOKS);
			rs=pstmt.executeQuery();
			while(rs.next()){
				book=new Book();
				book.setBookId(rs.getInt("bookId"));
				book.setBookName(rs.getString("bookName"));
				book.setBookCategoryId(rs.getInt("bookCategoryId"));
				book.setPrice(rs.getFloat("price"));
				book.setAmount(rs.getInt("amount"));
				//将book对象加入al容器中
				al.add(book);	
			}
			DBConn.releaseConnection(rs,pstmt,null);
		}catch(SQLException se){
			System.err.println("显示所有图书失败!出现异常:"+se.getMessage());
		}
		
		return al;
	}
	
    /**
     * 检测图书是否存在方法
     * @param _categoryName
     * @return boolean
     */
    public static boolean checkBookName(String _bookName){
        boolean exist = false;
        try
        {
            pstmt = con.prepareStatement(SQLConstants.SELECT_CHECK_BOOKNAME);
            pstmt.setString(1, _bookName);
            rs = pstmt.executeQuery();
            exist = rs.next();
            DBConn.releaseConnection(rs,pstmt,null);
        }catch(SQLException se){
            System.err.println("检测图书是否存在失败！出现异常："+se.getMessage());
        }
        return exist;
    }
    
    /**
     * 添加图书信息
     * @param _baseDTO
     * @return int
     */
    public static int insertBook(Book _book){
    	int result=0;
       	
        try{
   
            pstmt = con.prepareStatement(SQLConstants.INSERT_BOOK);
            pstmt.setString(1, _book.getBookName());
            pstmt.setInt(2,_book.getBookCategoryId());
            pstmt.setFloat(3,_book.getPrice());
            pstmt.setInt(4,_book.getAmount());
            
            result = pstmt.executeUpdate();
            
            DBConn.releaseConnection(null,pstmt,null);
       }catch(SQLException se)            {
            System.out.println("添加图书信息失败！出现异常："+se.getMessage());
       }
    	return result;
    }
    
    
    /**
     * 根据图书Id查询图书信息
     * @param _bookId
     * @return Book
     */
    public static Book selectBookById(int _bookId){
    	Book book=null;
    	
    	try{
    		pstmt=con.prepareStatement(SQLConstants.SELECT_BOOK_ID);
    		pstmt.setInt(1,_bookId);
    		rs=pstmt.executeQuery();    		
    		
    		while(rs.next()){
    			book=new Book();
    			book.setBookId(rs.getInt("bookId"));
    			book.setBookName(rs.getString("bookName"));
    			book.setBookCategoryId(rs.getInt("bookCategoryId"));
    			book.setPrice(rs.getFloat("price"));
    			book.setAmount(rs.getInt("amount"));
    			
    			return book;   			
    		}
    		DBConn.releaseConnection(rs,pstmt,null);
    		
    	}catch(SQLException se){
    		System.err.println("根据Id查询图书信息失败！出现异常："+se.getMessage());
    	}
    	
    	return null;
    }
    
    
    /**
     * 关键字查询图书信息
     * @param _book
     * @return ArrayList
     */
    public static ArrayList searchBook(Book _book){
    	ArrayList al=new ArrayList();

    	String keyWord = _book.getBookName();
		int bookCategoryId = _book.getBookCategoryId();
		
		try{
			String SELECT_BOOK_BOOKCATEGORY_ALL=SQLConstants.SELECT_BOOK+keyWord+"%' and bc.bookCategoryId=b.bookCategoryId";    //全部类别的SQL语句
			String SELECT_BOOK_BOOKCATEGORYID=SQLConstants.SELECT_BOOK+keyWord+"%' and bc.bookCategoryId="+
					bookCategoryId+" and bc.bookCategoryId=b.bookCategoryId";        //某图书类别的SQL语句

			if(bookCategoryId>0){
				pstmt=con.prepareStatement(SELECT_BOOK_BOOKCATEGORYID);
			}else{
				pstmt=con.prepareStatement(SELECT_BOOK_BOOKCATEGORY_ALL);
			}
			
			rs=pstmt.executeQuery();
			while(rs.next()){
				Book book=new Book();
				book.setBookId(rs.getInt("bookId"));
				book.setBookName(rs.getString("bookName"));
				book.setBookCategoryId(rs.getInt("bookCategoryId"));
				book.setPrice(rs.getFloat("price"));
				book.setAmount(rs.getInt("amount"));
				
				al.add(book);
			}
			
			
		}catch(SQLException se){
			System.err.println("关键字查询图书信息失败！出现异常："+se.getMessage());
		}
		
		
    	return al;
    }
    
    
    /**
     * 修改图书信息
     * @param _bookId
     * @param _baseDTO
     * @return int
     */
    public static int updateBook(int _bookId,Book _book){
    	int result=0;
        try
        {
            pstmt = con.prepareStatement(SQLConstants.UPDATE_BOOK);
            pstmt.setString(1, _book.getBookName());
            pstmt.setInt(2,_book.getBookCategoryId());
            pstmt.setFloat(3,_book.getPrice());
            pstmt.setInt(4,_book.getAmount());
            pstmt.setInt(5,_bookId);
            result = pstmt.executeUpdate();
           
            DBConn.releaseConnection(null,pstmt,null);
        }
        catch(SQLException se){
            System.err.println("修改图书类别信息失败！出现异常："+se.getMessage());
        }
    	return result;
    }
    
    /**
     * 删除图书信息
     * @param _bookId
     * @return int
     */
    public static int deleteBook(int _bookId){
    	int result=0;
    	
       	try{
    		pstmt=con.prepareStatement(SQLConstants.DELETE_BOOK);
    		pstmt.setInt(1,_bookId);
    		result=pstmt.executeUpdate();
    		
    		DBConn.releaseConnection(null,pstmt,null);
    	}catch(SQLException se){
    		
    		System.err.println("删除图书信息失败，出现异常："+se.getMessage());
    	}
    	
    	return result;
    }
}