使用querydsl配合jpa完成多表多条件查询

准备

querdsl ： 官网
jpa+querydsl查询示例：示例

落地实现

环境：

• springboot：2.3.1
• querydsl：4.3.1

依赖（下面括号参数springboot2.3.1内部已有，不用管）

        <!--query dsl-->
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-apt</artifactId>
            <version>${querydsl.version}</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-jpa</artifactId>
            <version>${querydsl.version}</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.6.1</version>
        </dependency>

基础entity或po所在模块加以下插件

<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>src/generated-sources/java</outputDirectory>
                            <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

全局配置

/**
 * @author wjl
 */
@Configuration
public class QuerydslConfig {


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

yml中配置打印sql和参数

spring:
  datasource:
    name: wangsr
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      driver-class-name: com.mysql.cj.jdbc.Driver
      #自动提交从池中返回的连接
      auto-commit: true
      #池中维护的最小空闲连接数
      minimum-idle: 5
      ## 数据库连接超时时间,默认30秒，即30000
      idle-timeout: 60000
      #等待连接的超时时间，毫秒
      connection-timeout: 30000
    url: jdbc:mysql://localhost:3306/suc-account-books?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull
    username: root
    password: root
  jpa:
    hibernate:
      ddl-auto: update  #更新数据表不存在则自动创建
    show-sql: true  #打印sql

## 配置打印jpa sql参数
logging:
  level:
     org.hibernate.type.descriptor.sql.BasicBinder: trace

具体repository配置，举例(UserInfo为一个entity)

package cn.wangsr.account.dao;

import cn.wangsr.account.model.entity.UserInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.stereotype.Repository;

/**
 * @author wjl
 */
@Repository
public interface UserRepository  extends JpaRepository<UserInfo,Long>,QuerydslPredicateExecutor<UserInfo> {
}

具体使用

    @Autowired
    UserRepository userRepository;
    @Autowired
    JPAQueryFactory queryFactory;

private void test(Long userId,String groupName){
        QUserGroupPO qUserGroupPO = QUserGroupPO.userGroupPO;
        StringTemplate groupName_q  = Expressions.
                stringTemplate("CONCAT('%',{0},'%')",groupName);
        StringTemplate userId_q  = Expressions.
                stringTemplate("CONCAT('%,',{0},',%')",userId);
      jpaQueryFactory.select(Projections.bean(MessageShowPageDTO.class,
                qUserGroupPO.id.as("chatId"),
                qUserGroupPO.groupName.as("chatName"),
                qUserGroupPO.avatarUrl ))
                .from(qUserGroupPO)
                .where(qUserGroupPO.groupUsersIds.like(userId_q)
                .and(qUserGroupPO.groupName.like(groupName_q)))
                .fetch();
    }

注意：启动类配置entity扫描和自定义repository扫描

说明

• StringTemplate 用于执行数据库函数，可以自定义参数

 StringTemplate groupName_q  = Expressions.
                stringTemplate("CONCAT('%',{0},'%')",groupName);