SpringBoot整合系列-整合MyBatis
出处地址：https://www.cnblogs.com/V1haoge/p/9971036.html
SpringBoot整合Mybatis
步骤
第一步：添加必要的jar包
<dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.1</version></dependency>
第二步：添加必要的配置
application.properties
##配置数据源spring.datasource.url = jdbc:h2:mem:dbtest spring.datasource.username = sa spring.datasource.password = sa spring.datasource.driverClassName =org.h2.Driver
第三步：添加配置类
// 该配置类用于配置自动扫描器，用于扫描自定义的mapper接口,MyBatis会针对这些接口生成代理来调用对应的XMl中的SQL@Configuration@MapperScan("com.example.springbootdemo.mapper")public class MyBatisConfig {
}这个注解必须手动配置是因为mapper接口的位置完全就是用户自定义的，自动配置的时候也不可能找到还不存在的位置。
第四步：定义实体类型
@Data@NoArgsConstructor@AllArgsConstructor@ToString@EqualsAndHashCode@Builder@ApiModel("书籍模型")public class Book { @ApiModelProperty(value = "书籍ID", notes = "书籍ID",example = "1") private Integer bookId; @ApiModelProperty(value = "书籍页数", notes = "书籍页数",example = "100") private Integer pageNum; @ApiModelProperty(value = "书籍名称", notes = "书籍名称",example = "Java编程思想") private String bookName; @ApiModelProperty(value = "书籍类型", notes = "书籍类型",hidden = false) private BookType BookType; @ApiModelProperty(value = "书籍简介") private String bookDesc; @ApiModelProperty(value = "书籍价格") private Double bookPrice; @ApiModelProperty(value = "创建时间",hidden = true) private LocalDateTime createTime; @ApiModelProperty(value = "修改时间",hidden = true) private LocalDateTime modifyTime;
}还有一个枚举类型
public enum BookType {
TECHNOLOGY,//技术
LITERARY,//文学
HISTORY//历史
;
}实体类中使用了swagger2和Lombok中的注解，需要添加对应的jar包
第五步：定义mapper接口
public interface BookRepository { int addBook(Book book); int updateBook(Book book); int deleteBook(int id); Book getBook(int id); List<Book> getBooks(Book book);
}第六步：定义mapper配置
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.example.springbootdemo.mapper.BookRepository">
<insert id="addBook" parameterType="Book">
INSERT INTO BOOK( <if test="pageNum != null">
PAGE_NUM, </if>
<if test="bookType != null">
BOOK_TYPE, </if>
<if test="bookName != null">
BOOK_NAME, </if>
<if test="bookDesc != null">
BOOK_DESC, </if>
<if test="bookPrice != null">
BOOK_PRICE, </if>
CREATE_TIME,
MODIFY_TIME)
VALUES ( <if test="pageNum != null">
#{pageNum}, </if>
<if test="bookType != null">
#{bookType}, </if>
<if test="bookName != null">
#{bookName}, </if>
<if test="bookDesc != null">
#{bookDesc}, </if>
<if test="bookPrice != null">
#{bookPrice}, </if>
sysdate,sysdate) </insert>
<update id="updateBook" parameterType="Book">
UPDATE BOOK SET <if test="pageNum != null">
PAGE_NUM = #{pageNum}, </if>
<if test="bookType != null">
BOOK_TYPE = #{bookType}, </if>
<if test="bookDesc != null">
BOOK_DESC = #{bookDesc}, </if>
<if test="bookPrice != null">
BOOK_PRICE = #{bookPrice}, </if>
<if test="bookName != null">
BOOK_NAME = #{bookName}, </if>
MODIFY_TIME=sysdate
WHERE 1=1 <if test="bookId != null">
and BOOK_ID = #{bookId} </if>
</update>
<delete id="deleteBook" parameterType="int">
delete from BOOK where BOOK_id=#{bookId} </delete>
<select id="getBook" parameterType="int" resultMap="bookResultMap">
select * from BOOK where BOOK_ID=#{bookId} </select>
<select id="getBooks" resultMap="bookResultMap">
select * from BOOK WHERE 1=1 <if test="bookId != null">
and BOOK_ID = #{bookId} </if>
<if test="pageNum != null">
and PAGE_NUM = #{pageNum} </if>
<if test="bookType != null">
and BOOK_TYPE = #{bookType} </if>
<if test="bookDesc != null">
and BOOK_DESC = #{bookDesc} </if>
<if test="bookPrice != null">
and BOOK_PRICE = #{bookPrice} </if>
<if test="bookName != null">
and BOOK_NAME = #{bookName} </if>
</select>
<resultMap id="bookResultMap" type="Book">
<id column="BOOK_ID" property="bookId"/>
<result column="PAGE_NUM" property="pageNum"/>
<result column="BOOK_NAME" property="bookName"/>
<result column="BOOK_TYPE" property="bookType"/>
<result column="BOOK_DESC" property="bookDesc"/>
<result column="BOOK_PRICE" property="bookPrice"/>
<result column="CREATE_TIME" property="createTime"/>
<result column="MODIFY_TIME" property="modifyTime"/>
</resultMap></mapper>在这个配置文件中我们使用了MyBatis的动态SQL和参数映射
第七步：再次添加必要的配置
application.properties
#配置Xml配置的位置mybatis.mapper-locations=classpath*:/mapper/*.xml#配置实体类型别名mybatis.type-aliases-package=com.example.springbootdemo.entity
这里的两个配置也和之前的扫描器注解一样，都是自动配置时未知的，需要手动配置，当然可能会存在默认的位置，但是一旦我们自定义了，就必须手动添加配置
第八步：定义service和controller
@Service@Log4j2public class BookService {
@Autowired
private BookRepository bookRepository;
public ResponseEntity<Book> addBook(final Book book) { int num = bookRepository.addBook(book); return ResponseEntity.ok(book);
}
public ResponseEntity<Integer> updateBook(final Book book){ return ResponseEntity.ok(bookRepository.updateBook(book));
}
public ResponseEntity<Integer> deleteBook(final int bookId){ return ResponseEntity.ok(bookRepository.deleteBook(bookId));
}
public ResponseEntity<Book> getBook(final int bookId) {
Book book = bookRepository.getBook(bookId); return ResponseEntity.ok(book);
}
public ResponseEntity<List<Book>> getBooks(final Book book){ return ResponseEntity.ok(bookRepository.getBooks(book));
}
}@RestController@RequestMapping("/book")@Api(description = "书籍接口")@Log4j2public class BookApi { @Autowired
private BookService bookService;
@RequestMapping(value = "/addBook", method = RequestMethod.PUT) @ApiOperation(value = "添加书籍", notes = "添加一本新书籍", httpMethod = "PUT") public ResponseEntity<Book> addBook(final Book book){ return bookService.addBook(book);
}
@RequestMapping(value = "/updateBook", method = RequestMethod.POST) @ApiOperation(value = "更新书籍", notes = "根据条件更新书籍信息", httpMethod = "POST") public ResponseEntity<Integer> updateBook(final Book book){ return bookService.updateBook(book);
}
@RequestMapping(value = "/deleteBook", method = RequestMethod.DELETE) @ApiOperation(value = "获取一本书籍", notes = "根据ID获取书籍", httpMethod = "DELETE") public ResponseEntity<Integer> deleteBook(final int bookId){ return bookService.deleteBook(bookId);
}
@RequestMapping(value = "/getBook", method = RequestMethod.GET) @ApiOperation(value = "获取一本书籍", notes = "根据ID获取书籍", httpMethod = "GET") public ResponseEntity<Book> getBook(final int bookId){ return bookService.getBook(bookId);
}
@RequestMapping(value = "/getBooks", method = RequestMethod.GET) @ApiOperation(value = "获取书籍", notes = "根据条件获取书籍", httpMethod = "GET") public ResponseEntity<List<Book>> getBooks(final Book book){ return bookService.getBooks(book);
}
}这里使用了swagger2的注解
至此设置完毕。
第十步：浏览器访问
http://localhost:8080/swagger-ui.html
通过swagger界面可以看到我们定义的接口。
高级功能
分页（两种，简单分页RowBounds和拦截器分页，插件）
RowBounds分页
使用RowBounds分页适用于小数据量的分页查询
使用方式是在查询的Mapper接口上添加RowBounds参数即可，service传参时需要指定其两个属性，当前页和每页数
1-定义分页模型
@Data@Builder@ToString@EqualsAndHashCode@NoArgsConstructor@AllArgsConstructorpublic class MyPage<T> { private Integer pageId;//当前页
private Integer pageNum;//总页数
private Integer pageSize;//每页数
private Integer totalNum;//总数目
private List<T> body;//分页结果
private Integer srartIndex;//开始索引
private boolean isMore;//是否有下一页}2-定义mapper
public interface BookRepository {
// 省略多余内容
int count(Book book); List<Book> getBooks(Book book, RowBounds rowBounds);
}BookRepository.xml
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.example.springbootdemo.mapper.BookRepository">
<!--省略多余内容-->
<select id="getBooks" resultMap="bookResultMap">
select * from BOOK WHERE 1=1 <if test="bookId != null">
and BOOK_ID = #{bookId} </if>
<if test="pageNum != null">
and PAGE_NUM = #{pageNum} </if>
<if test="bookType != null">
and BOOK_TYPE = #{bookType} </if>
<if test="bookDesc != null">
and BOOK_DESC = #{bookDesc} </if>
<if test="bookPrice != null">
and BOOK_PRICE = #{bookPrice} </if>
<if test="bookName != null">
and BOOK_NAME = #{bookName} </if>
</select>
<select id="count" resultType="int">
select count(1) from BOOK WHERE 1=1 <if test="bookId != null">
and BOOK_ID = #{bookId} </if>
<if test="pageNum != null">
and PAGE_NUM = #{pageNum} </if>
<if test="bookType != null">
and BOOK_TYPE = #{bookType} </if>
<if test="bookDesc != null">
and BOOK_DESC = #{bookDesc} </if>
<if test="bookPrice != null">
and BOOK_PRICE = #{bookPrice} </if>
<if test="bookName != null">
and BOOK_NAME = #{bookName} </if>
</select>
<resultMap id="bookResultMap" type="Book">
<id column="BOOK_ID" property="bookId"/>
<result column="PAGE_NUM" property="pageNum"/>
<result column="BOOK_NAME" property="bookName"/>
<result column="BOOK_TYPE" property="bookType"/>
<result column="BOOK_DESC" property="bookDesc"/>
<result column="BOOK_PRICE" property="bookPrice"/>
<result column="CREATE_TIME" property="createTime"/>
<result column="MODIFY_TIME" property="modifyTime"/>
</resultMap></mapper>3-定义service
@Service@Log4j2public class BookService {
@Autowired
private BookRepository bookRepository; // 省略多余内容
// 使用RowBounds实现分页
public ResponseEntity<MyPage<Book>> getBooksByRowBounds(int pageId,int pageSize){
MyPage<Book> myPage = new MyPage<>();
myPage.setPageId(pageId);
myPage.setPageSize(pageSize);
List<Book> books = bookRepository.getBooks(Book.builder().build(), new RowBounds(pageId,pageSize)); int totalNum = bookRepository.count(Book.builder().build());
myPage.setBody(books);
myPage.setTotalNum(totalNum); return ResponseEntity.ok(myPage);
}
}4-定义controller
@RestController@RequestMapping("/book")@Api(description = "书籍接口")@Log4j2public class BookApi {
@Autowired
private BookService bookService; // 省略多余内容
@RequestMapping(value = "/getBooksPageByRowBounds", method = RequestMethod.GET) @ApiOperation(value = "分页获取书籍", notes = "通过RowBounds分页获取书籍", httpMethod = "GET") public ResponseEntity<PageInfo<Book>> getBooksPageByRowBounds(final int pageId, final int pageNum){ return bookService.getBooksByRowBounds(pageId, pageNum);
}
}拦截器分页
当面对大数据量的分页时，RowBounds就力不从心的，这时需要我们使用分页拦截器实现分页。
这里其实可以直接使用插件PageHelper，其就是以拦截器技术实现的分页查询插件。
具体使用方法见SpringBoot整合MyBatis分页插件PageHelper
自定义类型转换器（枚举转换器）
public class BookTypeEnumHandler extends BaseTypeHandler<BookType> { /**
* 用于定义设置参数时，该如何把Java类型的参数转换为对应的数据库类型
*/
@Override
public void setNonNullParameter(PreparedStatement ps, int i, BookType parameter, JdbcType jdbcType) throws SQLException { int j = 0; for (BookType bookType : BookType.values()){ if(bookType.equals(parameter)){
ps.setString(i, j +""); return;
}
j++;
}
} /**
* 用于定义通过字段名称获取字段数据时，如何把数据库类型转换为对应的Java类型
*/
@Override
public BookType getNullableResult(ResultSet rs, String columnName) throws SQLException { int j = Integer.valueOf(rs.getString(columnName)); if(j >= BookType.values().length) { return null;
} int i = 0; for(BookType bookType:BookType.values()){ if(j == i){ return bookType;
}
i++;
} return null;
} /**
* 用于定义通过字段索引获取字段数据时，如何把数据库类型转换为对应的Java类型
*/
@Override
public BookType getNullableResult(ResultSet rs, int columnIndex) throws SQLException { return null;
} /**
* 用定义调用存储过程后，如何把数据库类型转换为对应的Java类型
*/
@Override
public BookType getNullableResult(CallableStatement cs, int columnIndex) throws SQLException { return null;
}
}使用@Mapper（不常用，可不看）
注意：使用@Mapper注解的时候是不需要添加xml配置Mapper文件的，SQL脚本在接口方法的注解内部定义
第一步：定义实体类
@Data@Builder@ToString@EqualsAndHashCode@NoArgsConstructor@AllArgsConstructorpublic class Tree { private Integer treeId; private String treeName; private Integer treeAge; private Double treeHight; private TreeType treeType; private TreeState treeState; private String treeDesc;
}第二步：定义持久层
@Mapperpublic interface TreeRepository {
@Insert("INSERT INTO TREE (TREE_NAME,TREE_AGE,TREE_HIGHT,TREE_TYPE,TREE_STATE,TREE_DESC) VALUES (#{treeName},#{treeAge},#{treeHight},#{treeType},#{treeState},#{treeDesc}) ") int addTree(Tree tree);
// 此处treeState是一个枚举，此处执行一直报错
@Update("UPDATE TREE SET TREE_STATE=#{treeState} WHERE TREE_ID=#{treeId}") int updateState(final int treeId, final TreeState treeState);
@Delete("DELETE FROM TREE WHERE TREE_ID=#{treeId}") int deleteTree(final int treeId);
@Results({ @Result(id = true, column = "TREE_ID",property = "treeId"), @Result(column = "TREE_NAME",property = "treeName"), @Result(column = "TREE_AGE", property = "treeAge"), @Result(column = "TREE_HIGHT",property = "treeHight"), @Result(column = "TREE_TYPE",property = "treeType",typeHandler = EnumOrdinalTypeHandler.class), @Result(column = "TREE_STATE",property = "treeState",typeHandler = EnumOrdinalTypeHandler.class), @Result(column = "TREE_DESC", property = "treeDesc")
}) @Select("SELECT * FROM TREE WHERE TREE_ID=#{treeId}") Tree getTree(final int treeId);
@Results({ @Result(id = true, column = "TREE_ID",property = "treeId"), @Result(column = "TREE_NAME",property = "treeName"), @Result(column = "TREE_AGE", property = "treeAge"), @Result(column = "TREE_HIGHT",property = "treeHight"), @Result(column = "TREE_TYPE",property = "treeType",typeHandler = EnumOrdinalTypeHandler.class), @Result(column = "TREE_STATE",property = "treeState",typeHandler = EnumOrdinalTypeHandler.class), @Result(column = "TREE_DESC", property = "treeDesc")
}) @Select("SELECT * FROM TREE") List<Tree> getTrees(RowBounds rowBounds);
}注意：重点就在这个接口中，我们添加接口注解@Mapper，表示这是一个持久层Mapper,它的实例化依靠SpringBoot自动配置完成。
在接口方法上直接添加对应的执行注解，在注解中直接定义SQL，这种SQL仍然可以使用表达式#{}来获取参数的值。
注意@Result注解中定义的两个关于枚举的类型处理器EnumOrdinalTypeHandler，其实其为MyBatis内部自带的两种枚举处理器之一，
用于存储枚举序号，还有一个EnumTypeHandler用于存储枚举名称。
第三步：定义service和controller
@Service@Log4j2public class TreeService {
@Autowired
private TreeRepository treeRepository;
public ResponseEntity<Tree> addTree(final Tree tree){
treeRepository.addTree(tree); return ResponseEntity.ok(tree);
}
public ResponseEntity<Tree> updateTree(final int treeId, final TreeState treeState){
treeRepository.updateState(treeId,treeState); return ResponseEntity.ok(Tree.builder().treeId(treeId).treeState(treeState).build());
}
public ResponseEntity<Integer> deleteTree(final int treeId){ return ResponseEntity.ok(treeRepository.deleteTree(treeId));
}
public ResponseEntity<Tree> getTree(final int treeId){ return ResponseEntity.ok(treeRepository.getTree(treeId));
}
public ResponseEntity<MyPage<Tree>> getTrees(final int pageId,final int pageSize){
List<Tree> trees = treeRepository.getTrees(new RowBounds(pageId,pageSize));
MyPage<Tree> treeMyPage = new MyPage<>();
treeMyPage.setPageId(pageId);
treeMyPage.setPageSize(pageSize);
treeMyPage.setBody(trees); return ResponseEntity.ok(treeMyPage);
}
}@RestController@RequestMapping("/tree")@Api(description = "树木接口")public class TreeApi {
@Autowired
private TreeService treeService;
@RequestMapping(value = "/addTree",method = RequestMethod.PUT) @ApiOperation(value = "添加树木",notes = "添加新树木",httpMethod = "PUT") public ResponseEntity<Tree> addTree(final Tree tree){ return treeService.addTree(tree);
}
@RequestMapping(value = "/updateTree",method = RequestMethod.POST) @ApiOperation(value = "更新状态",notes = "修改树木状态",httpMethod = "POST") public ResponseEntity<Tree> updateTree(final int treeId,final TreeState treeState){ return treeService.updateTree(treeId,treeState);
}
@ApiOperation(value = "获取树木",notes = "根据ID获取一棵树",httpMethod = "GET") @RequestMapping(value = "/getTree",method = RequestMethod.GET) public ResponseEntity<Tree> getTree(final int treeId){ return treeService.getTree(treeId);
}
}注意：这个例子中更新状态的时候还是无法成功，这个状态是枚举值
點擊查看更多內容
為 TA 點贊
評論
評論
共同學習，寫下你的評論
評論加載中...
作者其他優質文章
正在加載中
感謝您的支持，我會繼續努力的～
掃碼打賞，你說多少就多少
贊賞金額會直接到老師賬戶
支付方式
打開微信掃一掃，即可進行掃碼打賞哦