Java + Mysql + JDBC 图书馆 管理系统

项目主要包括管理员、采购员、仓库管理、用户四个角色
管理员：基本系统各种数据的修改，用户的管理等
采购员：发起请购、采购、验货等
仓库管理：入库、出库、管理、盘点等
用户：借阅、还书、充值等

比较菜鸟的做法，也没花费多长时间，就当熟练自己的Java基础跟Mysql的用法了
java文件跟数据库已经打包上传到CSDN，需要的同学可以下载
下载链接

Mysql

#用户信息记录
CREATE TABLE userInfo(
	uid INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
	uname VARCHAR(10) COMMENT '用户名',
	rname VARCHAR(10) COMMENT '真实姓名',
	upassword VARCHAR(20) COMMENT ' 用户密码', 
	usex VARCHAR(2) COMMENT '性别',
	uaddress VARCHAR(20) COMMENT '地址',
	rid INT COMMENT '角色ID',
	FOREIGN KEY(rid) REFERENCES role(rid),
	blacklist INT COMMENT '账户锁定/黑名单',
	money DOUBLE COMMENT'账户余额' 
)

#角色表
CREATE TABLE role(
	rid INT AUTO_INCREMENT PRIMARY KEY COMMENT '角色ID',
	rname VARCHAR(10) COMMENT '角色名称'
)

#用户权限表
CREATE TABLE roleLimit(
	rLid INT AUTO_INCREMENT PRIMARY KEY COMMENT '权限表ID', 
	`mid` INT COMMENT '菜单ID',
	FOREIGN KEY(`mid`) REFERENCES menu(`mid`),
	rid INT COMMENT '角色ID',
	FOREIGN KEY(rid) REFERENCES role(rid)
);

#菜单表
CREATE TABLE menu(
	`mid` INT AUTO_INCREMENT PRIMARY KEY COMMENT '菜单ID',
	mname VARCHAR(10) COMMENT '带单名称'
)

#商品信息表
CREATE TABLE productInfo(
	pid INT AUTO_INCREMENT PRIMARY KEY COMMENT '商品ID',
	pname VARCHAR(20) COMMENT '商品名称',
	price DOUBLE COMMENT '商品单价',
	priceOut DOUBLE COMMENT '商品出租价格'
	
)

#请购单抬头表
CREATE TABLE purchaseInfo(
	purId INT AUTO_INCREMENT PRIMARY KEY COMMENT '请购ID',
	orderNum VARCHAR(12) COMMENT '请购单号',
	purName VARCHAR(10) COMMENT '请购人',
	purDate VARCHAR(12) COMMENT '请购日期',
	purCondition VARCHAR(10) COMMENT '请购状态',
	purMoneyAll DOUBLE COMMENT '请购总金额'
)

#请购单明细
CREATE TABLE purchaseList(
	plistId INT AUTO_INCREMENT PRIMARY KEY COMMENT '明细单ID',
	orderNum VARCHAR(12) COMMENT '请购单号',
	pid INT COMMENT '商品ID',
	pNum INT COMMENT '商品数量', 
	pname VARCHAR(20) COMMENT '商品名称',
	price DOUBLE COMMENT '商品单价'
	
)

#采购单
CREATE TABLE buyInfo(
	bId INT AUTO_INCREMENT PRIMARY KEY COMMENT '采购单号',
	orderNum VARCHAR(12) COMMENT '请购单号',
	purName VARCHAR(10) COMMENT '请购人',
	purDate VARCHAR(12) COMMENT '请购日期',
	purCondition VARCHAR(10) COMMENT '请购状态',
	purMoneyAll DOUBLE COMMENT '请购总金额'
)

#采购单明细
CREATE TABLE buyList(
	buyId INT AUTO_INCREMENT PRIMARY KEY COMMENT '明细单ID',
	bId INT COMMENT '采购单号',
	pid INT COMMENT '商品ID',
	pNum INT COMMENT '商品数量',
	pname VARCHAR(20) COMMENT '商品名称',
	price DOUBLE COMMENT '商品单价'
)

#仓库总表
CREATE TABLE cangku(
	pid INT COMMENT '商品ID',
	pname VARCHAR(20) COMMENT '商品名称',
	price DOUBLE COMMENT '商品单价',
	priceOut DOUBLE COMMENT '商品出租价格',
	pNum INT COMMENT '商品总数量',
	pNumIn INT COMMENT '商品入库数量',
	pNumOut INT COMMENT '商品出库数量',
	numNow INT COMMENT '仓库当前剩余数量'
)

#货架
CREATE TABLE goodsList(
	pid INT COMMENT '商品ID',
	pname VARCHAR(20) COMMENT '商品名称',
	gnumNow INT COMMENT '当前剩余数量',
	priceOut DOUBLE COMMENT '商品出租价格'
)

#借书表
CREATE TABLE borrow(
	uname VARCHAR(10) COMMENT '用户名',
	pid INT COMMENT '商品ID',
	pname VARCHAR(20) COMMENT '商品名称',
	bnum INT COMMENT'借阅数量',
	priceOut DOUBLE COMMENT '商品出租价格',
	bDate DATE COMMENT '借书日期',
	rDate DATE COMMENT '还书日期',
	bCondition VARCHAR(10) COMMENT'当前状态'
)

Java
BaseDao

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

public class BaseDao {
	// 数据库连接对象
	Connection conn = null;
	// sql语句执行对象
	PreparedStatement pstat = null;
	// 结果集
	ResultSet rs = null;

	/**
	 * 获取数据库连接
	 * 
	 * @return
	 */
	public Connection getConnection() {
		try {
			// 2.加载驱动，相当于windows中打开数据库工具
			Class.forName("com.mysql.jdbc.Driver");
			// 3.连接数据库 连接地址 连接哪个数据库 用户名 密码
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/library", "root", "123456");
		} catch (ClassNotFoundException e) {
		} catch (SQLException e) {
		}
		return conn;
	}

	/**
	 * 通用查询
	 * 
	 * @param sql sql脚本
	 * @param obj 参数对象
	 * @return 结果集
	 */
	public ResultSet query(String sql, Object[] obj) {
		conn = getConnection();
		try {
			pstat = conn.prepareStatement(sql);
			if (obj != null) {
				for (int i = 0; i < obj.length; i++) {
					pstat.setObject(i + 1, obj[i]);
				}
			}
			rs = pstat.executeQuery();
		} catch (SQLException e) {
		}
		return rs;
	}

