Springboot整合mybatis框架
半自动化的ORM框架，本文仅介绍单表的CRUD操作
一、快速入门
1、添加依赖
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.1.1</version>
</dependency>
2、添加配置文件
# 设置mapper配置文件
mybatis.mapper-locations=classpath:mapper/**/*.xml
3、创建Mapper接口和Doman类
- @Mapper
@Mapper
public interface UserMapper {
List<User> getOne(Long id);
List<User> findList(); //查询
Integer insert(User user); //插入
Integer update(User user); // 更新
Integer delete(Long id); //删除
}
4、创建查询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.xls.mapper.UserMapper">
<select id="findList" resultType="com.xls.entiy.User">select * from user</select>
<insert id="insert" parameterType="com.xls.entiy.User">insert into user (NAME) values (#{name})</insert>
<update id="update" parameterType="com.xls.entiy.User">update user set name=#{name} where id=#{id}</update>
<delete id="delete" parameterType="Integer">delete from user where id=#{id}</delete>
</mapper>
二、Mybatis 采用xml配置文件方式
1、Mybatis 传参和响应结果整理
public interface DemoMapper{
// 1、占位符和@Param注解
Employee selectByGenderAndAge(Short gender,String age );
Employee selectByGenderAndAge(@Param("gender") Short gender,@Param("age") String age );
// 2、对象传参和对象响应
List<Employee> selectByBeans(Employee employee);
// 3、Map传参和Map结果返回
List<Map<String,Object>> selectByMapParams(Map<String,Object> params);
// 3.1、根据@MapKey("id")注解返回Map类型
@MapKey("id")
Map<String,Object> getEmpByIdReturnMap(Integer id);
// 4、json传参和json返回
JSONObject findByJSONObject(JSONObject params);
// 5、插入
void insertEntity(Employee employee);
// 6、更新
void updateEntityById(Employee employee);
// 7、删除
void deleteById(Long id);
}
（2）对应XML配置文件
- 标签resultMap
- 标签sql, include
- 标签 if ,else
- 标签 trim,where ,set
- 标签 foreach
- 标签select/insert/update/delete
- id 唯一标识
- parameterType 参数类型
- resultType 结果映射类型，注意字段设置需要和字段对应
- resultMap 结果映射
- flushCache 用于设置在调用 SQL 语句后是否要求 MyBatis 清空之前查询的本地缓存和二级缓存
- useCache 启动二级缓存的开关，默认值为 true，表示将査询结果存入二级缓存中
<mapper namespace="com.xls.mapper.UserMapper">
<!-- 结果映射-->
<resultMap id="BaseResultMap" type="org.maple.pojo.Employee">
<id column="id" property="id"/>
<result column="last_name" property="name"/>
</resultMap>
<!-- 字段SQL引用-->
<sql id="Base_Column_List">id,name,age,email</sql>
<!-- where查询条件SQL-->
<sql id="whereClause">
<where>
<if test="id !=null and id > 0"> id = #{id}</if>
<if test="name !=null and name != null"> name = #{name}</if>
</where>
</sql>
<!--insert 执行SQL拼接-->
<sql id = "insertClause">
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="time!= null">time,</if>
<if test="authorname!= null">authorname,</if>
<if test="content!= null">content</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="time != null">#{time},</if>
<if test="authorname != null">#{authorname},</if>
<if test="content != null">#{content}</if>
</trim>
</sql>
<!-- update查询条件SQL-->
<sql id = "updateSetClause">
<trim prefix="set" suffixoverride="," sufix="where id=#{id}">
<if test="name and name!=null">name=#{name},</if>
<if test="gender!=null">gender="#{gender}",</if>
</trim>
</sql>
<!-- 1、占位符传参，一般不用-->
<select id="selectEntity" resultMap="BaseResultMap" >
select <include refid="Base_Column_List" />from employee <include refid="whereClause" />
</select>
<!-- 1.1、表达式传惨，分为${}和#{}，一般井号经过预编译，防止SQL注入-->
<select id="selectByName" resultMap="BaseResultMap" >
select <include refid="Base_Column_List" />from employee <include refid="whereClause" />
</select>
<!-- 2、对象传参，返回对象结果-->
<select id="selectByBeans" resultMap="BaseResultMap" parameterType="com.wg.demo.po.Employee">
select <include refid="Base_Column_List" /> from employee <include refid="whereClause" />
</select>
<!-- 3、Map集合传参,Map返回一般不用，特殊情况下使用-->
<select id="selectByMap" resultMap="BaseResultMap" parameterType="map">
select <include refid="Base_Column_List" /> from employee <include refid="whereClause" />
</select>
<!-- 4、JSON对象传参和JSON返回，一般不用，特殊情况下使用-->
<select id="findByJSONObject" resultType="com.alibaba.fastjson.JSONObject"
parameterType="com.alibaba.fastjson.JSONObject">
select <include refid="Base_Column_List" /> from employee <include refid="whereClause" />
</select>
<!-- 5、更新-->
<insert id="insertEntity" parameterType="com.wg.demo.po.Employee">
INSERT INTO lp_user_test_batch <include refid="insertClause" />
</insert>
<!-- 6、更新-->
<update id="updateByPriKey"parameterType="com.wg.demo.po.Employee">
UPDATE commit_separate_statement <include refid="updateSetClause"/>
</update>
<!-- 7、更新-->
<delete id="deleteById" parameterType=parameterType="com.wg.demo.po.Employee">
delete from commit_separate_statement <include refid="whereClause" />
</delete>
</mapper>
2、Mybatis批量处理
<sql id = "updateClause">
#{item.id},#{item.userId}, #{item.userName},#{item.userAge},#{item.type}
</sql>
<!-- 1、使用foreach传递collection参数-->
<select id="findByList" resultMap="BaseResultMap", parameterType="list" >
SELECT <include refid="Base_Column_List" />
from employee where age in
<foreach collection="list" open="(" separator="," close=")" item="item">#{item}</foreach>
</select>
<!-- 1、批量新增-->
<insert id="batchSave" parameterType="java.util.List">
INSERT INTO lp_user_test_batch( <include refid="Base_Column_List" /> ) VALUES
<foreach collection="list" item="item" index="index" open="(" separator="," close=")" >
<include refid="updateClause"/>
</foreach>
</insert>
<!-- 2、批量新增或更新,注意：这里需要提前设置key-->
<insert id="batchSaveOrUpdate" parameterType="java.util.List">
INSERT INTO lp_user_test_batch( <include refid="Base_Column_List" /> )
VALUES
<foreach collection="list" item="item" index="index" open="(" separator="," close=")">
<include refid="updateClause"/>
</foreach>
ON DUPLICATE KEY
UPDATE user_name = VALUES (user_name),user_age = VALUES (user_age),
type = VALUES(type),update_time = VALUES(update_time)
</insert>
<!-- 3、通过先查后操作的插入或更新-->
<insert id="insertOrUpdate" parameterType="cn.dabby.apiauth.db.entity.CollectionInfoBean">
<selectKey keyProperty="count" resultType="int" order="BEFORE">
select count(*) from tb_collection_info where cert_token = #{cert_token}
</selectKey>
<if test="count > 0">
UPDATE tb_collection_info <include refid="updateSetClause"/>
</if>
<if test="count == 0">
INSERT INTO tb_collection_info <include refid="insertClause" />
</if>
</insert>
<!-- 4、根据传参的ID判断是插入操作还是更新操作-->
<insert id="insertOrUpdate" parameterType="cn.dabby.apiauth.db.entity.CollectionInfoBean">
<if test="#{id}!=null">
UPDATE tb_collection_info <include refid="updateSetClause"/>
</if>
<if test="#{id}==null">
INSERT INTO tb_collection_info <include refid="insertClause" />
</if>
</insert>
4、其他标签整理使用
<!--choose+when+otherwise-->
<choose>
<when test="判断条件1">SQL语句1</when >
<when test="判断条件2">SQL语句2</when >
<otherwise>SQL语句4</otherwise>
</choose>
三、使用注解的方式实现CRUD操作【一般不用】
- @Select 查询
- resultType 结果类型
- @Results/@Result 结果映射
- id 是否ID
- column 映射字段
- property 对应属性
- @Insert 插入
- @Update 更新
- @Delete 删除
- @SelectKey 获取插入后的id
- statement 查询nextkey
- before是否前置
- keyProperty主键映射字段
- resultType 返回类型
//不需要写UserMapper的实现类
public interface UserMapper {
// 插入并返回ID，用于返回该条数据插入后的id
@SelectKey(statement = "select last_insert_id()",
before = false, keyProperty = "id", resultType = Integer.class)
@Insert("insert into t_user (`last_name`,`sex`) values(#{lastName},#{sex})")
public int saveUser(User user);
//删除
@Delete("delete from t_user where id=#{id}")
public int deleteById(Integer id);
//更新
@Update("update t_user set last_name=#{lastName},sex=#{sex} where id=#{id}")
public int updateUser(User user);
//查询
@Select("select * from t_user where id=#{id}")
@Results({
@Result(id=true,column="id",property="id"),@Result(column = "ename",property = "ename"),
@Result(column = "esex",property = "esex"),@Result(column = "esalary",property = "esalary")
})
public User queryUserById(Integer id);
}
3、Mybatis 流式查询
流式查询是查询成功后不是返回一个结果集而是返回一个迭代器，应用每次从迭代器中获取结果，能够有效降低内存的使用。
1、使用流式查询的Mapper
@Mapper
public interface FooMapper {
@Select("select * from foo limit #{limit}")
Cursor<Foo> scan(@Param("limit") int limit);
}
四、整合Mybatis自定义拦截器实现
1、说明：
- Interceptor 拦截器接口
2、添加拦截器配置类
如果说不需要配置属性，则在spring boot中，不需要去编写配置类，
只需要像我一样在拦截器上加个@Component即可
@Configuration
public class MybatisInterceptorConfig {
@Bean
public String myInterceptor(SqlSessionFactory sqlSessionFactory) {
// 执行器拦截器
ExecutorInterceptor executorInterceptor = new ExecutorInterceptor();
Properties properties = new Properties();
properties.setProperty("prop1","value1");
executorInterceptor.setProperties(properties);
sqlSessionFactory.getConfiguration().addInterceptor(executorInterceptor);
// 参数拦截器
sqlSessionFactory.getConfiguration().addInterceptor(new ParamInterceptor());
// 结果拦截器
sqlSessionFactory.getConfiguration().addInterceptor(new ResultInterceptor());
return "interceptor";
}
}
3、Executor拦截器实现
@Slf4j
@Intercepts({
@Signature( type= Executor.class, method = "query",
args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})
})
@Component
public class ExecutorInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
String sql = ExecutorPluginUtils.getSqlByInvocation(invocation);
log.error("拦截器ExecutorInterceptor:"+sql);
ExecutorPluginUtils.resetSql2Invocation( invocation, sql);
return invocation.proceed();
}
@Override
public Object plugin(Object o) {
return Plugin.wrap(o, this);
}
// 这里设置属性
@Override
public void setProperties(Properties properties) {
}
}
4、ParameterHandler 拦截器实现
@Slf4j
@Intercepts({@Signature(type = ParameterHandler.class,
method = "setParameters",args = PreparedStatement.class),})
@Component
public class ParamInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
log.error("拦截器ParamInterceptor");
//拦截 ParameterHandler 的 setParameters 方法 动态设置参数
if (invocation.getTarget() instanceof ParameterHandler) {
ParameterHandler parameterHandler = (ParameterHandler) invocation.getTarget();
PreparedStatement ps = (PreparedStatement) invocation.getArgs()[0];
// 反射获取 BoundSql 对象，此对象包含生成的sql和sql的参数map映射
Field boundSqlField = parameterHandler.getClass().getDeclaredField("boundSql");
boundSqlField.setAccessible(true);
BoundSql boundSql = (BoundSql) boundSqlField.get(parameterHandler);
// 反射获取 参数对像
Field parameterField = parameterHandler.getClass().getDeclaredField("parameterObject");
parameterField.setAccessible(true);
Object parameterObject = parameterField.get(parameterHandler);
if (parameterObject instanceof Map) {
//将参数中的name值改为2
((Map) parameterObject).put("name","2");
}
// 改写的参数设置到原parameterHandler对象
parameterField.set(parameterHandler, parameterObject);
parameterHandler.setParameters(ps);
log.error(JSON.toJSONString(boundSql.getParameterMappings()));
log.error(JSON.toJSONString(parameterObject));
}
return invocation.proceed();
}
@Override
public Object plugin(Object o) {
return Plugin.wrap(o, this);
}
@Override
public void setProperties(Properties properties) {
}
}
5、ResultSetHandler 结果拦截器实现
@Slf4j
@Component
@Intercepts({ @Signature(type = ResultSetHandler.class, method = "handleResultSets", args={Statement.class})})
public class ResultInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
log.error("拦截器ResultInterceptor");
Object result = invocation.proceed();
if (result instanceof ArrayList) {
ArrayList resultList = (ArrayList) result;
for (int i = 0; i < resultList.size(); i++) {
Object oi = resultList.get(i);
Class c = oi.getClass();
Class[] types = {String.class};
Method method = c.getMethod("setAddress", types);
// 调用obj对象的 method 方法
method.invoke(oi, "china");
}
}
return result;
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
}
6、StatementHandler 拦截器实现
@Intercepts({@Signature( type = StatementHandler.class,
method = "prepare", args = {Connection.class, Integer.class})})
@Component
@Slf4j
public class StatementInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = (StatementHandler) PluginUtils.realTarget(invocation.getTarget());
MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler);
MappedStatement mappedStatement = (MappedStatement) metaStatementHandler
.getValue("delegate.mappedStatement");
//只拦截select方法
if (!SqlCommandType.SELECT.equals(mappedStatement.getSqlCommandType())) {
return invocation.proceed();
}
BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");
//获取到sql
String originalSql = boundSql.getSql();
//可以对originalSql进行改写
log.error("拦截器StatementInterceptor:"+originalSql);
metaStatementHandler.setValue("delegate.boundSql.sql", originalSql);
Object parameterObject = boundSql.getParameterObject();
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
}
8、自定义SqlPrintInterceptor拦截器类
@Intercepts({@Signature(type = Executor.class, method = "query",
args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
@Signature(type = Executor.class, method = "update",
args = {MappedStatement.class, Object.class})})
@Slf4j
public class SqlPrintInterceptor implements Interceptor {
private static final DateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
@Override
public Object intercept(Invocation invocation) throws Throwable {
MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
Object parameterObject = null;
if (invocation.getArgs().length > 1) {
parameterObject = invocation.getArgs()[1];
}
long start = System.currentTimeMillis();
Object result = invocation.proceed();
String statementId = mappedStatement.getId();
BoundSql boundSql = mappedStatement.getBoundSql(parameterObject);
Configuration configuration = mappedStatement.getConfiguration();
String sql = getSql(boundSql, parameterObject, configuration);
long end = System.currentTimeMillis();
long timing = end - start;
if(logger.isInfoEnabled()){
logger.info("执行sql耗时:" + timing + " ms" + " - id:" + statementId + " - Sql:" );
logger.info(" "+sql);
}
return result;
}
@Override
public Object plugin(Object target) {
if (target instanceof Executor) {
return Plugin.wrap(target, this);
}
return target;
}
@Override
public void setProperties(Properties properties) {}
private String getSql(BoundSql boundSql, Object parameterObject, Configuration configuration) {
String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
if (parameterMappings != null) {
for (int i = 0; i < parameterMappings.size(); i++) {
ParameterMapping parameterMapping = parameterMappings.get(i);
if (parameterMapping.getMode() != ParameterMode.OUT) {
Object value;
String propertyName = parameterMapping.getProperty();
if (boundSql.hasAdditionalParameter(propertyName)) {
value = boundSql.getAdditionalParameter(propertyName);
} else if (parameterObject == null) {
value = null;
} else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
value = parameterObject;
} else {
MetaObject metaObject = configuration.newMetaObject(parameterObject);
value = metaObject.getValue(propertyName);
}
sql = replacePlaceholder(sql, value);
}
}
}
return sql;
}
private String replacePlaceholder(String sql, Object propertyValue) {
String result;
if (propertyValue != null) {
if (propertyValue instanceof String) {
result = "'" + propertyValue + "'";
} else if (propertyValue instanceof Date) {
result = "'" + DATE_FORMAT.format(propertyValue) + "'";
} else {
result = propertyValue.toString();
}
} else {
result = "null";
}
return sql.replaceFirst("\\?", Matcher.quoteReplacement(result));
}
}
扫一扫
242
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
