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

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

## JPA单表多条件查询 ##

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

@Override
    	public Page<OrgInfo> findOrgByCondition(OrgParam orgParam, Pageable pageable)
    	{
    		return orgRepository.findAll((root, query, cb) -> {
    			List<Predicate> predicates = new ArrayList<>();
                if (!StringUtils.isEmpty(orgParam.getFlag())){
                    predicates.add(cb.equal(root.get("flag"),orgParam.getFlag()));
                }
                if (!StringUtils.isEmpty(orgParam.getParentId())){
                    predicates.add(cb.equal(root.get("parentId"),orgParam.getParentId()));
                }
                if (!StringUtils.isEmpty(orgParam.getName())){
                    predicates.add(cb.like(root.get("name"),orgParam.getName()+"%"));
                }
                if (orgParam.getMinSeqno()!=null && orgParam.getMaxSeqno()!=null) {
                    Predicate agePredicate = cb.between(root.get("seqno"), orgParam.getMinSeqno(),
                    		orgParam.getMaxSeqno());
                    predicates.add(agePredicate);
                }
                return query.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction();
    		}, pageable);
    	}

## JPA多表多条件查询 ##

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

package com.gf.erp.dao;
    
    
    import java.sql.Date;
    import java.util.List;
    import java.util.Map;
    
    import javax.persistence.NamedNativeQuery;
    import javax.persistence.SqlResultSetMapping;
    import javax.transaction.Transactional;
    
    import org.springframework.data.domain.Page;
    import org.springframework.data.domain.Pageable;
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.jpa.repository.Modifying;
    import org.springframework.data.jpa.repository.Query;
    import org.springframework.data.repository.query.Param;
    
    import com.gf.erp.model.Account;
    import com.gf.erp.model.FunctionInfo;
    import com.gf.erp.model.InStoreInfo;
    import com.gf.erp.model.OrderInfo;
    import com.gf.erp.model.OutStoreInfo;
    import com.gf.erp.model.StoreCheckInfo;
    import com.gf.erp.model.StoreInfo;
    import com.gf.erp.model.StoreNameInfo;
    import com.gf.erp.model.UserInfo;
    
    public interface StoreCheckDao extends JpaRepository<StoreCheckInfo,String> {
    
    	@Query(value = "select s.* from gufang_storecheck s,gf_content2user cu where s.storeId=cu.entityId "+
    		"and s.flag=:flag and cu.userId=:userId and cu.module='STORENAME' "+
    		"and IF (:storeId is not null, s.storeId=:storeId,1 = 1) "+
    		"and IF (:beginDt is not null, s.beginDt>=:beginDt,1 = 1) "+
    		"and IF (:endDt is not null, s.endDt<=:endDt,1 = 1) ",
    		countQuery = "select count(s.id) from gufang_storecheck s,gf_content2user cu where s.storeId=cu.entityId "+
    		"and s.flag=:flag and cu.userId=:userId and cu.module='STORENAME' "+
    		"and IF (:storeId is not null, s.storeId=:storeId,1 = 1) "+
    		"and IF (:beginDt is not null, s.beginDt>=:beginDt,1 = 1) "+
    		"and IF (:endDt is not null, s.endDt<=:endDt,1 = 1) ",
    		nativeQuery = true
    	)
    	public Page<StoreCheckInfo> findStoreNameListByUserId(@Param("flag") String flag,
    			@Param("userId") String userId,@Param("storeId") String storeId,
    			@Param("beginDt") Date beginDt,@Param("endDt") Date endDt,
    			Pageable pageable);
    
    }

## 使用EntityManager定义多条件查询 ##

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

@PersistenceContext
    private EntityManager entityManager;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FpeGlhbmdfY2hlbg_size_16_color_FFFFFF_t_70]