	/**
	 * 增删改
	 * 
	 * @param sql sql脚本
	 * @param obj 参数对象
	 * @return 结果集
	 */
	public int update(String sql, Object[] obj) {
		conn = getConnection();
		int count = 0;
		try {
			pstat = conn.prepareStatement(sql);
			if (obj != null) {
				for (int i = 0; i < obj.length; i++) {
					pstat.setObject(i + 1, obj[i]);
				}
			}
			count = pstat.executeUpdate();
		} catch (SQLException e) {
		}
		return count;
	}

	/**
	 * 资源释放
	 * 
	 * @param conn
	 * @param pstas
	 * @param rs
	 */
	public void closeAll() {
		if (rs != null)
			try {
				rs.close();
			} catch (SQLException e) {
			}
		if (pstat != null)
			try {
				pstat.close();
			} catch (SQLException e) {
			}
		if (conn != null)
			try {
				conn.close();
			} catch (SQLException e) {
			}
	}
}

Login

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Scanner;

public class Login {
	int i = 0;
	int rid = 0;
	int page = 0;
	String nameNow = null;
	BaseDao bd = new BaseDao();
	Scanner sc = new Scanner(System.in);
	Map<Object, Object> map = new HashMap<>();

	/**
	 * 初始化选择界面
	 * 
	 * @throws SQLException
	 */
	public void hello() throws SQLException {
		System.out.println("================");
		System.out.println("欢迎来到YF1805图书馆！");
		System.out.println("1.登录");
		System.out.println("2.注册");
		System.out.println("3.找回密码");
		System.out.println("4.退出");
		int index = sc.nextInt();
		switch (index) {
		case 1:
			login();
			break;
		case 2:
			register();
			break;
		case 3:
			forgetPwd();
			break;
		case 4:
			System.out.println("已退出图书馆!");
			System.exit(1);
			break;
		default:
			System.out.println("输入错误,请重新选择!");
			hello();
			break;
		}
	}

	/**
	 * 登录
	 * 
	 * @throws SQLException
	 */
	public void login() throws SQLException {
		System.out.println("================");
		System.out.print("请输入用户名:");
		String name = sc.next();
		System.out.print("请输入密码:");
		String pwd = sc.next();
		blacklist(name, pwd);
		Object[] obj = { name, pwd };
		String sql = "SELECT * FROM userInfo\r\n" + "WHERE uname=? AND upassword = ?";
		ResultSet rs = bd.query(sql, obj);
		if (rs.next()) {
			System.out.println("登录成功!");
			rid = rs.getInt("rid");
			nameNow = rs.getString("uname");
			bd.closeAll();
			Object[] o = { name };
			String sql2 = "UPDATE userInfo SET blacklist=0 WHERE uname=?";
			bd.update(sql2, o);
			bd.closeAll();
			page = 0;
			UI(rid);
		} else {
			System.out.println("登录失败,请重试!");
			login();
		}
	}

	/**
	 * 账户锁定/黑名单
	 * 
	 * @throws SQLException
	 */
	public void blacklist(String name, String pwd) throws SQLException {
		String sql = "SELECT blacklist FROM userInfo WHERE uname=?";
		Object[] obj = { name };
		ResultSet rs = bd.query(sql, obj);
		if (rs.next()) {
			if (rs.getInt("blacklist") >= 3) {
				System.out.println("由于多次密码错误,该账号已被锁定,请尝试找回密码或者联系管理员!");
				bd.closeAll();
				hello();
			} else {
				String sql2 = "UPDATE userInfo SET blacklist=blacklist+1 WHERE uname=?";
				bd.update(sql2, obj);
				bd.closeAll();
			}
		}
	}

	/**
	 * 注册
	 * 
	 * @throws SQLException
	 */
	public void register() throws SQLException {
		System.out.println("================");
		String uname = null;
		String upassword = null;
		String usex = null;
		while (true) {
			boolean b = false;
			String sq = "SELECT * FROM userInfo WHERE uname=?";
			System.out.print("请输入用户名:");
			uname = sc.next();
			Object[] o = { uname };
			ResultSet r = bd.query(sq, o);
			if (r.next()) {
				if (r.getObject("uname") != null) {
					System.out.println("用户名重复,请重新输入!");
				}
			} else {
				b = true;
			}
			if (b)
				break;
		}
		System.out.print("请输入真实姓名:");
		String rname = sc.next();
		while (true) {
			boolean b = false;
			System.out.print("请输入密码:");
			String pwdNew1 = sc.next();
			System.out.print("请再次输入密码:");
			String pwdNew2 = sc.next();
			if (pwdNew1.equals(pwdNew2)) {
				upassword = pwdNew1;
				b = true;
			} else
				System.out.println("两次密码不一致,请重新输入!");
			if (b)
				break;
		}
		System.out.println("请选择性别:1.男2.女");
		int sex = sc.nextInt();
		switch (sex) {
		case 1:
			usex = "男";
			break;
		case 2:
			usex = "女";
			break;
		default:
			System.out.println("输入错误,默认为男性!");
			usex = "男";
			break;
		}
		System.out.print("请输入地址:");
		String uaddress = sc.next();
		String sql = "INSERT INTO userInfo(uname,rname,upassword,usex,uaddress,rid)\r\n" + "VALUE(?,?,?,?,?,4)";
		Object[] obj = { uname, rname, upassword, usex, uaddress };
		bd.update(sql, obj);
		bd.closeAll();
		System.out.println("注册成功!");
		hello();
	}

	/**
	 * 找回密码
	 * 
	 * @throws SQLException
	 */
	public void forgetPwd() throws SQLException {
		System.out.println("================");
		System.out.print("请输入需要找回的账号:");
		String uname = sc.next();
		Object[] o1 = { uname };
		String s1 = "SELECT * FROM userInfo WHERE uname=?";
		ResultSet r1 = bd.query(s1, o1);
		if (r1.next()) {
			System.out.print("请输入该账号的真实姓名:");
			String rname = sc.next();
			Object[] o2 = { uname, rname };
			String s2 = "SELECT * FROM userInfo WHERE uname=? AND rname=?";
			ResultSet r2 = bd.query(s2, o2);
			if (r2.next()) {
				String upassword = null;
				while (true) {
					boolean b = false;
					System.out.print("请输入新密码:");
					String pwdNew1 = sc.next();
					System.out.print("请再次输入新密码:");
					String pwdNew2 = sc.next();
					if (pwdNew1.equals(pwdNew2)) {
						upassword = pwdNew1;
						b = true;
					} else
						System.out.println("两次密码不一致,请重新输入!");
					if (b)
						break;
				}
				Object[] o3 = { upassword, uname };
				String s3 = "UPDATE userInfo SET upassword=? WHERE uname=?";
				bd.update(s3, o3);
				System.out.println("重置密码成功,请登录!");
				hello();
			} else {
				System.out.println("真名姓名不匹配,请重新输入账号!");
				forgetPwd();
			}
		} else {
			System.out.println("不存在该用户,请重新输入账号!");
			forgetPwd();
		}
	}

