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 层代码了，详情可见 图书管理系统实战（二）