package com.gf.erp.service.impl;
    
    import java.sql.Date;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    import java.util.Set;
    
    import javax.annotation.Resource;
    import javax.persistence.EntityManager;
    import javax.persistence.PersistenceContext;
    import javax.persistence.Query;
    import javax.persistence.criteria.CriteriaQuery;
    import javax.persistence.criteria.Predicate;
    
    import org.springframework.data.domain.Page;
    import org.springframework.data.domain.PageRequest;
    import org.springframework.data.domain.Pageable;
    import org.springframework.data.domain.Sort;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Propagation;
    import org.springframework.transaction.annotation.Transactional;
    import org.springframework.util.StringUtils;
    
    import com.gf.erp.dao.FuncDao;
    import com.gf.erp.dao.InStoreDao;
    import com.gf.erp.dao.InStoreDetailDao;
    import com.gf.erp.dao.OrderDao;
    import com.gf.erp.dao.OrderItemDao;
    import com.gf.erp.dao.OutStoreDao;
    import com.gf.erp.dao.OutStoreDetailDao;
    import com.gf.erp.dao.PrdtDao;
    import com.gf.erp.dao.PrdtTypeDao;
    import com.gf.erp.dao.StoreCheckDao;
    import com.gf.erp.dao.StoreDao;
    import com.gf.erp.dao.StoreNameDao;
    import com.gf.erp.dto.PageData;
    import com.gf.erp.dto.ResultData;
    import com.gf.erp.model.FunctionInfo;
    import com.gf.erp.model.InStoreDetailInfo;
    import com.gf.erp.model.InStoreInfo;
    import com.gf.erp.model.OrderInfo;
    import com.gf.erp.model.OutStoreDetailInfo;
    import com.gf.erp.model.OutStoreInfo;
    import com.gf.erp.model.ProductInfo;
    import com.gf.erp.model.ProductTypeInfo;
    import com.gf.erp.model.QueryParam;
    import com.gf.erp.model.StoreCheckInfo;
    import com.gf.erp.model.StoreInfo;
    import com.gf.erp.model.StoreNameInfo;
    import com.gf.erp.service.FuncService;
    import com.gf.erp.service.PrdtService;
    import com.gf.erp.service.StoreService;
    import com.gf.erp.util.Util;
    
    @Service
    @Transactional(readOnly=false,rollbackFor = Exception.class, propagation = Propagation.REQUIRED)
    public class StoreServiceImpl implements StoreService{
    	@Resource
    	private PrdtDao prdtRepo;
    	@Resource
    	private StoreDao storeRepo;
    	@Resource
    	private StoreNameDao storeNameDao;
    	@Resource
    	private StoreDao storeDao;
    	@Resource
    	private OutStoreDao osDao;
    	@Resource
    	private InStoreDao isDao;
    	@Resource
    	private OrderDao orderDao;
    	@Resource
    	private StoreCheckDao storeChkDao;
    	
    	@PersistenceContext
    	private EntityManager entityManager;
    	
    	@Override
    	public PageData<StoreNameInfo> findStoreNameList(String flag,Integer page,Integer size,String name) {
    		PageData<StoreNameInfo> rtn = new PageData<StoreNameInfo>();
    		Sort sort = new Sort(Sort.Direction.DESC,"seqNo");
            Pageable p = PageRequest.of(page,size,sort);
            Page<StoreNameInfo> list = storeNameDao.findStoreNameList(p,flag,name);
            rtn.setSize(size);
            rtn.setPage(page);
            rtn.setTotal(list.getTotalElements());
            rtn.setList(list.getContent());
    		return rtn;
    	}
    	
    
    	@Override
    	public List<StoreNameInfo> findStoreNameListByUserId(String flag, String userId) {
    		return storeNameDao.findStoreNameListByUserId(flag,userId);
    	}
    
    	@Override
    	public Boolean saveStoreName(StoreNameInfo sni) {
    		try
    		{
    			storeNameDao.save(sni);
    			return true;
    		}
    		catch(Exception e)
    		{
    			throw new RuntimeException(e);
    		}
    	}
    
    	@Override
    	public ResultData checkDeleteStore(StoreNameInfo sni) {
    		ResultData rtn = new ResultData(true);
    		List<OrderInfo> list = orderDao.findByStoreId(sni.getId());
    		if(list.size()>0)
    		{
    			rtn.setIsok(false);
    			rtn.addMsg("仓库删除", "仓库:["+sni.getName()+"] 存在关联订单记录,禁止删除");
    			return rtn;
    		}
    		List<StoreInfo> list2 = storeDao.findByStoreId(sni.getId());
    		if(list2.size()>0)
    		{
    			rtn.setIsok(false);
    			rtn.addMsg("仓库删除", "仓库:["+sni.getName()+"] 存在关联库存记录,禁止删除");
    			return rtn;
    		}
    		List<OutStoreInfo> list3 = osDao.findByStoreIdOrToStoreId(sni.getId(), sni.getId());
    		if(list3.size()>0)
    		{
    			rtn.setIsok(false);
    			rtn.addMsg("仓库删除", "仓库:["+sni.getName()+"] 存在关联出库记录,禁止删除");
    			return rtn;
    		}
    		List<InStoreInfo> list4 = isDao.findByStoreIdOrFromStoreId(sni.getId(), sni.getId());
    		if(list4.size()>0)
    		{
    			rtn.setIsok(false);
    			rtn.addMsg("仓库删除", "仓库:["+sni.getName()+"] 存在关联入库记录,禁止删除");
    			return rtn;
    		}
    		return rtn;
    	}
    
    	@Override
    	public Boolean deleteStoreName(String id) {
    		try
    		{
    			storeNameDao.deleteById(id);
    			return true;
    		}
    		catch(Exception e)
    		{
    			throw new RuntimeException(e);
    		}
    	}
    
    	@Override
    	public PageData<StoreInfo> findStoreList(String flag, Integer page, Integer size, String storeId,
    			String prdtName) {
    		PageData<StoreInfo> rtn = new PageData<StoreInfo>();
    		Sort sort = new Sort(Sort.Direction.DESC,"count");
            Pageable p = PageRequest.of(page,size,sort);
            Page<StoreInfo> list = storeDao.findStoreList(p,flag,storeId,prdtName);
            rtn.setSize(size);
            rtn.setPage(page);
            rtn.setTotal(list.getTotalElements());
            rtn.setList(list.getContent());
    		return rtn;
    	}
    	
    	@Override
    	public PageData<StoreCheckInfo> findStoreChkListByUserId(String flag,String userId,String storeId,
    			Date beginDt,Date endDt,Integer page,Integer size) {
    		PageData<StoreCheckInfo> rtn = new PageData<StoreCheckInfo>();
    		Sort sort = new Sort(Sort.Direction.DESC,"endDt");
            Pageable p = PageRequest.of(page,size,sort);
            
            Page<StoreCheckInfo> list = storeChkDao.findStoreChkListByUserId(flag, userId, storeId, beginDt, endDt, p);
            rtn.setSize(size);
            rtn.setPage(page);
            rtn.setTotal(list.getTotalElements());
            rtn.setList(list.getContent());
    		return rtn;
    	}
    
    	@Override
    	public PageData<StoreCheckInfo> findStoreChkList(String flag,String storeId,
    			Date beginDt,Date endDt,Integer page,Integer size) {
    		PageData<StoreCheckInfo> rtn = new PageData<StoreCheckInfo>();
    		Sort sort = new Sort(Sort.Direction.DESC,"endDt");
            Pageable p = PageRequest.of(page,size,sort);
            
            Page<StoreCheckInfo> list = storeChkDao.findStoreChkList(flag,storeId, beginDt, endDt, p);
            rtn.setSize(size);
            rtn.setPage(page);
            rtn.setTotal(list.getTotalElements());
            rtn.setList(list.getContent());
    		return rtn;
    	}
    	
    	@Override
    	public PageData<StoreCheckInfo> findStoreChkList2(String flag,String storeId,
    			Date beginDt,Date endDt,Integer page,Integer size) {
    		PageData<StoreCheckInfo> rtn = new PageData<StoreCheckInfo>();
    		Sort sort = new Sort(Sort.Direction.DESC,"endDt");
    		Integer startPosition = (page-1)*size;
            
            String hql = "select new StoreCheckInfo(s.id,s.storeId,s.jbUserId,s.beginDt,"+
            		"s.endDt,s.status,s.isCurrent,s.flag,s.testMode,sn.name) from StoreCheckInfo s,"+
            		"StoreNameInfo sn where s.storeId=sn.id ";
            if(!Util.isNull(storeId))
            {
            	hql = hql + "and s.storeId=:storeId";
            }
            Query q = entityManager.createQuery(hql);
            if(!Util.isNull(storeId))
            {
            	q.setParameter("storeId", storeId);
            }
            Long total  = new Long(q.getResultList().size());
            q.setFirstResult(startPosition);
            q.setMaxResults(size);
            List<StoreCheckInfo> list = q.getResultList();
            rtn.setSize(size);
            rtn.setPage(page);
            rtn.setTotal(total);
            rtn.setList(list);
    		return rtn;
    	}
    }

