JPA多表多条件查询解决思路

柔光的暖阳◎ 2023-07-07 12:50 15阅读 0赞

JPA单表多条件查询

可以在Service层使用Predicate实现
ServiceImpl实现类，查询条件可以自定义

  1. @Override
  2.     public Page<OrgInfo> findOrgByCondition(OrgParam orgParam, Pageable pageable)
  3.     {
  4.         return orgRepository.findAll((root, query, cb) -> {
  5.             List<Predicate> predicates = new ArrayList<>();
  6.             if (!StringUtils.isEmpty(orgParam.getFlag())){
  7.                 predicates.add(cb.equal(root.get("flag"),orgParam.getFlag()));
  8.             }
  9.             if (!StringUtils.isEmpty(orgParam.getParentId())){
  10.                 predicates.add(cb.equal(root.get("parentId"),orgParam.getParentId()));
  11.             }
  12.             if (!StringUtils.isEmpty(orgParam.getName())){
  13.                 predicates.add(cb.like(root.get("name"),orgParam.getName()+"%"));
  14.             }
  15.             if (orgParam.getMinSeqno()!=null && orgParam.getMaxSeqno()!=null) {
  16.                 Predicate agePredicate = cb.between(root.get("seqno"), orgParam.getMinSeqno(),
  17.                         orgParam.getMaxSeqno());
  18.                 predicates.add(agePredicate);
  19.             }
  20.             return query.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction();
  21.         }, pageable);
  22.     }

JPA多表多条件查询

如果针对多表多条件查询，可以使用原生SQL实现

  1. package com.gf.erp.dao;
  4. import java.sql.Date;
  5. import java.util.List;
  6. import java.util.Map;
  8. import javax.persistence.NamedNativeQuery;
  9. import javax.persistence.SqlResultSetMapping;
  10. import javax.transaction.Transactional;
  12. import org.springframework.data.domain.Page;
  13. import org.springframework.data.domain.Pageable;
  14. import org.springframework.data.jpa.repository.JpaRepository;
  15. import org.springframework.data.jpa.repository.Modifying;
  16. import org.springframework.data.jpa.repository.Query;
  17. import org.springframework.data.repository.query.Param;
  19. import com.gf.erp.model.Account;
  20. import com.gf.erp.model.FunctionInfo;
  21. import com.gf.erp.model.InStoreInfo;
  22. import com.gf.erp.model.OrderInfo;
  23. import com.gf.erp.model.OutStoreInfo;
  24. import com.gf.erp.model.StoreCheckInfo;
  25. import com.gf.erp.model.StoreInfo;
  26. import com.gf.erp.model.StoreNameInfo;
  27. import com.gf.erp.model.UserInfo;
  29. public interface StoreCheckDao extends JpaRepository<StoreCheckInfo,String> {
  31.     @Query(value = "select s.* from gufang_storecheck s,gf_content2user cu where s.storeId=cu.entityId "+
  32.         "and s.flag=:flag and cu.userId=:userId and cu.module='STORENAME' "+
  33.         "and IF (:storeId is not null, s.storeId=:storeId,1 = 1) "+
  34.         "and IF (:beginDt is not null, s.beginDt>=:beginDt,1 = 1) "+
  35.         "and IF (:endDt is not null, s.endDt<=:endDt,1 = 1) ",
  36.         countQuery = "select count(s.id) from gufang_storecheck s,gf_content2user cu where s.storeId=cu.entityId "+
  37.         "and s.flag=:flag and cu.userId=:userId and cu.module='STORENAME' "+
  38.         "and IF (:storeId is not null, s.storeId=:storeId,1 = 1) "+
  39.         "and IF (:beginDt is not null, s.beginDt>=:beginDt,1 = 1) "+
  40.         "and IF (:endDt is not null, s.endDt<=:endDt,1 = 1) ",
  41.         nativeQuery = true
  42.     )
  43.     public Page<StoreCheckInfo> findStoreNameListByUserId(@Param("flag") String flag,
  44.             @Param("userId") String userId,@Param("storeId") String storeId,
  45.             @Param("beginDt") Date beginDt,@Param("endDt") Date endDt,
  46.             Pageable pageable);
  48. }

使用EntityManager定义多条件查询

在服务实现类中注入EntityManager，自定义HQL查询数据，并且可以通过多表数据组装对象

  1. @PersistenceContext
  2. private EntityManager entityManager;

