borrowcasedaoimpl.java

使用jdbc作的一个图书馆管理系统

package lib_jdbc.daoimpl;


import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.List;

import lib_jdbc.JDBCConnection;
import lib_jdbc.dao.BorrowCaseDAO;
import lib_jdbc.vo.BorrowCaseVO;

public class BorrowCaseDAOImpl implements BorrowCaseDAO {

	@Override
	public boolean delete(String id) {
		
		String sql = "DELETE FROM borrwcase WHERE caseno=?" ;
		PreparedStatement pstmt = null ;
		JDBCConnection jdbc = null ;
		
		try {
			jdbc = new JDBCConnection() ;
			pstmt = jdbc.getConnection().prepareStatement(sql) ;			
			pstmt.setString(1,id) ;
			// 进行数据库更新操作
			pstmt.executeUpdate() ;
			pstmt.close() ;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			jdbc.close();
		}
		return false;
	}



	@Override
	public List<BorrowCaseVO> findAll() {
		List<BorrowCaseVO> all = new ArrayList<BorrowCaseVO>();
		String sql = "SELECT * FROM BorrowCase" ;
		PreparedStatement pstmt = null ;
		JDBCConnection dbc = null ;
		
		try {
			dbc = new JDBCConnection() ;
			pstmt = dbc.getConnection().prepareStatement(sql) ;			
			// 进行数据库查询操作
			ResultSet rs = pstmt.executeQuery() ;
			while(rs.next())
			{
				// 查询出内容，之后将查询出的内容赋值给person对象
				BorrowCaseVO bc = new BorrowCaseVO() ;
				bc.setCaseNo(rs.getString(1));
				bc.setBookID(rs.getString(2));
				bc.setReaderNo(rs.getString(3));
				bc.setBorrowDate(rs.getDate(4));
				bc.setReturnDate(rs.getDate(5));
				bc.setRenew(rs.getInt(6));
				bc.setFine(rs.getDouble(7));
				bc.setReturnedFine(rs.getInt(8));

				// 将查询出来的数据加入到List对象之中
				all.add(bc) ;
			}
			rs.close() ;
			pstmt.close() ;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		
		}finally{
			dbc.close();
		}
		
		return all;

	}

	@Override
	public List<BorrowCaseVO> findByBIDAndRID(String bid, String rid) {
		List<BorrowCaseVO> all = new ArrayList<BorrowCaseVO>();
		String sql = "SELECT * FROM BorrowCase WHERE BOOKID = ? and readerno = ?" ;
		PreparedStatement pstmt = null ;
		JDBCConnection dbc = null ;
		
		
		try {
			dbc = new JDBCConnection() ;
			pstmt = dbc.getConnection().prepareStatement(sql) ;		
			pstmt.setString(1, bid);
			pstmt.setString(2, rid);
			// 进行数据库查询操作
			ResultSet rs = pstmt.executeQuery() ;
			
			while(rs.next())
			{
				// 查询出内容，之后将查询出的内容赋值给person对象
				BorrowCaseVO bc = new BorrowCaseVO() ;
				bc.setCaseNo(rs.getString(1));
				bc.setBookID(rs.getString(2));
				bc.setReaderNo(rs.getString(3));
				bc.setBorrowDate(rs.getDate(4));
				bc.setReturnDate(rs.getDate(5));
				bc.setRenew(rs.getInt(6));
				bc.setFine(rs.getDouble(7));
				bc.setReturnedFine(rs.getInt(8));

				// 将查询出来的数据加入到List对象之中
				all.add(bc) ;
			}
			rs.close() ;
			pstmt.close() ;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		
		}finally{
			dbc.close();
		}
		
		return all;
	}

