使用所学Spring知识，实现简易的图书查询系统功能。实现查询全部图书。根据书籍编号查询信息。根据书名查询书籍信息。根据状态查询书籍信息。

小咪咪 2023-01-21 04:25 7阅读 0赞

> [相关 知识 >>>][Link 1]
> 
> [相关 练习 >>>][Link 2]

## ##

## 实现要求： ##

使用所学Spring知识，实现简易的图书查询系统功能。

**要求如下：**

*  查询全部图书。
 *  根据书籍编号查询信息。
 *  根据书名查询书籍信息。
 *  根据状态查询书籍信息。

**运行结果：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70][]

## 实现思路： ##

**具体实现步骤：**

（1）在MySQL数据库中创建t00\_book表，代码如下：

-- ----------------------------
    -- Table structure for t00_book
    -- ----------------------------
    DROP TABLE IF EXISTS `t00_book`;
    CREATE TABLE `t00_book`  (
      `bookId` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '图书编号',
      `bookName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '图书名字',
      `author` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '作者',
      `pages` int(11) NULL DEFAULT NULL COMMENT '页数',
      `flag` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '状态（0-借出，1-在库）'
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

（2）在t00\_book表中插入初始化数据，代码如下：

-- ----------------------------
    -- Records of t00_book
    -- ----------------------------
    INSERT INTO `t00_book` VALUES ('1000', '《西游记》', '吴承恩', 324, '1');
    INSERT INTO `t00_book` VALUES ('1002', '《红楼梦》', '曹雪芹', 425, '1');
    INSERT INTO `t00_book` VALUES ('1003', '《三国演义》', '罗贯中', 346, '1');
    INSERT INTO `t00_book` VALUES ('1004', '《水浒传》', '施耐庵', 298, '1');
    INSERT INTO `t00_book` VALUES ('1005', '《儒林外史》', '吴敬梓', 234, '1');
    INSERT INTO `t00_book` VALUES ('1006', '《离骚》', '屈原', 186, '1');
    INSERT INTO `t00_book` VALUES ('1007', '《牡丹亭》', '汤显祖', 168, '1');
    INSERT INTO `t00_book` VALUES ('1007', 'Python高级编程', '张大大', 322, '1');

（3）引入jar包：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 1][]

（4）在applicationContext.xml配置文件中打开注解扫描功能，代码如下：

<?xml version="1.0" encoding="UTF-8"?>
    <beans  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    		xmlns="http://www.springframework.org/schema/beans" 
    		xmlns:p="http://www.springframework.org/schema/p" 
    		xmlns:context="http://www.springframework.org/schema/context" 
    		xsi:schemaLocation="http://www.springframework.org/schema/beans 
    		http://www.springframework.org/schema/beans/spring-beans-4.2.xsd 
    		http://www.springframework.org/schema/context 
    		http://www.springframework.org/schema/context/spring-context-4.2.xsd ">
    	
    	
    	<context:component-scan base-package="com.mhys.book"></context:component-scan>
    	
    	
    	
    </beans>

（5）在com.mhys.book.pojo包下创建DataSource类，添加driverClassName，url，username，password4个属性，然后又注入属性值，代码如下：

package com.mhys.book.pojo;
    
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.stereotype.Component;
    
    @Component
    public class DataSource {
    	@Value("com.mysql.jdbc.Driver")
    	private String driverClassName;
    	@Value("jdbc:mysql://127.0.0.1:3306/t00_book")
    	private String url;
    	@Value("root")
    	private String username;
    	@Value("123456")
    	private String password;
    	
    	public String getDriverClassName() {
    		return driverClassName;
    	}
    	public void setDriverClassName(String driverClassName) {
    		this.driverClassName = driverClassName;
    	}
    	public String getUrl() {
    		return url;
    	}
    	public void setUrl(String url) {
    		this.url = url;
    	}
    	public String getUsername() {
    		return username;
    	}
    	public void setUsername(String username) {
    		this.username = username;
    	}
    	public String getPassword() {
    		return password;
    	}
    	public void setPassword(String password) {
    		this.password = password;
    	}
    	
    	
    
    }

（6）在com.mhys.book.pojo包下创建Book类，添加bookId，bookName，author，pages，flag5个属性，然后注入属性值，代码如下

package com.mhys.book.pojo;
    
    public class Book {
    	private String bookId;
    	private String bookName;
    	private String author;
    	private int pages;
    	private String flag;
    	
    	public String getBookId() {
    		return bookId;
    	}
    	public void setBookId(String bookId) {
    		this.bookId = bookId;
    	}
    	public String getBookName() {
    		return bookName;
    	}
    	public void setBookName(String bookName) {
    		this.bookName = bookName;
    	}
    	public String getAuthor() {
    		return author;
    	}
    	public void setAuthor(String author) {
    		this.author = author;
    	}
    	public int getPages() {
    		return pages;
    	}
    	public void setPages(int pages) {
    		this.pages = pages;
    	}
    	public String getFlag() {
    		return flag;
    	}
    	public void setFlag(String flag) {
    		this.flag = flag;
    	}
    	
    	
    }

（7）在com.mhys.book.dao包下创建BaseDao类，添加dataSource属性并注入属性值，然后声明getCounection()方法，代码如下：

package com.mhys.book.dao;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    
    
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    
    import javax.annotation.PostConstruct;
    
    import org.junit.Test;
    import org.junit.runner.RunWith;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.test.context.ContextConfiguration;
    import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
    
    import com.mhys.book.pojo.DataSource;
    
    @RunWith(SpringJUnit4ClassRunner.class)
    @ContextConfiguration("classpath:applicationContext.xml")
    public class BaseDao {
    	@Autowired
    	private DataSource dataSource;
    	
    	@PostConstruct
    	public Connection getConnection() throws Exception{
    		Class.forName(dataSource.getDriverClassName());
    		String url = dataSource.getUrl();
    		String user = dataSource.getUsername();
    		String password = dataSource.getPassword();
    		Connection connection = DriverManager.getConnection(url, user, password);
    		
    		return connection;
    	}
    	
    	public void close(ResultSet rs,PreparedStatement ps,Connection conn) throws Exception{
    		if(rs != null){
    			rs.close();
    		}
    		if(ps != null){
    			ps.close();
    		}
    		if(conn != null){
    			conn.close();
    		}
    	}
    
    }

（8）在com.mhys.book.dao包下创建BookDao类，声明getAllBooksInfo()方法、getBooksInfoByid()方法，添加getBooksInfoByBookName()方法和getBooksInfoByflag()方法，代码如下：

package com.mhys.book.dao;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    
    import org.junit.Test;
    import org.springframework.stereotype.Component;
    
    
    @Component
    public class BookDao extends BaseDao {
    	
    	//查询全部
    	public ResultSet getAllBooksInfo() throws Exception{
    		String sql = "select bookId,bookName,author,pages,flag from t00_book";
    		Connection connection = this.getConnection();
    		PreparedStatement ps = connection.prepareStatement(sql);
    		ResultSet rs = ps.executeQuery();
    		return rs;
    	}
    	
    	//根据书籍编号查询信息
    	public ResultSet getBooksInfoById(String bookId) throws Exception{
    		String sql = "select bookId,bookName,author,pages,flag from t00_book where bookId=?";
    		Connection connection = this.getConnection();
    		PreparedStatement ps = connection.prepareStatement(sql);
    		ps.setString(1, bookId);
    		ResultSet rs = ps.executeQuery();
    		return rs;
    	}
    	
    	//根据书名查询书籍信息
    	public ResultSet getBooksInfoByBookName(String bookName) throws Exception{
    		String sql = "select bookId,bookName,author,pages,flag from t00_book where bookName like ?";
    		Connection connection = this.getConnection();
    		PreparedStatement ps = connection.prepareStatement(sql);
    		ps.setString(1, "%"+bookName+"%");
    		ResultSet rs = ps.executeQuery();
    		return rs;
    	}
    	
    	//根据状态查询书籍信息
    	public ResultSet getBooksInfoByflag(String flag) throws Exception{
    		String sql = "select bookId,bookName,author,pages,flag from t00_book where flag=?";
    		Connection connection = this.getConnection();
    		PreparedStatement ps = connection.prepareStatement(sql);
    		ps.setString(1, flag);
    		ResultSet rs = ps.executeQuery();
    		return rs;
    	}
    	
    	
    }

（9）在com.mhys.book.service包下创建BookService类，添加bookDao属性并注入属性值，然后声明 getAllBooksInfo() 方法和 getBooksInfoById()方法、getBooksInfoByBookName()方法、getBooksInfoByflag()方法、和print()方法，代码如下：

package com.mhys.book.service;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Component;
    
    import com.mhys.book.dao.BookDao;
    
    @Component
    public class BookService {
    	@Autowired
    	private BookDao bookDao;
    	
    	//查询全部
    	public void getAllBooksInfo() throws Exception{
    		ResultSet rs =  bookDao.getAllBooksInfo();
    		print(rs);
    	}
    	
    	//根据书籍编号查询信息
    	public void getBooksInfoById(String bookId) throws Exception{
    		ResultSet rs =  bookDao.getBooksInfoById(bookId);
    		print(rs);
    	}
    	
    	//根据书名查询书籍信息
    	public void getBooksInfoByBookName(String bookName) throws Exception{
    		ResultSet rs =  bookDao.getBooksInfoByBookName(bookName);
    		print(rs);
    	}
    	
    	//根据状态查询书籍信息
    	public void getBooksInfoByflag(String flag) throws Exception{
    		ResultSet rs = bookDao.getBooksInfoByflag(flag);
    		print(rs);
    	}
    	
    	//释放资源
    	public void close(String flag) throws Exception{
    		ResultSet rs = bookDao.getBooksInfoByflag(flag);
    		print(rs);
    	}
    	
    	//打印结果
    	public void print(ResultSet rs) throws SQLException{
    		if(rs==null){
    			System.out.println("查询无结果！");
    		}else{
    			System.out.println("编号\t\t书名\t\t作者\t\t总页数\t\t状态");
    			while (rs.next()) {
    			    for (int i = 1; i <= 5; i++) {
    			    	String value = "";
    			    	if(i==5){
    			    		value = rs.getString(i).equals("0")?"借出":"在库";
    			    	}else{
    			    		value = rs.getString(i);
    			    	}
    			    	System.out.print(value+"\t\t");
    			    }
    			    System.out.println("");
    			}
    		}
    		
    	}
    }

（10）在com.mhys.book.main包下创建MainClass类，添加bookService属性并注入属性值，然后声明main()方法、start()方法、printMenu()方法、getAllBooksInfo()方法、getBooksInfoById()方法、getBooksInfoByBookName()方法、getBooksInfoByflag()方法，代码如下：

package com.mhys.book.main;
    
    import java.util.Scanner;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.support.ClassPathXmlApplicationContext;
    import org.springframework.stereotype.Component;
    
    import com.mhys.book.service.BookService;
    
    @Component
    public class MainClass {
    	@Autowired
    	private BookService bookService;
    	
    	public static void main(String[] args) throws Exception {
    		ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
    		MainClass main = (MainClass) context.getBean("mainClass");
    		main.start();
    	}
    	
    	public void start() throws Exception{
    		Scanner scan = new Scanner(System.in);
    		System.out.println("----------欢迎使用图书管理查询系统----------");
    		printMenu();
    		
    		while(true)
    		{
    			//读取用户输入
    			int choice = scan.nextInt();
    			
    			if(choice == 5)
    			{
    				System.out.println("成功退出系统，欢迎再次光临！");
    				break;
    			}
    			switch(choice)//switch形式
    			{
    			case 1: getAllBooksInfo(); break;
    			case 2: getBooksInfoById(); break;
    			case 3: getBooksInfoByBookName(); break;
    			case 4: getBooksInfoByflag(); break;
    			default: System.out.println("输入非法"); 
    			continue;
    			}
    			printMenu(); 
    		}
    
    	}
    	
    	public void printMenu(){
    		System.out.println("请选择查询条件：1、查询全部\t2、根据书籍编号查询\t3、根据书籍名称查询\t4、根据状态查询\t5、退出系统");
    	}
    	
    	public void getAllBooksInfo() throws Exception{
    		bookService.getAllBooksInfo();
    	}
    	public void getBooksInfoById() throws Exception{
    		Scanner scan = new Scanner(System.in);
    		System.out.println("请输入书籍编号：");
    		String bookId = scan.next();
    		bookService.getBooksInfoById(bookId);
    	}
    	public void getBooksInfoByBookName() throws Exception{
    		Scanner scan = new Scanner(System.in);
    		System.out.println("请输入书名：");
    		String bookName = scan.next();
    		bookService.getBooksInfoByBookName(bookName);
    	}
    	public void getBooksInfoByflag() throws Exception{
    		Scanner scan = new Scanner(System.in);
    		System.out.println("请输入状态编号：0、借出\t1、在库");
    		String flag = scan.next();
    		bookService.getBooksInfoByflag(flag);
    	}
    }

[Link 1]: https://blog.csdn.net/weixin_44893902/article/details/116674135
[Link 2]: https://blog.csdn.net/weixin_44893902/article/details/116724692
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70]: /images/20221021/4477f371671548fdaacb19fd1f3a37e9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 1]: /images/20221021/254c7b8bdd53415f86a6cd3808d72a95.png