本文共 7788 字，大约阅读时间需要 25 分钟。

图书管理类项目

目的:展示所有图书(条件:图书名 借书人名 -->模糊查询)

(一)数据库表结构分析和建表语句所用到的sql语句

表结构

图书表:bookId bookName price store des bookType借出表:borrowId  uid bookId borrowDate  归还表:backId borrowId backDate delayMoney购买记录表:bookId buyID buyDate buyNum totalPrice用户表:userId userName grade phone userType图书类型表:typeId typeName defaultDate delayMoneyPerDay

建表语句

#图书表CREATE TABLE IF NOT EXISTS books( bookId INT(11) PRIMARY KEY AUTO_INCREMENT, bookName VARCHAR(10),  price DOUBLE(11,2),  store INT(11),  des VARCHAR(200),  bookType VARCHAR(20)  );  INSERT INTO books (bookName, price, store, des, bookType) VALUES ( '我从全世界路过', 25, 100, '张嘉佳著', 'a');  INSERT INTO books (bookName, price, store, des, bookType) VALUES ('西游记', 15, 200, '吴承恩著', 'b');  #借出表 CREATE TABLE borrow( 	borrowId INT(11)PRIMARY KEY AUTO_INCREMENT,	bookId INT(11), 	borrowDate DATE ,	uid INT(11));#归还表CREATE TABLE back( 	backId INT(11)PRIMARY KEY AUTO_INCREMENT,	borrowId INT(11),	backDate DATE, 	delayMoney DOUBLE(11,2) ); #购买记录表CREATE TABLE `library`.`buyRecord`( 	buyId INT(11) PRIMARY KEY AUTO_INCREMENT,	bookid INT(11), 	buyDate DATE, 	buyNu INT(11), 	totalPrice DOUBLE(11,2) );#用户表CREATE TABLE users(	userId INT(11) PRIMARY KEY AUTO_INCREMENT, 	userName VARCHAR(20), 	grade VARCHAR(20),	phone VARCHAR(11), 	userType INT(11),  ); INSERT INTO users (userName, grade, phone, userType) VALUES ('admin', 'xx', 'xx', 0); INSERT INTO users (userName, grade, phone, userType) VALUES ('小明', '大一', '15155689754', 1); #图书类型表CREATE TABLE booksType( 	typeId INT(11) PRIMARY KEY AUTO_INCREMENT, 	typeName VARCHAR(20), 	defaultDate INT(11), 	delayMoneyPerDay DOUBLE(11,2), ); INSERT INTO booksType (typeName, defaultDate, delayMoneyPerDay) VALUES ( 'a', 5, 1); INSERT INTO booksType (typeName, defaultDate, delayMoneyPerDay) VALUES ( 'b', 3, 0.5); #登录sql语句SELECT *FROM users uWHERE u.userName='admin';#图书名模糊查询语句 SELECT * FROM books b WHERE b.bookName LIKE CONCAT('%','我','%'); #根据借书人名查书名 SELECT * FROM books b WHERE b.bookId IN (              SELECT br.bookId              FROM borrow br              WHERE br.uid IN		(SELECT u.userId		FROM users u		WHERE u.userName like concat('%',#{userName},'%'))        );

(二)使用mybatis连接数据库,查询

1)导入相关jar包(连接数据库 mybatis junit等)

package exam.pojo;/** * @author sunyong * @date 2020/06/15 * @description * 图书类 */public class Books {    private int bookId;//书编号    private String bookName;//书名    private double price;//售出单价    private int store;//库存量    private String des;//描述    private String bookType;//图书类型    public int getBookId() {        return bookId;    }    public void setBookId(int bookId) {        this.bookId = bookId;    }    public String getBookName() {        return bookName;    }    public void setBookName(String bookName) {        this.bookName = bookName;    }    public double getPrice() {        return price;    }    public void setPrice(double price) {        this.price = price;    }    public int getStore() {        return store;    }    public void setStore(int store) {        this.store = store;    }    public String getDes() {        return des;    }    public void setDes(String des) {        this.des = des;    }    public String getBookType() {        return bookType;    }    public void setBookType(String bookType) {        this.bookType = bookType;    }    //重写tostring方便打印    @Override    public String toString() {        return "Books{" +                "bookId=" + bookId +                ", bookName='" + bookName + '\'' +                ", price=" + price +                ", store=" + store +                ", des='" + des + '\'' +                ", bookType='" + bookType + '\'' +                '}';    }    }

3)编写相关配置文件

