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);