admindao.java

图书馆管理系统 能满足 查询

package com.jxyd.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.jxyd.sql.Mysql;
import com.jxyd.sql.SimpleSqlControl;
import com.jxyd.vo.BookBean;
import com.jxyd.vo.LendBean;

public class AdminDAO {
	private String Book_table = "book";
	private String Lend_table = "lend";

	/**
	 * 根据图书的条码号返回对应图书的bean
	 * 
	 * @param bookcode
	 * @return
	 */
	public BookBean SelectBookbycode(String bookcode) {
		SimpleSqlControl scon = new SimpleSqlControl();
		BookBean bb = null;
		try {
			String sql = "select * from " + Book_table + " where CodeId="
					+ bookcode;
			bb = (BookBean) scon.sqlSearchObject(sql, "com.jxyd.vo.BookBean");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			scon.freeConnection();
		}
		return bb;
	}

	/**
	 * 借书时，向数据库中插入一条借书记录
	 * 
	 * @param lb
	 * @return
	 */
	public boolean LentBook(LendBean lb) {
		boolean flag = false;
		Mysql ms = new Mysql();
		String sql = "insert into "
				+ Lend_table
				+ " (UserId,BookCode,LendDate,ShouldReturnDate,state,Type,BookName,UserName,ISBN) values (?,?,?,?,?,?,?,?,?)";
		try {
			ms.prepareStatement(sql);
			ms.setString(1, lb.getUserId());
			ms.setString(2, lb.getBookCode());
			ms.setString(3, lb.getLendDate());
			ms.setString(4, lb.getShouldReturnDate());
			ms.setInt(5, lb.getState());
			ms.setInt(6, lb.getType());
			ms.setString(7, lb.getBookName());
			ms.setString(8, lb.getUserName());
			ms.setString(9, lb.getISBN());
			ms.execute();
			ms.commit();
			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ms.freeResource();
		}
		return flag;
	}