在这里插入图片描述

  1. package com.gf.erp.service.impl;
  3. import java.sql.Date;
  4. import java.util.ArrayList;
  5. import java.util.HashMap;
  6. import java.util.List;
  7. import java.util.Map;
  8. import java.util.Set;
  10. import javax.annotation.Resource;
  11. import javax.persistence.EntityManager;
  12. import javax.persistence.PersistenceContext;
  13. import javax.persistence.Query;
  14. import javax.persistence.criteria.CriteriaQuery;
  15. import javax.persistence.criteria.Predicate;
  17. import org.springframework.data.domain.Page;
  18. import org.springframework.data.domain.PageRequest;
  19. import org.springframework.data.domain.Pageable;
  20. import org.springframework.data.domain.Sort;
  21. import org.springframework.stereotype.Service;
  22. import org.springframework.transaction.annotation.Propagation;
  23. import org.springframework.transaction.annotation.Transactional;
  24. import org.springframework.util.StringUtils;
  26. import com.gf.erp.dao.FuncDao;
  27. import com.gf.erp.dao.InStoreDao;
  28. import com.gf.erp.dao.InStoreDetailDao;
  29. import com.gf.erp.dao.OrderDao;
  30. import com.gf.erp.dao.OrderItemDao;
  31. import com.gf.erp.dao.OutStoreDao;
  32. import com.gf.erp.dao.OutStoreDetailDao;
  33. import com.gf.erp.dao.PrdtDao;
  34. import com.gf.erp.dao.PrdtTypeDao;
  35. import com.gf.erp.dao.StoreCheckDao;
  36. import com.gf.erp.dao.StoreDao;
  37. import com.gf.erp.dao.StoreNameDao;
  38. import com.gf.erp.dto.PageData;
  39. import com.gf.erp.dto.ResultData;
  40. import com.gf.erp.model.FunctionInfo;
  41. import com.gf.erp.model.InStoreDetailInfo;
  42. import com.gf.erp.model.InStoreInfo;
  43. import com.gf.erp.model.OrderInfo;
  44. import com.gf.erp.model.OutStoreDetailInfo;
  45. import com.gf.erp.model.OutStoreInfo;
  46. import com.gf.erp.model.ProductInfo;
  47. import com.gf.erp.model.ProductTypeInfo;
  48. import com.gf.erp.model.QueryParam;
  49. import com.gf.erp.model.StoreCheckInfo;
  50. import com.gf.erp.model.StoreInfo;
  51. import com.gf.erp.model.StoreNameInfo;
  52. import com.gf.erp.service.FuncService;
  53. import com.gf.erp.service.PrdtService;
  54. import com.gf.erp.service.StoreService;
  55. import com.gf.erp.util.Util;
  57. @Service
  58. @Transactional(readOnly=false,rollbackFor = Exception.class, propagation = Propagation.REQUIRED)
  59. public class StoreServiceImpl implements StoreService{
  60.     @Resource
  61.     private PrdtDao prdtRepo;
  62.     @Resource
  63.     private StoreDao storeRepo;
  64.     @Resource
  65.     private StoreNameDao storeNameDao;
  66.     @Resource
  67.     private StoreDao storeDao;
  68.     @Resource
  69.     private OutStoreDao osDao;
  70.     @Resource
  71.     private InStoreDao isDao;
  72.     @Resource
  73.     private OrderDao orderDao;
  74.     @Resource
  75.     private StoreCheckDao storeChkDao;
  77.     @PersistenceContext
  78.     private EntityManager entityManager;
  80.     @Override
  81.     public PageData<StoreNameInfo> findStoreNameList(String flag,Integer page,Integer size,String name) {
  82.         PageData<StoreNameInfo> rtn = new PageData<StoreNameInfo>();
  83.         Sort sort = new Sort(Sort.Direction.DESC,"seqNo");
  84.         Pageable p = PageRequest.of(page,size,sort);
  85.         Page<StoreNameInfo> list = storeNameDao.findStoreNameList(p,flag,name);
  86.         rtn.setSize(size);
  87.         rtn.setPage(page);
  88.         rtn.setTotal(list.getTotalElements());
  89.         rtn.setList(list.getContent());
  90.         return rtn;
  91.     }
  94.     @Override
  95.     public List<StoreNameInfo> findStoreNameListByUserId(String flag, String userId) {
  96.         return storeNameDao.findStoreNameListByUserId(flag,userId);
  97.     }
  99.     @Override
  100.     public Boolean saveStoreName(StoreNameInfo sni) {
  101.         try
  102.         {
  103.             storeNameDao.save(sni);
  104.             return true;
  105.         }
  106.         catch(Exception e)
  107.         {
  108.             throw new RuntimeException(e);
  109.         }
  110.     }
  112.     @Override
  113.     public ResultData checkDeleteStore(StoreNameInfo sni) {
  114.         ResultData rtn = new ResultData(true);
  115.         List<OrderInfo> list = orderDao.findByStoreId(sni.getId());
  116.         if(list.size()>0)
  117.         {
  118.             rtn.setIsok(false);
  119.             rtn.addMsg("仓库删除", "仓库:["+sni.getName()+"] 存在关联订单记录,禁止删除");
  120.             return rtn;
  121.         }
  122.         List<StoreInfo> list2 = storeDao.findByStoreId(sni.getId());
  123.         if(list2.size()>0)
  124.         {
  125.             rtn.setIsok(false);
  126.             rtn.addMsg("仓库删除", "仓库:["+sni.getName()+"] 存在关联库存记录,禁止删除");
  127.             return rtn;
  128.         }
  129.         List<OutStoreInfo> list3 = osDao.findByStoreIdOrToStoreId(sni.getId(), sni.getId());
  130.         if(list3.size()>0)
  131.         {
  132.             rtn.setIsok(false);
  133.             rtn.addMsg("仓库删除", "仓库:["+sni.getName()+"] 存在关联出库记录,禁止删除");
  134.             return rtn;
  135.         }
  136.         List<InStoreInfo> list4 = isDao.findByStoreIdOrFromStoreId(sni.getId(), sni.getId());
  137.         if(list4.size()>0)
  138.         {
  139.             rtn.setIsok(false);
  140.             rtn.addMsg("仓库删除", "仓库:["+sni.getName()+"] 存在关联入库记录,禁止删除");
  141.             return rtn;
  142.         }
  143.         return rtn;
  144.     }
  146.     @Override
  147.     public Boolean deleteStoreName(String id) {
  148.         try
  149.         {
  150.             storeNameDao.deleteById(id);
  151.             return true;
  152.         }
  153.         catch(Exception e)
  154.         {
  155.             throw new RuntimeException(e);
  156.         }
  157.     }
  159.     @Override
  160.     public PageData<StoreInfo> findStoreList(String flag, Integer page, Integer size, String storeId,
  161.             String prdtName) {
  162.         PageData<StoreInfo> rtn = new PageData<StoreInfo>();
  163.         Sort sort = new Sort(Sort.Direction.DESC,"count");
  164.         Pageable p = PageRequest.of(page,size,sort);
  165.         Page<StoreInfo> list = storeDao.findStoreList(p,flag,storeId,prdtName);
  166.         rtn.setSize(size);
  167.         rtn.setPage(page);
  168.         rtn.setTotal(list.getTotalElements());
  169.         rtn.setList(list.getContent());
  170.         return rtn;
  171.     }
  173.     @Override
  174.     public PageData<StoreCheckInfo> findStoreChkListByUserId(String flag,String userId,String storeId,
  175.             Date beginDt,Date endDt,Integer page,Integer size) {
  176.         PageData<StoreCheckInfo> rtn = new PageData<StoreCheckInfo>();
  177.         Sort sort = new Sort(Sort.Direction.DESC,"endDt");
  178.         Pageable p = PageRequest.of(page,size,sort);
  180.         Page<StoreCheckInfo> list = storeChkDao.findStoreChkListByUserId(flag, userId, storeId, beginDt, endDt, p);
  181.         rtn.setSize(size);
  182.         rtn.setPage(page);
  183.         rtn.setTotal(list.getTotalElements());
  184.         rtn.setList(list.getContent());
  185.         return rtn;
  186.     }
  188.     @Override
  189.     public PageData<StoreCheckInfo> findStoreChkList(String flag,String storeId,
  190.             Date beginDt,Date endDt,Integer page,Integer size) {
  191.         PageData<StoreCheckInfo> rtn = new PageData<StoreCheckInfo>();
  192.         Sort sort = new Sort(Sort.Direction.DESC,"endDt");
  193.         Pageable p = PageRequest.of(page,size,sort);
  195.         Page<StoreCheckInfo> list = storeChkDao.findStoreChkList(flag,storeId, beginDt, endDt, p);
  196.         rtn.setSize(size);
  197.         rtn.setPage(page);
  198.         rtn.setTotal(list.getTotalElements());
  199.         rtn.setList(list.getContent());
  200.         return rtn;
  201.     }
  203.     @Override
  204.     public PageData<StoreCheckInfo> findStoreChkList2(String flag,String storeId,
  205.             Date beginDt,Date endDt,Integer page,Integer size) {
  206.         PageData<StoreCheckInfo> rtn = new PageData<StoreCheckInfo>();
  207.         Sort sort = new Sort(Sort.Direction.DESC,"endDt");
  208.         Integer startPosition = (page-1)*size;
  210.         String hql = "select new StoreCheckInfo(s.id,s.storeId,s.jbUserId,s.beginDt,"+
  211.                 "s.endDt,s.status,s.isCurrent,s.flag,s.testMode,sn.name) from StoreCheckInfo s,"+
  212.                 "StoreNameInfo sn where s.storeId=sn.id ";
  213.         if(!Util.isNull(storeId))
  214.         {
  215.             hql = hql + "and s.storeId=:storeId";
  216.         }
  217.         Query q = entityManager.createQuery(hql);
  218.         if(!Util.isNull(storeId))
  219.         {
  220.             q.setParameter("storeId", storeId);
  221.         }
  222.         Long total  = new Long(q.getResultList().size());
  223.         q.setFirstResult(startPosition);
  224.         q.setMaxResults(size);
  225.         List<StoreCheckInfo> list = q.getResultList();
  226.         rtn.setSize(size);
  227.         rtn.setPage(page);
  228.         rtn.setTotal(total);
  229.         rtn.setList(list);
  230.         return rtn;
  231.     }
  232. }