### 设置###log4j.rootLogger = debug,stdout,D,E### 输出信息到控制抬 ###log4j.appender.stdout = org.apache.log4j.ConsoleAppenderlog4j.appender.stdout.Target = System.outlog4j.appender.stdout.layout = org.apache.log4j.PatternLayoutlog4j.appender.stdout.layout.ConversionPattern = [%-5p] %d{yyyy-MM-dd HH:mm:ss,SSS} method:%l%n%m%n### 输出DEBUG 级别以上的日志到=E://logs/error.log ###log4j.appender.D = org.apache.log4j.DailyRollingFileAppenderlog4j.appender.D.File = F://logs/log.loglog4j.appender.D.Append = truelog4j.appender.D.Threshold = DEBUG log4j.appender.D.layout = org.apache.log4j.PatternLayoutlog4j.appender.D.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss}  [ %t:%r ] - [ %p ]  %m%n### 输出ERROR 级别以上的日志到=E://logs/error.log ###log4j.appender.E = org.apache.log4j.DailyRollingFileAppenderlog4j.appender.E.File =E://logs/error.log log4j.appender.E.Append = truelog4j.appender.E.Threshold = ERROR log4j.appender.E.layout = org.apache.log4j.PatternLayoutlog4j.appender.E.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss}  [ %t:%r ] - [ %p ]  %m%n

数据库配置文件

driver=com.mysql.jdbc.Driverurl=jdbc:mysql://192.168.56.101:3306/library?relaxAutoCommit=true&zeroDateTimeBehavior=convertToNullusername=kb07password=ok

编写mybatis的配置文件(编写接口对应的xml文件将该xml文件引入mybatis配置文件的映射中)

4)新建dao层包,在该包下建一个图书表的dao接口和对应的映射文件–>需要注意的是映射文件中的命名空间为该类的全类名,每个子语句的id为方法名需要一一对应,然后在mybatis文件中引入该映射文件

package exam.dao;import exam.pojo.Books;import org.apache.ibatis.annotations.Param;import java.util.List;public interface BooksDao {    List
   
     getBookByName(@Param("name") String name);    List
    
      getBookByBorrowName(@Param("userName") String userName);}
    
   

映射文件:

   
       
            select *        from books b        where b.bookName like concat('%',#{name},'%');        
            select *        from books b        where b.bookId in (              select br.bookId              from borrow br              where br.uid in              (select u.userId              from users u              where u.userName like concat('%',#{userName},'%'))        );    
   

5)编写一个工具类与数据库之间建立会话

package exam.utils;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import java.io.IOException;import java.io.InputStream;/** * @author sunyong * @date 2020/06/09 * @description */public class MapperConfig {    private  static SqlSessionFactory factory ;    //静态代码块只加载一次    static {        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();        InputStream is = null;        try {             is= Resources.getResourceAsStream("mybatis-config.xml");            factory=builder.build(is);        } catch (IOException e) {            e.printStackTrace();        }finally {            try {                is.close();            } catch (IOException e) {                e.printStackTrace();            }        }    }    //私有构造    private MapperConfig(){}    //得到SqlSession    public static SqlSession getSession(){        return factory.openSession(true);    }}

6)建立图书表的测试类测试程序

package exam.dao;import exam.pojo.Books;import exam.pojo.Users;import exam.utils.MapperConfig;import org.apache.ibatis.session.SqlSession;import org.junit.Test;import java.util.List;import static org.junit.Assert.*;public class BooksDaoTest {//测试书名模糊查询    @Test    public void test(){        SqlSession session = MapperConfig.getSession();        List
   
     booksList = session.getMapper(BooksDao.class).getBookByName("我");        System.out.println(booksList);    }    //测试借书人名模糊查询    @Test    public void test1(){        SqlSession session = MapperConfig.getSession();        List
    
      booksList = session.getMapper(BooksDao.class).getBookByBorrowName("小");        System.out.println(booksList);    }}
    
   

7)测试结果

转载地址：http://qbjxi.baihongyu.com/