Jpa框架下拼接原生sql 并执行

川长思鸟来 2022-04-14 04:42 688阅读 0赞

利用jpa的entityManager 执行sql 并执行  
其中：  
EntityManager.createNativeQuery(SQL)返回的是Object对象  
entityManager.createNativeQuery(SQL,WebInfo.class)返回的是映射后的实例对象

Query.getSingleResult() 执行SQL语句，返回一个查询结果，常用的还有以下方法  
Query.getResultList() () 执行SQL语句，返回一个List集合  
Query.getFirstResult() () 执行SQL语句，返回一个系列结果集合的第一个

直接上例子：

1.  EntityManager.createNativeQuery(SQL)返回的是Object对象  
    entityManager.createNativeQuery(SQL,WebInfo.class)返回的是映射后的实例对象

public List<User> getByCompanyFinanceRoleManager(Long companyID , String authorityName){
            StringBuffer querySql = new StringBuffer("select a.* from art_user a , art_user_authority b where a.id = b.user_id and a.company_id = :companyId " +
                    " and b.authority_name =  :authorityName");
            Query query = entityManager.createNativeQuery(querySql.toString() , User.class);
            query.setParameter("companyId" , companyID);
            query.setParameter("authorityName" , authorityName);
            List<User> list = query.getResultList();
            return list;
        }

1.  Query.getSingleResult() 执行SQL语句，返回一个查询结果

public Long getByFinanceRoleApplicationCount(ApplicationSearchParamDTO param){
            StringBuffer queryCount = new StringBuffer("select count(er.id) from atl_application er , atl_loan_application b where er.application_oid = b.application_oid and er.status not in (1010 ,1011)");
            getSql(queryCount , param);
            Query count = entityManager.createNativeQuery(queryCount.toString() );
            setQueryParam(count , param);
            Object obj = count.getSingleResult();
            Long countNum = ((BigInteger) obj).longValue();
            return countNum;
        }
    
    public void getSql(StringBuffer querySql , ApplicationSearchParamDTO param ){
            //公司oid
            if (!StringUtils.isEmpty(param.getCompanyOID())) {
                querySql.append(" and er.company_oid = :companyOID ");
            }
            //申请人oid
            if (CollectionUtils.isNotEmpty(param.getApplicantOIDs())){
                querySql.append(" and er.applicant_oid in ( :applicantOID ) ");
            }
            if (!StringUtils.isEmpty(param.getBusinessCode())){
                querySql.append(" and b.business_code like CONCAT('%' , :businessCode , '%') ");
            }
            if (CollectionUtils.isNotEmpty(param.getDepartmentOIDs()) && CollectionUtils.isNotEmpty(param.getFinanceRoleCorporationOids()) && CollectionUtils.isEmpty(param.getCorporationOIDs())) {
                querySql.append(" and ( b.department_oid in ( :departmentOID ) or er.corporation_oid in ( :corporationOID ) OR b.department_oid is null OR er.corporation_oid is null )");
            }else if(CollectionUtils.isNotEmpty(param.getDepartmentOIDs()) && CollectionUtils.isEmpty(param.getCorporationOIDs())){
                querySql.append(" and ( b.department_oid in ( :departmentOID ) OR b.department_oid is null ) ");
            }else if(CollectionUtils.isNotEmpty(param.getFinanceRoleCorporationOids()) && CollectionUtils.isEmpty(param.getCorporationOIDs())){
                querySql.append(" and ( er.corporation_oid in ( :corporationOID ) OR er.corporation_oid is null ) ");
            }
            if (CollectionUtils.isNotEmpty(param.getCorporationOIDs())){
                querySql.append(" and er.corporation_oid in ( :corporationOID ) ");
            }
            //开始时间
            if (param.getStartDate() != null) {
                querySql.append(" and er.last_modified_date >= :startDate ");
            }
            //结束时间
            if (param.getEndDate() != null) {
                querySql.append(" and er.last_modified_date <= :endDate ");
            }
            //单据类型
            if (CollectionUtils.isNotEmpty(param.getType())) {
                querySql.append(" and er.type in ( :type ) ");
            }
            //单据状态
            if (CollectionUtils.isNotEmpty(param.getStatus())) {
                querySql.append(" and er.status in ( :status )");
            }
           /* //申请单oid
            if (CollectionUtils.isNotEmpty(param.getApplicationOIDs())) {
                querySql.append(" and er.application_oid in ( :applicationOID )");
            }*/
    
            //反选
            if(CollectionUtils.isNotEmpty(param.getExcludedApplicationOIDs())){
                querySql.append(" and er.application_oid not in ( :excludedApplicationOID )");
            }
        }

1.  Query.getResultList()

public List<DepartmentDTO> getDepartmentsOfReportLine(UUID reportLineOID) {
            String sql = "SELECT d.department_oid, d.`name` FROM art_department d INNER JOIN art_report_obj ro on ro.obj_oid = d.department_oid AND ro.obj_type = '2' "
                + "and ro.report_line_oid = '" + reportLineOID + "'";
            Query query = entityManager.createNativeQuery(sql);
            List<DepartmentDTO> list = new ArrayList<>();
            List<Object[]> rtList = query.getResultList();
            for (Object[] objects : rtList) {
                DepartmentDTO departmentDTO = new DepartmentDTO();
                departmentDTO.setDepartmentOID(UUID.fromString(objects[0].toString()));
                departmentDTO.setName(objects[1].toString());
                list.add(departmentDTO);
            }
            return list;
        }
        ```
    
    直接参考例子，拼接正确sql 即可。
    
    
    如果觉得本篇文章帮到你的话， 请关注我新建的公众号： **程序员BenJi **
    定期分享优质的知识思维导图，帮助大家建立结构化的知识体系，欢迎关注~