图书管理系统2

DBHelp.java

package com.jdbc.librarysystem.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class DBHelp<T> {
	public static final String DRIVER = "com.mysql.jdbc.Driver";
	public static final String URL = "jdbc:mysql:///librarysystem";
	public static final String USERNAME = "root";
	public static final String PASSWORD = "root";
	
	
	/**
	 * 获取数据库连接对象
	 * @return Connection对象
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public Connection getConnection () throws ClassNotFoundException, SQLException{
		Class.forName(DRIVER);
		return DriverManager.getConnection(URL,USERNAME,PASSWORD);
	}
	
	/**
	 * 执行insert update delete 语句
	 * @param sql
	 * @param objects ?的对应参数值
	 * @return 该执行影响的行数
	 */
	public int executeSQL(String sql, Object...objects ){
		int rows = 0;
		Connection conn = null;
		PreparedStatement stat = null;
		
		try {
			conn = getConnection();
			stat = conn.prepareStatement(sql);
			
			for (int i = 0; i < objects.length; i++) {
				stat.setObject(i+1, objects[i]);
			}
			
			rows = stat.executeUpdate();
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(stat,conn);
		}
		return rows;
	}
	/**
	 * 返回查询结果给对象
	 * @param sql
	 * @param rowMapper
	 * @param objects
	 * @return 查询结果
	 */
	public T executeQureyToObject(String sql, RowMapper<T> rowMapper,Object...objects){
		T result = null;
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		
		try {
			conn = getConnection();
			stat = conn.prepareStatement(sql);
			for (int i = 0; i < objects.length; i++) {
				stat.setObject(i+1, objects[i]);
			}
			
			rs = stat.executeQuery();
			if(rs.next()){
				result = rowMapper.mapRow(rs);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	/**
	 * 返回查询结果给list集合
	 * @param sql
	 * @param rowMapper
	 * @param objects
	 * @return 查询结果
	 */
	public List<T> executeQueryToList(String sql, RowMapper<T> rowMapper, Object...objects){
		List<T> list = new ArrayList<T>();
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		
		try {
			conn = getConnection();
			stat = conn.prepareStatement(sql);
			for (int i = 0; i < objects.length; i++) {
				stat.setObject(i+1, objects[i]);
			}
			rs = stat.executeQuery();
			while(rs.next()){
				T result = rowMapper.mapRow(rs);
				list.add(result);
			}
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	
	/**
	 * 释放数据库资源
	 * @param stat
	 * @param conn
	 */
	public void close(Statement stat, Connection conn){
		close(null,stat,conn);
	}
	
	/**
	 * 释放数据库资源
	 * @param rs
	 * @param stat
	 * @param conn
	 */
	public void close(ResultSet rs, Statement stat, Connection conn){
		try {
			if(rs != null){
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(stat != null){
					stat.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if(conn != null){
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
}

LibrarySystem.java

package com.jdbc.librarysystem.app;

import java.io.BufferedWriter;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.util.List;
import java.util.Scanner;
import com.jdbc.librarysystem.dao.AdminDao;
import com.jdbc.librarysystem.dao.BookDao;
import com.jdbc.librarysystem.dao.CardDao;
import com.jdbc.librarysystem.dao.RecordDao;
import com.jdbc.librarysystem.entity.Admin;
import com.jdbc.librarysystem.entity.Book;
import com.jdbc.librarysystem.entity.Card;
import com.jdbc.librarysystem.entity.Record;

public class LibrarySystem {
	private Scanner input = new Scanner(System.in);
	private AdminDao<Admin> adao = new AdminDao<Admin>();
	private BookDao<Book> bdao = new BookDao<Book>();
	private CardDao<Card> cdao = new CardDao<Card>();
	private RecordDao<Record> rdao = new RecordDao<Record>();

	
	/**
	 * 程序开始运行
	 * 管理员登陆
	 */
	public void start(){
		System.out.println("欢迎使用图书管理系统");
		System.out.println("请输入管理员账号:");
		String username = input.next();
		
		System.out.println("请输入管理员密码:");
		String password = input.next();
		
		Admin admin = adao.findByNameAndPassword(username, password);
		if(admin != null){
			System.out.println("登陆成功");
			manage(admin);
		} else {
			System.out.println("账号或密码错误");
			start();
		}
	}
	
	
	/**
	 * 管理员登陆之后界面
	 * @param admin
	 */
	private void manage(Admin admin) {
		System.out.println("请选择服务选项:");
		System.out.println("------------------");
		System.out.println("1.借书");
		System.out.println("2.还书");
		System.out.println("3.添加新书");
		System.out.println("4.修改书籍");
		System.out.println("5.删除书籍");
		System.out.println("6.查看所有书籍");
		System.out.println("7.查找指定书籍");
		System.out.println("8.新建图书证");
		System.out.println("9.将所有书籍导出为Excel");
		System.out.println("10.将所有图书证信息导出为Excel");
		System.out.println("11.退出系统");
		System.out.println("------------------");
		
		String index = input.next();
		if("1".equals(index)) {
			borrowBook(admin);
		} else if("2".equals(index)) {
			returnBook(admin);
		} else if("3".equals(index)) {
			addNewBook(admin);
		} else if("4".equals(index)) {
			updateBook(admin);
		} else if("5".equals(index)){
			deleteBook(admin);
		} else if("6".equals(index)) {
			checkAllBook(admin);
		} else if("7".equals(index)){
			searchBook(admin);
		} else if("8".equals(index)) {
			newCard(admin);
		} else if("9".equals(index)){
			outputAllBook(admin);
		} else if("10".equals(index)) {
			outAllCard(admin);
		} else if("11".equals(index)){
			System.exit(0);
		} else {
			System.out.println("输入错误");
			start();
		}
	}
	
	/**
	 * 导出所有借书证信息为csv文件
	 * @param admin
	 */
	
	private void outAllCard(Admin admin) {
		List<Card> card = cdao.showAll();
		try {
			BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream("c:/allCard.csv")));
			bw.write("借书证号" + "," + "姓名" + "," + "性别" + "," + "地址" + "," + "联系方式" + "\r\n");
			for(Card c : card){
				bw.write(c.getNumber() + "," + c.getName() + "," + c.getSex() + "," + c.getAddress() + "," + c.getTel() + "\r\n");
			}
			
			bw.flush();
			bw.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		System.out.println("图书证信息导出成功");
		manage(admin);
	}


	/**
	 * 导出所有借书记录信息为csv文件
	 * @param admin
	 */
	/*private void outAllRecord(Admin admin) {
		List<Record> record = rdao.showAll();
		try {
			BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream("c:/allRecord.csv")));
			bw.write("书籍名" + "," + "借书人" + "\r\n");
			for(Record rd : record){
				Book book = bdao.findById(rd.getBid());
				Card card = cdao.findById(rd.getCid());
				bw.write(book.getBookName() +  "," + card.getName() + "\r\n");
			}
			
			bw.flush();
			bw.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		System.out.println("导出成功");
		manage(admin);
	}
*/
	
	/**
	 * 导出所有图书信息为csv文件
	 * @param admin
	 */
	private void outputAllBook(Admin admin) {
		List<Book> book = bdao.showAll();
		try {
			BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream("c:/allbook.csv")));
			bw.write("书籍编号"+","+"书名"+","+"作者"+","+"出版社"+","+"现有数量"+","+"剩余数量" + "\r\n");
			for(Book bk : book){
				bw.write(bk.getBookId()  + "," + bk.getBookName() + "," + bk.getAuthor()  + "," + bk.getPubName()  + "," + bk.getMaxNum()  + "," + bk.getNowNum() + "\r\n" );
			}
			
			bw.flush();
			bw.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		System.out.println("书籍信息导出成功");
		manage(admin);
	}

	/**
	 * 新建图书证
	 * @param admin
	 */
	private void newCard(Admin admin) {
		System.out.println("请输入要办理的借书证号:");
		String number = input.next();
		
		Card c = cdao.findByNum(number);
		if(c != null){
			System.out.println("该借书证已存在！");
			manage(admin);
		} else {
			System.out.println("请输入办理该借书证的人名:");
			String name = input.next();
			
			System.out.println("请输入性别:(男/女)");
			String sex = input.next();
			
			System.out.println("请输入家庭住址:");
			String address = input.next();
			
			System.out.println("请输入联系电话:");
			String tel = input.next();
			
			Card card = new Card(number,name,sex,address,tel);
			
			int rows = cdao.save(card);
			if(rows > 0){
				System.out.println("新借书证办理成功");
			} else {
				System.out.println("数据繁忙，请稍候...");
			}
			manage(admin);
		}
	}

	/**
	 * 查找指定图书
	 * @param admin
	 */
	private void searchBook(Admin admin) {
		System.out.println("请选择查询方法:");
		System.out.println("------------");
		System.out.println("1.按书籍编号查询");
		System.out.println("2.按书名查询");
		System.out.println("3.按作者查询");
		System.out.println("4.按出版社查询");
		System.out.println("5.按现有数量查询");
		System.out.println("6.按剩余数量查询");
		System.out.println("------------");
		String index = input.next();
		
		if("1".equals(index)) {
			System.out.println("请输入要查找的内容:");
			String ind = input.next();
			List<Book> book = bdao.showAllByBookId(ind);
			for(Book bk : book){
				System.out.println(bk.getBookId() + "\t\t\t" + bk.getBookName() + "\t\t\t" + bk.getAuthor() + "\t\t\t" + bk.getPubName() + "\t\t\t" + bk.getMaxNum() + "\t\t\t" + bk.getNowNum());
			}
		} else if("2".equals(index)) {
			System.out.println("请输入要查找的内容:");
			String ind = input.next();
			List<Book> book = bdao.showAllByAuthor(ind);
			for(Book bk : book){
				System.out.println(bk.getBookId() + "\t\t\t" + bk.getBookName() + "\t\t\t" + bk.getAuthor() + "\t\t\t" + bk.getPubName() + "\t\t\t" + bk.getMaxNum() + "\t\t\t" + bk.getNowNum());
			}
		} else if("3".equals(index)) {
			System.out.println("请输入要查找的内容:");
			String ind = input.next();
			List<Book> book = bdao.showAllByAuthor(ind);
			for(Book bk : book){
				System.out.println(bk.getBookId() + "\t\t\t" + bk.getBookName() + "\t\t\t" + bk.getAuthor() + "\t\t\t" + bk.getPubName() + "\t\t\t" + bk.getMaxNum() + "\t\t\t" + bk.getNowNum());
			}
		} else if("4".equals(index)) {
			System.out.println("请输入要查找的内容:");
			String ind = input.next();
			List<Book> book = bdao.showAllByPubName(ind);
			for(Book bk : book){
				System.out.println(bk.getBookId() + "\t\t\t" + bk.getBookName() + "\t\t\t" + bk.getAuthor() + "\t\t\t" + bk.getPubName() + "\t\t\t" + bk.getMaxNum() + "\t\t\t" + bk.getNowNum());
			}
		} else if("5".equals(index)){
			System.out.println("请输入要查找的内容:");
			String ind = input.next();
			List<Book> book = bdao.showAllByMaxNum(ind);
			for(Book bk : book){
				System.out.println(bk.getBookId() + "\t\t\t" + bk.getBookName() + "\t\t\t" + bk.getAuthor() + "\t\t\t" + bk.getPubName() + "\t\t\t" + bk.getMaxNum() + "\t\t\t" + bk.getNowNum());
			}
		} else if("6".equals(index)) {
			System.out.println("请输入要查找的内容:");
			String ind = input.next();
			List<Book> book = bdao.showAllByNowNum(ind);
			for(Book bk : book){
				System.out.println(bk.getBookId() + "\t\t\t" + bk.getBookName() + "\t\t\t" + bk.getAuthor() + "\t\t\t" + bk.getPubName() + "\t\t\t" + bk.getMaxNum() + "\t\t\t" + bk.getNowNum());
			}
		}
		manage(admin);
	}
	
	/**
	 * 查看所有书籍
	 * @param admin
	 */
	private void checkAllBook(Admin admin) {
		List<Book> book = bdao.showAll();
		for(Book bk : book){
			System.out.println(bk.getBookId() + "\t\t\t" + bk.getBookName() + "\t\t\t" + bk.getAuthor() + "\t\t\t" + bk.getPubName() + "\t\t\t" + bk.getMaxNum() + "\t\t\t" + bk.getNowNum());
		}
		manage(admin);
	}

	/**
	 * 删除书籍
	 * @param admin
	 */
	private void deleteBook(Admin admin) {
		List<Book> book = bdao.showAll();
		System.out.println("书籍编号\t\t\t书名\t\t\t作者\t\t\t出版社\t\t\t数量\t\t\t剩余数量");
		for(Book bk : book){
			System.out.println(bk.getBookId() + "\t\t\t" + bk.getBookName() + "\t\t\t" + bk.getAuthor() + "\t\t\t" + bk.getPubName() + "\t\t\t" + bk.getMaxNum() + "\t\t\t" + bk.getNowNum());
		}
		System.out.println("请输入要删除的书籍编号:");
		String bookId = input.next();
		Book book1 = bdao.findByBookId(bookId);
		if(book1 != null){
			Record record = rdao.findByBid(book1.getId());
			if(record != null){
				System.out.println("该书有借出，尚未归还，无法删除");
			} else {
				int rows = bdao.delete(bookId);
				if(rows > 0){
					System.out.println("书籍删除成功");
				} else {
					System.out.println("数据繁忙，请稍后...");
				}
			}
		} else {
			System.out.println("该书不存在");
		}
		manage(admin);
	}

	/**
	 * 修改书籍信息
	 * @param admin
	 */
	private void updateBook(Admin admin) {
		List<Book> book = bdao.showAll();
		System.out.println("书籍编号\t\t\t书名\t\t\t作者\t\t\t出版社\t\t\t数量\t\t\t剩余数量");
		for(Book bk : book){
			System.out.println(bk.getBookId() + "\t\t\t" + bk.getBookName() + "\t\t\t" + bk.getAuthor() + "\t\t\t" + bk.getPubName() + "\t\t\t" + bk.getMaxNum() + "\t\t\t" + bk.getNowNum());
		}
		System.out.println("请输入要修改的书籍编号:");
		String bookId = input.next();
		Book book1 = bdao.findByBookId(bookId);
		if(book1 != null){
			System.out.println(book1.getBookId() + "\t\t\t" + book1.getBookName() + "\t\t\t" + book1.getAuthor() + "\t\t\t" + book1.getPubName() + "\t\t\t" + book1.getMaxNum() + "\t\t\t" + book1.getNowNum());
			update(book1, admin);
		} else {
			System.out.println("该书籍不存在");
		}
		manage(admin);
	}
	
	/**
	 * 按属性修改书籍信息
	 * @param 指定的书籍
	 * @param admin
	 */
	
	private void update(Book book, Admin admin) {
		System.out.println("请选择修改内容:");
		System.out.println("--------------");
		System.out.println("1.修改书籍名称");
		System.out.println("2.修改书籍作者");
		System.out.println("3.修改出版社信息");
		System.out.println("4.修改总数量");
		System.out.println("5.修改现有数量");
		System.out.println("--------------");
		
		String index = input.next();
		if("1".equals(index)){
			System.out.println("书籍名称修改为:");
			String bookName = input.next();
			book.setBookName(bookName);
			int rows = bdao.update(book);
			if(rows >0){
				System.out.println("书籍信息修改成功");
			} else {
				System.out.println("数据繁忙，请稍后...");
			}
			System.out.println("是否继续修改?Y/N");
			String ind = input.next();
			if("Y".equalsIgnoreCase(ind)){
				update(book, admin);
			}
		} else if("2".equals(index)){
			System.out.println("书籍作者修改为:");
			String author = input.next();
			book.setAuthor(author);
			int rows = bdao.update(book);
			if(rows >0){
				System.out.println("书籍信息修改成功");
			} else {
				System.out.println("数据繁忙，请稍后...");
			}
			System.out.println("是否继续修改?Y/N");
			String ind = input.next();
			if("Y".equalsIgnoreCase(ind)){
				update(book, admin);
			}
		} else if("3".equals(index)){
			System.out.println("出版社信息修改为:");
			String pubName = input.next();
			book.setPubName(pubName);
			int rows = bdao.update(book);
			if(rows >0){
				System.out.println("书籍信息修改成功");
			} else {
				System.out.println("数据繁忙，请稍后...");
			}
			System.out.println("是否继续修改?Y/N");
			String ind = input.next();
			if("Y".equalsIgnoreCase(ind)){
				update(book, admin);
			}
		} else if("4".equals(index)){
			System.out.println("总数量修改为:");
			String mn = input.next();
			try {
				//修改书籍数量
				int maxNum = new Integer(mn).intValue();
					book.setMaxNum(maxNum);
					int rows = bdao.update(book);
					if(rows >0){
						System.out.println("书籍信息修改成功");
					} else {
						System.out.println("数据繁忙，请稍后...");
					}
					System.out.println("是否继续修改?Y/N");
					String ind = input.next();
					if("Y".equalsIgnoreCase(ind)){
						update(book, admin);
					}
			} catch (Exception e) {
				//如果输入的修改的书籍数量不能转换为int类型，捕获异常
				System.out.println("现有数量应为数字");
			}
		} else if("5".equals(index)){
			System.out.println("现有数量修改为(数字):");
			String nn = input.next();
			try {
				//修改书籍数量
				int nowNum = new Integer(nn).intValue();
					book.setNowNum(nowNum);
					int rows = bdao.update(book);
					if(rows >0){
						System.out.println("书籍信息修改成功");
					} else {
						System.out.println("数据繁忙，请稍后...");
					}
					System.out.println("是否继续修改?Y/N");
					String ind = input.next();
					if("Y".equalsIgnoreCase(ind)){
						update(book, admin);
					}
			} catch (Exception e) {
				//如果输入的修改的书籍数量不能转换为int类型，捕获异常
				System.out.println("现有数量应为数字");
			}
		} else {
			System.out.println("选择错误");
		}
		manage(admin);
	}
	
	/**
	 * 添加新书
	 * @param admin
	 */
	
	private void addNewBook(Admin admin) {
		
		System.out.println("请输入要添加的书籍编号:");
		String bookId = input.next();

		System.out.println("请输入要添加的书籍名:");
		String bookName = input.next();
		
		System.out.println("请输入书籍的作者:");
		String author = input.next();
		
		System.out.println("请输入出版社名称:");
		String pubName = input.next();
		
		System.out.println("请输入添加数量(数字):");
		String mn = input.next();
		try {
			//修改书籍数量
			int maxNum = new Integer(mn).intValue();
			
			int nowNum = maxNum;
			Book book = new Book(bookId,bookName,author,pubName,maxNum,nowNum);
			
			int rows = bdao.save(book);
			if(rows > 0){
				System.out.println("书籍添加成功");
			} else {
				System.out.println("数据繁忙，请稍后...");
			}
		} catch (Exception e) {
			//如果输入的书籍数量不能转换为int类型，捕获该异常
			System.out.println("添加数量应为数字");
		} finally {
			manage(admin);
		}
	}

	/**
	 * 还书
	 * @param admin
	 */
	private void returnBook(Admin admin) {
		System.out.println("请输入您的借书证号:");
		String number = input.next();
		
		Card card = cdao.findByNum(number);
		if(card != null){
			List<Record> record = rdao.showAll();
			System.out.println("当前所借书籍:");
			System.out.println("书籍编号\t\t\t书名\t\t\t作者\t\t\t出版社");
			
			for(Record rd : record){
				Book book = bdao.findById(rd.getId());
				System.out.println(book.getBookId() + "\t\t\t" + book.getBookName() + "\t\t\t" + book.getAuthor() + "\t\t\t" + book.getPubName());
			}
			
			System.out.println("请选择要还的书的编号:");
			String index = input.next();
			
			Book book1 = bdao.findByBookId(index);
			if(book1 != null){
				Record record1 = rdao.findByBid(book1.getId());
				book1.setNowNum(book1.getNowNum() + 1);
				
				int rows1 = bdao.update(book1);
				int rows2 = rdao.delete(record1.getId());
				
				if(rows1 > 0 && rows2 > 0){
					System.out.println("还书成功");
				} else {
					System.out.println("数据繁忙，请稍后...");
				}
			} else {
				System.out.println("该书籍编号不存在");
			}
		} else {
				System.out.println("该借书证号不存在！");
		}
		manage(admin);
	}

	/**
	 * 借书
	 * @param admin
	 */
	private void borrowBook(Admin admin) {
		System.out.println("请输入借书证号:");
		String number = input.next();
		
		Card card = cdao.findByNum(number);
		if(card != null){
			List<Book> book = bdao.showAll();
			System.out.println("书籍编号\t\t\t书名\t\t\t作者\t\t\t出版社\t\t\t数量\t\t\t剩余数量");
			for(Book bk : book){
				System.out.println(bk.getBookId() + "\t\t\t" + bk.getBookName() + "\t\t\t" + bk.getAuthor() + "\t\t\t" + bk.getPubName() + "\t\t\t"
						+ bk.getMaxNum() + "\t\t\t" + bk.getNowNum());
			}
			System.out.println("请选择要借的书的编号:");
			String index = input.next();
			Book book1 = bdao.findByBookId(index);
			if(book1 != null){
				if(book1.getNowNum() > 0){
					book1.setNowNum(book1.getNowNum() - 1);
					
					int rows1 = bdao.update(book1);
					Record record1 = new Record();
					record1.setBid(book1.getId());
					record1.setCid(card.getId());
					
					int rows2 = rdao.save(record1);
					if(rows1 > 0 && rows2 > 0){
						System.out.println("借书成功");
					} else {
						System.out.println("数据繁忙，请稍后...");
					}
				} else {
					System.out.println("该书已经被借完");
					borrowBook(admin);
				}
			} else {
				System.out.println("该书籍编号不存在！");
			}
		} else {
			System.out.println("该借书证不存在！");
		}
		manage(admin);
	}
}

RowMapper

package com.jdbc.librarysystem.util;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 创建一个RowMapper接口
 * @author 抽烟别碰电脑
 *
 * @param <T>占位符
 */
public interface RowMapper<T> {
		public T mapRow(ResultSet rs) throws SQLException;
}

Test.java

package com.jdbc.librarysystem.app;

public class Test {
	public static void main(String[] args) {
		LibrarySystem ls = new LibrarySystem();
		ls.start();
	}
}