利用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; }
2、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 )"); } }
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执行原生SQL，自定义SQL占位符增加参数
JPA 实际上就是 Hibernate 的封装，根据Interface 方法名，生成对应的方法，也支持Query注解的方式和原生SQL，原生SQL如下：
1、注解@Query方式执行原生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、复杂原生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);方式对“?”进行参数占位补充。
Note：
返回值：由于是一个insert操作，另外成功则返回操作的条数，没有做数据改变则返回 0 。
如果出现 “jpa Executing an update/delete query ”异常，那么是因为你没有添加事物和“@Modifying”比较，把注解加上就可以。
以上为个人经验，希望能给大家一个参考，也希望大家多多支持。
也只有懂的接受自己的失败，才能更好的去发挥自身优势，也才能够更好的去实现自我;