	/**
	 * 添加图书DAO
	 * 
	 * @param bb
	 * @return
	 */
	public boolean AddBook(BookBean bb) {
		boolean flag = false;
		Mysql ms = new Mysql();
		String sql = "insert into "
				+ Book_table
				+ " (Title,CodeId,ISBN,publishCom,num,classId,author,publishDate,Mark,Price,AddDate) values (?,?,?,?,?,?,?,?,?,?,?)";
		try {
			ms.prepareStatement(sql);
			ms.setString(1, bb.getTitle());
			ms.setString(2, bb.getCodeId());
			ms.setInt(3, bb.getISBN());
			ms.setString(4, bb.getPublishCom());
			ms.setInt(5, bb.getNum());
			ms.setString(6, bb.getClassId());
			ms.setString(7, bb.getAuthor());
			ms.setString(8, bb.getPublishDate());
			ms.setString(9, bb.getMark());
			ms.setDouble(10, bb.getPrice());
			ms.setString(11, bb.getAddDate());
			ms.execute();
			ms.commit();
			flag = true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return flag;
	}

	/**
	 * 查询全部库存图书,按图书分类号查询,并分页显示
	 * 
	 * @param start
	 * @param pageSize
	 * @return
	 */
	public ArrayList<?> SelectAllBook(Integer start, Integer pageSize,
			String book, String sv) {
		String sql = "select * from " + Book_table;

		if (book != null&&!"".equals(book)) {
			if (sv .equals("1") ) {
				sql = sql + " where CodeId like'%" + book + "%'";
			} else if (sv.equals("2") ) {
				sql = sql + " where Title like'%" + book + "%'";
			} else if (sv.equals("3")) {
				sql = sql + " where author like'%" + book + "%'";
			}
		}
		sql = sql + " order by id desc limit " + start + "," + pageSize;

		SimpleSqlControl scon = new SimpleSqlControl();
		ArrayList<?> list = null;
		try {
			list = scon.sqlSearch(sql, "com.jxyd.vo.BookBean");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 当进行借书操作时，刚藏图书的总数减1
	 * 
	 * @param bookCode
	 * @param num
	 * @return
	 */
	public boolean UpdateBook(String bookCode, Integer num) {
		boolean flag = false;
		Mysql ms = new Mysql();
		String sql = "update " + Book_table + " set num=" + num
				+ " where CodeId=" + bookCode;
		try {
			ms.prepareStatement(sql);
			ms.execute();
			ms.commit();
			flag = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ms.freeResource();
		}
		return flag;
	}

	/**
	 * 执行还书操作时，更新借书记录
	 * 
	 * @param lb
	 * @param lendtime
	 * @return
	 */
	public boolean ReturBook(LendBean lb) {
		boolean flag = false;
		String sql = "update " + Lend_table + " set ReturnDate='"
				+ lb.getReturnDate() + "',LendTime=" + lb.getLendTime()
				+ ",YesNo=" + lb.getYesNo() + ",state=" + lb.getState()
				+ ",Amerce='" + lb.getAmerce() + "' where UserId='"
				+ lb.getUserId() + "' and BookCode='" + lb.getBookCode() + "'";
		Mysql ms = new Mysql();
		try {
			ms.prepareStatement(sql);
			ms.execute();
			ms.commit();
			flag = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ms.freeResource();
		}

		return flag;
	}

	/**
	 * 根据读者借阅证号和图书编号返回一条读者的借阅记录
	 * 
	 * @param bookCode
	 * @param userCode
	 * @return
	 */
	public LendBean SelectLend(String bookCode, String userCode) {
		String sql = "select * from " + Lend_table + " where UserId="
				+ userCode + " and BookCode=" + bookCode;
		SimpleSqlControl scon = new SimpleSqlControl();
		LendBean lb = null;
		try {
			lb = (LendBean) scon.sqlSearchObject(sql, "com.jxyd.vo.LendBean");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return lb;
	}

	/**
	 * 查询一个读者所借阅的全部图书并分页显示
	 * 
	 * @param UserCode
	 * @param start
	 * @param pageSize
	 * @return
	 */
	public ArrayList<?> SelectLendBook(String userCode, Integer start,
			Integer pageSize) {
		String sql = "select * from " + Lend_table + " where UserId="
				+ userCode + "  order by id desc limit " + start + ","
				+ pageSize;
		SimpleSqlControl scon = new SimpleSqlControl();
		ArrayList<?> list = null;
		try {
			list = scon.sqlSearch(sql, "com.jxyd.vo.LendBean");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			scon.freeConnection();
		}
		return list;
	}

	/**
	 * 根据读者的借阅证编号返回读者借阅的总数
	 * 
	 * @param userCode
	 * @return
	 */
	public Integer count(String userCode) {
		Mysql ms = new Mysql();
		String sql = "select count(*) from " + Lend_table + " where UserId="
				+ userCode;
		ResultSet rs;
		int count = 0;
		try {
			rs = ms.executeQuery(sql);
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ms.freeResource();
		}
		return count;
	}

	/**
	 * 查询馆藏图书的总数
	 * 
	 * @return
	 */
	public Integer count(String book,String sv) {
		Mysql ms = new Mysql();
		String sql = "select count(*) from " + Book_table;
		if (book != null&&!"".equals(book)) {
			if (sv .equals("1") ) {
				sql = sql + " where CodeId like'%" + book + "%'";
			} else if (sv.equals("2") ) {
				sql = sql + " where Title like'%" + book + "%'";
			} else if (sv.equals("3")) {
				sql = sql + " where author like'%" + book + "%'";
			}
		}
		ResultSet rs;
		int count = 0;
		try {
			rs = ms.executeQuery(sql);
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ms.freeResource();
		}
		return count;
	}

	/**
	 * 输出对应id的图书
	 * 
	 * @param id
	 * @return
	 */
	public boolean DeleteBook(Integer id) {
		boolean flag = false;
		String sql = "delete from " + Book_table + " where id=" + id;
		Mysql ms = new Mysql();
		try {
			ms.prepareStatement(sql);
			ms.execute();
			ms.commit();
			flag = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	

}