userdao.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.LendBean;
import com.jxyd.vo.UserBean;

public class UserDAO {
	private String User_table = "user";
	private String Lend_table = "lend";

	/**
	 * 通过用户的借阅证号和用户密码查询用户信息，返回用户Bean
	 * 
	 * @param UserId
	 * @return
	 */
	public UserBean SelectUserByUId(String BarCode, String pwd) {
		SimpleSqlControl scon = new SimpleSqlControl();
		String sql = "select * from " + User_table + " where BarCode='"
				+ BarCode + "' and Password='" + pwd + "'";
		UserBean ub = null;
		try {
			ub = (UserBean) scon.sqlSearchObject(sql, "com.jxyd.vo.UserBean");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			scon.freeConnection();
		}

		return ub;
	}

	/**
	 * 根据用户的图书证编号返回读者Bean
	 * 
	 * @param BarCode
	 * @return
	 */
	public UserBean SelectUserByUId(String BarCode) {
		SimpleSqlControl scon = new SimpleSqlControl();
		String sql = "select * from " + User_table + " where BarCode='"
				+ BarCode + "'";
		UserBean ub = null;
		try {
			ub = (UserBean) scon.sqlSearchObject(sql, "com.jxyd.vo.UserBean");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			scon.freeConnection();
		}

		return ub;
	}

	/**
	 * 当前借阅数目加1
	 * 
	 * @param nowLendNum,BarCode
	 * @return
	 */
	public boolean UpdateNowLend(Integer nowLendNum, String BarCode) {
		boolean flag = false;
		Mysql ms = new Mysql();
		String sql = "update " + User_table + " set NowLendNum=" + nowLendNum
				+ " where UserId=" + BarCode;
		try {
			ms.execute(sql);
			ms.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ms.freeResource();
		}
		return flag;
	}

	/**
	 * 添加读者
	 * 
	 * @param ub
	 * @return
	 */
	public boolean AddUer(UserBean ub) {
		boolean flag = false;
		Mysql ms = new Mysql();
		String sql = "insert into "
				+ User_table
				+ " (UserId,Name,Unit,Sign,Age,YJ,LimitLendNum,LimitlendTime,mark,Password,BarCode,AddDate,sex,mphone) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			ms.prepareStatement(sql);
			ms.setString(1, ub.getUserId());
			ms.setString(2, ub.getName());
			ms.setString(3, ub.getUnit());
			ms.setInt(4, ub.getSign());
			ms.setInt(5, ub.getAge());
			ms.setDouble(6, ub.getYJ());
			ms.setInt(7, ub.getLimitLendNum());
			ms.setInt(8, ub.getLimitlendTime());
			ms.setString(9, ub.getMark());
			ms.setString(10, ub.getPassword());
			ms.setString(11, ub.getBarCode());
			ms.setString(12, ub.getAddDate());
			ms.setInt(13, ub.getSex());
			ms.setString(14, ub.getMphone());
			ms.execute();
			ms.commit();
			flag = true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			ms.freeResource();
		}
		return flag;
	}

	/**
	 * 查询全部读者
	 * 
	 * @param start
	 * @param pageSize
	 * @param book
	 * @param sv
	 * @return
	 */
	public ArrayList<?> SelectAllBook(Integer start, Integer pageSize,
			String book, String sv) {
		String sql = "select * from " + User_table;

		if (book != null && !"".equals(book)) {
			if (sv.equals("1")) {
				sql = sql + " where UserId like'%" + book + "%'";
			} else if (sv.equals("2")) {
				sql = sql + " where Name like'%" + book + "%'";
			} else if (sv.equals("3")) {
				sql = sql + " where BarCode 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.UserBean");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			scon.freeConnection();
		}
		return list;
	}

	public Integer count(String book, String sv) {
		Mysql ms = new Mysql();
		String sql = "select count(*) from " + User_table;
		if (book != null && !"".equals(book)) {
			if (sv.equals("1")) {
				sql = sql + " where UserId like'%" + book + "%'";
			} else if (sv.equals("2")) {
				sql = sql + " where Name like'%" + book + "%'";
			} else if (sv.equals("3")) {
				sql = sql + " where BarCode 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;
	}

	public boolean DeleteUser(Integer id) {
		boolean flag = false;
		String sql = "delete from " + User_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();
		} finally {
			ms.freeResource();
		}
		return flag;
	}

	public boolean ChangPwd(Integer id, String pwd) {
		boolean flag = false;
		String sql = "update " + User_table + " set Password=" + pwd
				+ " where id=" + id;
		Mysql ms = new Mysql();
		try {
			ms.prepareStatement(sql);
			ms.execute();
			ms.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ms.freeResource();
		}
		return flag;
	}

	public boolean Updatemark(Integer id, String mark) {
		boolean flag = false;
		String sql = "update " + User_table + " set mark='" + mark
				+ "' where id=" + id;
		Mysql ms = new Mysql();
		try {
			if (mark != null) {
				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 usercode
	 * @param start
	 * @param pageSize
	 * @return
	 */
	public ArrayList<?> selectlend(String usercode, Integer start,
			Integer pageSize) {
		String sql = "select * from " + Lend_table + " where UserId="
				+ usercode;
		sql = sql + " 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) {
			e.printStackTrace();
		} finally {
			scon.freeConnection();
		}
		return list;
	}

	public Integer count(String usercode) {// 返回读者的借阅记录的总数
		String sql = "select count(*) from " + Lend_table + " where UserId="
				+ usercode;
		Mysql ms = new Mysql();
		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;
	}

	/**
	 * 查询全部的借出记录
	 */
	public ArrayList<LendBean> SelectAllLent(Integer start, Integer pageSize,
			Integer remain) {
		ArrayList<LendBean> list = null;
		String sql = "";
		if (remain == null) {
			sql = "select * from " + Lend_table
					+ " where state=1 order by id desc ";
		} else {
			sql = "select * from " + Lend_table
					+ " where state=1 and remain=1 order by id desc limit " + start + ","
					+ pageSize;
		}
		SimpleSqlControl scon = new SimpleSqlControl();
		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 id
	 * @param remain
	 * @return
	 */
	public boolean Updateremain(Integer id, Integer remain) {
		boolean flag = false;
		String sql = "update " + Lend_table + " set remain='" + remain
				+ "' 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();
		} finally {
			ms.freeResource();
		}

		return flag;
	}

	/**
	 * 查询借阅记录的总数
	 */
	public Integer count() {
		Mysql ms = new Mysql();
		String sql = "select count(*) from " + Lend_table+" where remain=1";
		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;
	}
	
	public static void main(String[] args) {
		UserDAO ud=new UserDAO();
		System.out.println("=========="+ud.count());
	}

}