SpringData JpaのJPQLマルチテーブル関連クエリー

9522 ワード
Jpa 
package com.touchhealth.trade.service.afterSale;

import com.touchealth.common.page.Pager;
import com.touchhealth.trade.bo.afterSale.AfterSaleBo;
import com.touchhealth.trade.entity.AfterSaleDo;
import com.touchhealth.trade.entity.afterSale.AfterSale;
import com.touchhealth.trade.helper.afterSale.AfterSaleHelper;
import com.touchhealth.trade.repository.afterSale.AfterSaleRepository;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Auther: dangshilin
 * @Date: 2018/7/30
 */
@Service("afterSaleService")
public class AfterSaleServiceImpl implements AfterSaleService{

    @Autowired
    private AfterSaleRepository afterSaleRepository;

    @PersistenceContext
    private EntityManager em;


    /**
     *          
     * @param afterSaleBo
     * @param pager
     * @return
     */
    @Override
    public Pager list(AfterSaleBo afterSaleBo, Pager pager) {
        String makerMobile = afterSaleBo.getMakerMobile();
        Integer applyType = afterSaleBo.getApplyType();
        Date endTime = afterSaleBo.getEndTime();
        Date startTime = afterSaleBo.getStartTime();
        String sourceCode = afterSaleBo.getSourceCode();
        Integer processState = afterSaleBo.getProcessState();
        int pageSize = pager.getPageSize();
        int pageNo = pager.getPageNo();
        int begin = pager.getPageSize()*(pager.getPageNo()-1);
        //         jpql
        String sql = "select new com.touchhealth.trade.entity.AfterSaleDo (a.id,a.afterSaleNo,a.reservationNo,a.makerId,a.makerName,a.makerMobile,a.createdAt,a.applyReason,a.examineFailReason,a.applyExpectTime,a.firstReservationTime,a.processState,a.applyType,a.deleteFlag,a.updatedAt,a.updatedOperatorId,a.updatedOperatorName,a.deletedAt,a.deletedOperatorId,a.deletedOperatorName,a.remark,s.sourceCode) from AfterSale a,Source s,Reservation r where a.reservationNo = r.reservationNo and r.id = s.typeObjectId and s.type = 1";
        //     sql
        String countSql = "select count(*) from t_after_sale a,t_source s,t_reservation r where a.reservation_no = r.reservation_no and r.id = s.type_object_id and s.type = 1";
        //     
        List params = new ArrayList<>();
        int i = 1;
        int j = 1;
        if (StringUtils.isNotBlank(makerMobile)) {
            sql = sql + " and a.makerMobile = ?" + i++;
            countSql = countSql + " and a.maker_mobile = ?" + j++;
            params.add(makerMobile);
        }
        if (null != applyType){
            sql = sql + " and a.applyType = ?" + i++;
            countSql = countSql + " and a.apply_type = ?" + j++;
            params.add(applyType);
        }
        if (StringUtils.isNotBlank(sourceCode)){
            sql = sql + " and s.sourceCode = ?" + i++;
            countSql = countSql + " and s.source_code = ?" + j++;
            params.add(sourceCode);
        }
        if (null != startTime){
            Date createdAt = startTime;
            sql = sql + " and s.createdAt >= ?" + i++;
            countSql = countSql + " and s.created_at >= ?" + j++;
            params.add(createdAt);
        }
        if (null != endTime){
            Date createdAt = endTime;
            sql = sql + " and a.createdAt <= ?" + i++;
            countSql = countSql + " and a.created_at <= ?" + j++;
            params.add(createdAt);
        }
        if (null != processState){
            sql = sql + " and a.processState = ?" + i++;
            countSql = countSql + " and a.process_state = ?" + j++;
            params.add(processState);
        }else {
            //         1-    2-     3-     4-   
            sql = sql + " and a.processState in (2,3,4)";
            countSql = countSql + " and a.process_state in (2,3,4)";
        }
        sql = sql + " ORDER by a.createdAt DESC ";
        countSql = countSql + " ORDER by a.created_at DESC ";
        Query query = em.createQuery(sql);
        Query countQuery = em.createNativeQuery(countSql);
        for (int k = 1; k < params.size() + 1; k++) {
            query.setParameter(k, params.get(k - 1));
            countQuery.setParameter(k, params.get(k - 1));
        }
        query.setFirstResult(begin);
        query.setMaxResults(pageSize);
        List resultList = query.getResultList();
        //     
        em.close();
        pager.setResultList(AfterSaleHelper.convertAfterSaleDoListToAfterSaleBoList(resultList));
        BigInteger count = (BigInteger)countQuery.getSingleResult();
        double records = count.intValue();
        pager.setRecords((int) records);
        double total = records/pageSize;
        pager.setTotal((int) Math.ceil(total));
        return pager;
    }