	@Override
	public List<BorrowCaseVO> findByBooksID(String bid) {
		List<BorrowCaseVO> all = new ArrayList<BorrowCaseVO>();
		String sql = "SELECT * FROM BorrowCase WHERE BOOKID = ? " ;
		PreparedStatement pstmt = null ;
		JDBCConnection dbc = null ;
		
		
		try {
			dbc = new JDBCConnection() ;
			pstmt = dbc.getConnection().prepareStatement(sql) ;		
			pstmt.setString(1, bid);
			// 进行数据库查询操作
			ResultSet rs = pstmt.executeQuery() ;
			
			while(rs.next())
			{
				// 查询出内容，之后将查询出的内容赋值给person对象
				BorrowCaseVO bc = new BorrowCaseVO() ;
				bc.setCaseNo(rs.getString(1));
				bc.setBookID(rs.getString(2));
				bc.setReaderNo(rs.getString(3));
				bc.setBorrowDate(rs.getDate(4));
				bc.setReturnDate(rs.getDate(5));
				bc.setRenew(rs.getInt(6));
				bc.setFine(rs.getDouble(7));
				bc.setReturnedFine(rs.getInt(8));

				// 将查询出来的数据加入到List对象之中
				all.add(bc) ;
			}
			rs.close() ;
			pstmt.close() ;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		
		}finally{
			dbc.close();
		}
		
		return all;
	}

	@Override
	public List<BorrowCaseVO> findByFined() {
		List<BorrowCaseVO> all = new ArrayList<BorrowCaseVO>();
		String sql = "SELECT * FROM BorrowCase WHERE fine>0" ;
		PreparedStatement pstmt = null ;
		JDBCConnection dbc = null ;
		
		
		try {
			dbc = new JDBCConnection() ;
			pstmt = dbc.getConnection().prepareStatement(sql) ;		
	
			// 进行数据库查询操作
			ResultSet rs = pstmt.executeQuery() ;
			
			while(rs.next())
			{
				// 查询出内容，之后将查询出的内容赋值给person对象
				BorrowCaseVO bc = new BorrowCaseVO() ;
				bc.setCaseNo(rs.getString(1));
				bc.setBookID(rs.getString(2));
				bc.setReaderNo(rs.getString(3));
				bc.setBorrowDate(rs.getDate(4));
				bc.setReturnDate(rs.getDate(5));
				bc.setRenew(rs.getInt(6));
				bc.setFine(rs.getDouble(7));
				bc.setReturnedFine(rs.getInt(8));

				// 将查询出来的数据加入到List对象之中
				all.add(bc) ;
			}
			rs.close() ;
			pstmt.close() ;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		
		}finally{
			dbc.close();
		}
		
		return all;
	}

	@Override
	public BorrowCaseVO findByID(String id) {
		BorrowCaseVO bc = null ;
		String sql = "SELECT * FROM borrowcase WHERE caseno=?" ;
		PreparedStatement pstmt = null ;
		JDBCConnection dbc = null ;
		
		try {
			dbc = new JDBCConnection() ;
			pstmt = dbc.getConnection().prepareStatement(sql) ;			
			pstmt.setString(1,id) ;
			// 进行数据库查询操作
			ResultSet rs = pstmt.executeQuery() ;
			if(rs.next())
			{
				// 查询出内容，之后将查询出的内容赋值给person对象
				bc = new BorrowCaseVO() ;
				bc.setCaseNo(rs.getString(1));
				bc.setBookID(rs.getString(2));
				bc.setReaderNo(rs.getString(3));
				bc.setBorrowDate(rs.getDate(4));
				bc.setReturnDate(rs.getDate(5));
				bc.setRenew(rs.getInt(6));
				bc.setFine(rs.getDouble(7));
				bc.setReturnedFine(rs.getInt(8));
			}
			rs.close() ;
			pstmt.close() ;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		
		}finally{
			dbc.close();
		}

		return bc;
	}

