Spring Data JPA中使用QueryDSL进行查询

1、引入querydsl

1.1、导包

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.1.4.RELEASE</version>
</parent>
<dependencies>
    <!--    querydsl    -->
    <dependency>
        <groupId>com.querydsl</groupId>
        <artifactId>querydsl-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>com.querydsl</groupId>
        <artifactId>querydsl-apt</artifactId>
        <scope>provided</scope>
    </dependency>
</dependencies>

1.2、添加插件

<build>
    <plugins>
        <plugin>
            <groupId>com.mysema.maven</groupId>
            <artifactId>apt-maven-plugin</artifactId>
            <version>1.1.3</version>
            <executions>
                <execution>
                    <goals>
                        <goal>process</goal>
                    </goals>
                    <configuration>
                        <outputDirectory>target/generated-sources/java</outputDirectory>
                        <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
                    </configuration>
                </execution>
            </executions>
        </plugin>
    </plugins>
</build>

插件的作用是把带有@Entity注解的实体类在指定路径target/generated-sources/java下生成一个衍生的实体类，我们后面就是用这个衍生出来的实体类去构建动态查询的条件进行动态查询。

1.3、注入bean

@Bean
public JPAQueryFactory jpaQueryFactory(EntityManager entityManager) {
    return new JPAQueryFactory(entityManager);
}

2、创建实体类

@Entity
@Table(name = "actor")
@Data
public class Actor {
    /**
     * 主键生成采用数据库自增方式，比如MySQL的AUTO_INCREMENT
     */
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name = "actor_name", nullable = false, length = 128, unique = true)
    private String actorName;
    @Column(name = "actor_age", nullable = false)
    private int actorAge;
    @Column(name = "actor_email", length = 64, unique = true)
    private String actorEmail;
    @Column(name = "create_time", nullable = false, length = 32)
    private String createTime = DateUtil.format(new Date(), "yyyy-MM-dd HH:mm:ss SSS");
}

如果是IDEA工具，执行Maven插件的compile就能在指定目录生成QActor类。

如果是Eclipse，右键项目工程，执行maven的update操作就可以在指定目录中生成实体类。

3、创建Repository

需要继承 QuerydslPredicateExecutor

public interface QuerydslRepository extends JpaRepository<Actor, Long>, QuerydslPredicateExecutor<Actor> {
}

4、使用QueryDSL

4.1、原生dsl查询

4.1.1、直接根据条件查询

@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class QuerydslTest {
    @Autowired
    private JPAQueryFactory jpaQueryFactory;
    /**
     * 直接根据条件查询
     */
    @Test
    public void testFindByActorNameAndActorEmail() {
        QActor qActor = QActor.actor;
        Actor actor = jpaQueryFactory.selectFrom(qActor)
                .where(
                        qActor.actorName.eq("高庸涵"),
                        qActor.actorEmail.eq("123456789@qq.com")
                )
                .fetchOne();
        log.info(JSONUtil.toJsonPrettyStr(actor));
    }
}

4.1.2、查询所有并根据字段排序

/**
 * 查询所有并根据字段排序
 */
@Test
public void testFindAll() {
    QActor qActor = QActor.actor;
    List<Actor> actorList = jpaQueryFactory.selectFrom(qActor)
            .orderBy(
                    qActor.actorAge.asc()
            )
            .fetch();
    log.info(JSONUtil.toJsonPrettyStr(actorList));
}

4.1.3、分页查询，并根据字段排序

/**
 * 分页查询，并根据字段排序
 */
@Test
public void testFindByPagination() {
    int page = 0; // 第几页
    int pageSize = 10; // 每页大小
    QActor qActor = QActor.actor;
    QueryResults<Actor> actorQueryResults = jpaQueryFactory.selectFrom(qActor)
            .orderBy(
                    qActor.actorAge.asc()
            )
            .offset(page)
            .limit(pageSize)
            .fetchResults();
    // 获取分页参数
    long total = actorQueryResults.getTotal();
    long totalPage = (total % pageSize == 0) ? (total / pageSize) : (total / pageSize + 1);
    log.info("分页查询第:[{}]页,pageSize:[{}],共有:[{}]数据,共有:[{}]页", page, pageSize, total, totalPage);
    List<Actor> actorListByPagination = actorQueryResults.getResults();
    log.info(JSONUtil.toJsonPrettyStr(actorListByPagination));
}