	/**
	 * 登录完毕后对应的目录
	 * 
	 * @param rid
	 * @throws SQLException
	 */
	public void UI(int rid) throws SQLException {
		System.out.println("================");
		i = 1;
		Object[] obj = { rid, 0 + 10 * page, 10 };
		String sql = "SELECT * FROM roleLimit r \r\n" + "JOIN menu m ON m.`mid`=r.`mid`\r\n" + "WHERE rid=? LIMIT ?,?";
		ResultSet rs = bd.query(sql, obj);
		while (rs.next()) {
			System.out.println(i + "." + rs.getObject("mname"));
			map.put(i++, rs.getObject("mname"));
		}
		bd.closeAll();
		select(rid);
	}

	/**
	 * 目录选择
	 * 
	 * @throws SQLException
	 */
	public void select(int rid) throws SQLException {
		Object[] obj = { rid };
		String sql2 = "SELECT COUNT(1) FROM roleLimit r \r\n" + "JOIN menu m ON m.`mid`=r.`mid`\r\n" + "WHERE rid=?";
		ResultSet rs2 = bd.query(sql2, obj);
		if (rs2.next()) {
			if (((double) rs2.getInt("COUNT(1)") / 10) > 1 && page == 0) {
				System.out.println(i + ".下一页");
				map.put(i++, "下一页");
			} else if ((page + 1) <= ((int) rs2.getInt("COUNT(1)") / 10)) {
				System.out.println(i + ".上一页");
				map.put(i++, "上一页");
				System.out.println(i + ".下一页");
				map.put(i++, "下一页");
			} else if ((page + 1) > ((int) rs2.getInt("COUNT(1)") / 10) && page != 0) {
				System.out.println(i + ".上一页");
				map.put(i++, "上一页");
			}
		}
		System.out.print("请选择:");
		int i = sc.nextInt();
		String index = map.get(i).toString();
		Menu m = new Menu();
		switch (index) {
		case "修改密码":
			m.repwd(nameNow);
			System.out.println("密码修改成功，请重新登录!");
			Login l = new Login();
			l.login();
			break;
		case "发起请购":
			m.purchase(nameNow);
			System.out.println("发起请购成功,返回主菜单!");
			UI(rid);
			break;
		case "查看请购":
			m.purchaseList(nameNow);
			System.out.println("查看请购完毕,返回主菜单!");
			UI(rid);
			break;
		case "审核请购":
			m.purchaseCheck();
			System.out.println("审核请购完毕,返回主菜单!");
			UI(rid);
			break;
		case "查看采购":
			m.buyList(nameNow);
			System.out.println("查看采购完毕,返回主菜单!");
			UI(rid);
			break;
		case "审核采购":
			m.buyCheck();
			System.out.println("审核采购完毕,返回主菜单!");
			UI(rid);
			break;
		case "采购验货":
			m.buyAll(nameNow);
			System.out.println("订单采购完毕,返回主菜单!");
			UI(rid);
			break;
		case "查看仓库":
			m.ckCheck();
			System.out.println("仓库查看完毕,返回主菜单!");
			UI(rid);
			break;
		case "仓库入库":
			m.ckIn();
			System.out.println("仓库入库完毕,返回主菜单!");
			UI(rid);
			break;
		case "仓库出库":
			m.ckOut();
			System.out.println("仓库出库完毕,返回主菜单!");
			UI(rid);
			break;
		case "查看货架":
			m.goodsList();
			System.out.println("查看货架完毕,返回主菜单!");
			UI(rid);
			break;
		case "借阅图书":
			m.borrow(nameNow);
			System.out.println("借阅图书完毕,返回主菜单!");
			UI(rid);
			break;
		case "归还图书":
			m.rturn(nameNow);
			System.out.println("归还图书完毕,返回主菜单!");
			UI(rid);
			break;
		case "结账":
			m.pay(nameNow);
			System.out.println("图书结账完毕,返回主菜单!");
			UI(rid);
			break;
		case "查看账户":
			m.money(nameNow);
			System.out.println("查看账户完毕,返回主菜单!");
			UI(rid);
			break;
		case "账户充值":
			m.moneyIn(nameNow);
			System.out.println("账户充值完毕,返回主菜单!");
			UI(rid);
			break;
		case "添加图书":
			m.addBook();
			System.out.println("图书添加完毕,返回主菜单!");
			UI(rid);
			break;
		case "删除图书":
			m.delBook();
			System.out.println("删除图书完毕,返回主菜单!");
			UI(rid);
			break;
		case "解除锁定":
			m.delBlacklist();
			System.out.println("解除锁定完毕,返回主菜单!");
			UI(rid);
			break;
		case "账号管理":
			m.management(nameNow);
			System.out.println("管理完毕,返回主菜单!");
			UI(rid);
			break;
		case "注销":
			System.out.println("注销完毕,请重新登录!");
			hello();
			break;
		case "盘点":
			m.pandian();
			System.out.println("盘点完毕,返回主菜单!");
			UI(rid);
			break;
		case "下一页":
			page++;
			UI(rid);
			break;
		case "上一页":
			page--;
			UI(rid);
			break;
		default:
			System.out.println("error");
			break;
		}
	}
}

Menu

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.Random;
import java.util.Scanner;

public class Menu {
	BaseDao bd = new BaseDao();
	Scanner sc = new Scanner(System.in);
	Random ran = new Random();
	Map<Object, Object> m = new HashMap<>();
	int pwdCount = 0;

