Lison <cundream@163.com>, v1.0.0, 2019.10.13

SpringBoot系统搭建集成-005-整合MyBatis

mybatis框架已经成为一种大众普遍使用的数据库开发工具，因此，在这里我们就不讲其他的数据库连接操作方式了，直奔主题，整合mybaits： 首先修改pom文件，引入相关包

<properties> <org.mybatis.spring.boot.version>1.3.1</org.mybatis.spring.boot.version> </properties> <!--mybaits --> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>${org.mybatis.spring.boot.version}</version> </dependency> <dependency> <groupId>commons-collections</groupId> <artifactId>commons-collections</artifactId> </dependency>

在配置文件中加入mybaits配置：

# Mybatis配置 mybatis: configuration: # 下划线转驼峰 map-underscore-to-camel-case: true mapper-locations: classpath:mappers/*.xml type-aliases-package: com.xkcoding.orm.mybatis.entity

然后新建一个mapper包，下面新建UserMapper接口类：

@Mapper public interface UserMapper { /** * 查询所有用户 * * @return 用户列表 */ @Select("select * from user") List<User> listUsers(); /** * 根据id查询用户 * * @param id 主键id * @return 当前id的用户，不存在则是 {@code null} */ @Select("SELECT * FROM user WHERE id = #{id}") User selectUserById(@Param("id") Long id); /** * 保存用户 * * @param user 用户 * @return 成功 - {@code 1} 失败 - {@code 0} */ int addUserInfo(User user); }

然后再资源文件夹下新建mapper文件夹，在下面新建UserMapper.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.github.cundream.springbootbuilding.mapper.UserMapper"> <insert id="addUserInfo" parameterType="com.github.cundream.springbootbuilding.entity.User"> INSERT INTO `user`(`id`, `userName`, `passWord`, `realName`) VALUES (#{id}, #{userName}, #{passWord}, #{realName}) </insert> </mapper>

然后再entity增加实体类service和web类中新增接口：

public class User { private Integer id; private String userName; private String passWord; private String realName; //省略getter setter } public interface UserService { List<User> getUserById(); } @Service public class UserServiceImpl implements UserService { @Autowired UserMapper userMapper; @Override public List<User> getUserById() { return userMapper.listUsers(); } } @RestController @RequestMapping("/user") public class UserController { @Autowired private UserService userService; @RequestMapping("getUserList") public List<User> GetUser(){ return userService.getUserById(); } }

在启动类增加Mapper扫描配置

@MapperScan("com.github.cundream.springbootbuilding.mapper")

另外，新建一个interceptor包，新增一个拦截器，用于打印sql的完整语句，包括参数，mybatis会打印相关查询语句，但是对于复杂语句操作，拦截器类可以帮助我们更清楚的追踪错误，代码如下

** * @author : Lison * @Date: 2019/10/16 14:32 * @Description: 拦截打印完整sql语句 */ @Component @Intercepts({ @Signature(type = Executor.class, method = "update", args = { MappedStatement.class, Object.class }), @Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class }) }) public class MybatisInterceptor implements Interceptor { private Logger logger = Logger.getLogger(MybatisInterceptor.class); @SuppressWarnings("unused") private Properties properties; @Override public Object intercept(Invocation invocation) throws Throwable { try { MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0]; Object parameter = null; if (invocation.getArgs().length > 1) { parameter = invocation.getArgs()[1]; } String sqlId = mappedStatement.getId(); BoundSql boundSql = mappedStatement.getBoundSql(parameter); Configuration configuration = mappedStatement.getConfiguration(); String sql = getSql(configuration, boundSql, sqlId, 0); logger.info(sql); } catch (Exception e) { e.printStackTrace(); logger.error(e); } return invocation.proceed(); } public static String getSql(Configuration configuration, BoundSql boundSql, String sqlId, long time) { String sql = showSql(configuration, boundSql); StringBuilder str = new StringBuilder(100); str.append(sqlId); str.append(":"); str.append(sql); return str.toString(); } private static String getParameterValue(Object obj) { String value = null; if (obj instanceof String) { value = "'" + obj.toString() + "'"; } else if (obj instanceof Date) { DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA); value = "'" + formatter.format(new Date()) + "'"; } else { if (obj != null) { value = obj.toString(); } else { value = ""; } } return value; } /** * @param configuration * @param boundSql * @return */ public static String showSql(Configuration configuration, BoundSql boundSql) { Object parameterObject = boundSql.getParameterObject(); List<ParameterMapping> parameterMappings = boundSql.getParameterMappings(); String sql = boundSql.getSql().replaceAll("[\\s]+", " "); if (CollectionUtils.isNotEmpty(parameterMappings) && parameterObject != null) { TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry(); if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) { sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(parameterObject))); } else { MetaObject metaObject = configuration.newMetaObject(parameterObject); for (ParameterMapping parameterMapping : parameterMappings) { String propertyName = parameterMapping.getProperty(); if (metaObject.hasGetter(propertyName)) { Object obj = metaObject.getValue(propertyName); sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj))); } else if (boundSql.hasAdditionalParameter(propertyName)) { Object obj = boundSql.getAdditionalParameter(propertyName); sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj))); } else { sql = sql.replaceFirst("\\?", "缺失"); } // 打印出缺失，提醒该参数缺失并防止错位 } } } return sql; } @Override public Object plugin(Object o) { return Plugin.wrap(o, this); } @Override public void setProperties(Properties properties) { this.properties = properties; } }

