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;