	/**
	 * 修改密码
	 * 
	 * @throws SQLException
	 */
	public void repwd(String nameNow) throws SQLException {
		System.out.println("================");
		Object[] obj = { nameNow };
		String sql = "SELECT * FROM userInfo WHERE uname=?";
		ResultSet rs = bd.query(sql, obj);
		System.out.print("请输入当前密码:");
		String pwdOld = sc.nextLine();
		if (rs.next()) {
			if (rs.getObject("upassword").equals(pwdOld)) {
				pwdCount = 0;
				while (true) {
					boolean b = false;
					System.out.print("请输入新密码:");
					String pwdNew1 = sc.nextLine();
					System.out.print("请再次输入新密码:");
					String pwdNew2 = sc.nextLine();
					if (pwdNew1.equals(pwdNew2)) {
						Object[] obj2 = { pwdNew1, nameNow };
						String sql2 = "UPDATE userInfo SET upassword=? WHERE uname=?";
						bd.update(sql2, obj2);
						bd.closeAll();
						b = true;
					} else
						System.out.println("两次密码不一致,请重新输入!");
					if (b)
						break;
				}
			} else {
				System.out.println("当前密码输入错误，请重新输入！");
				pwdCount++;
				if (pwdCount == 3) {
					System.out.println("当前密码已连续输错3次,为保护账户安全,请重新登录!");
					Login l = new Login();
					l.login();
				}
				repwd(nameNow);
			}
		}
	}

	/**
	 * 审核请购
	 * 
	 * @throws SQLException
	 */
	public void purchaseCheck() throws SQLException {
		System.out.println("================");
		int i = 1;
		String sql = "SELECT uname FROM userInfo u\r\n" + "JOIN role r ON u.`rid`=r.`rid`\r\n"
				+ "WHERE r.rid=2 OR r.rid=1";
		ResultSet rs = bd.query(sql, null);
		while (rs.next()) {
			System.out.println(i + "." + rs.getObject("uname"));
			m.put(i++, rs.getObject("uname"));
		}
		bd.closeAll();
		System.out.print("请选择审核哪个采购员的请购申请:");
		int num = sc.nextInt();
		String name = m.get(num).toString();
		Object[] obj = { name };
		String s = "SELECT COUNT(1) FROM purchaseInfo WHERE purName=? AND purCondition='待审核' ";
		ResultSet rs2 = bd.query(s, obj);
		while (rs2.next()) {
			if (rs2.getInt("COUNT(1)") != 0) {
				purchaseList(name);
				System.out.println("请选择是否通过审核！");
				System.out.println("1.通过");
				System.out.println("2.不通过");
				int xz = sc.nextInt();
				if (xz == 1) {
					String sql2 = "UPDATE purchaseInfo SET purCondition='通过' WHERE purName=? AND purCondition='待审核'";
					if (bd.update(sql2, obj) != 0)
						System.out.println("该请购已经通过!");
					buyInfo(name);
				} else {
					String sql3 = "UPDATE purchaseInfo SET purCondition='不通过' WHERE purName=? AND purCondition='待审核'";
					if (bd.update(sql3, obj) != 0)
						System.out.println("该请购已经拒绝通过!");
				}
			} else
				System.out.println("该采购员没有需要审核的订单!");
		}
		// bd.closeAll();

	}

	/**
	 * 发起请购
	 * 
	 * @throws SQLException
	 */
	public void purchase(String nameNow) throws SQLException {
		System.out.println("================");
		String purCondition = "待审核";
		String time = new SimpleDateFormat("yyyyMMdd").format(new Date());
		String time2 = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		int four = (int) (ran.nextDouble() * (9999 - 1000 + 1)) + 1000;
		String orderNum = time + four;// 单号创建
		System.out.print("请输入请购商品ID:");
		int pid = sc.nextInt();
		System.out.print("请输入请购商品数量:");
		int pNum = sc.nextInt();
		String sql = "SELECT * FROM productInfo WHERE pid=?";
		Object[] obj = { pid };
		ResultSet rs = bd.query(sql, obj);
		String pname = null;
		Double price = null;
		if (rs.next()) {
			pname = rs.getString("pname");// 商品名称
			price = rs.getDouble("price");// 商品价格
		}
		bd.closeAll();
		Object[] obj2 = { orderNum, nameNow, time2, purCondition, pNum * price };
		String sql2 = "INSERT INTO purchaseInfo(orderNum,purName,purDate,purCondition,purMoneyAll)\r\n"
				+ "VALUE(?,?,?,?,?) ";
		bd.update(sql2, obj2);
		bd.closeAll();
		Object[] obj3 = { orderNum, pid, pname, pNum, price };
		String sql3 = "INSERT INTO purchaseList(orderNum,pid,pname,pNum,price)\r\n" + "VALUE(?,?,?,?,?)";
		bd.update(sql3, obj3);
		bd.closeAll();
	}

	/**
	 * 查看请购
	 * 
	 * @throws SQLException
	 */
	public void purchaseList(String nameNow) throws SQLException {
		System.out.println("================");
		Object[] obj = { nameNow };
		String sql = "SELECT * FROM purchaseInfo WHERE purName=?";
		ResultSet rs = bd.query(sql, obj);
		System.out.println("-------------------------请购单抬头表-------------------------");
		System.out.println("请购ID\t请购单号\t\t请购人\t请购日期\t\t请购状态\t请购总金额(元)");
		while (rs.next()) {
			System.out.print(rs.getObject("purId") + "\t");
			System.out.print(rs.getObject("orderNum") + "\t");
			System.out.print(rs.getObject("purName") + "\t");
			System.out.print(rs.getObject("purDate") + "\t");
			System.out.print(rs.getObject("purCondition") + "\t");
			System.out.println(rs.getObject("purMoneyAll"));
		}
		bd.closeAll();
		String sql2 = "SELECT p1.* FROM purchaseList p1\r\n" + "JOIN purchaseInfo p2 ON p1.orderNum=p2.orderNum\r\n"
				+ "WHERE purName=?";
		ResultSet rs2 = bd.query(sql2, obj);
		System.out.println("-------------------------请购单明细表-------------------------");
		while (rs2.next()) {
			System.out.println("明细ID\t请购单号\t\t商品ID\t商品数量\t商品名称\t商品单价(元)");
			System.out.print(rs2.getObject("plistId") + "\t");
			System.out.print(rs2.getObject("orderNum") + "\t");
			System.out.print(rs2.getObject("pid") + "\t");
			System.out.print(rs2.getObject("pNum") + "\t");
			System.out.print(rs2.getObject("pname") + "\t");
			System.out.println(rs2.getObject("price"));
		}
		bd.closeAll();
	}

