databookmanager.java

一个图书管理系统

package bookManager.bo;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import bookManager.db.DBHelper;
import bookManager.interfaces.IBookManager;
import bookManager.vo.Book;


public class DataBookManager implements IBookManager {
	
	Connection conn = null;

	public DataBookManager() {
		conn = DBHelper.getConnection();
//		creatBookManagerTable();
	}

	public String addBook(Book book) {
		String sql = "insert into book values(?,?,?,?,?)";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,book.getBookId());
			pstmt.setString(2,book.getBookName());
			pstmt.setString(3,book.getAuthor());
			pstmt.setString(4,book.getPublishingHouse());
			pstmt.setDouble(5,book.getPrice().doubleValue());
			pstmt.executeUpdate();
			
			return "增加书成功！";
			
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			return "失败";
		}

	}

	public String deleteBook(String bookID) {
		String sql = "DELETE FROM BOOK WHERE  bookId =?";
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,bookID);
			int i = pstmt.executeUpdate();
			
			if(i>0){
				return "书已删除！";
			}else{
				return "书没有删除,请检查书编号是否正确！";
			}
			
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
			return "没有删掉";
		}
		

	}

	public Book searchByID(String bookID) {
		Book book =  null;
		String sql = "select * FROM BOOK WHERE  bookId =?";
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,bookID);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				book = new Book();
				book.setBookId(rs.getString(1));
				book.setBookName(rs.getString(2));
				book.setAuthor(rs.getString(3));
				book.setPublishingHouse(rs.getString(4));
				book.setPrice(new Double(rs.getString(5)));
			}
				    
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		

		return book;
	}

	public Book searchByName(String bookName) {
		Book book =  null;
		String sql = "select * FROM BOOK WHERE  bookName =?";
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,bookName);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				book = new Book();
				book.setBookId(rs.getString(1));
				book.setBookName(rs.getString(2));
				book.setAuthor(rs.getString(3));
				book.setPublishingHouse(rs.getString(4));
				book.setPrice(new Double(rs.getString(5)));
			}
				    
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		

		return book;
	}

	public int size() {
		int count = 0;
		String sql = "SELECT COUNT(*) FROM BOOK";
		PreparedStatement pstmt =null;
		try {
			pstmt = conn.prepareStatement(sql);
			ResultSet resultSet=pstmt.executeQuery();
			resultSet.next();
			count = resultSet.getInt(1);
		}catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}
	
	public List getAll() {
		Connection conn = DBHelper.getConnection();
		String sql = "SELECT * FROM BOOK";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List list = new ArrayList();
		try {
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Book book = new Book();
				
				book.setBookId(rs.getString(1));
				book.setBookName(rs.getString(2));
				book.setAuthor(rs.getString(3));
				book.setPublishingHouse(rs.getString(4));
				book.setPrice(new Double(rs.getString(5)));
				
				list.add(book);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public void creatBookManagerTable() {
		try {
			 conn.createStatement().execute(
							"create table book (bookId varchar2(10) primary key,"
									+ "bookName varchar2(20) not null,author varchar2(8), "
									+ "publishingHouse varchar2(20), price number(6,2)"
									+ ") ");
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}