SpringDataJPA+QueryDSL聯合查詢

最近在研究SpringDataJPA和QueryDSL，以前都是各寫各的，沒有想到這兩個還能聯合适用，試了試，記錄一下。

先寫出兩種方式各自的查詢：

1，SpringDataJPA

根據名稱和類型查詢，但是不能模糊查詢

dao

package com.common.dao;

import com.cecft.common.entity.Org;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface OrgDao extends JpaSpecificationExecutor<Org>, JpaRepository<Org, String>, PagingAndSortingRepository<Org, String>{
    List<Org> findByNameAndType(String name, Integer type);
}
           

service

public List<Org> findByNameAndType (String name, Integer type) {
        List<Org> list = orgDao.findByNameAndType(name,type);
        return list;
}
           

2，QueryDSL

可以模糊查詢，但是代碼量不少哈

service

public List<Org> findByNameAndType (String name, Integer type) {
        QOrg qOrg = QOrg.org;
        BooleanBuilder builder = new BooleanBuilder();
        if (StringUtils.isNotBlank(name)) {
            builder.and(qOrg.name.like("%"+name+"%"));
        }
        if (type != null) {
            builder.and(qOrg.type.eq(type));
        }
        JPAQuery<Org> query = queryFactory.select(qOrg).from(qOrg).where(builder).orderBy(qOrg.updateTime.desc());

        QueryResults<Org> fetchResults = query.fetchResults();
        List<Org> list = fetchResults.getResults();
        return list;
    }
           

3，聯合查詢

dao

package com.common.dao;

import com.cecft.common.entity.Org;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface OrgDao extends JpaSpecificationExecutor<Org>, JpaRepository<Org, String>, PagingAndSortingRepository<Org, String>, QuerydslPredicateExecutor<Org> {

}
           

dao中繼承了QuerydslPredicateExecutor

注意：
	我開始用的是QueryDslPredicateExecutor，但是報錯：Cannot resolve symbol 'QueryDslPredicateExecutor'，後來百度一下，找不到該對應類，原來是因為我使用的是springBoot2.X，在springBoot2.X之後，該類修改成了QuerydslPredicateExecutor。把這個類名修改成QuerydslPredicateExecutor即可搞定啦。參考文章：
	(https://blog.csdn.net/qq_17351077/article/details/103166260)
           

service

public List<Org> findByNameAndType (String name, Integer type) {
        QOrg qOrg = QOrg.org;
        List<Org> list = (List<Org>) orgDao.findAll(qOrg.name.like("%"+name+"%").and(qOrg.type.eq(1)),qOrg.updateTime.desc());
        return list;
    }
           

測試：

@Test
    public void testOrg() {
        List<Org> list = orgService.findByNameAndType("山東",0);
        if (CollectionUtils.isNotEmpty(list)) {
            System.out.print(list.get(0).getName()+list.get(0).getType());
        }
    }
           

輸出：

Hibernate: 
    select
        org0_.id as id1_83_,
        org0_.create_by as create_b2_83_,
        org0_.create_time as create_t3_83_,
        org0_.del_flag as del_flag4_83_,
        org0_.update_by as update_b5_83_,
        org0_.update_time as update_t6_83_,
        org0_.address as address7_83_,
        org0_.att_id as att_id8_83_,
        org0_.category as category9_83_,
        org0_.code as code10_83_,
        org0_.des as des11_83_,
        org0_.enterprise_info as enterpr12_83_,
        org0_.enterprise_link as enterpr13_83_,
        org0_.legal_person as legal_p14_83_,
        org0_.name as name15_83_,
        org0_.order_num as order_n16_83_,
        org0_.pid as pid17_83_,
        org0_.region_scode1 as region_18_83_,
        org0_.region_scode2 as region_19_83_,
        org0_.region_scode3 as region_20_83_,
        org0_.tel as tel21_83_,
        org0_.type as type22_83_ 
    from
        sys_org org0_ 
    where
        (
            org0_.name like ? escape '!'
        ) 
        and org0_.type=? 
    order by
        org0_.update_time desc

山東印刷的部門1