booksdaoimpl.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.BooksDAO;
import lib_jdbc.vo.BooksVO;

public class BooksDAOImpl implements BooksDAO {

	@Override
	
	public boolean delete(String id) {
		// TODO Auto-generated method stub
		String sql = "DELETE FROM Books WHERE bookid=?" ;
		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<BooksVO> findAll() {
		// TODO Auto-generated method stub
		List<BooksVO> all = new ArrayList<BooksVO>();
		String sql = "SELECT * FROM Books" ;
		PreparedStatement pstmt = null ;
		JDBCConnection dbc = null ;
		
		try {
			dbc = new JDBCConnection() ;
			pstmt = dbc.getConnection().prepareStatement(sql) ;			
			// 进行数据库查询操作
			ResultSet rs = pstmt.executeQuery() ;
			while(rs.next())
			{
				// 查询出内容，之后将查询出的内容赋值给person对象
				BooksVO books = new BooksVO() ;
				books.setBookID(rs.getString(1));
				books.setTitle(rs.getString(2));
				books.setAuthor(rs.getString(3));
				books.setPublisher(rs.getString(4));
				books.setISBN(rs.getString(5));
				books.setPrice(rs.getDouble(6));
				books.setTotal(rs.getInt(7));
				books.setSuppliedNum(rs.getInt(8));

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

	@Override
	public BooksVO findByID(String id) {
		
		BooksVO books = null ;
		String sql = "SELECT * FROM books WHERE bookid=?" ;
		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对象
				books = new BooksVO() ;
				books.setBookID(rs.getString(1));
				books.setTitle(rs.getString(2));
				books.setAuthor(rs.getString(3));
				books.setPublisher(rs.getString(4));
				books.setISBN(rs.getString(5));
				books.setPrice(rs.getDouble(6));
				books.setTotal(rs.getInt(7));
				books.setSuppliedNum(rs.getInt(8));
			}
			rs.close() ;
			pstmt.close() ;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			dbc.close();
		}

		return books;
	}

	@Override
	public boolean insert(BooksVO books) {
		String sql = "INSERT INTO books VALUES (?,?,?,?,?,?,?,?)" ;
		PreparedStatement pstmt = null ;
		JDBCConnection dbc = null ;
		boolean flag =false;
		try {
			dbc = new JDBCConnection() ;
			pstmt = dbc.getConnection().prepareStatement(sql) ;	
			pstmt.setString(1, books.getBookID());
			pstmt.setString(2,books.getTitle()) ;
			pstmt.setString(3,books.getAuthor()) ;
			pstmt.setString(4,books.getPublisher()) ;
			pstmt.setString(5,books.getISBN()) ;
			pstmt.setDouble(6,books.getPrice()) ;
			pstmt.setInt(7, books.getTotal());
			pstmt.setInt(8,books.getSuppliedNum());
			
			// 进行数据库更新操作
			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(BooksVO books) {
		String sql = "UPDATE books SET title=?,author=?,publisher=?," +
				"isbn=?,price=?,total=?,suppliednum=? WHERE bookid=?" ;
		PreparedStatement pstmt = null ;
		JDBCConnection dbc = null ;
		boolean flag = false;
		try {
			dbc = new JDBCConnection() ;
			pstmt = dbc.getConnection().prepareStatement(sql) ;			
			pstmt.setString(1,books.getTitle()) ;
			pstmt.setString(2,books.getAuthor()) ;
			pstmt.setString(3,books.getPublisher()) ;
			pstmt.setString(4,books.getISBN()) ;
			pstmt.setDouble(5,books.getPrice()) ;
			pstmt.setInt(6, books.getTotal());
			pstmt.setInt(7,books.getSuppliedNum());
			pstmt.setString(8, books.getBookID());
			// 进行数据库更新操作
			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;
	}



}