JPA多表数据组织实体对象

需要在JPQL中使用构造方法创建对象
在这里插入图片描述

  1. package com.gf.erp.dao;
  4. import java.util.List;
  5. import java.util.Map;
  7. import javax.persistence.NamedNativeQuery;
  8. import javax.persistence.SqlResultSetMapping;
  9. import javax.transaction.Transactional;
  11. import org.springframework.data.domain.Page;
  12. import org.springframework.data.domain.Pageable;
  13. import org.springframework.data.jpa.repository.JpaRepository;
  14. import org.springframework.data.jpa.repository.Modifying;
  15. import org.springframework.data.jpa.repository.Query;
  17. import com.gf.erp.model.UserInfo;
  19. public interface UserDao extends JpaRepository<UserInfo,String> {
  21.     @Query(value = "select * from orgmodel_user",nativeQuery = true)
  22.     Page<UserInfo> findPageUser(Pageable pageable);
  24.     @Query(value = "select u from UserInfo u where u.orgId=?1")
  25.     List<UserInfo> getUserListByOrgId(String orgId);
  27.     List<UserInfo> findByOrgPathLike(String path);
  29.     UserInfo findByFlagAndLoginId(String flag,String loginId);
  31.     @Query(value = "select new UserInfo(u.id,u.name,u.loginId,u.password,u.cardId,"+
  32.             "u.birthday,u.address,u.companyMail,u.privateMail,"+
  33.             "u.companyTeleNo,u.homeTeleNo,u.mobile,u.title,"+
  34.             "u.orgId,u.orgPath,u.desc,u.enabled,u.locked,"+
  35.             "u.managerId,u.shopId,u.failureDate,u.failureCount,"+
  36.             "u.openid,u.securityLevel,u.seqno,u.flag,"+
  37.             "o.name,o.name) from UserInfo u,OrgInfo o where u.orgId=o.id",
  38.             countQuery = "select count(u) from UserInfo u")
  39.     Page<UserInfo> findPageFilledUser(Pageable pageable);
  41.     @Query(value = "select o.name as department,u.* from orgmodel_user u,orgmodel_org o where u.orgid=o.id",
  42.             countQuery = "select count(*) from orgmodel_user",
  43.             nativeQuery = true)
  44.     List<Map<String,Object>> findMapUser(Pageable pageable);
  46.     @Modifying
  47.     @Query("update UserInfo u set u.locked=?2 where u.id=?1")
  48.     void updateUserLockById(String id,String locked);
  49. }

发表评论

表情：
评论列表 （有 0 条评论，15人围观）

还没有评论，来说两句吧...

相关阅读

    相关 Spring Data JPA多表查询

    多表查询在Spring Data JPA中有两种实现方式 第一种创建一个结果集接口来接收多表连查的结果 第二种利用JPA的关联映射来实现 先来熟悉一下几个注解 <t

    我就是我我就是我/ 0 赞/ 674 阅读

    相关 多条件查询

    > 多条件查询就是将sql语句进行拼接，在这里需要用到一个where 1=1 表示条件永远成立，这样就少了对where和and的处理 上代码，一目了然 String

    一时失言乱红尘一时失言乱红尘/ 0 赞/ 472 阅读