springboot完美使用jpa，支持原生sql绑定参数查询与分页转model，完胜jdbctemplate

springboot结合hibernate的jpa，简单的单表使用jpa的repository查询，稍微复杂的可以使用createNativeQuery执行原生sql语句查询，非常方便

jpa的原生单表查询，与JpaRepository的findBy。。。等等的快捷查询方式就不写了，下面的工具类可以方便的执行原生sql，与分页

import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
import org.hibernate.query.Query;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;

import javax.persistence.EntityManager;
import java.math.BigInteger;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class JpaUtil {

    /**
     *
     * @param selectL        select * or select id
     * @param fromAndWhere   from tableName, and where clause, and order
     * @param param          bind param in map, k-v, k ":name", v like "%test%"
     * @param pageable       page start from 0
     * @param clazz          vo, entity
     * @return               getTotalElements() is count, getContent() is list
     */
    public static <T> Page<T> page(String selectL, String fromAndWhere,  Map<String,Object> param, Pageable pageable, Class<T> clazz){
        EntityManager em = SpringUtil.getBean(EntityManager.class);
        // count
        String selectC= "select count(*) ";
        Query queryCount = em.createNativeQuery(selectC+" "+fromAndWhere).unwrap(org.hibernate.query.Query.class);
        param.forEach((k,v)-> queryCount.setParameter(k,v));
        int count = ((BigInteger) queryCount.getSingleResult()).intValue();
        if (count == 0) return new PageImpl<>(null,pageable,count);

        //select
        Query queryList = em.createNativeQuery(selectL+" "+fromAndWhere+" "+limit(pageable)).unwrap(org.hibernate.query.Query.class)
                .setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        param.forEach((k,v)-> queryList.setParameter(k,v));
        List<Map<String,Object>> res = queryList.getResultList();
        List<T> objList = Lists.transform(res,one-> JSONObject.parseObject(JSONObject.toJSONString(one), clazz));

        return new PageImpl(objList,pageable,count);
    }

    private static String limit(Pageable pageable){
        if (pageable.getOffset() == 0)
            return " limit "+pageable.getPageSize();
        return String.format(" limit %s,%s", pageable.getOffset(), pageable.getPageSize());
    }

    public static <T> List<T> list(String selectL, String fromAndWhere, Map<String,Object> param, Class<T> clazz){
        EntityManager em = SpringUtil.getBean(EntityManager.class);

        Query query = em.createNativeQuery(selectL+" "+fromAndWhere).unwrap(org.hibernate.query.Query.class)
                .setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        param.forEach((k,v)-> query.setParameter(k,v));
        List<Map<String,Object>> res = query.getResultList();

        return res.stream().map(one -> JSONObject.parseObject(JSONObject.toJSONString(one), clazz)).collect(Collectors.toList());
    }
}

测试类

import com.bank_3d.data.entity.TWordFilterE;
import com.bank_3d.data.vo.Intention;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static org.junit.Assert.*;

@RunWith(SpringRunner.class)
@SpringBootTest
public class JpaUtilTest {

    @Test
    public void getPage() {

        Page<TWordFilterE> wordFilterES = JpaUtil.page("select *",
                " from t_word where type=1 ",
                Collections.emptyMap(),
                PageRequest.of(1, 3),TWordFilterE.class);

        System.out.println(wordFilterES.getTotalElements());
        System.out.println(wordFilterES.getTotalPages());
        System.out.println(wordFilterES.getContent());

        Map<String,Object> param = new HashMap<>();
        param.put("type",2);
        Page<TWordFilterE> wordFilterES2 = JpaUtil.page("select *",
                " from t_word_filter where type=:type ",
                param,
                PageRequest.of(1, 3),TWordFilterE.class);
        System.out.println(wordFilterES2.getContent());
    }

    @Test
    public void list() {

        List<Intention> res = JpaUtil.list("select intention_name as name","from t_intent",Collections.emptyMap(),Intention.class);

        System.out.println(res);

        Map<String,Object> param = new HashMap<>();
        param.put("name","%查%");
        List<Intention> res2 =
                JpaUtil.list("select intention_name as name","from t_intention where intention_name like :name",param,Intention.class);
        System.out.println(res2);
    }
}

避免提示setResultTransformer过期

import org.hibernate.query.internal.NativeQueryImpl;

Query query = getEntityManager().createNativeQuery(sql);
query.unwrap(NativeQueryImpl.class)
     .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);