首页手记 springboot整合Mybatis+Mapper+...

springboot整合Mybatis+Mapper+Pagehelper(修订-详尽版)

标签：

SpringBoot

本篇是SpringBoot项目实战（4）：集成Mybatis和SpringBoot项目实战（5）：集成分页插件这两篇的修订版。

本文知识点：

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>