配置文件
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- Mybatis -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.1</version>
</dependency>
<!-- MySQL 驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- lombok 简化 Java 代码 -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
</dependencies>
spring:
datasource:
url: jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
编写代码
/*
Navicat Premium Data Transfer
Source Server : mysql57
Source Server Type : MySQL
Source Server Version : 50728
Source Host : localhost:3306
Source Schema : lms
Target Server Type : MySQL
Target Server Version : 50728
File Encoding : 65001
Date: 22/02/2020 18:02:10
*/
-- ----------------------------
-- Table structure for t_book
-- ----------------------------
DROP TABLE IF EXISTS `t_book`;
CREATE TABLE `t_book` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`isbn` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`book_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`author` varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`book_pub` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`book_pub_time` date NULL DEFAULT NULL,
`book_num` smallint(5) UNSIGNED NOT NULL,
`book_stock` smallint(5) UNSIGNED NOT NULL,
`create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
`update_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '图书表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of t_book
-- ----------------------------
INSERT INTO `t_book` VALUES (1, '978-7-300-19900-9', '觅渡（修订版）', '梁衡', '中国人民大学出版社', '2014-09-02', 98, 99, '2020-02-22 11:11:20', '2020-02-22 11:55:00');
@Data
public class Book {
private Long id;
private String isbn;
private String bookName;
private String author;
private String bookPub;
private String bookPubTime;
private Integer bookNum;
private Integer bookStock;
}
/**
* 若此处不写 @Mapper 注解，则需要在启动类上加入 @MapperScan()，并指明 Mapper 所在的包
* 例如：@MapperScan("com.jeson.dao")
**/
@Mapper
public interface BookMapper {
/**
* 返回所有图书信息
* Results 注解解决 实体属性名 和 数据库字段名 不一致的问题
* @return List<Book>
*/
@Select("select * from t_book")
@Results(id = "bookResultMap", value = {
@Result(property = "bookName", column = "book_name"),
@Result(property = "bookPub", column = "book_pub"),
@Result(property = "bookPubTime", column = "book_pub_time"),
@Result(property = "bookNum", column = "book_num"),
@Result(property = "bookStock", column = "book_stock"),
})
List<Book> selectAll();
/**
* 查询图书 ID 并返回对应图书信息
* ResultMap 注解可以通过 id 直接引用已经定义的 Results
* @param id 图书 ID
* @return Book
*/
@Select("select * from t_book where id = #{id}")
@ResultMap("bookResultMap")
Book selectById(@Param("id")Long id);
/**
* 查询图书名字并返回对应图书信息
* 模糊查询须用 concat() 连接字符串
* 否则报错 Parameter index out of range (1 > number of parameters, which is 0)
* @param bookName 图书名字
* @return Book
*/
@Select("select * from t_book where book_name like concat('%',#{bookName},'%')")
@ResultMap("bookResultMap")
Book selectByBookName(@Param("bookName")String bookName);
/**
* 插入一条图书信息，返回插入行数
* t_book 表中 id, create_time, update_time 由数据库自动生成
* @param book 图书实体类
* @return int
*/
@Insert("insert into t_book(isbn, book_name, author, book_pub, book_pub_time, book_num, book_stock) " +
"values(#{isbn}, #{bookName}, #{author}, #{bookPub}, #{bookPubTime}, #{bookStock}, #{bookStock})")
int insert(Book book);
/**
* 通过图书 ID 删除对应图书信息，返回删除行数
* @param id 图书 ID
* @return int
*/
@Delete("delete from t_book where id = #{id}")
int delete(@Param("id")Long id);
/**
* 通过图书 ID 更新对应图书信息，返回更改行数
* @param book 图书实体类
* @return int
*/
@Update("update t_book set isbn = #{isbn}, book_name = #{bookName}, author = #{author}, book_pub = #{bookPub}, " +
"book_pub_time = #{bookPubTime}, book_num = #{bookNum}, book_stock = #{bookStock} where id = #{id}")
int update(Book book);
}
- 控制器 BookController（仅为示例，未编写 Service 层代码）
@RestController
public class BookController {
@Autowired
BookMapper bookMapper;
@GetMapping("/findAll")
public List<Book> findAll() {
return bookMapper.selectAll();
}
@GetMapping("/find/{id}")
public Book findById(@PathVariable("id") Long id) {
return bookMapper.selectById(id);
}
/**
* 传参形式为 k = v 时的写法
* @GetMapping("/findById")
* public Book findById(@RequestParam("id") Long id) {
* return bookMapper.selectById(id);
* }
**/
@PostMapping("/insert")
public int insert(@RequestBody Book book) {
return bookMapper.insert(book);
}
@PutMapping("/update")
public int update(@RequestBody Book book) {
return bookMapper.update(book);
}
@DeleteMapping("/delete/{id}")
public int delete(@PathVariable("id") Long id) {
return bookMapper.delete(id);
}
}
其他问题
