jpa整合mybatis模板解析、hibernate整合mybatis模板解析
jpa整合mybatis模板解析、hibernate整合mybatis模板解析
jpa是hibernate的封装，主要用于spring全家桶套餐。
hibernate难以编写复杂的SQL。例如一个订单查询，查询条件有时间纬度、用户纬度、状态纬度、搜> 索、分页........... 等等。正常开发你可能首先想到用一堆if判断再拼接SQL执行。这样会导致一个方法一堆> 代码，代码可读性、可维护性差、
于是模板引擎应运而生，mybatis更是佼佼者。通过在xml中编写if、for等操作实现复杂查询。
现在就有了这篇文章，在用hibernate的情况下使用mybatis 的xml解析实现复杂查询、
什么？你是说为什么不直接用mybatis？抱歉，接手项目就是用jpa、hibernate。难道要我用mybatis重新写上百个表映射实体对象吗？
依赖
在hibernate的项目中，引入mybatis的依赖
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.11</version>
</dependency>
代码封装
我这里直接将代码封装为spring的一个组件
import cn.com.agree.aweb.pojo.ParamObject;
import cn.com.agree.aweb.pojo.SqlResult;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.builder.xml.XMLMapperBuilder;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.hibernate.Session;
import org.hibernate.query.Query;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import javax.annotation.PostConstruct;
import javax.persistence.EntityManager;
import java.util.ArrayList;
import java.util.List;
/**
* @author lingkang
* Created by 2022/10/10
* 之前发现使用freemarker进行SQL模板使用，个人觉得代码可读性低。
* hibernate缺少比较好的模板引擎，这里封装mybatis的模板引擎
* 编写复杂SQL时，可以通过 mybatis 的 xml 进行编写hibernate的sql语句
* 增加代码可读性和可维护性
* 对应模板id：命名空间.id
* 需要注意id的全局唯一性
*/
@Slf4j
@Component
public class MybatisTemplate {
private Configuration configuration = new Configuration();
@Autowired
private EntityManager em;
@Value("${spring.jpa.show-sql:false}")
private boolean showSql;
@PostConstruct
public void init() {
new XMLMapperBuilder(
MybatisTemplate.class.getClassLoader().getResourceAsStream("mapper/mapper.xml"),
configuration, null, null
).parse();// 解析
}
public Session getSession() {
return em.unwrap(Session.class);
}
/**
* @param id mapper.xml中的查询id，命名空间.id
* @param param 入参
* @param <T>
* @return
*/
public <T> List<T> selectForList(String id, ParamObject param) {
return selectForQuery(id, param).list();
}
/**
* @param id mapper.xml中的查询id，命名空间.id
* @param param 入参
* @return
*/
public Query selectForQuery(String id, ParamObject param) {
SqlResult sql = getSql(id, param);
Query query = getSession().createQuery(sql.getSql());
if (param != null && !param.isEmpty()) {
int i = 1;
for (Object val : sql.getParams()) {
query.setParameter(i, val);
i++;
}
}
return query;
}
public SqlResult getSql(String id, ParamObject param) {
MappedStatement mappedStatement = configuration.getMappedStatement(id);
BoundSql boundSql = mappedStatement.getBoundSql(param);
return getSqlResult(boundSql, mappedStatement, param);
}
private SqlResult getSqlResult(BoundSql boundSql, MappedStatement mappedStatement, ParamObject paramObject) {
SqlResult sqlResult = new SqlResult();
sqlResult.setSql(sqlParamAddIndex(boundSql.getSql()));
sqlResult.setParams(getParam(boundSql, mappedStatement, paramObject));
if (showSql) {
log.info(sqlResult.toString());
}
return sqlResult;
}
private List<Object> getParam(BoundSql boundSql, MappedStatement mappedStatement, ParamObject paramObject) {
List<Object> params = new ArrayList<>();
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
TypeHandlerRegistry typeHandlerRegistry = mappedStatement.getConfiguration().getTypeHandlerRegistry();
for (ParameterMapping parameterMapping : parameterMappings) {
if (parameterMapping.getMode() != ParameterMode.OUT) {
Object value;
String propertyName = parameterMapping.getProperty();
if (boundSql.hasAdditionalParameter(propertyName)) {
value = boundSql.getAdditionalParameter(propertyName);
} else if (paramObject == null) {
value = null;
} else if (typeHandlerRegistry.hasTypeHandler(paramObject.getClass())) {
value = paramObject;
} else {
MetaObject metaObject = configuration.newMetaObject(paramObject);
value = metaObject.getValue(propertyName);
}
params.add(value);
}
}
if ((paramObject == null || paramObject.isEmpty()) && !params.isEmpty()) {
throw new IllegalArgumentException("解析xml入参不匹配，xml需要的参数变量数：" + params.size() + " 入参：" + paramObject);
}
return params;
}
/**
* @param sql select user from user where id=? and status=?
* @return select user from user where id=?1 and status=?2
*/
private String sqlParamAddIndex(String sql) {
StringBuffer buffer = new StringBuffer(sql);
int i = 1, index = 0;
while ((index = buffer.indexOf("?", index)) != -1) {
buffer.insert(index + 1, i);
index++;
i++;
}
return buffer.toString();
}
}
import java.util.Arrays;
import java.util.HashMap;
/**
* @author lingkang
* Created by 2022/10/11
* 对参数简单封装
*/
public class ParamObject extends HashMap<String, Object> {
public ParamObject add(String key, String value) {
put(key, value);
return this;
}
public ParamObject addList(String key, Object... item) {
put(key, Arrays.asList(item));
return this;
}
}
import lombok.Data;
import java.util.List;
/**
* @author lingkang
* Created by 2022/10/10
*/
@Data
public class SqlResult {
private String sql;
private List<Object> params;
}
mapper.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="mapper">
<select id="getFileMenuAFA">
select po
from ServiceVersionResourceVersionToFilePO po where po.serviceVersionResourceVersion.serviceVersion.service.id is not null
and po.file.id is not null
and po.file.platformVersion.id is not null
<if test="tenantId">
and po.file.tenantId = #{tenantId}
</if>
</select>
<select id="getFileMenuAFE">
select po
from GroupVerToFilePO po
where po.groupVersion.group.id is not null
and po.file.id is not null
and po.file.platformVersion.id is not null
<if test="tenantId">
and po.file.tenantId = #{tenantId}
</if>
</select>
<select id="getFileList">
select
<!--是否使用分页-->
<if test="!usePage">
po
</if>
<if test="usePage">
count(*)
</if>
from FilePO po
where 1=1
<!--文件类型-->
<if test="fileType != null and fileType.size > 0">
and po.type in
<foreach collection="fileType" open="(" close=")" item="item" separator=",">
#{item}
</foreach>
</if>
<!-- 租户 -->
<if test="tenantId">
and po.tenantId = #{tenantId}
</if>
<!-- 类型：服务、平台 -->
<if test="type">
<if test="'platform' == type">
and po.platformVersion.platform.id = #{id}
</if>
<if test="'platformVersion' == type">
and po.platformVersion.id = #{id}
</if>
<if test="'system' == type">
<if test="id.endsWith('-afa')">
and po.id in (select svrvfp.file.id
from ServiceVersionResourceVersionToFilePO svrvfp
where svrvfp.serviceVersionResourceVersion.serviceVersion.service.system.id = #{id})
</if>
<if test="!id.endsWith('-afa')">
and po.id in
(select gvfp.file.id from GroupVerToFilePO gvfp where gvfp.groupVersion.group.system.id = #{id})
</if>
</if>
<if test="'service' == type">
<if test="id.startsWith('grp')">
and po.id in (select gvfp.file.id from GroupVerToFilePO gvfp where gvfp.groupVersion.group.id = #{id})
</if>
<if test="!id.startsWith('grp')">
and po.id in (select svrvfp.file.id
from ServiceVersionResourceVersionToFilePO svrvfp
where svrvfp.serviceVersionResourceVersion.serviceVersion.service.id = #{id})
</if>
</if>
</if>
<!-- 搜索 -->
<if test="search != null and search != ''">
and (po.name like #{search}
or po.customName like #{search}
or po.des like #{search}
or
po.platformVersion.platform.name like #{search})
</if>
<if test="!usePage">
order by po.createTime desc
</if>
</select>
</mapper>
调用
@Autowired
private MybatisTemplate mybatisTemplate;
TenantVo tenant = UserUtils.getCurrentTenant();
ParamObject conditions = new ParamObject();
if (tenant != null) {
conditions.put("tenantId", tenant.getId());
}
// 注意id为 命名空间.id，也可以直接用id，只要复核mybatis 的规范即可
List<ServiceVersionResourceVersionToFilePO> afa = mybatisTemplate.selectForList("mapper.getFileMenuAFA", conditions);