然后访问：

http://127.0.0.1:8082/bootbuliding/user/getUserList

登录后点击SQL监控：

可以监控到我们操作的具体语句和相关的参数

集成Mapper插件和分页助手

Spring Boot 如何集成通用Mapper插件和分页助手插件，简化Mybatis开发，带给你难以置信的开发体验。

引入依赖和配置

pom.xml配置

<mybatis.pagehelper.version>5.1.10</mybatis.pagehelper.version> <!-- 通用Mapper --> <dependency> <groupId>tk.mybatis</groupId> <artifactId>mapper-spring-boot-starter</artifactId> <version>${mybatis.mapper.version}</version> </dependency> <!-- 分页助手 --> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>${mybatis.pagehelper.version}</version> </dependency>

application.yml配置

pagehelper: auto-dialect: true helper-dialect: mysql reasonable: true params: count=countSql

Mapper配置

// 注意：这里的Mapper是tk.mybatis.mapper.common.Mapper包下的 public interface UserMapper extends Mapper<User>, MySqlMapper<User> { }

UserMapperTest.java

@Slf4j public class UserMapperTest extends SpringbootbuildingApplicationTests { @Autowired private UserMapper userMapper; @Test public void testInsert() { Long id = IdUtil.newInstance().nextId(); User testSave3 = User.builder().id(id).userName("testSave3").passWord("123456").build(); userMapper.insertUseGeneratedKeys(testSave3); Assert.assertNotNull(testSave3.getId()); log.debug("【测试主键回写#testSave3.getId()】= {}", testSave3.getId()); } @Test public void testInsertList() { List<User> userList = Lists.newArrayList(); for (long i = 5; i < 14; i++) { Long salt = IdUtil.newInstance().nextId(); User user = User.builder().id(i).userName("testSave"+i).passWord("123456").realName("test"+i).build(); userList.add(user); } int i = userMapper.insertList(userList); Assert.assertEquals(userList.size(), i); List<Long> ids = userList.stream().map(User::getId).collect(Collectors.toList()); log.debug("【测试主键回写#userList.ids】= {}", ids); } /** * 测试通用Mapper - 删除 */ @Test public void testDelete() { Long primaryKey = 1L; int i = userMapper.deleteByPrimaryKey(primaryKey); Assert.assertEquals(1, i); User user = userMapper.selectByPrimaryKey(primaryKey); Assert.assertNull(user); } /** * 测试通用Mapper - 更新 */ @Test public void testUpdate() { Long primaryKey = 1L; User user = userMapper.selectByPrimaryKey(primaryKey); user.setUserName("通用Mapper名字更新"); int i = userMapper.updateByPrimaryKeySelective(user); Assert.assertEquals(1, i); User update = userMapper.selectByPrimaryKey(primaryKey); Assert.assertNotNull(update); Assert.assertEquals("通用Mapper名字更新", update.getUserName()); log.debug("【update】= {}", update); } /** * 测试通用Mapper - 查询单个 */ @Test public void testQueryOne(){ User user = userMapper.selectByPrimaryKey(1L); Assert.assertNotNull(user); log.debug("【user】= {}", user); } /** * 测试通用Mapper - 查询全部 */ @Test public void testQueryAll() { List<User> users = userMapper.selectAll(); Assert.assertTrue(CollUtil.isNotEmpty(users)); log.debug("【users】= {}", users); } /** * 测试分页助手 - 分页排序查询 */ @Test public void testQueryByPageAndSort() { initData(); int currentPage = 1; int pageSize = 5; String orderBy = "id desc"; int count = userMapper.selectCount(null); PageHelper.startPage(currentPage, pageSize, orderBy); List<User> users = userMapper.selectAll(); PageInfo<User> userPageInfo = new PageInfo<>(users); Assert.assertEquals(5, userPageInfo.getSize()); Assert.assertEquals(count, userPageInfo.getTotal()); log.debug("【userPageInfo】= {}", userPageInfo); } /** * 测试通用Mapper - 条件查询 */ @Test public void testQueryByCondition() { initData(); Example example = new Example(User.class); // 过滤 example.createCriteria().andLike("name", "%Save1%").orEqualTo("phoneNumber", "17300000001"); // 排序 example.setOrderByClause("id desc"); int count = userMapper.selectCountByExample(example); // 分页 PageHelper.startPage(1, 3); // 查询 List<User> userList = userMapper.selectByExample(example); PageInfo<User> userPageInfo = new PageInfo<>(userList); Assert.assertEquals(3, userPageInfo.getSize()); Assert.assertEquals(count, userPageInfo.getTotal()); log.debug("【userPageInfo】= {}", userPageInfo); } /** * 初始化数据 */ private void initData() { testInsertList(); } }

//注意：这时@MapperScan的org.mybatis.spring.annotation.MapperScan;修改为 tk.mybatis.spring.annotation.MapperScan

@MapperScan tk.mybatis.spring.annotation.MapperScan;

参考

通用Mapper官方文档 、 pagehelper 官方文档

项目GitHub地址