databorrowmanager.java

一个图书管理系统

package bookManager.bo;

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

import bookManager.db.DBHelper;
import bookManager.interfaces.IBorrowManager;
import bookManager.vo.Book;
import bookManager.vo.Borrow;
import bookManager.vo.User;



public class DataBorrowManager implements IBorrowManager {
	Connection conn = null;

	public DataBorrowManager() {
		conn = DBHelper.getConnection();
//		creatBorrowManagerTable();
	}

	public String addBorrow(Borrow borrow) {
		String sql = "insert into borrow values(?,?,?)";
		
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,borrow.getBorrowID());
			User user = borrow.getUser();
			Book book = borrow.getBook();
			
			if(user != null||book !=null){
				pstmt.setString(2, user.getUserId());
				pstmt.setString(3,book.getBookId());
				pstmt.executeUpdate();
				conn.prepareStatement("commit").execute();
				
				return "借书成功！";
			}else{
				return "借书失败！检查会员编号或书编号是否正确";
			}
						
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			return "借书失败！";
		}

	}

	public String deleteBorrow(String borrowID) {
		String sql = "DELETE FROM BORROW WHERE  borrowId =?";
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, borrowID);
			int i = pstmt.executeUpdate();
			if(i >0){
				return "还书成功！";	
			}else{
				return "没有这条借书记录，请重新输入！";
			}
			
			
			
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
			return "没有还书";
		}
		

	}

	public Borrow searchByID(String borrowID) {
		Borrow borrow =  null;
		/*String sql = "select a.borrowId,b.*,c.* FROM BORROW a,BOOK b,USERS c WHERE  a.borrowId ="+ borrowID  +"and  a.bookID = b.bookId and a.userID = c.userId";
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				borrow = new Borrow();
				borrow.setBorrowID(rs.getString(1));
				borrow.setBook(new Book(rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),new Double(rs.getString(6))));
				borrow.setUser(new User(rs.getString(7),rs.getString(8)));
			
			}
				    
		} catch (SQLException e) {
			
			e.printStackTrace();
		}*/
		String sql = "SELECT * FROM BORROW WHERE borrowId =?";
		PreparedStatement pstmt;
		String borrowId=null;
		String userID =null;
		String bookID =null;
		try {
			pstmt =conn.prepareStatement(sql);
			pstmt.setString(1,borrowID); 
			ResultSet rs = pstmt.executeQuery();
			
			if(rs.next()){
			borrowId = rs.getString(1);
			userID = rs.getString(2);
			bookID = rs.getString(3);
			borrow =new Borrow(borrowId,(new DataUserManager()).searchUsID(userID),(new DataBookManager()).searchByID(bookID));
			}
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}

		return borrow;
	}

	
	public int size() {
		int count = 0;
		String sql = "SELECT COUNT(*) FROM BORROW";
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(sql);
			ResultSet resultSet=pstmt.executeQuery();
			resultSet.next();
			count = resultSet.getInt(1);
		
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return count;
	}

	public void creatBorrowManagerTable() {
		try {
			 conn.createStatement().execute(
							"create table borrow (borrowId varchar2(10) primary key,"
									+ "userID varchar2(10) not null," 
									+"bookID varchar2(10) not null)");			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}