	/**
	 * 采购单生成
	 * 
	 * @throws SQLException
	 */
	public void buyInfo(String name) throws SQLException {
		String s = "SELECT * FROM purchaseInfo WHERE purName=? AND purCondition='通过'";
		Object[] o = { name };
		ResultSet rs = bd.query(s, o);
		String sql = "INSERT INTO buyInfo(orderNum,purName,purDate,purCondition,purMoneyAll)\r\n" + "VALUE(?,?,?,?,?)";
		while (rs.next()) {
			Object[] obj = { rs.getObject("orderNum"), name, rs.getObject("purDate"), "待审核",
					rs.getObject("purMoneyAll") };
			bd.update(sql, obj);
		}
		String s2 = "SELECT p1.* FROM purchaseList p1\r\n" + "JOIN purchaseInfo p2 ON p1.orderNum=p2.orderNum\r\n"
				+ "WHERE purName=? AND purCondition='通过'";
		ResultSet rs2 = bd.query(s2, o);
		String s3 = "SELECT bId FROM buyInfo WHERE purName=?";
		ResultSet rs3 = bd.query(s3, o);
		String sql2 = "INSERT INTO buyList(bId,pid,pNum,pname,price)\r\n" + "VALUE(?,?,?,?,?)";
		while (rs2.next() && rs3.next()) {
			Object[] obj = { rs3.getObject("bId"), rs2.getObject("pid"), rs2.getObject("pNum"), rs2.getObject("pname"),
					rs2.getObject("price") };
			bd.update(sql2, obj);
		}
		// 添加采购后删除原请购单
		String d1 = "SELECT * FROM purchaseInfo WHERE purName=? AND purCondition='通过'";
		ResultSet rsd1 = bd.query(d1, o);
		while (rsd1.next()) {
			Object[] o1 = { rsd1.getObject("orderNum") };
			String sql3 = "DELETE from purchaseInfo WHERE orderNum=?";
			bd.update(sql3, o1);
			String sql4 = "DELETE from purchaseList WHERE orderNum=?";
			bd.update(sql4, o1);
		}
		bd.closeAll();
	}

	/**
	 * 查看采购
	 */
	public void buyList(String nameNow) throws SQLException {
		System.out.println("================");
		Object[] obj = { nameNow };
		String sql = "SELECT * FROM buyInfo WHERE purName=?";
		ResultSet rs = bd.query(sql, obj);
		System.out.println("-------------------------采购单抬头表-------------------------");
		System.out.println("采购ID\t采购单号\t\t采购人\t请购日期\t\t采购状态\t采购总金额(元)");
		while (rs.next()) {
			System.out.print(rs.getObject("bId") + "\t");
			System.out.print(rs.getObject("orderNum") + "\t");
			System.out.print(rs.getObject("purName") + "\t");
			System.out.print(rs.getObject("purDate") + "\t");
			System.out.print(rs.getObject("purCondition") + "\t");
			System.out.println(rs.getObject("purMoneyAll"));
		}
		String sql2 = "SELECT p1.* FROM buyList p1\r\n" + "JOIN buyInfo p2 ON p1.bId=p2.bId\r\n" + "WHERE purName=?";
		ResultSet rs2 = bd.query(sql2, obj);
		System.out.println("-------------------------采购单明细表-------------------------");
		while (rs2.next()) {
			System.out.println("明细ID\t采购单号\t\t商品ID\t商品数量\t商品名称\t商品单价(元)");
			System.out.print(rs2.getObject("buyId") + "\t");
			System.out.print(rs2.getObject("bId") + "\t");
			System.out.print(rs2.getObject("pid") + "\t");
			System.out.print(rs2.getObject("pNum") + "\t");
			System.out.print(rs2.getObject("pname") + "\t");
			System.out.println(rs2.getObject("price"));
		}
		bd.closeAll();
	}

	/**
	 * 审核采购
	 */
	public void buyCheck() throws SQLException {
		System.out.println("================");
		int i = 1;
		String sql = "SELECT uname FROM userInfo u\r\n" + "JOIN role r ON u.`rid`=r.`rid`\r\n"
				+ "WHERE r.rid=2 OR r.rid=1";
		ResultSet rs = bd.query(sql, null);
		while (rs.next()) {
			System.out.println(i + "." + rs.getObject("uname"));
			m.put(i++, rs.getObject("uname"));
		}
		System.out.print("请选择审核哪个采购员的采购申请:");
		int num = sc.nextInt();
		String name = m.get(num).toString();
		Object[] obj = { name };
		String s = "SELECT COUNT(1) FROM buyInfo WHERE purName=? AND purCondition='待审核' ";
		ResultSet rs2 = bd.query(s, obj);
		while (rs2.next()) {
			if (rs2.getInt("COUNT(1)") != 0) {
				buyList(name);
				System.out.println("请选择是否通过审核！");
				System.out.println("1.通过");
				System.out.println("2.不通过");
				int xz = sc.nextInt();
				if (xz == 1) {
					String sql2 = "UPDATE buyInfo SET purCondition='通过' WHERE purName=? AND purCondition='待审核'";
					if (bd.update(sql2, obj) != 0)
						System.out.println("该请购已经通过!");
				} else {
					String sql3 = "UPDATE buyInfo SET purCondition='不通过' WHERE purName=? AND purCondition='待审核'";
					if (bd.update(sql3, obj) != 0)
						System.out.println("该请购已经拒绝通过!");
				}
			} else
				System.out.println("该采购员没有需要审核的订单!");
		}
		bd.closeAll();
		m.clear();
	}

	/**
	 * 采购验货
	 */
	public void buyAll(String nameNow) {
		System.out.println("================");
		System.out.println("已对所有已经通过审核的采购订单完成购买并验货成功!");
		Object[] obj = { nameNow };
		String sql = "UPDATE buyInfo SET purCondition='完成采购' WHERE purName=? AND purCondition='通过'";
		bd.update(sql, obj);
		bd.closeAll();
	}

	/**
	 * 查看仓库
	 * 
	 * @throws SQLException
	 */
	public void ckCheck() throws SQLException {
		System.out.println("================");
		String sql = "SELECT * FROM cangku";
		ResultSet rs = bd.query(sql, null);
		while (rs.next()) {
			System.out.println("商品ID\t商品名称\t商品单价(元/本)\t商品出租单价(元/H)\t商品总数量\t商品入库数量 商品出库数量 仓库当前剩余数量");
			System.out.print(rs.getObject("pid") + "\t");
			System.out.print(rs.getObject("pname") + "\t");
			System.out.print(rs.getObject("price") + "\t\t");
			System.out.print(rs.getObject("priceOut") + "\t\t");
			System.out.print(rs.getObject("pNum") + "\t");
			System.out.print(rs.getObject("pNumIn") + "\t");
			System.out.print(rs.getObject("pNumOut") + "\t");
			System.out.println(rs.getObject("numNow"));
		}
		bd.closeAll();
	}

