本文知识点：
springboot如何集成mybatis
springboot如何集成通用mapper
springboot如何集成pagehelper分页插件
如何通过xml、通用mapper和注解这三种方式查询数据库
注[1]：本文（本系列）所有涉及到数据库的内容，默认使用MySQL5.6，高于或低于这个版本时可能会存在兼容问题，具体问题，请自行查阅相关资料。
注[2]：本文例子中涉及到Freemarker相关内容，请参考springboot整合Freemark模板(修订-详尽版)
准备工作
目录结构
└─me
└─zhyd
└─springboot
└─mybatis
├─config
├─controller
├─entity
├─mapper
├─service
│ └─impl
└─util
准备数据库
DROP TABLE IF EXISTS `message`;
CREATE TABLE `message` (
`id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`nick_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '昵称',
`ip` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'IP',
`insert_time` datetime(0) NULL DEFAULT NULL COMMENT '提交时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
注：为方便测试，此处可以使用存储过程批量插入一些测试例子
DROP PROCEDURE IF EXISTS `autoInsert`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `autoInsert`()
BEGIN
DECLARE
i INT DEFAULT 0 ; -- 开始
SET autocommit = 0 ; -- 结束
WHILE (i <= 100) DO
REPLACE INTO message (
`id`,
`nick_name`,
`ip`,
`insert_time`
)
VALUE
(
i,
'码一码',
'127.0.0.1',
NOW()
) ;
SET i = i + 1 ;
END
WHILE ;
SET autocommit = 1 ; COMMIT ;
END
;;
delimiter ;
使用call autoInsert();调用存储过程即可。本例使用100条数据作为测试
添加依赖
<!--springboot数据持久化所需jar配置 start -->
<!--支持使用 JDBC 访问数据库 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!--mybatis-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.1</version>
</dependency>
<!--mapper-->
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
<version>1.1.4</version>
</dependency>
<!--pagehelper-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.9</version>
</dependency>
<!--mysql-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!--springboot数据持久化所需jar配置 end -->
配置属性文件
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/springboot_learning?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: root
# MyBatis
mybatis:
type-aliases-package: com.zyd.mybatis.com.rest.entity
mapper-locations: classpath:/mybatis/*.xml
# mapper
mapper:
mappers:
- me.zhyd.springboot.mybatis.util.BaseMapper
not-empty: false
identity: MYSQL
# pagehelper
pagehelper:
helper-dialect: mysql
reasonable: "true"
support-methods-arguments: "true"
params: count=countSql
配置mybatis
@Component
@MapperScan("me.zhyd.springboot.mybatis.mapper")
public class MybatisConfig {
}
配置BaseMapper
public interface BaseMapper<T> extends Mapper<T>, MySqlMapper<T> {
}
bean实体
public class Message implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String nickName;
private String ip;
private Date insertTime;
// getter setter 略
}
编写mapper.xml
mapper.xml主要用来解决通用mapper无法处理的查询请求。比如模糊搜索，比如多表关联查询等
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//testMybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="me.zhyd.springboot.mybatis.mapper.MessageMapper">
<resultMap id="message_map" type="me.zhyd.springboot.mybatis.bean.Message">
<id property="id" column="ID" jdbcType="INTEGER"></id>
<result property="ip" column="IP" jdbcType="VARCHAR"></result>
<result property="insertTime" column="INSERT_TIME" jdbcType="DATE"></result>
<result property="nickName" column="NICK_NAME" jdbcType="VARCHAR"></result>
</resultMap>
<select id="listByMapperXml" resultMap="message_map">
select * from message
</select>
</mapper>
编写自己的mapper
@Repository
public interface MessageMapper extends BaseMapper<Message> {
List<Message> listByMapperXml();
}
当继承了BaseMapper后，表示当前mapper已经集成了通用mapper所有的功能（具体功能请参考官方帮助文档）。
当通用mapper已不能满足自己的查询需求时，可以自定义sql方法，通过在mapper.xml中实现即可，比如例子中的listByMapperXml方法。
使用注解方式开发mapper
@Mapper
@Repository
public interface MessageAnnotationMapper {
@Select("SELECT * FROM message")
@Results({
@Result(property = "id", column = "id", javaType = Integer.class, jdbcType = JdbcType.INTEGER),
@Result(property = "nickName", column = "nick_name", javaType = String.class, jdbcType = JdbcType.VARCHAR),
@Result(property = "ip", column = "ip", javaType = String.class, jdbcType = JdbcType.VARCHAR),
@Result(property = "insertTime", column = "INSERT_TIME", javaType = Date.class, jdbcType = JdbcType.DATE)
})
List<Message> list();
}
注：具体的service层实现，由于过于简单，本文不做赘述。可参考文末源码查看具体内容。
编写controller
本例就三种实现方式分别测试
@Controller
public class MybatisController {
@Autowired
private IMessageService messageService;
/**
* 通过自定义的mapper xml查询
*
* @param model
* @param currentPage
* @param pageSize
* @return
*/
@RequestMapping("/listByMapperXml/{currentPage}/{pageSize}")
public String listByMapperXml(Model model, @PathVariable("currentPage") int currentPage,
@PathVariable("pageSize") int pageSize) {
PageHelper.startPage(currentPage, pageSize);
model.addAttribute("selectTypeMsg", "通过自定义的mapper xml查询");
model.addAttribute("selectType", "listByMapperXml");
model.addAttribute("page", new PageInfo<>(messageService.listByMapperXml()));
return "index";
}
/**
* 通过通用mapper查询
*
* @param model
* @param currentPage
* @param pageSize
* @return
*/
@RequestMapping("/listByMapper/{currentPage}/{pageSize}")
public String listByMapper(Model model, @PathVariable("currentPage") int currentPage,
@PathVariable("pageSize") int pageSize) {
PageHelper.startPage(currentPage, pageSize);
model.addAttribute("selectTypeMsg", "通过通用mapper查询");
model.addAttribute("selectType", "listByMapper");
model.addAttribute("page", new PageInfo<>(messageService.listByMapper()));
return "index";
}
/**
* 通过注解查询
*
* @param model
* @param currentPage
* @param pageSize
* @return
*/
@RequestMapping("/listByAnnotation/{currentPage}/{pageSize}")
public String listByAnnotation(Model model, @PathVariable("currentPage") int currentPage,
@PathVariable("pageSize") int pageSize) {
PageHelper.startPage(currentPage, pageSize);
model.addAttribute("selectTypeMsg", "通过注解查询");
model.addAttribute("selectType", "listByAnnotation");
model.addAttribute("page", new PageInfo<>(messageService.listByAnnotation()));
return "index";
}
}
编写页面
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
<title>Spring Boot 集成Mybatis + Mapper + Pagehelper 测试例子</title>
</head>
<body>
<h1>Spring Boot 集成Mybatis + Mapper + Pagehelper 测试例子</h1>
<em>${.now?string("yyyy-MM-dd HH:mm:ss.sss")}</em>
<br>
<strong>${selectTypeMsg}</strong>
<#if page.list?exists>
<br>
当前页共 <b>${page.list?size }</b>条记录，总共${page.total!(0)}条记录
<table style="border: 1px solid lightgray;width: 100%">
<#assign index = 1> <#list page.list as message>
<tr
<#if index%2 == 0>style="background-color: lightgray;"</#if>>
<td>${message.id}</td>
<td>${message.ip}</td>
<td>${message.nickName}</td>
<td>${message.insertTime?string('yyyy-MM-dd HH:mm:ss.SSS')}</td>
</tr>
<#assign index = index + 1> </#list>
<tr>
<td colspan="4">
<ul>
<#list 1..page.pages as pageNumber>
<li style="float: left;width: 20px;list-style: none;">
<a href="http://localhost:8080/${selectType}/${pageNumber }/${page.pageSize}"
style="${(currentPage == pageNumber)?string('color:red;font-size:17px;font-weight: bold;','')}">${pageNumber }</a>
</li>
</#list>
</ul>
</td>
</tr>
</table>
</#if>
<p>Author: <a href="https://www.zhyd.me" target="_blank">https://www.zhyd.me</a> @码一码</p>
</body>
</html>
运行测试
listByMapperXml
listByMapper
listByAnnotation
到此为止，本篇已详细介绍了在springboot中如何整合Mybatis + Mapper，以及使用Pagehelper实现分页的使用方法。
作者：慕冬雪
链接：http://www.imooc.com/article/259252
来源：慕课网
本文首次发布于慕课网 ，转载请注明出处，谢谢合作
