在JPA 中的entityManager.createNativeQuery(sql) 我们用来执行原生的sql，什么叫原生的sql，你写的这个sql呢，放到数据库中拼接上参数就可以直接执行了
代码举例
@Repository
public class PurchaseProjectRepositoryImpl extends PurchaseProjectRepository {
@PersistenceContext
private EntityManager entityManager;
@Override
public Pagination<PurchaseProjectEntity> paginationByPageQry(PurchaseProjectPageQry pageQry) {
StringBuffer sql = new StringBuffer();
sql.append("select p.* from ( select project.id as id , project.tenant_id as tenantId, project.purchase_project_name as purchaseProjectName, " +
"project.purchase_project_code as purchaseProjectCode, project.purchase_subject_id as purchaseSubjectId, project.project_type as projectType, " +
"project.purchase_subject_name as purchaseSubjectName, project.budget_amount as budgetAmount, project.unit as unit, project.currency as currency, project.currency_name as currencyName, " +
"project.is_in_annual_plan as inAnnualPlan , project.management_consulting as managementConsulting, project.is_digital_projects digitalProjects, project.project_original_budget as projectOriginalBudget," +
" project.project_original_budget_unit as projectOriginalBudgetUnit, project.brief_description as briefDescription, project.project_plan as projectPlan, project.project_annex as projectAnnex, " +
"project.applyer_id as applyerId, project.applyer_name as applyerName, project.apply_department_id as applyDepartmentId," +
" project.apply_department_name as applyDepartmentName, project.contact_number as contactNumber, project.status as status, project.ext_map as extMap, project.version as version, " +
" project.business_id as businessId, project.task_key as taskKey , project.unit_name as unitName, project.template_id as templateId , project.create_time as createTime" +
" from purchase_project project where project.is_deleted = :deleted and project.tenant_id = :tenantId and project.creator_id = :creatorId ");
if (Objects.nonNull(pageQry.getPurchaseProjectCode())) {
sql.append(" and project.purchase_project_code like :purchaseProjectCode");
}
if (Objects.nonNull(pageQry.getPurchaseProjectName())) {
sql.append(" and project.purchase_project_name like :purchaseProjectName");
}
if (Objects.nonNull(pageQry.getPurchaseSubjectId())) {
sql.append(" and project.purchase_subject_id = :purchaseSubjectId");
}
if (Objects.nonNull(pageQry.getLowBudgetAmount())) {
sql.append(" and project.budget_amount >= :lowBudgetAmount");
}
if (Objects.nonNull(pageQry.getTopBudgetAmount())) {
sql.append(" and project.budget_amount <= :topBudgetAmount");
}
if (Objects.nonNull(pageQry.getTransPurchase())) {
sql.append(" and project.id in ( select sc.related_business_id from purchase_scheme sc " +
"where sc.related_business_id = project.id and sc.is_deleted = :deleted and sc.tenant_id = :tenantId " +
"and sc.is_trans_purchase = :transPurchase " +
")");
}
if (Objects.nonNull(pageQry.getIds())) {
sql.append(" and project.id in :ids");
}
if (Objects.nonNull(pageQry.getLowCreatProjectTime())) {
sql.append(" and project.create_time >= :lowCreatProjectTime");
}
if (Objects.nonNull(pageQry.getTopCreatProjectTime())) {
sql.append(" and project.create_time <= :topCreatProjectTime");
}
if (Objects.nonNull(pageQry.getCurrency())) {
sql.append(" and project.currency = :currency");
}
if (Objects.nonNull(pageQry.getStatus())) {
sql.append(" and project.status = :status");
}
sql.append(" union all select pro.id as id , pro.tenant_id as tenantId, pro.purchase_project_name as purchaseProjectName, " +
" pro.purchase_project_code as purchaseProjectCode, pro.purchase_subject_id as purchaseSubjectId, pro.project_type as projectType," +
" pro.purchase_subject_name as purchaseSubjectName, pro.budget_amount as budgetAmount, pro.unit as unit, pro.currency as currency, pro.currency_name as currencyName, " +
" pro.is_in_annual_plan as inAnnualPlan , pro.management_consulting as managementConsulting, pro.is_digital_projects digitalProjects, pro.project_original_budget as projectOriginalBudget," +
" pro.project_original_budget_unit as projectOriginalBudgetUnit, pro.brief_description as briefDescription, pro.project_plan as projectPlan, pro.project_annex as projectAnnex, " +
"pro.applyer_id as applyerId, pro.applyer_name as applyerName, pro.apply_department_id as applyDepartmentId," +
" pro.apply_department_name as applyDepartmentName, pro.contact_number as contactNumber, pro.status as status, pro.ext_map as extMap, pro.version as version, " +
"pro.business_id as businessId, pro.task_key as taskKey , pro.unit_name as unitName, pro.template_id as templateId ,pro.create_time as createTime" +
" from purchase_project pro inner join purchase_project_member mem on pro.id = mem.purchase_project_id ");
sql.append(" where pro.is_deleted = :deleted and pro.tenant_id = :tenantId and mem.is_deleted = :deleted and mem.tenant_id = :tenantId");
if (Objects.nonNull(pageQry.getPurchaseProjectCode())) {
sql.append(" and pro.purchase_project_code like :purchaseProjectCode");
}
if (Objects.nonNull(pageQry.getPurchaseProjectName())) {
sql.append(" and pro.purchase_project_name like :purchaseProjectName");
}
if (Objects.nonNull(pageQry.getPurchaseSubjectId())) {
sql.append(" and pro.purchase_subject_id = :purchaseSubjectId");
}
if (Objects.nonNull(pageQry.getLowBudgetAmount())) {
sql.append(" and pro.budget_amount >= :lowBudgetAmount");
}
if (Objects.nonNull(pageQry.getTopBudgetAmount())) {
sql.append(" and pro.budget_amount <= :topBudgetAmount");
}
if (Objects.nonNull(pageQry.getTransPurchase())) {
sql.append(" and pro.id in ( select sc.related_business_id from purchase_scheme sc " +
"where sc.related_business_id = project.id and sc.is_deleted = :deleted and sc.tenant_id = :tenantId " +
"and sc.is_trans_purchase = :transPurchase " +
")");
}
if (Objects.nonNull(pageQry.getIds())) {
sql.append(" and pro.id in :ids");
}
if (Objects.nonNull(pageQry.getLowCreatProjectTime())) {
sql.append(" and pro.create_time >= :lowCreatProjectTime");
}
if (Objects.nonNull(pageQry.getTopCreatProjectTime())) {
sql.append(" and pro.create_time <= :topCreatProjectTime");
}
if (Objects.nonNull(pageQry.getCurrency())) {
sql.append(" and pro.currency = :currency");
}
if (Objects.nonNull(pageQry.getStatus())) {
sql.append(" and pro.status = :status");
}
sql.append(" and mem.project_member_id =:projectMemberId and pro.creator_id != :creatorId ) as p");
sql.append(" order by p.createTime desc");
Query nativeQuery = entityManager.createNativeQuery(sql.toString()).unwrap(NativeQueryImpl.class)
.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
if (Objects.nonNull(pageQry.getPurchaseProjectCode())) {
nativeQuery.setParameter("purchaseProjectCode", "%" + pageQry.getPurchaseProjectCode() + "%");
}
if (Objects.nonNull(pageQry.getPurchaseProjectName())) {
nativeQuery.setParameter("purchaseProjectName", "%" + pageQry.getPurchaseProjectName() + "%");
}
if (Objects.nonNull(pageQry.getPurchaseSubjectId())) {
nativeQuery.setParameter("purchaseSubjectId", pageQry.getPurchaseSubjectId());
}
if (Objects.nonNull(pageQry.getLowBudgetAmount())) {
nativeQuery.setParameter("lowBudgetAmount", pageQry.getLowBudgetAmount());
}
if (Objects.nonNull(pageQry.getTopBudgetAmount())) {
nativeQuery.setParameter("topBudgetAmount", pageQry.getTopBudgetAmount());
}
if (Objects.nonNull(pageQry.getTransPurchase())) {
nativeQuery.setParameter("transPurchase", pageQry.getTransPurchase());
}
if (Objects.nonNull(pageQry.getIds())) {
nativeQuery.setParameter("ids", pageQry.getIds());
}
if (Objects.nonNull(pageQry.getLowCreatProjectTime())) {
nativeQuery.setParameter("lowCreatProjectTime", pageQry.getLowCreatProjectTime());
}
if (Objects.nonNull(pageQry.getTopCreatProjectTime())) {
nativeQuery.setParameter("topCreatProjectTime", pageQry.getTopCreatProjectTime());
}
if (Objects.nonNull(pageQry.getCurrency())) {
nativeQuery.setParameter("currency", pageQry.getCurrency());
}
if (Objects.nonNull(pageQry.getStatus())) {
nativeQuery.setParameter("status", pageQry.getStatus());
}
nativeQuery.setParameter("tenantId",pageQry.getTenantId());
nativeQuery.setParameter("deleted",Boolean.FALSE);
nativeQuery.setParameter("projectMemberId", pageQry.getCreatorId());
nativeQuery.setParameter("creatorId", pageQry.getCreatorId());
List map = nativeQuery.getResultList();
nativeQuery.setFirstResult((pageQry.getPageNum() - 1) * pageQry.getPageSize());
nativeQuery.setMaxResults(pageQry.getPageSize());
List<Map<String, Object>> list = nativeQuery.getResultList();
List<PurchaseProjectEntity> purchaseProjectEntities = this.swapEntity(list);
Pagination<PurchaseProjectEntity> pagination = new Pagination();
pagination.setList(purchaseProjectEntities);
pagination.setPageNum(pageQry.getPageNum());
pagination.setPageSize(pageQry.getPageSize());
pagination.setTotalCount(map.size());
return pagination;
}
再注入 EntityManage的时候使用了 @PersistenceContext 注解，那可以不可以用@Autowired呢？
具体的分析如下
1.@PersistenceContext是jpa专有的注解，而@Autowired是spring自带的注释
2.EntityManager不是线程安全的，当多个请求进来的时候，spring会创建多个线程，@PersistenceContext就是用来为每个线程创建一个EntityManager的，而@Autowired只创建了一个，为所有线程共用，有可能报错
3.在使用EntityManager的时，请采用@PersistenceContext进行注解，而不要使用@Autowired
entityManager.createNativeQuery(sql.toString()).unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
这段代码就是将查询出的结果数据转化成Map的
其中entityManager.unwrap(NativeQuery.class)和entityManager.unwrap(SQLQuery.class)在Hibernaterv5.2之后已经作废，新版本的使用NativeQueryImpl.class
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
