dao.java

图书管理系统java+swing+SQL Server实现 包含了图书管理系统的所有功能 纯JAVA实现

				reader.setKeepMoney(rs.getDouble("keepMoney"));
				reader.setZhengjian(rs.getInt("zhengjianType"));
				reader.setISBN(rs.getString("ISBN"));
				reader.setBztime(rs.getDate("banzhengtime"));
				list.add(reader);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return list;
	}

	public static List selectReaderBySex(String readerSex) {// 查询条形编码为readerISBN的读者的信息
		List list = new ArrayList();
		String sql = "select * from lee_reader where sex = '" + readerSex + "'";
		ResultSet rs = Dao.executeQuery(sql);
		System.out.println(sql);
		try {
			while (rs.next()) {
				Reader reader = new Reader();
				reader.setName(rs.getString("name"));
				reader.setSex(rs.getString("sex"));
				reader.setAge(rs.getString("age"));
				reader.setZhiye(rs.getString("zhiye"));
				reader.setIdentityCard(rs.getString("identityCard"));
				reader.setDate(rs.getDate("date"));
				reader.setMaxNum(rs.getString("maxNum"));
				reader.setTel(rs.getString("tel"));
				reader.setKeepMoney(rs.getDouble("keepMoney"));
				reader.setZhengjian(rs.getInt("zhengjianType"));
				reader.setISBN(rs.getString("ISBN"));
				reader.setBztime(rs.getDate("banzhengtime"));
				list.add(reader);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return list;
	}

	public static int updateReader(String id, String name, String sex,
			String age, String identityCard, Date date, String maxNum,
			String tel, Double keepMoney, String zhengjian, String zhiye,
			Date bzTime, String ISBN) {// 更新读者
		int i = 0;
		try {
			String sql = "update lee_reader set name='" + name + "',sex='"
					+ sex + "',age='" + age + "',identityCard='" + identityCard
					+ "',date='" + date + "',maxNum='" + maxNum + "',tel='"
					+ tel + "',keepMoney=" + keepMoney + ",zhengjianType='"
					+ zhengjian + "',zhiye='" + zhiye + "',banzhengtime='"
					+ bzTime + "'where ISBN='" + ISBN + "'";
			i = Dao.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return i;
	}

	public static int updateReaderBorrowBook(String remainBorrowNum,
			String readerISBN) {
		int i = 0;
		try {
			String sql = "update lee_reader set remainBorrowNum = '"
					+ remainBorrowNum + "' where ISBN = '" + readerISBN + "'";
			i = Dao.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return i;
	}

	public static int updateReaderBackBook(String readerISBN) {
		int i = 0;
		try {
			String sql = "update lee_reader set remainBorrowNum = remainBorrowNum + 1"
					+ " where ISBN = '" + readerISBN + "'";
			i = Dao.executeUpdate(sql);
			System.out.println(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return i;
	}

	public static int DelReader(String ISBN) {// 删除读者信息
		int i = 0;
		try {
			String sql = "Delete from lee_reader where ISBN = '" + ISBN + "'";
			i = Dao.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return i;
	}

	/**
	 * 对订购信息表操作
	 */
	public static int insertBookOrder(String ISBN, Date date, String number,
			String operator, String checkAndAccept, Double zhekou) {
		int i = 0;
		try {
			String sql = "insert into Lee_order(ISBN,date,number,operator,checkAndAccept,zhekou) "
					+ "values('"
					+ ISBN
					+ "','"
					+ number
					+ "','"
					+ operator
					+ "','" + checkAndAccept + "','" + zhekou + "')";
			i = Dao.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return i;
	}

	public static List selectBookOrder() {// 查询图书订购
		List list = new ArrayList();
		String sql = "select * from lee_Order a inner join lee_bookInfo b on a.ISBN = b.ISBN";
		ResultSet rs = Dao.executeQuery(sql);
		try {
			while (rs.next()) {
				OrderAndBookInfo order = new OrderAndBookInfo();
				order.setISBN(rs.getString(1));
				order.setOrderdate(rs.getDate(2));
				order.setNumber(rs.getString(3));
				order.setOperator(rs.getString(4));
				order.setCheckAndAccept(rs.getString(5));
				order.setZk(rs.getDouble(6));
				order.setTypeId(rs.getString(8));
				order.setBookname(rs.getString(9));
				order.setWriter(rs.getString(10));
				order.setTranlator(rs.getString(11));
				order.setPublisher(rs.getString(12));
				order.setDate(rs.getDate(13));
				order.setPrice(rs.getDouble(14));
				list.add(order);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return list;
	}

	public static List selectBookOrder(String ISBN) {
		List list = new ArrayList();
		String sql = "select * from lee_Order where ISBN ='" + ISBN + "'";
		ResultSet rs = Dao.executeQuery(sql);
		try {
			while (rs.next()) {
				OrderAndBookInfo order = new OrderAndBookInfo();
				order.setISBN(rs.getString(1));
				order.setOrderdate(rs.getDate(2));
				order.setNumber(rs.getString(3));
				order.setOperator(rs.getString(4));
				order.setCheckAndAccept(rs.getString(5));
				order.setZk(rs.getDouble(6));
				order.setTypeId(rs.getString(8));
				order.setBookname(rs.getString(9));
				order.setWriter(rs.getString(10));
				order.setTranlator(rs.getString(11));
				order.setPublisher(rs.getString(12));
				order.setDate(rs.getDate(13));
				order.setPrice(rs.getDouble(14));
				list.add(order);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return list;
	}

	public static int updateCheckBookOrder(String ISBN) {
		int i = 0;
		try {
			String sql = "updata lee_order set checkAndAccept=0 where ISBN='"
					+ ISBN + "'";
			i = Dao.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return i;
	}

	/**
	 * 对借阅表进行操作
	 */
	public static int InserBookBorrow(String bookISBN, String readerISBN,
			String operatorId, Timestamp borrowDate, Timestamp backDate) {
		int i = 0;
		try {
			String sql = "insert into lee_borrow(bookISBN,readerISBN,operatorId,borrowDate,backDate,isback)values('"
					+ bookISBN
					+ "','"
					+ readerISBN
					+ "','"
					+ operatorId
					+ "','" + borrowDate + "','" + backDate + "','" + 0 + "')";
			System.out.println(sql);
			i = Dao.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return i;
	}

	public static List selectBorrow(String readerISBN) {
		List list = new ArrayList();
		String sql = "select * from lee_borrow where readerISBN='" + readerISBN
				+ "'";
		ResultSet rs = Dao.executeQuery(sql);
		try {
			while (rs.next()) {
				Borrow borrow = new Borrow();
				borrow.setId(rs.getInt("id"));
				borrow.setBookISBN(rs.getString("bookISBN"));
				borrow.setReaderISBN(rs.getString("readerISBN"));
				borrow.setBorrowDate(rs.getString("borrowDate"));
				borrow.setBackDate(rs.getString("backDate"));
				borrow.setBookName(rs.getString("borrowBookName"));
				list.add(borrow);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return list;
	}

	/**
	 * 查询还书内容，tb_bookinfo tb_reader tb_borrow之间的查询
	 */
	public static List selectBookBack(String readerISBN) {// 查找已经还的书
		List list = new ArrayList();
		String sql = "SELECT a.ISBN AS bookISBN, a.bookname, a.typeId ,b.id,b.operatorId, b.borrowDate, b.backDate, c.name AS readerName, "
				+ "c.ISBN AS readerISBN FROM lee_bookInfo a INNER JOIN lee_borrow b ON a.ISBN = b.bookISBN INNER JOIN lee_reader c ON b.readerISBN "
				+ "= c.ISBN WHERE (c.ISBN = '" + readerISBN + "' and isback=0)";
		System.out.println(sql);
		ResultSet rs = Dao.executeQuery(sql);
		try {
			while (rs.next()) {
				Back back = new Back();
				back.setId(rs.getInt("id"));
				back.setBookISBN(rs.getString("bookISBN"));
				back.setBookName(rs.getString("bookname"));
				back.setTypeId(rs.getInt("typeId"));
				back.setOperatorId(rs.getString("operatorId"));
				back.setBorrowDate(rs.getString("borrowDate"));
				back.setBackDate(rs.getString("backDate"));
				back.setReaderName(rs.getString("readerName"));
				back.setReaderISBN(rs.getString("readerISBN"));
				list.add(back);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return list;
	}

	public static List selectBookBackByBookISBN(String bookISBN) {// 查找已经还的书
		List list = new ArrayList();
		String sql = "SELECT a.ISBN AS bookISBN, a.bookname, a.typeId ,b.id,b.operatorId, b.borrowDate, b.backDate, c.name AS readerName, "
				+ "c.ISBN AS readerISBN FROM lee_bookInfo a INNER JOIN lee_borrow b ON a.ISBN = b.bookISBN INNER JOIN lee_reader c ON b.readerISBN "
				+ "= c.ISBN WHERE (a.ISBN = '" + bookISBN + "' and isback=0)";
		System.out.println(sql);
		ResultSet rs = Dao.executeQuery(sql);
		try {
			while (rs.next()) {
				Back back = new Back();
				back.setId(rs.getInt("id"));
				back.setBookISBN(rs.getString("bookISBN"));
				back.setBookName(rs.getString("bookname"));
				back.setTypeId(rs.getInt("typeId"));
				back.setOperatorId(rs.getString("operatorId"));
				back.setBorrowDate(rs.getString("borrowDate"));
				back.setBackDate(rs.getString("backDate"));
				back.setReaderName(rs.getString("readerName"));
				back.setReaderISBN(rs.getString("readerISBN"));
				list.add(back);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return list;
	}

	public static int updateBookBack(String bookISBN, String readerISBN, int id) {// 归还图书操作
		int i = 0;
		try {
			String sql = "update lee_borrow set isback = 1 where bookISBN='"
					+ bookISBN + "' and readerISBN='" + readerISBN
					+ "' and id=" + id + "";
			i = Dao.executeUpdate(sql);
			System.out.println(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return i;
	}

	public static int updateBookBackRollBack(String bookISBN,
			String readerISBN, int id) {// 归还图书操作
		int i = 0;
		String sql = "update lee_borrow set isback = 1 where bookISBN='"
				+ bookISBN + "' and readerISBN='" + readerISBN + "' and id="
				+ id + "";
		try {
			i = Dao.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return i;
	}

	// new
	public static List selectbooksearch() {// 返回图书类型的集合
		List list = new ArrayList();
		String sql = "select * from lee_bookInfo";
		ResultSet rs = Dao.executeQuery(sql);
		try {
			while (rs.next()) {
				BookInfo bookinfo = new BookInfo();
				bookinfo.setISBN(rs.getString(1));
				bookinfo.setTypeid(rs.getString(2));
				bookinfo.setBookname(rs.getString(3));
				bookinfo.setWriter(rs.getString(4));
				bookinfo.setTranslator(rs.getString(5));
				bookinfo.setPublisher(rs.getString(6));
				bookinfo.setDate(rs.getDate(7));
				bookinfo.setPrice(rs.getDouble(8));
				list.add(bookinfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return list;
	}

	public static List selectbookmohu(String bookname) {// 通过模糊书名进行模糊查找
		List list = new ArrayList();
		String sql = "select * from lee_bookInfo where bookname like '%"
				+ bookname + "%'";
		System.out.println(sql);
		ResultSet rs = Dao.executeQuery(sql);
		try {
			while (rs.next()) {
				BookInfo bookinfo = new BookInfo();
				bookinfo.setISBN(rs.getString(1));
				bookinfo.setTypeid(rs.getString(2));
				bookinfo.setBookname(rs.getString(3));
				bookinfo.setWriter(rs.getString(4));
				bookinfo.setTranslator(rs.getString(5));
				bookinfo.setPublisher(rs.getString(6));
				bookinfo.setDate(rs.getDate(7));
				bookinfo.setPrice(rs.getDouble(8));
				list.add(bookinfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return list;
	}

	public static List selectbookmohuwriter(String writer) {// 通过模糊作者名进行模糊查找
		List list = new ArrayList();
		String sql = "select * from lee_bookInfo where writer like '%" + writer
				+ "%'";
		System.out.println(sql);
		ResultSet s = Dao.executeQuery(sql);
		try {
			while (s.next()) {
				BookInfo bookinfo = new BookInfo();
				bookinfo.setISBN(s.getString(1));
				bookinfo.setTypeid(s.getString(2));
				bookinfo.setBookname(s.getString(3));
				bookinfo.setWriter(s.getString(4));
				bookinfo.setTranslator(s.getString(5));
				bookinfo.setPublisher(s.getString(6));
				bookinfo.setDate(s.getDate(7));
				bookinfo.setPrice(s.getDouble(8));
				list.add(bookinfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		Dao.close();
		return list;
	}

	public static int insertOperator(String id, String name, String sex,
			int age, String identityCard, Date workdate, String tel,
			String admin, String password) {
		// 插入操作员
		int i = 0;
		try {
			String sql = "insert into lee_operator(id,name,sex,age,identityCard,workdate,tel,admin,password) values"
					+ "('"
					+ id
					+ "','"
					+ name
					+ "','"
					+ sex
					+ "','"
					+ age
					+ "','"
					+ identityCard
					+ "','"
					+ workdate
					+ "','"
					+ tel
					+ "','" + admin + "','" + password + "')";
			i = Dao.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return i;
	}

	public static List selectUser() {// 选择用户
		List list = new ArrayList();
		String sql = "select id,name,sex,age,identityCard,workdate,tel,password from lee_operator where admin = 0";
		ResultSet rs = Dao.executeQuery(sql);
		try {
			while (rs.next()) {
				user user = new user();
				user.setId(rs.getInt(1));
				user.setName(rs.getString(2));
				user.setSex(rs.getString(3));
				user.setAge(rs.getInt(4));
				user.setIdentityCard(rs.getString(5));
				user.setWorkdate(rs.getDate(6));
				user.setTel(rs.getString(7));
				user.setPassword(rs.getString(8));
				list.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return list;
	}

	public static int delUser(int id) {// 删除用户
		int i = 0;
		try {
			String sql = "delete from lee_operator where id = '" + id + "'";
			i = Dao.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return i;
	}

	public static int updateUser(int id, String name, String sex, int age,
			String identityCard, Date workdate, String tel, String password) {
		// 更新用户信息
		int i = 0;
		try {
			String sql = "update lee_operator set name = '" + name + "',sex='"
					+ sex + "',age=" + age + ",identityCard='" + identityCard
					+ "',workdate='" + workdate + "',tel='" + tel
					+ "',password='" + password + "' where id='" + id + "'";
			i = Dao.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return i;
	}

	public static int updatePass(String password, String name) {// 修改密码
		int i = 0;
		try {
			String sql = "update lee_operator set password='" + password
					+ "' where name='" + name + "'";

			i = Dao.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return i;
	}
	// 
}