图书管理系统（三）图书管理系统实战（一）

2021-10-04 22:36:18

1. 准备数据库

新建数据库 bookmanager，然后创建两张表：图书表 book 和预约图书表 appointment；

-- 建数据库
CREATE DATABASE `bookmanager`;

-- 创建图书表
CREATE TABLE `book` (
    `book_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '图书ID',
    `name` varchar(100) NOT NULL COMMENT '图书名称',
    `number` int(11) NOT NULL COMMENT '馆藏数量',
    PRIMARY KEY (`book_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='图书表';

-- 插入数据
INSERT INTO `book`(`book_id`, `name`, `number`) VALUES (1, "Effective Java", 10),(2, "算法", 10),(3, "MySQL 必知必会", 10);

-- 创建预约图书表
CREATE TABLE `appointment` (
    `book_id` int(11) NOT NULL COMMENT '图书ID',
    `student_id` int(11) NOT NULL COMMENT '学号',
    `appoint_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '预约时间' ,
    PRIMARY KEY (`book_id`, `student_id`),
    INDEX `idx_appoint_time` (`appoint_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='预约图书表';

2. 实体类编写

数据库准备好之后，就可以给对应表创建实体类，创建实体类之前，我们可以在 pom.xml 中引入 lombok 依赖，减少代码的编写；

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.12</version>
</dependency>

2.1 Book.java

package com.cunyu.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @author : cunyu
 * @version : 1.0
 * @className : Book
 * @date : 2020/7/23 15:53
 * @description : Book 实体类
 */

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Book {
    private int bookId;
    private String name;
    private int number;
}

2.2 Appointment.java

package com.cunyu.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

/**
 * @author : cunyu
 * @version : 1.0
 * @className : Appointment
 * @date : 2020/7/23 15:57
 * @description : Appointment 实体类
 */

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Appointment {
    private int bookId;
    private int studentId;
    private Date appointTime;
    private Book book;
}

3. dao 接口类编写

3.1 BookDao.java

package com.cunyu.dao;

import com.cunyu.pojo.Book;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @InterfaceName : BookDao
 * @Author : cunyu
 * @Date : 2020/7/23 16:02
 * @Version : 1.0
 * @Description : Book 接口
 **/

public interface BookDao {

    /**
     * @param bookId 图书 id
     * @return 对应 id 的图书
     * @description 根据图书 id 查找对应图书
     * @date 2020/7/23 16:04
     * @author cunyu1943
     * @version 1.0
     */
    Book queryById(@Param("bookId") int bookId);

    /**
     * @param offset 查询起始位置
     * @param limit  查询条数
     * @return 查询出的所有图书列表
     * @description 查询所有图书
     * @date 2020/7/23 16:08
     * @author cunyu1943
     * @version 1.0
     */
    List<Book> queryAll(@Param("offset") int offset, @Param("limit") int limit);

    /**
     * @param bookId 图书 id
     * @return 更新的记录行数
     * @description 借阅后更新馆藏
     * @date 2020/7/23 16:09
     * @author cunyu1943
     * @version 1.0
     */
    int reduceNumber(@Param("bookId") int bookId);
}

3.2 AppointmentDao.java

package com.cunyu.dao;

import com.cunyu.pojo.Appointment;
import org.apache.ibatis.annotations.Param;

/**
 * @InterfaceName : AppointmentDao
 * @Author : cunyu
 * @Date : 2020/7/23 16:03
 * @Version : 1.0
 * @Description : Appointment 接口
 **/

public interface AppointmentDao {

    /**
     * @param bookId    图书 id
     * @param studentId 学生 id
     * @return 插入的行数
     * @description 插入预约图书记录
     * @date 2020/7/23 16:13
     * @author cunyu1943
     * @version 1.0
     */
    int insertAppointment(@Param("bookId") int bookId, @Param("studentId") int studentId);

    /**
     * @param bookId    图书 id
     * @param studentId 学生 id
     * @return
     * @description 通过主键查询预约图书记录，并且携带图书实体
     * @date 2020/7/23 16:16
     * @author cunyu1943
     * @version 1.0
     */
    Appointment queryByKeyWithBook(@Param("bookId") int bookId, @Param("studentId") int studentId);
}

3.3 mapper 编写

编写好 dao 接口之后，并不需要我们自己去实现，MyBatis 会给我们动态实现，但是需要我们配置相应的 mapper。在 src/main/resources/mapper 下新建 BookDao.xml 和 AppointmentDao.xml，用于对应上面的 dao 接口；

3.3.1 BookDao.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cunyu.dao.BookDao">
    <select id="queryById" resultType="Book" parameterType="int">
        SELECT book_id, name, number
        FROM book
        WHERE book_id = #{bookId}
    </select>

    <select id="queryAll" resultType="Book">
        SELECT *
        FROM book
        ORDER BY book_id
        LIMIT #{offset},#{limit}
    </select>

    <update id="reduceNumber">
        UPDATE book
        SET number = number - 1
        WHERE book_id = #{bookId}
          AND number > 0
    </update>
</mapper>

3.3.2 AppointmentDao.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cunyu.dao.AppointmentDao">
    <insert id="insertAppointment">
        <!-- ignore 主键冲突，报错 -->
        INSERT ignore INTO appointment (book_id, student_id) VALUES (#{bookId}, #{studentId})
    </insert>

    <select id="queryByKeyWithBook" resultType="Appointment">
        <!-- 告知MyBatis 把结果映射到 Appointment 的同时映射 Book 属性 -->
        SELECT
        appointment.book_id,
        appointment.student_id,
        appointment.appoint_time,
        book.book_id "book.book_id",
        book.`name` "book.name",
        book.number "book.number"
        FROM
        appointment
        INNER JOIN book ON appointment.book_id = book.book_id
        WHERE
        appointment.book_id = #{bookId}
        AND appointment.student_id = #{studentId}
    </select>
</mapper>

4. 测试

经过 准备数据库 -> 实体类编写 -> 接口类编写 -> mapper 配置 这一套流程之后，我们就可以进行模块化测试了，看看我们的接口是否成功实现。

4.1 BookDaoTest.java

package com.cunyu.dao;

import com.cunyu.pojo.Book;
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 java.util.List;

/**
 * @author : cunyu
 * @version : 1.0
 * @className : BookDaoTest
 * @date : 2020/7/23 18:02
 * @description : BookDao 测试类
 */

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:spring/spring-*.xml")
public class BookDaoTest {
    // 自动注入
    @Autowired
    private BookDao bookDao;

    @Test
    public void testQueryById() {
        int bookId = 1;
        Book book = bookDao.queryById(bookId);
        System.out.println("ID 对应的图书信息：" + book);
    }

    @Test
    public void testQueryAll() {
        List<Book> bookList = bookDao.queryAll(0, 3);
        System.out.println("所有图书信息：");
        for (Book book : bookList
        ) {
            System.out.println(book);
        }

    }

    @Test
    public void testReduceNumber() {
        int bookId = 3;
        int update = bookDao.reduceNumber(bookId);
        System.out.println("update = " + update);
    }
}

运行两次测试后，数据库的结果如下图：图书管理系统（三）图书管理系统实战（一）

4.2 AppointmentDaoTest.java

package com.cunyu.dao;

import com.cunyu.pojo.Appointment;
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;

/**
 * @author : cunyu
 * @version : 1.0
 * @className : AppointmentDaoTest
 * @date : 2020/7/23 18:21
 * @description : AppointmentDao 测试
 */

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:spring/spring-*.xml")
public class AppointmentDaoTest {

    @Autowired
    AppointmentDao appointmentDao;

    @Test
    public void testInsertAppointment() {
        int bookId = 2;
        int studentId = 18301333;
        int insert = appointmentDao.insertAppointment(bookId, studentId);
        System.out.println("Insert = " + insert);
    }

    @Test
    public void testQueryByKeyWithBook(){
        int bookId = 2;
        int studentId = 18301333;
        Appointment appointment=appointmentDao.queryByKeyWithBook(bookId,studentId);
        System.out.println(appointment);
        System.out.println(appointment.getBook());
    }
}

预约后，appointment 表中插入记录；

图书管理系统（三）图书管理系统实战（一） 5. 总结

至此，我们做的工作总结下来主要有如下几点：

设计数据库

创建实体类

编写 dao 接口类

编写 dao 接口对应 mapper，交由 MyBatis 动态实现

对 dao 接口方法实现进行测试

好了，图书管理系统第一阶段到此就结束了，下一步我们就可以对其进行优化，并编写 service 层和 controller 层代码了，详情可见图书管理系统实战（二）

码农公寓