mybatis框架已经成为一种大众普遍使用的数据库开发工具,因此,在这里我们就不讲其他的数据库连接操作方式了,直奔主题,整合mybaits:
首先修改pom文件,引入相关包:
<dependencies>
<!--排除默认日志框架-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<exclusions>
<exclusion>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-logging</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--log4j-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-log4j</artifactId>
<version>1.3.8.RELEASE</version>
</dependency>
<!--druid-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.18</version>
</dependency>
<!-- 数据库驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.25</version>
</dependency>
<!--mybaits -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>${mybatis.spring.boot.version}</version>
</dependency>
<dependency>
<groupId>commons-collections</groupId>
<artifactId>commons-collections</artifactId>
</dependency>
</dependencies>
在配置文件中加入mybaits配置:
# Mybatis配置
mybatis.mapperLocations=classpath:mapper/*.xml
mybatis.configLocation=classpath:config/mybatis.xml
在资源文件夹下新建config文件,在其他新建mybatis.xml配置文件:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<!-- map下划线自动转大写 -->
<setting name="mapUnderscoreToCamelCase" value="true" />
<!-- 打印查询语句 -->
<setting name="logImpl" value="STDOUT_LOGGING" />
<!-- 返回字段为空时null也显示该字段 -->
<setting name="callSettersOnNulls" value="true"/>
</settings>
<!-- 别名定义 -->
<typeAliases>
<!-- 批量别名定义,指定包名,mybatis自动扫描包中的po类,自动定义别名,别名是类名(首字母大写或小写都可以,一般用小写) -->
<!-- <package name="com.hotpot..pojo" /> -->
<!--<typeAlias type="com.hotpot.sys.pojo.SysUser" alias="sysUser"/> -->
<!-- 批量别名定义
指定包名,mybatis自动扫描包中的po类,自动定义别名,别名就是类名(首字母大写或小写都可以)
-->
<!--<package name="com.demo.pojo"/>-->
</typeAliases>
<plugins>
<!-- 打印sql拦截器 -->
<plugin interceptor="com.springboot.interceptor.MybatisInterceptor"></plugin>
</plugins>
</configuration>
然后新建一个mapper包,下面新建UserMapper接口类:
/**
* @Package:com.springboot.test.mapper
* @ClassName:UserMapper
* @Description:user mapper类
* @Author Shuyu.Wang
* @Date 2017-12-07 17:27
**/
@Mapper
public interface UserMapper {
List<Map<String,Object>> listUsers();
}
然后再资源文件夹下新建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.springboot.test.mapper.UserMapper">
<select id="listUsers" resultType="java.util.Map">
select * from user
</select>
</mapper>
然后再web类中新增接口:
@Autowired
private UserMapper userMapper;
@RequestMapping(value = "/db",method = RequestMethod.GET)
public List<Map<String,Object>> db(){
return userMapper.listUsers();
}
另外,新建一个interceptor包,新增一个拦截器,用于打印sql的完整语句,包括参数,mybatis会打印相关查询语句,但是对于复杂语句操作,拦截器类可以帮助我们更清楚的追踪错误,代码如下:
package com.springboot.interceptor;
import org.apache.commons.collections.CollectionUtils;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.apache.log4j.Logger;
import java.text.DateFormat;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Properties;
import java.util.regex.Matcher;
/**
* @Title: MybatisInterceptor.java
* @Package com.ganinfo.utils
* @Description: 拦截打印完整sql语句
* @author Shuyu.Wang
* @date Creation time: 2017年9月4日
* @version V1.0
*/
@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("*************");
logger.info("***"+sql);
logger.info("*************");
} 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 target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties0) {
this.properties = properties0;
}
}
启动程序,访问:http://127.0.0.1:8082/boot/db
返回:
[{“password”:”123123”,”address”:null,”phone”:”12312312312”,”userName”:”阿道夫”,”userId”:1,”account”:”啊”}]
控制台打印如下:
然后访问:http://127.0.0.1:8082/boot/druid2/index.html
登录后点击SQL监控:
可以监控到我们操作的具体语句和相关的参数。