使用JPA EntityManagerFactory执行本地SQL

自定义查询方法

import lombok.extern.slf4j.Slf4j;
import org.hibernate.Query;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import java.util.List;

/**
 * Created by Luojq on 2020/7/6
 */
@Slf4j
@Repository
public class NativeRepository {

    @Autowired
    @Qualifier("entityManagerFactory")
    EntityManagerFactory entityManagerFactory;

    /**
     * 自定义查询方法
     * @return List<Map>
     */
    protected List queryList(String sql, List params) {
        long s = System.currentTimeMillis();
        List list = null;
        EntityManager entityManager = null;
        try{
            entityManager = entityManagerFactory.createEntityManager();
            Query query = getQuery(sql, params, entityManager);
            list =  query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        }catch (Exception e){
            log.error("执行SQL:[ {} ] 异常！params:{}", sql, params, e);
        }finally {
            if (null != entityManager){
                entityManager.clear();
                entityManager.close();
            }
            log.info("执行SQL:[ {} ]，运行时间：{} ms", sql, (System.currentTimeMillis() - s));
        }
        return list;
    }

    List queryList(String sql, List params, Integer page, Integer pageNum) {
        return queryList(getQueryPageSql(sql, page, pageNum), params);
    }

    protected String getQueryPageSql(String sql, Integer page, Integer pageNum) {
        StringBuilder sqlBuffer = new StringBuilder();
        sqlBuffer.append("select * from ( select row_.*, rownum rownum_ from ( ");
        sqlBuffer.append(sql);
        sqlBuffer.append(" ) row_ where rownum <= ");
        sqlBuffer.append((page + 1) * pageNum);
        sqlBuffer.append(" ) where rownum_ > ");
        sqlBuffer.append(page * pageNum);
        return sqlBuffer.toString();
    }

    /**
     * 统计查询
     */
    protected Long queryCount(String sql, List params) {
        Long count;
        EntityManager entityManager = null;
        try{
            entityManager = entityManagerFactory.createEntityManager();
            Query query = getQuery(sql, params, entityManager);
            count =  Long.valueOf(query.uniqueResult().toString());
        }catch (Exception e){
            count = -1L;
            log.error("执行SQL:[ {} ] 异常！params:{}", sql, params, e);
        }finally {
            if (null != entityManager){
                entityManager.clear();
                entityManager.close();
            }
        }
        return count;
    }

    private Query getQuery(String sql, List params, EntityManager entityManager) {
        Query query = entityManager.createNativeQuery(sql).unwrap(Query.class);

        if (!CollectionUtils.isEmpty(params)) {
            for (int i = 0; i < params.size(); i++) {
                query.setParameter(i, params.get(i));
            }
        }
        return query;
    }

}

使用方式

@Slf4j
@Repository
public class ReportNativeRepository extends NativeRepository {

	private Integer zPage = 0;
    private Integer zPageNum = 0;
	
	public Long getOpenedCount(Date startTime, Date endTime) {
        List<Object> params = new ArrayList<>();
        String sql = "select count(1) from T_*** t " +
                " where t.c1 = '2' " +
                "  and t.date between ? and ? ";

        params.add(startTime);
        params.add(endTime);
        return queryCount(sql, params);
    }

    /**
     * 查询
     * @param startTime 开始时间
     * @param endTime   结束时间
     */
    public List getOpenedImages(Date startTime, Date endTime) {
        return getOpenedImages(startTime, endTime, zPage, zPageNum);
    }

    public List getOpenedImages(Date startTime, Date endTime, Integer page, Integer pageNum) {
        String sql = "select i.1 ID, i.2, i.3 " +
                " from T_*** i" +
                "  left join T_*** s on s.*_*  = i.*_* " +
                " where s.*_* = '2' " +
                "  and s.date between ? and ? ";

        if ((page + pageNum) != 0) {
            sql = getQueryPageSql(sql, page, pageNum);
        }
        List<Object> params = new ArrayList<>();
        params.add(startTime);
        params.add(endTime);
        return queryList(sql, params);
    }

}