准备
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);
评论区