	/**
	 * 仓库入库
	 * 
	 * @throws SQLException
	 */
	public void ckIn() throws SQLException {
		System.out.println("================");
		int i = 0;
		String ss = "SELECT * FROM buyInfo\r\n" + "WHERE purCondition = '完成采购'";
		String s = "SELECT pname FROM buyInfo b1\r\n" + "JOIN buyList b2 ON b1.bId=b2.bId\r\n"
				+ "WHERE purCondition = '完成采购'\r\n" + "GROUP BY pname";
		ResultSet r = bd.query(s, null);
		ResultSet rr = bd.query(ss, null);
		System.out.println("当前可入库的书本有:");
		if (rr.next()) {
			while (r.next()) {
				i++;
				m.put(i, r.getString("pname"));
				System.out.println(i + "." + r.getString("pname"));
			}
			System.out.print("请选择:");
			int index = sc.nextInt();
			Object[] obj = { m.get(index).toString() };
			String sql = "SELECT SUM(pNum) FROM buyInfo b1\r\n" + "JOIN buyList b2 ON b1.bId=b2.bId\r\n"
					+ "WHERE purCondition = '完成采购' AND pname=?";
			ResultSet rs = bd.query(sql, obj);
			if (rs.next()) {
				String sq = "SELECT * FROM cangku WHERE pname=?";
				ResultSet sqr = bd.query(sq, obj);
				if (sqr.next()) {
					Object[] obj2 = { rs.getObject("SUM(pNum)"), rs.getObject("SUM(pNum)"), rs.getObject("SUM(pNum)"),
							m.get(index).toString() };
					String sql2 = "UPDATE cangku SET pNumIn=pNumIn+?,pNum=pNum+?,numNow=numNow+?\r\n" + "WHERE pname=?";
					bd.update(sql2, obj2);
				} else {
					String sq2 = "SELECT * FROM productInfo WHERE pname=?";
					ResultSet sq2r = bd.query(sq2, obj);
					if (sq2r.next()) {
						Object[] obj3 = { sq2r.getObject("pid"), m.get(index).toString(), sq2r.getObject("price"),
								sq2r.getObject("priceOut"), rs.getObject("SUM(pNum)"), rs.getObject("SUM(pNum)"),
								rs.getObject("SUM(pNum)") };
						String sql3 = "INSERT INTO cangku(pid,pname,price,priceOut,pNum,pNumIn,pNumOut,numNow)\r\n"
								+ "VALUE(?,?,?,?,?,?,0,?)";
						bd.update(sql3, obj3);
					}
				}
			}
			// 入库后删除入库过的采购单
			String del = "SELECT * FROM buyInfo b1\r\n" + "JOIN buyList b2 ON b1.bId=b2.bId\r\n"
					+ "WHERE purCondition = '完成采购' AND pname=?";
			ResultSet delRs = bd.query(del, obj);
			while (delRs.next()) {
				Object[] delObj = { delRs.getObject("bId") };
				String del2 = "DELETE FROM buyInfo WHERE bId=?";
				bd.update(del2, delObj);
				String del3 = "DELETE FROM buyList WHERE bId=?";
				bd.update(del3, delObj);
			}
		} else {
			System.out.println("暂时没有可以入库的书籍!");
		}
		m.clear();
		bd.closeAll();
	}

	/**
	 * 仓库出库
	 * 
	 * @throws SQLException
	 */
	public void ckOut() throws SQLException {
		System.out.println("================");
		int i = 0;
		int num = 0;
		String ss = "SELECT * FROM cangku";
		String s = "SELECT pname FROM cangku";
		ResultSet r = bd.query(s, null);
		ResultSet rr = bd.query(ss, null);
		System.out.println("当前可出库的书本有:");
		if (rr.next()) {
			while (r.next()) {
				i++;
				m.put(i, r.getString("pname"));
				System.out.println(i + "." + r.getString("pname"));
			}
			System.out.print("请选择:");
			int index = sc.nextInt();
			Object[] obj = { m.get(index).toString() };
			String q1 = "SELECT * FROM cangku WHERE pname=?";
			ResultSet q1rs = bd.query(q1, obj);
			System.out.println("提示:单次最少出库1本,最多出库100本!");
			if (q1rs.next()) {
				while (true) {
					boolean b = false;
					System.out.print("请输入需要出库多少本:");
					num = sc.nextInt();
					if (1 <= num && num <= 100 && num <= q1rs.getInt("numNow"))
						b = true;
					else if (num > q1rs.getInt("numNow")) {
						System.out.println("数量超出仓库剩余数量,请重新输入!");
					} else
						System.out.println("输入数量有误,请重新输!");
					if (b)
						break;
				}
			}
			String sql = "SELECT * FROM productInfo WHERE pname=?";
			ResultSet sqlrs = bd.query(sql, obj);
			if (sqlrs.next()) {
				String sql1 = "SELECT * FROM goodsList WHERE pname=?";
				ResultSet sql1rs = bd.query(sql1, obj);
				if (sql1rs.next()) {
					// 原有数量增加
					Object[] o4 = { num, m.get(index).toString() };
					String sql4 = "UPDATE goodsList SET numNow=numNow+?\r\n" + "WHERE pname=?";
					bd.update(sql4, o4);
				} else {
					// 添加至货架
					Object[] o2 = { sqlrs.getObject("pid"), sqlrs.getObject("pname"), num,
							sqlrs.getObject("priceOut") };
					String sql2 = "INSERT INTO goodsList(pid,pname,numNow,priceOut)\r\n" + "VALUE(?,?,?,?)";
					bd.update(sql2, o2);
				}
				// 修改原仓库库存
				Object[] o3 = { num, num, m.get(index).toString() };
				String sql3 = "UPDATE cangku SET pNumOut=pNumOut+?,numNow=numNow-?\r\n" + "WHERE pname=?";
				bd.update(sql3, o3);
			}
		}
		m.clear();
		bd.closeAll();
	}

	/**
	 * 查看货架
	 * 
	 * @throws SQLException
	 */
	public void goodsList() throws SQLException {
		System.out.println("================");
		String sql = "SELECT * FROM goodsList";
		System.out.println("商品ID\t商品名称\t当前剩余数量    商品出租价格(元/天)");
		ResultSet rs = bd.query(sql, null);
		while (rs.next()) {
			System.out.print(rs.getObject("pid") + "\t");
			System.out.print(rs.getObject("pname") + "\t");
			System.out.print(rs.getObject("gnumNow") + "\t  ");
			System.out.println(rs.getObject("priceOut"));
		}
		bd.closeAll();
	}