4.1.4、根据条件模糊查询，并指定某个字段的范围

/**
 * 根据条件模糊查询，并指定某个字段的范围
 */
@Test
public void testFindByLikeNameAndEmailAndBetweenAgeOrderById() {
    QActor qActor = QActor.actor;
    List<Actor> actorList = jpaQueryFactory.selectFrom(qActor)
            .where(
                    qActor.actorName.like("name%"),
                    qActor.actorEmail.like("email%"),
                    qActor.actorAge.between(20, 50)
            )
            .orderBy(
                    qActor.id.asc()
            )
            .fetch();
    log.info(JSONUtil.toJsonPrettyStr(actorList));
}

4.2、jpa整合dsl查询

4.2.1、模糊查询并分页排序

@Autowired
private QuerydslRepository querydslRepository;
/**
 * 模糊查询并分页排序
 */
@Test
public void testFindByActorNameAndActorEmailPagination() {
    int page = 0; // 第几页
    int pageSize = 10; // 每页大小
    QActor qActor = QActor.actor;
    // 模糊查询条件
    BooleanExpression expression = qActor.actorName.like("name%").and(qActor.actorEmail.like("email%"));
    // 排序、分页参数
    Sort sort = new Sort(Sort.Direction.DESC, "actorAge");
    PageRequest pageRequest = PageRequest.of(page < 0 ? 0 : page, pageSize, sort);
    Page<Actor> actorPage = querydslRepository.findAll(expression, pageRequest);
    log.info("分页查询第:[{}]页,pageSize:[{}],共有:[{}]数据,共有:[{}]页", page, pageSize, actorPage.getTotalElements(), actorPage.getTotalPages());
    List<Actor> actorListByPagination = actorPage.getContent();
    log.info(JSONUtil.toJsonPrettyStr(actorListByPagination));
}

4.2.2、动态查询并分页排序

/**
 * 动态查询并分页排序
 */
@Test
public void testFindByDynamicQuery() {
    Integer actorAge = 45;
    String actorEmail = "email";
    String actorName = null;
    String createTime = "2020-11-21";
    int page = 0; // 第几页
    int pageSize = 10; // 每页大小
    QActor qActor = QActor.actor;
    // 初始化组装条件(类似where 1=1)
    Predicate predicate = qActor.isNotNull().or(qActor.isNull());
    //执行动态条件拼装
    // 相等
    predicate = actorAge == null ? predicate : ExpressionUtils.and(predicate, qActor.actorAge.eq(actorAge));
    // like 模糊匹配
    predicate = actorEmail == null ? predicate : ExpressionUtils.and(predicate, qActor.actorEmail.like(actorEmail + "%"));
    predicate = actorName == null ? predicate : ExpressionUtils.and(predicate, qActor.actorName.like(actorName + "%"));
    // 包含，相当于like %xxx%
    predicate = createTime == null ? predicate : ExpressionUtils.and(predicate, qActor.createTime.contains(createTime));
    // 排序、分页参数
    Sort sort = new Sort(Sort.Direction.ASC, "id");
    PageRequest pageRequest = PageRequest.of(page < 0 ? 0 : page, pageSize, sort);
    Page<Actor> actorPage = querydslRepository.findAll(predicate, pageRequest);
    log.info("分页查询第:[{}]页,pageSize:[{}],共有:[{}]数据,共有:[{}]页", page, pageSize, actorPage.getTotalElements(), actorPage.getTotalPages());
    List<Actor> actorListByPagination = actorPage.getContent();
    log.info(JSONUtil.toJsonPrettyStr(actorListByPagination));
}

代码地址

• github：https://github.com/senlinmu1008/jpa-action/tree/master/jpa-dynamic-query
• gitee：https://gitee.com/ppbin/jpa-action/tree/master/jpa-dynamic-query