spring jpa 扩展 JpaSpecificationExecutor

最新推荐文章于 2024-09-02 16:52:36 发布

xuyelong1

最新推荐文章于 2024-09-02 16:52:36 发布

阅读量661

点赞数

分类专栏： spring

本文链接：https://blog.youkuaiyun.com/xuyelong1/article/details/53668092

版权

spring 专栏收录该内容

1 篇文章

订阅专栏

本文详细探讨了Spring JPA中的JpaSpecificationExecutor接口，它是如何用于实现复杂查询的。通过实例展示了如何使用Specification进行动态查询，以及它如何与Spring Data JPA结合，提升数据访问的灵活性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

package com.cheche365.cheche.core.repository;

import com.cheche365.cheche.core.model.InternalUser;
import com.cheche365.cheche.core.model.TelMarketingCenter;
import com.cheche365.cheche.core.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.*;
import org.springframework.data.domain.Page;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.support.JpaEntityInformation;
import org.springframework.data.jpa.repository.support.SimpleJpaRepository;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;
import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;
import java.util.Date;
import java.util.List;

@Repository
public interface TelMarketingCenterRepository extends TelMarketingCenterRepositoryCustom, PagingAndSortingRepository<TelMarketingCenter, Long> {

    List<TelMarketingCenter> findByOperatorAndDisplayOrderByCreateTime(InternalUser operator, boolean display);

    @Query(value = "SELECT * FROM tel_marketing_center  WHERE operator=?1 AND display=?2 AND status=?3", nativeQuery = true)
    List<TelMarketingCenter> findDisplayByStatus(InternalUser operator, boolean display, long status);

    @Query(value = " SELECT * FROM tel_marketing_center " +
        "WHERE trigger_time < NOW()+INTERVAL 15 MINUTE AND trigger_time IS NOT NULL " +
        "AND operator =?1 ORDER BY trigger_time DESC", nativeQuery = true)
    List<TelMarketingCenter> findByInternalUserAndTriggerTime(Long operatorId);

    @Query(value = "select * from tel_marketing_center tmc where tmc.status = ?1 and DATE(tmc.update_time) = ?2", nativeQuery = true)
    List<TelMarketingCenter> findByStatusAndUpdateTime(Long status, Date updateTime);

    TelMarketingCenter findFirstByMobile(String mobile);

    TelMarketingCenter findFirstByUser(User user);

    @Query(value="select count(*) from tel_marketing_center where operator=?1",nativeQuery = true)
    Integer countByOperator(Long operatorId);

    @Query(value="select * from tel_marketing_center where operator=?1 limit ?2",nativeQuery = true)
    List<TelMarketingCenter> findByOperator(InternalUser internalUser,Integer limit);

    List<TelMarketingCenter> findByMobile(String mobile);

    List<TelMarketingCenter> findByUser(User user);

    @Query(value = "select IFNULL(max(id),0) from tel_marketing_center where create_time <= ?1", nativeQuery = true)
    Long findMaxIdByTime(Date createTime);

    @Query(value = "SELECT * " +
    " FROM tel_marketing_center it " +
    " WHERE it.id IN (?1)" , nativeQuery = true)
    List<TelMarketingCenter> findUserSourceByIds(List ids);

    @Query(value = " SELECT * " +
    " FROM tel_marketing_center t " +
    " WHERE t.mobile IN ?1  " , nativeQuery = true)
    List<TelMarketingCenter> findTelMarketingCenterByMobiles(List mobiles);

    @Query(value = " SELECT s.description, count(*)                       " +
        " FROM tel_marketing_center t                               " +
        " JOIN tel_marketing_center_source s ON t.source = s.id     " +
        " WHERE t.create_time BETWEEN ?1 AND ?2 GROUP BY t.source   " , nativeQuery = true)
    List<Object[]> findSourceInputAmount(Date startTime, Date endTime);


    @Query(value = " SELECT center.* " +
        " FROM tel_marketing_center center " +
        " LEFT JOIN mobile_area area " +
        " ON center.mobile = area.mobile " +
        " WHERE area.mobile IS NULL AND center.id>?1  LIMIT ?2 " , nativeQuery = true)
    List<TelMarketingCenter> findByAreaIsNull(Long id,Integer limit);

    @Query(value = " SELECT count(*) " +
        " FROM tel_marketing_center center " +
        " LEFT JOIN mobile_area area " +
        " ON center.mobile = area.mobile " +
        " WHERE area.mobile IS NULL   " , nativeQuery = true)
    Long countByAreaIsNull();

    @Query(value = " SELECT t.* FROM tel_marketing_center t                                                                         " +
        " where t.`status` not in (?5)                                                                      " +
        " and (((t.trigger_time < NOW() OR t.trigger_time IS NULL) and t.expire_time BETWEEN ?1 and ?2)                             " +
        "  or (t.expire_time < NOW() and (DAYOFYEAR(t.trigger_time) < DAYOFYEAR(NOW()+INTERVAL -1 year) OR t.trigger_time IS NULL) " +
        "     and DAYOFYEAR(t.expire_time) BETWEEN DAYOFYEAR(?1+INTERVAL -1 year) and DAYOFYEAR(?2+INTERVAL -1 year)))            " +
        "     LIMIT ?4 OFFSET ?3                                                                                                  " , nativeQuery = true)
    List<TelMarketingCenter> findPushableData(Date startDate, Date endDate, int startIndex, int pageSize, List statusParams);

    @Query(value = "select count(*) from tel_marketing_center t where t.trigger_time BETWEEN ?1 AND ?2 and (case when ISNULL(?3) then 1 = 1 else t.operator = ?3 end)", nativeQuery = true)
    Integer countByTriggerTimeBetween(Date startDate, Date endDate, Long operator);

    @Query(value = "select t.* from tel_marketing_center t where t.trigger_time BETWEEN ?1 AND ?2 and (case when ISNULL(?3) then 1 = 1 else t.operator = ?3 end) order by t.trigger_time desc LIMIT ?4, ?5", nativeQuery = true)
    List<TelMarketingCenter> findByTriggerTimeBetween(Date startDate, Date endDate, Long operator, Integer firstResult, Integer maxResult);

}


interface TelMarketingCenterRepositoryCustom extends  JpaSpecificationExecutor<TelMarketingCenter>{
    Page<TelMarketingCenter> queryAllLimit(Specification<TelMarketingCenter> spec, Pageable pageable);
}

class TelMarketingCenterRepositoryImpl extends SimpleJpaRepository<TelMarketingCenter,Long> implements TelMarketingCenterRepositoryCustom {
    public TelMarketingCenterRepositoryImpl(JpaEntityInformation entityInformation, EntityManager entityManager) {
        super(entityInformation, entityManager);
    }

    public TelMarketingCenterRepositoryImpl(Class domainClass, EntityManager em) {
        super(domainClass, em);
    }

    @Autowired
    public TelMarketingCenterRepositoryImpl(EntityManager entityManager){
        super(TelMarketingCenter.class, entityManager);
    }

    @Override
    public Page<TelMarketingCenter> queryAllLimit(Specification<TelMarketingCenter> spec, Pageable pageable) {
        TypedQuery query = super.getQuery(spec, pageable);
        return readPage(query, pageable, spec);
    }

    @Override
    protected Page readPage(TypedQuery query, Pageable pageable, Specification spec) {
        query.setFirstResult(pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());
        return new PageImpl(query.getResultList());
    }
}