    /**
     *            
     * @param afterSaleBo
     * @return
     */
    @Override
    public List exportList(AfterSaleBo afterSaleBo) {
        String makerMobile = afterSaleBo.getMakerMobile();
        Integer applyType = afterSaleBo.getApplyType();
        Date endTime = afterSaleBo.getEndTime();
        Date startTime = afterSaleBo.getStartTime();
        String sourceCode = afterSaleBo.getSourceCode();
        Integer processState = afterSaleBo.getProcessState();
        //         jpql
        String sql = "select new com.touchhealth.trade.entity.AfterSaleDo (a.id,a.afterSaleNo,a.reservationNo,a.makerId,a.makerName,a.makerMobile,a.createdAt,a.applyReason,a.examineFailReason,a.applyExpectTime,a.firstReservationTime,a.processState,a.applyType,a.deleteFlag,a.updatedAt,a.updatedOperatorId,a.updatedOperatorName,a.deletedAt,a.deletedOperatorId,a.deletedOperatorName,a.remark,s.sourceCode,r.serviceItemName,r.reservedPartyName,r.reservationPartyInfo) from AfterSale a,Source s,Reservation r where a.reservationNo = r.reservationNo and r.id = s.typeObjectId and s.type = 1";
        //     
        List params = new ArrayList<>();
        int i = 1;
        if (StringUtils.isNotBlank(makerMobile)) {
            sql = sql + " and a.makerMobile = ?" + i++;
            params.add(makerMobile);
        }
        if (null != applyType){
            sql = sql + " and a.applyType = ?" + i++;
            params.add(applyType);
        }
        if (StringUtils.isNotBlank(sourceCode)){
            sql = sql + " and s.sourceCode = ?" + i++;
            params.add(sourceCode);
        }
        if (null != startTime){
            Date createdAt = startTime;
            sql = sql + " and s.createdAt >= ?" + i++;
            params.add(createdAt);
        }
        if (null != endTime){
            Date createdAt = endTime;
            sql = sql + " and a.createdAt <= ?" + i++;
            params.add(createdAt);
        }
        if (null != processState){
            sql = sql + " and a.processState = ?" + i++;
            params.add(processState);
        }else {
            //         1-    2-     3-     4-   
            sql = sql + " and a.processState in (2,3,4)";
        }
        sql = sql + " ORDER by a.createdAt DESC ";
        Query query = em.createQuery(sql);
        for (int k = 1; k < params.size() + 1; k++) {
            query.setParameter(k, params.get(k - 1));
        }
        List resultList = query.getResultList();
        //     
        em.close();
        List afterSaleBoList = AfterSaleHelper.convertAfterSaleDoListToAfterSaleBoList(resultList);
        return afterSaleBoList;
    }
}

以上がビジネス層コードである.
@Autowired
private AfterSaleRepository afterSaleRepository;
. 
@PersistenceContext
private EntityManager em;
Persistence context 。 entity manager 。Entity manager persistence context ， flush 。 persistence context ， EntityManager 。 persistence context ， entity manager ， 。Java Persistence persistence context， transaction-scoped persistence context extended persistence context。Transaction-scoped persistence context persistence context ， 。 ，transaction-scoped persistence context ， （detached）。 persistence context 。 ， @PersistenceContext （ XML ） EntityManager 。
注意:
最初のピット:
Persistence contextは、管理するエンティティオブジェクトのセットからなる集合であり、entity managerによって管理されるからである.だからつなぎ合わせるsqlの中で“......from AfterSale a,Source s,Reservation r......”AfterSale,Source,Reservationは、テーブル名ではなくデータベーステーブルにマッピングされたエンティティクラスの名前です.クエリーの内容は、データベーステーブルフィールドではなくエンティティクラスの属性です.またNativeQueryはHQLクエリーを使用するため、つづりのcountSqlにはデータベーステーブル名とデータベースサブセグメント名が対応する.
2番目のピット:
クエリーの結果を受信するにはオブジェクトが必要です.このオブジェクトはsetを提供するほか、getメソッドにはそれに対応するパラメトリック構造メソッドを提供する必要がある.なお、結果の属性に完全に対応するパラメトリック構造方法である.
ページ分けが必要でなければcountSql関連のPager関連を削除すればよい.以上は個人的な浅見にすぎません.間違いがあれば、指摘を歓迎します.
JPA Persistence Unit
requestsモジュールは微博熱検索ランキングに登ります