	/**
	 * 借阅图书
	 * 
	 * @throws SQLException
	 */
	public void borrow(String nameNow) throws SQLException {
		System.out.println("================");
		System.out.print("请输入需要借阅图书的ID:");
		int id = sc.nextInt();
		String s1 = "SELECT * FROM goodsList WHERE pid=?";
		Object[] o1 = { id };
		ResultSet r1 = bd.query(s1, o1);
		if (r1.next()) {
			String s2 = "SELECT * FROM borrow WHERE pid=? AND uname=?";
			Object[] o2 = { id, nameNow };
			ResultSet r2 = bd.query(s2, o2);
			if (r2.next()) {
				System.out.println("每本图书仅让借阅一次,请选择其他图书!");
			} else {
				String s3 = "SELECT * FROM productInfo WHERE pid=?";
				ResultSet r3 = bd.query(s3, o1);
				if (r3.next()) {
					// 添加借阅记录
					java.sql.Date bDate = new java.sql.Date(System.currentTimeMillis());
					Object[] o4 = { nameNow, id, r3.getObject("pname"), 1, r3.getObject("priceOut"), bDate, null,
							"正在借阅" };
					String s4 = "INSERT INTO borrow(uname,pid,pname,bnum,priceOut,bDate,rDate,bCondition)\r\n"
							+ "VALUE(?,?,?,?,?,?,?,?)";
					bd.update(s4, o4);
					// 货架减少
					Object[] o5 = { 1, r3.getObject("pname") };
					String s5 = "UPDATE goodsList SET numNow=numNow-?\r\n" + "WHERE pname=?";
					bd.update(s5, o5);
				}
			}
		} else {
			System.out.println("商家暂时没有该ID的图书,请联系管理员及时补充!");
		}
		bd.closeAll();
	}

	/**
	 * 归还图书
	 * 
	 * @throws SQLException
	 */
	public void rturn(String nameNow) throws SQLException {
		System.out.println("================");
		int i = 0;
		Object[] obj = { nameNow };
		String ss = "SELECT * FROM borrow WHERE uname=?";
		String s = "SELECT pname FROM borrow WHERE uname=? AND bCondition='正在借阅'";
		ResultSet r = bd.query(s, obj);
		ResultSet rr = bd.query(ss, obj);
		if (rr.next()) {
			System.out.println("当前可归还的书本有:");
			while (r.next()) {
				i++;
				m.put(i, r.getString("pname"));
				System.out.println(i + "." + r.getString("pname"));
			}
			System.out.print("请选择:");
			int index = sc.nextInt();
			// 更新借阅记录
			java.sql.Date rDate = new java.sql.Date(System.currentTimeMillis());
			Object[] o = { rDate, "已归还,待结账", m.get(index).toString(), nameNow };
			String sql = "UPDATE borrow SET rDate=?,bCondition=? WHERE pname=? AND uname=?";
			bd.update(sql, o);
			// 货架增加
			Object[] o5 = { 1, m.get(index).toString() };
			String s5 = "UPDATE goodsList SET numNow=numNow+?\r\n" + "WHERE pname=?";
			bd.update(s5, o5);
		} else {
			System.out.println("暂时没有可归还图书!");
		}
		m.clear();
		bd.closeAll();
	}

	/**
	 * 结账
	 * 
	 * @throws SQLException
	 */
	public void pay(String nameNow) throws SQLException {
		System.out.println("================");
		Object[] obj = { nameNow };
		String sql = " SELECT * FROM borrow WHERE uname=? AND bCondition='已归还,待结账' ";
		ResultSet rs = bd.query(sql, obj);
		while (rs.next()) {
			int time = (int) (rs.getDate("rDate").getTime() - rs.getDate("bDate").getTime()) / 1000 / 60 / 60 / 24 + 1;
			double money = rs.getDouble("priceOut") * time;
			Object[] o1 = { money, nameNow };
			String s1 = "UPDATE userInfo SET money=money-? WHERE uname=?";
			bd.update(s1, o1);
		}
		String s2 = "DELETE FROM borrow WHERE uname=? AND bCondition='已归还,待结账'";
		bd.update(s2, obj);
		bd.closeAll();
	}

	/**
	 * 账户信息查看
	 * 
	 * @throws SQLException
	 */
	public void money(String nameNow) throws SQLException {
		System.out.println("================");
		Object[] o = { nameNow };
		String sql = "SELECT * FROM userInfo WHERE uname=?";
		ResultSet rs = bd.query(sql, o);
		if (rs.next()) {
			System.out.println("当前用户名:" + rs.getObject("uname"));
			System.out.println("当前用户余额:" + rs.getDouble("money") + "元");
		}
		bd.closeAll();
	}

	/**
	 * 充值
	 * 
	 * @param nameNow
	 */
	public void moneyIn(String nameNow) {
		System.out.println("================");
		Double money = 0.0;
		System.out.println("单次充值50-100送10,超过100送30,超过200送60,以此类推!");
		while (true) {
			boolean b = false;
			System.out.print("请输入充值金额:");
			double i = sc.nextDouble();
			if (0 < i && i < 50) {
				money = i;
				b = true;
			} else if (50 <= i && i < 100) {
				money = i + 10;
				b = true;
			} else if (i >= 100) {
				money = (int) (i / 100) * 30 + i;
				b = true;
			} else {
				System.out.println("输入错误,请重新输入!");
			}
			if (b)
				break;
		}
		Object[] o = { money, nameNow };
		String sql = "UPDATE userInfo SET money=money+? WHERE uname=?";
		bd.update(sql, o);
		System.out.println("您的账户已成功充值" + money + "元!");
		bd.closeAll();
	}

	/**
	 * 添加图书
	 * 
	 * @throws SQLException
	 */
	public void addBook() throws SQLException {
		System.out.println("================");
		System.out.print("请输入需要添加图书的名称:");
		String pname = sc.next();
		String s = "SELECT * FROM productInfo WHERE pname=?";
		Object[] p = { pname };
		ResultSet rs = bd.query(s, p);
		if (rs.next()) {
			System.out.println("已存在该名称的图书!");
		} else {
			System.out.print("请输入需要添加图书的进货单价:");
			double price = sc.nextDouble();
			System.out.print("请输入需要添加图书的出租单价:");
			double priceOut = sc.nextDouble();
			Object[] obj = { pname, price, priceOut };
			String sql = "INSERT INTO productInfo(pname,price,priceOut)\r\n" + "VALUE(?,?,?)";
			bd.update(sql, obj);
		}
		bd.closeAll();
	}

