entityManager执行本地sql,预编译，自定义返回对象

jpa自定义sql

 另一种方式

 

传集合的话还是传集合进去就可以了 ，不用分割成字符串

       List roles=Lists.newArrayList();
        if("1".equals(getType)){
            roles=Lists.newArrayList("地市公司领导","区县公司领导");
        }else if("2".equals(getType)){
            roles=Lists.newArrayList("地市公司部门主任","区县公司部门主任","地市公司配电部专责","区县公司配电部专责");
        }

 like用法

         "        having GROUP_CONCAT(t.ROLE_NAME SEPARATOR ',')  like :roleName";
----------------------------------------------------------------------------------------------------
     map.put("roleName","%"+vo.getRoleName()+"%");

 

 分页处理

    public static <T> RestResult<List<T>> nativeQueryPage(PPage pPage, Class<T> tClass){
        int index=pPage.getPage()==null||pPage.getPage()<=0?1:pPage.getPage();
        int pageSize=pPage.getPageSize()==null||pPage.getPageSize()<=0?10:pPage.getPageSize();
        StringUtils.isEmptyReMsg(pPage.getSql(),"sql不能为空");
        EntityManager entityManager = pPage.getEntityManager();
        StringUtils.isEmptyReMsg(entityManager,"entityManager不能为空");
        String countsql="select count(*) from ( "+pPage.getSql()+" ) countt1";
        Query nativeQuery = entityManager.createNativeQuery(pPage.getSql());
        NativeQueryImplementor implementor = nativeQuery.setFirstResult((index - 1) * pageSize)
                .setMaxResults((index - 1) * pageSize + pageSize)
                .unwrap(NativeQueryImpl.class)
                .setResultTransformer(Transformers.aliasToBean(tClass));
        Query nativeQueryCount = entityManager.createNativeQuery(countsql);
        Optional.ofNullable(pPage.getArgs())
                .orElse(Maps.newHashMap())
                .forEach((k,v)->{
                    implementor.setParameter(k,v);
                    nativeQueryCount.setParameter(k,v);
                });
        List<T> resultList = implementor.getResultList();
        String singleResult = nativeQueryCount.getSingleResult().toString();
        return RestResult.successData(resultList).setTotal(Long.valueOf(singleResult));
    }