	@Override
	public List<BorrowCaseVO> findByReaderID(String rid) {
		List<BorrowCaseVO> all = new ArrayList<BorrowCaseVO>();
		String sql = "SELECT * FROM BorrowCase WHERE readerno = ?" ;
		PreparedStatement pstmt = null ;
		JDBCConnection dbc = null ;
		
		
		try {
			dbc = new JDBCConnection() ;
			pstmt = dbc.getConnection().prepareStatement(sql) ;		
			pstmt.setString(1, rid);
			// 进行数据库查询操作
			ResultSet rs = pstmt.executeQuery() ;
			
			while(rs.next())
			{
				// 查询出内容，之后将查询出的内容赋值给person对象
				BorrowCaseVO bc = new BorrowCaseVO() ;
				bc.setCaseNo(rs.getString(1));
				bc.setBookID(rs.getString(2));
				bc.setReaderNo(rs.getString(3));
				bc.setBorrowDate(rs.getDate(4));
				bc.setReturnDate(rs.getDate(5));
				bc.setRenew(rs.getInt(6));
				bc.setFine(rs.getDouble(7));
				bc.setReturnedFine(rs.getInt(8));

				// 将查询出来的数据加入到List对象之中
				all.add(bc) ;
			}
			rs.close() ;
			pstmt.close() ;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
	
		}finally{
			dbc.close();
		}
		
		return all;
		
	}

	@Override
	public boolean insert(BorrowCaseVO BorrowCase) {
		String sql = "INSERT INTO BorrowCase VALUES (?,?,?,?,?,?,?,?)" ;
		PreparedStatement pstmt = null ;
		JDBCConnection dbc = null ;
		boolean flag =false;
		try {
			dbc = new JDBCConnection() ;
			pstmt = dbc.getConnection().prepareStatement(sql) ;	
			pstmt.setString(1, BorrowCase.getCaseNo());
			pstmt.setString(2,BorrowCase.getBookID()) ;
			pstmt.setString(3,BorrowCase.getReaderNo()) ;
			pstmt.setDate(4,(Date) BorrowCase.getBorrowDate()) ;
			pstmt.setDate(5,(Date) BorrowCase.getReturnDate()) ;
			pstmt.setInt(6, BorrowCase.getRenew());
			pstmt.setDouble(7, BorrowCase.getFine());
			pstmt.setInt(8, BorrowCase.getReturnedFine());
			
			// 进行数据库更新操作
			int result = pstmt.executeUpdate() ;
			if(result>0)
				flag = true;
			pstmt.close() ;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			dbc.close();
		}
		return flag;

	}

	@Override
	public boolean updata(BorrowCaseVO BorrowCase) {
		String sql = "UPDATE borrowcase SET bookid=?,readerno=?,borrowdate=?," +
				"returndate=?,renew=?,fine=?,returnedfine =? WHERE caseno=?" ;
		PreparedStatement pstmt = null;
		JDBCConnection dbc = null;
		boolean flag = false;
		try {
			dbc = new JDBCConnection();
			pstmt = dbc.getConnection().prepareStatement(sql);
			pstmt.setString(1,BorrowCase.getBookID()) ;
			pstmt.setString(2,BorrowCase.getReaderNo()) ;
			pstmt.setDate(3,(Date) BorrowCase.getBorrowDate()) ;
			pstmt.setDate(4,(Date) BorrowCase.getReturnDate()) ;
			pstmt.setInt(5, BorrowCase.getRenew());
			pstmt.setDouble(6, BorrowCase.getFine());
			pstmt.setInt(7, BorrowCase.getReturnedFine());
			pstmt.setString(8, BorrowCase.getCaseNo());
			// 进行数据库更新操作
			int result = pstmt.executeUpdate();
			if (result > 0)
				flag = true;
			pstmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			dbc.close();
		}
		return flag;

	}

	@Override
	public boolean renew(String id) {
		String sql = "UPDATE borrowcase SET renew = 1 WHERE caseno=?";
		PreparedStatement pstmt = null;
		JDBCConnection dbc = null;
		boolean flag = false;
		
		try {
			dbc = new JDBCConnection();
			pstmt = dbc.getConnection().prepareStatement(sql);
			pstmt.setString(1, id);
			// 进行数据库更新操作
			int result = pstmt.executeUpdate();
			if (result > 0)
				flag = true;
			pstmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			dbc.close();
		}
		return flag;

	}

}