readerdaoimpl.java

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

package lib_jdbc.daoimpl;

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.ReaderDAO;
import lib_jdbc.vo.ReaderVO;

public class ReaderDAOImpl implements ReaderDAO {

	

	@Override
	public boolean delete(String id) {
		String sql = "DELETE FROM reader WHERE readerno=?" ;
		PreparedStatement pstmt = null ;
		JDBCConnection jdbc = null ;
		boolean flag = false;
		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 flag;

	}

	@Override
	public List<ReaderVO> findAll() {
		List<ReaderVO> all = new ArrayList<ReaderVO>();
		String sql = "SELECT * FROM reader" ;
		PreparedStatement pstmt = null ;
		JDBCConnection dbc = null ;
		
		try {
			dbc = new JDBCConnection() ;
			pstmt = dbc.getConnection().prepareStatement(sql) ;			
			// 进行数据库查询操作
			ResultSet rs = pstmt.executeQuery() ;
			while(rs.next())
			{
				// 查询出内容，之后将查询出的内容赋值给person对象
				ReaderVO reader = new ReaderVO() ;
				
				reader.setReaderNo(rs.getString(1));
				reader.setName(rs.getString(2));
				reader.setPassword(rs.getString(3));
				reader.setDepartment(rs.getString(4));
				reader.setFreeNum(rs.getInt(5));
				reader.setBorrowedNum(rs.getInt(6));
				reader.setTotalFine(rs.getDouble(7));

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

	@Override
	public ReaderVO findByID(String rid) {
		ReaderVO reader = null ;
		String sql = "SELECT * FROM reader 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() ;
			if(rs.next())
			{
				// 查询出内容，之后将查询出的内容赋值给person对象
				reader = new ReaderVO() ;
				reader.setReaderNo(rs.getString(1));
				reader.setName(rs.getString(2));
				reader.setPassword(rs.getString(3));
				reader.setDepartment(rs.getString(4));
				reader.setFreeNum(rs.getInt(5));
				reader.setBorrowedNum(rs.getInt(6));
				reader.setTotalFine(rs.getDouble(7));
			}
			rs.close() ;
			pstmt.close() ;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			dbc.close();
		}

		return reader;
	}

	@Override
	public ReaderVO findByNameAndPass(String name, String pass) {
		String sql = "SELECT * FROM reader WHERE name = ? and password = ?" ;
		PreparedStatement pstmt = null ;
		JDBCConnection dbc = null ;
		ReaderVO reader = null;
		
		try {
			dbc = new JDBCConnection() ;
			pstmt = dbc.getConnection().prepareStatement(sql) ;		
			pstmt.setString(1, name);
			pstmt.setString(2, pass);
			// 进行数据库查询操作
			ResultSet rs = pstmt.executeQuery() ;
			
			while(rs.next())
			{
				// 查询出内容，之后将查询出的内容赋值给person对象
				reader = new ReaderVO();
				
				reader.setReaderNo(rs.getString(1));
				reader.setName(rs.getString(2));
				reader.setPassword(rs.getString(3));
				reader.setDepartment(rs.getString(4));
				reader.setFreeNum(rs.getInt(5));
				reader.setBorrowedNum(rs.getInt(6));
				reader.setTotalFine(rs.getDouble(7));
				// 将查询出来的数据加入到List对象之中
				
			}
			rs.close() ;
			pstmt.close() ;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			dbc.close();
		}
		
		return reader;
	}

	@Override
	public boolean insert(ReaderVO reader) {
		String sql = "INSERT INTO reader VALUES (?,?,?,?,?,?,?)" ;
		PreparedStatement pstmt = null ;
		JDBCConnection dbc = null ;
		boolean flag =false;
		try {
			dbc = new JDBCConnection() ;
			pstmt = dbc.getConnection().prepareStatement(sql) ;	
			pstmt.setString(1, reader.getReaderNo());
			pstmt.setString(2,reader.getName()) ;
			pstmt.setString(3,reader.getPassword()) ;
			pstmt.setString(4,reader.getDepartment()) ;
			pstmt.setInt(5, reader.getFreeNum());
			pstmt.setInt(6, reader.getBorrowedNum());
			pstmt.setDouble(7, reader.getTotalFine());
			
			// 进行数据库更新操作
			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(ReaderVO reader) {
		String sql = "UPDATE reader SET " +
				"name=?," +
				"password=?," +
				"department = ?," +
				"freenum = ?," +
				"borrowednum=?" +
				"totalfine = ?" +
				" WHERE readerno=?;";
		PreparedStatement pstmt = null;
		JDBCConnection dbc = null;
		boolean flag = false;
		try {
			dbc = new JDBCConnection();
			pstmt = dbc.getConnection().prepareStatement(sql);
			pstmt.setString(1, reader.getName());
			pstmt.setString(2, reader.getPassword());
			pstmt.setString(3, reader.getDepartment());
			pstmt.setInt(4, reader.getFreeNum());
			pstmt.setInt(5, reader.getBorrowedNum());
			pstmt.setDouble(6, reader.getTotalFine());
			pstmt.setString(7, reader.getReaderNo());
			// 进行数据库更新操作
			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;

	}

}