	/**
	 * 删除图书
	 * 
	 * @throws SQLException
	 */
	public void delBook() throws SQLException {
		System.out.println("================");
		System.out.println("该操作会删除所有与之相关的记录,请慎重考虑!");
		System.out.println("输入错误的图书名称可以返回主菜单!");
		System.out.print("请输入需要删除图书的名称:");
		String pname = sc.next();
		Object[] obj = { pname };
		// 删除商品表
		String s1 = "DELETE from productInfo where pname=?";
		bd.update(s1, obj);
		// 删除请购
		String s2 = "select * from purchaseList where pname=?";
		ResultSet rs1 = bd.query(s2, obj);
		while (rs1.next()) {
			String s3 = "DELETE from productInfo where orderNum=?";
			Object[] o3 = { rs1.getObject("orderNum") };
			bd.update(s3, o3);
			String s4 = "DELETE from purchaseList where orderNum=?";
			bd.update(s4, o3);
		}
		// 删除采购
		String s5 = "SELECT * FROM buyList p1\r\n" + "JOIN buyInfo p2 ON p1.bId=p2.bId\r\n" + "WHERE pname=?";
		ResultSet rs2 = bd.query(s5, obj);
		while (rs2.next()) {
			String s6 = "DELETE from buyInfo where bId=?";
			Object[] o6 = { rs2.getObject("bId") };
			bd.update(s6, o6);
			String s7 = "DELETE from buyList where bId=?";
			bd.update(s7, o6);
		}
		// 删除仓库
		String s8 = "DELETE from cangku where pname=?";
		bd.update(s8, obj);
		// 删除借书记录
		String s9 = "DELETE from borrow where pname=?";
		bd.update(s9, obj);
		// 删除货架
		String s10 = "DELETE from goodsList where pname=?";
		bd.update(s10, obj);
		bd.closeAll();
	}

	/**
	 * 解除锁定
	 * 
	 * @throws SQLException
	 */
	public void delBlacklist() throws SQLException {
		System.out.println("================");
		int i = 0;
		String ss = "SELECT uname FROM userInfo\r\n" + "WHERE blacklist=3";
		String s = "SELECT uname FROM userInfo\r\n" + "WHERE blacklist=3 ";
		ResultSet r = bd.query(s, null);
		ResultSet rr = bd.query(ss, null);
		if (rr.next()) {
			System.out.println("当前被锁定的用户有:");
			while (r.next()) {
				i++;
				m.put(i, r.getString("uname"));
				System.out.println(i + "." + r.getString("uname"));
			}
			System.out.print("请选择:");
			int index = sc.nextInt();
			Object[] obj = { m.get(index).toString() };
			String sql = "UPDATE userInfo SET blacklist=0\r\n" + "WHERE uname=?";
			bd.update(sql, obj);
			System.out.println("已解除锁定!");
		} else {
			System.out.println("暂无被锁定的用户!");
		}
		m.clear();
		bd.closeAll();
	}

	/**
	 * 账号管理
	 * 
	 * @throws SQLException
	 */
	public void management(String nameNow) throws SQLException {
		System.out.println("================");
		int i = 0;
		String ss = "SELECT uname FROM userInfo\r\n";
		String s = "SELECT uname FROM userInfo\r\n";
		ResultSet r = bd.query(s, null);
		ResultSet rr = bd.query(ss, null);
		if (rr.next()) {
			System.out.println("当前用户有:");
			while (r.next()) {
				i++;
				m.put(i, r.getString("uname"));
				System.out.println(i + "." + r.getString("uname"));
			}
			System.out.print("请选择:");
			int index2 = sc.nextInt();
			Object[] obj = { m.get(index2).toString() };
			System.out.println("1.重置用户密码");
			System.out.println("2.修改用户权限");
			System.out.println("3.删除用户");
			System.out.print("请选择功能:");
			int index = sc.nextInt();
			switch (index) {
			case 1:
				String sql1 = "UPDATE userInfo SET upassword='123456'\r\n" + "WHERE uname=?";
				bd.update(sql1, obj);
				break;
			case 2:
				System.out.println("1.管理员2.采购3.仓库4.普通用户");
				System.out.print("请输入给定的权限值:");
				int rid = sc.nextInt();
				Object[] obj2 = { rid, m.get(index2).toString() };
				String sql2 = "UPDATE userInfo SET rid=?\r\n" + "WHERE uname=?";
				bd.update(sql2, obj2);
				break;
			case 3:
				if (nameNow.equals(m.get(index2).toString())) {
					System.out.println("不能删除当前登录用户!");
				} else {
					String sql3 = "DELETE FROM userInfo\r\n" + "WHERE uname=?";
					String sql4 = "DELETE FROM borrow WHERE uname=?";
					bd.update(sql3, obj);
					bd.update(sql4, obj);
				}
				break;
			default:
				System.out.println("输入错误!");
				break;
			}
		}
		m.clear();
		bd.closeAll();
	}

	/**
	 * 盘点
	 * 
	 * @throws SQLException
	 */
	public void pandian() throws SQLException {
		System.out.println("================");
		int book = 0;
		String s1 = "SELECT pname FROM productInfo";
		ResultSet r1 = bd.query(s1, null);
		while (r1.next()) {
			book++;
			m.put(book, r1.getObject("pname"));
		}
		for (int i = 1; i <= book; i++) {
			Object[] obj = { m.get(i).toString() };
			String s2 = "SELECT * FROM cangku c\r\n" + "JOIN goodsList g ON c.pname=g.pname\r\n" + "WHERE c.pname=?";
			String s3 = "SELECT COUNT(1) FROM borrow\r\n" + "WHERE pname=?";
			ResultSet r2 = bd.query(s2, obj);
			ResultSet r3 = bd.query(s3, obj);
			if (r2.next() && r3.next()) {
				System.out.println("书名:" + m.get(i).toString());
				System.out.println("总数量\t出库数量\t货架数量\t借出数量\t丢失数量");
				System.out.println(r2.getObject("pNum") + "\t" + r2.getObject("pNumOut") + "\t"
						+ r2.getObject("gnumNow") + "\t" + r3.getObject("COUNT(1)") + "\t"
						+ (r2.getInt("pNumOut") - r2.getInt("gnumNow") - r3.getInt("COUNT(1)")));
			}
		}
		m.clear();
		bd.closeAll();
	}
}

Test

import java.sql.SQLException;

public class Test {
	public static void main(String[] args) throws SQLException {
		Login l = new Login();
		l.hello();
	}
}