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 = https://www.cnblogs.com/lingkang/archive/2022/10/11/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);
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/513658.html
標籤：其他
上一篇：pytorch-實作天氣識別