## JPA多表数据组织实体对象 ##

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

package com.gf.erp.dao;
    
    
    import java.util.List;
    import java.util.Map;
    
    import javax.persistence.NamedNativeQuery;
    import javax.persistence.SqlResultSetMapping;
    import javax.transaction.Transactional;
    
    import org.springframework.data.domain.Page;
    import org.springframework.data.domain.Pageable;
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.jpa.repository.Modifying;
    import org.springframework.data.jpa.repository.Query;
    
    import com.gf.erp.model.UserInfo;
    
    public interface UserDao extends JpaRepository<UserInfo,String> {
    
        @Query(value = "select * from orgmodel_user",nativeQuery = true)
        Page<UserInfo> findPageUser(Pageable pageable);
        
        @Query(value = "select u from UserInfo u where u.orgId=?1")
        List<UserInfo> getUserListByOrgId(String orgId);
        
        List<UserInfo> findByOrgPathLike(String path);
        
        UserInfo findByFlagAndLoginId(String flag,String loginId);
        
        @Query(value = "select new UserInfo(u.id,u.name,u.loginId,u.password,u.cardId,"+
    			"u.birthday,u.address,u.companyMail,u.privateMail,"+
    			"u.companyTeleNo,u.homeTeleNo,u.mobile,u.title,"+
    			"u.orgId,u.orgPath,u.desc,u.enabled,u.locked,"+
    			"u.managerId,u.shopId,u.failureDate,u.failureCount,"+
    			"u.openid,u.securityLevel,u.seqno,u.flag,"+
    			"o.name,o.name) from UserInfo u,OrgInfo o where u.orgId=o.id",
        		countQuery = "select count(u) from UserInfo u")
        Page<UserInfo> findPageFilledUser(Pageable pageable);
        
        @Query(value = "select o.name as department,u.* from orgmodel_user u,orgmodel_org o where u.orgid=o.id",
        		countQuery = "select count(*) from orgmodel_user",
        		nativeQuery = true)
        List<Map<String,Object>> findMapUser(Pageable pageable);
        
        @Modifying
        @Query("update UserInfo u set u.locked=?2 where u.id=?1")
        void updateUserLockById(String id,String locked);
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FpeGlhbmdfY2hlbg_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200223215427777.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FpeGlhbmdfY2hlbg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FpeGlhbmdfY2hlbg_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200223200545253.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FpeGlhbmdfY2hlbg==,size_16,color_FFFFFF,t_70