在Jpa框架下拼接原生sql 并執(zhí)行的操作

更新時間：2021年06月11日 14:17:48 作者：多吃核桃會補(bǔ)腦

這篇文章主要介紹了在Jpa框架下拼接原生sql 并執(zhí)行的操作，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

利用jpa的entityManager 執(zhí)行sql 并執(zhí)行

其中：

EntityManager.createNativeQuery(SQL)

返回的是Object對象

entityManager.createNativeQuery(SQL,WebInfo.class)

返回的是映射后的實(shí)例對象

Query.getSingleResult()執(zhí)行SQL語句，返回一個查詢結(jié)果，常用的還有以下方法

Query.getResultList()執(zhí)行SQL語句，返回一個List集合

Query.getFirstResult()執(zhí)行SQL語句，返回一個系列結(jié)果集合的第一個

直接上例子：

1、EntityManager.createNativeQuery(SQL)返回的是Object對象

entityManager.createNativeQuery(SQL,WebInfo.class)返回的是映射后的實(shí)例對象

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

2、Query.getSingleResult() 執(zhí)行SQL語句，返回一個查詢結(jié)果

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 ");
        }
        //結(jié)束時間
        if (param.getEndDate() != null) {
            querySql.append(" and er.last_modified_date <= :endDate ");
        }
        //單據(jù)類型
        if (CollectionUtils.isNotEmpty(param.getType())) {
            querySql.append(" and er.type in ( :type ) ");
        }
        //單據(jù)狀態(tài)
        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 )");
        }
    }

3、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 即可。

Springboot JPA執(zhí)行原生SQL，自定義SQL占位符增加參數(shù)

JPA 實(shí)際上就是 Hibernate 的封裝，根據(jù)Interface 方法名，生成對應(yīng)的方法，也支持Query注解的方式和原生SQL，原生SQL如下：

1、注解@Query方式執(zhí)行原生SQL語句：

@Query(value = "select * from table_car_mark limit 0,10",nativeQuery = true)
 List<CarsMark> findTop10();

注解的方式需要增加一個“nativeQuery=true”來表示是原生 SQL

2、EntityManager.Query 方式：

String sql = "insert t_car_mark_v2(id,car_mark,trigger_event,operate_state,gps_time,gps_longtitude,gps_latitude,gps_speed,gps_direction,gps_state) VALUES(1379000,204819,4,1,20121101012203,116.4130173,39.8860664,0,0,1),(1378501,162481,4,0,20121101012202,116.3074417,39.8848457,54,240,1)";
Query query = em.createNativeQuery(sql);

3、復(fù)雜原生SQL，占位式：

...
import javax.persistence.EntityManager;
import javax.persistence.Query;
....
@Autowired
 private EntityManager em;
String sql = "insert t_car_mark_v2(id,car_mark,trigger_event,operate_state,gps_time,gps_longtitude,gps_latitude,gps_speed,gps_direction,gps_state) values(?,?,?,?,?,?,?,?,?)";
query = em.createNativeQuery(sql);
query.setParameter(1,1);
query.setParameter(2,'values');
query.setParameter(3,1);
query.setParameter(4,1);
query.setParameter(5,'values');
query.setParameter(6,'values');
query.setParameter(7,'values');
query.setParameter(8,'values');
query.setParameter(9,'values');
query.executeUpdate();

使用 query.setParameter(index,parms);方式對“?”進(jìn)行參數(shù)占位補(bǔ)充。

Note：

返回值：由于是一個insert操作，另外成功則返回操作的條數(shù)，沒有做數(shù)據(jù)改變則返回 0 。

如果出現(xiàn) “jpa Executing an update/delete query ”異常，那么是因?yàn)槟銢]有添加事物和“@Modifying”比較，把注解加上就可以。

以上為個人經(jīng)驗(yàn)，希望能給大家一個參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: