jpa+QueryDSL

越星晖

2023-12-01

QueryDSL

简介

QueryDSL仅仅是一个通用的查询框架，专注于通过Java API构建类型安全的SQL查询。
Querydsl可以通过一组通用的查询API为用户构建出适合不同类型ORM框架或者是SQL的查询语句，也就是说QueryDSL是基于各种ORM框架以及SQL之上的一个通用的查询框架。
借助QueryDSL可以在任何支持的ORM框架或者SQL平台上以一种通用的API方式来构建查询。目前QueryDSL支持的平台包括JPA，JDO，SQL，Java Collections，Mongodb，Lucene
官网地址：点击进入
github地址：点击进入

p6spy

优点

记录SQL语句的执行时间戳
记录SQL语句类型
记录SQL填入参数的和没有填入参数的SQL语句
根据配置的时间控制SQL语句的执行时间，对超出时间的SQL语句输出到日志文件中

原理

P6Spy通过劫持JDBC驱动，在调用实际JDBC驱动前拦截调用的目标语，达到SQL语句日志记录的目的

应用

替换你的JDBC Driver为com.p6spy.engine.spy.P6SpyDriver
修改JDBC Url为jdbc:p6spy:xxxx
配置spy.properties
添加自定义日志打印类

配置到项目

1、引入querydsl

导包，不需要携带版本号，springboot已经集成

<!--query dsl-->
<dependency>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-jpa</artifactId>
</dependency>
<dependency>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-apt</artifactId>
    <scope>provided</scope>
</dependency>
<!--query dsl end-->

<!-- jpa -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!-- jpa end -->

<!-- sql打印 -->
<dependency>
    <groupId>p6spy</groupId>
    <artifactId>p6spy</artifactId>
    <version>3.8.5</version>
</dependency>
<!-- sql打印 end -->

加入插件，让程序自动生成query type(查询实体，命名方式为：“Q”+对应实体名)。

<!--该插件可以生成querysdl需要的查询对象，执行mvn compile即可-->
<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>

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

注入bean

import com.querydsl.jpa.impl.JPAQueryFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.persistence.EntityManager;

/**
 * @auther YangFan
 * @Date 2021/3/25 9:14
 */
@Configuration
public class JPAFactory {
    @Bean
    public JPAQueryFactory jpaQueryFactory(EntityManager entityManager) {
        return new JPAQueryFactory(entityManager);
    }
}

2、配置文件

application.yml

使用p6spy我们可以直接查看到数据库执行的sql而不是预编译带?的SQL

修改数据库连接驱动和url
```
spring:
  datasource:
    driver-class-name: com.p6spy.engine.spy.P6SpyDriver
    url: jdbc:p6spy:mysql://localhost:3306/ly_test?useUnicode=true&characterEncoding=utf8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=UTC&createDatabaseIfNotExist=true&useJDBCCompliantTimezoneShift=true&allowPublicKeyRetrieval=true
    username: root
    password: admin
  jpa:
    show-sql: false
    hibernate:
      ddl-auto: update
```
spring.jpa.hibernate.ddl-auto的几个常用属性值：

none：默认值，什么都不做，每次启动项目，不会对数据库进行任何验证和操作

create：每次运行项目，没有表会新建表，如果表内有数据会被清空

create-drop：每次程序结束的时候会清空表

update：每次运行程序，没有表会新建表，但是表内有数据不会被清空，只会更新表结构

validate：运行程序会校验数据与数据库的字段类型是否相同，不同会报错

spy.properties

driverlist=com.mysql.cj.jdbc.Driver

# determines if property file should be reloaded
# Please note: reload means forgetting all the previously set
# settings (even those set during runtime - via JMX)
# and starting with the clean table
# (default is false)
#reloadproperties=false
reloadproperties=true

# specifies the appender to use for logging
# Please note: reload means forgetting all the previously set
# settings (even those set during runtime - via JMX)
# and starting with the clean table
# (only the properties read from the configuration file)
# (default is com.p6spy.engine.spy.appender.FileLogger)
#appender=com.p6spy.engine.spy.appender.Slf4JLogger
appender=com.p6spy.engine.spy.appender.StdoutLogger
#appender=com.p6spy.engine.spy.appender.FileLogger
#appender=com.p6spy.engine.spy.appender.Slf4JLogger

# class to use for formatting log messages (default is: com.p6spy.engine.spy.appender.SingleLineFormat)
#logMessageFormat=com.p6spy.engine.spy.appender.SingleLineFormat
#自定义日志格式，在类中定义
logMessageFormat=com.ly.cloud.demo.strategy.P6SpyLogger

databaseDialectDateFormat=yyyy-MM-dd HH:mm:ss

excludecategories=info,debug,result,resultset

自定义日志格式类P6SpyLogger

import com.p6spy.engine.spy.appender.MessageFormattingStrategy;
import org.hibernate.engine.jdbc.internal.BasicFormatterImpl;
import org.hibernate.engine.jdbc.internal.Formatter;

import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @auther YangFan
 * @Date 2021/3/25 10:12
 */
public class P6SpyLogger implements MessageFormattingStrategy {

    private SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SSS");

    private static final Formatter formatter;

    static {
        formatter = new BasicFormatterImpl();
    }

    @Override
    public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql, String url) {
        StringBuilder sb = new StringBuilder();
        return !"".equals(sql.trim()) ? sb.append(this.format.format(new Date())).append(" | took ").append(elapsed).append("ms | ").append(category).append(" | connection ").append(connectionId).append(formatter.format(sql)).append(";").toString()  : "";
    }
}

3、创建实体类

Actor

@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 = LocalDateTime.now().toString();
}

Work

@Entity
@Table(name = "work")
@Data
public class Work {

    /**
     * 主键生成采用数据库自增方式，比如MySQL的AUTO_INCREMENT
     */
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "word_name", nullable = false, length = 128)
    private String workName;
}

执行Maven插件的compile就能在指定目录生成QActor类和QWork类。

4、创建Repository

需要继承 QuerydslPredicateExecutor

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

}

5、使用QueryDSL

5.1、原生dsl查询

直接根据条件查询

@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class QuerydslTest {
    @Autowired
    private JPAQueryFactory jpaQueryFactory;
    
    ObjectMapper mapper = new ObjectMapper();

    /**
     * 直接根据条件查询
     */
    @Test
    public void testFindByActorNameAndActorEmail() {
        QActor qActor = QActor.actor;
        Actor actor = jpaQueryFactory.selectFrom(qActor)
                .where(
                        qActor.actorName.eq("嘀嘀嘀"),
                        qActor.actorEmail.eq("123456789@qq.com")
                )
                .fetchOne();
        try {
            String s = mapper.writeValueAsString(actor);
            log.info("s: {}", s);
        } catch (JsonProcessingException e) {

        }
    }
}

查询所有并根据字段排序

/**
 * 查询所有并根据字段排序
 */
@Test
public void testFindAll() {
    	QActor qActor = QActor.actor;
        List<Actor> actorList = jpaQueryFactory.selectFrom(qActor)
                .orderBy(
                        qActor.actorAge.asc()
                )
                .fetch();
        try {
            String s = mapper.writeValueAsString(actorList);
            log.info("s: {}", s);
        } catch (JsonProcessingException e) {

        }
}

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

/**
 * 分页查询，并根据字段排序
 */
@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 - 1) * pageSize)
                .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();
        try {
            String s = mapper.writeValueAsString(actorListByPagination);
            log.info("s: {}", s);
        } catch (JsonProcessingException e) {

        }
}

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

/**
 * 根据条件模糊查询，并指定某个字段的范围
 */
@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();
        try {
            String s = mapper.writeValueAsString(actorList);
            log.info("s: {}", s);
        } catch (JsonProcessingException e) {

        }
}

两张表关联查询

/**
  * 两张表关联查询
  */
@Test
public void testTwoTablesQuery() {
    QActor qActor = QActor.actor;
    QWork work = QWork.work;

    List<Tuple> fetch = jpaQueryFactory.select(qActor.id,qActor.actorName,qActor.actorAge,work.workName).from(qActor).leftJoin(work).on(qActor.id.eq(work.id)).orderBy(qActor.actorAge.desc()).fetch();
    System.err.println(fetch);
}

两张表关联查询将结果封装至vo中

/**
 * 查询并将结果封装至vo中
 */
@Test
public void testFindTwoTableToVo() {
    QActor qActor = QActor.actor;
    QWork work = QWork.work;
    List<ActorInfoVO> fetch = jpaQueryFactory.select(Projections.bean(ActorInfoVO.class, qActor.id, qActor.actorName, work.workName)).from(qActor).leftJoin(work).on(qActor.id.eq(work.id)).fetch();
    System.err.println(fetch);
}

5.2、jpa整合dsl查询

模糊查询并分页排序

@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();
        try {
            String s = mapper.writeValueAsString(actorListByPagination);
            log.info("s: {}", s);
        } catch (JsonProcessingException e) {

        }
}

动态查询并分页排序

/**
 * 动态查询并分页排序
 */
@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 + "%"));

        // 排序、分页参数
        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();
        try {
            String s = mapper.writeValueAsString(actorListByPagination);
            log.info("s: {}", s);
        } catch (JsonProcessingException e) {

        }
}

5.3、删除和修改

删除和修改需要添加@Transactional

修改

/**
 * 修改
 *
 * 需要添加@Transactional，否则报错
 */
@Transactional
@Test
public void testUpdate() {
    QWork work = QWork.work;
    long update = jpaQueryFactory.update(work).set(work.workName, "修改了之后的作品").where(work.id.eq(2l)).execute();
    System.err.println("update成功的条数" + update);
}

删除

/**
 * 删除
 *
 * 需要添加@Transactional，否则报错
 */
@Transactional
@Test
public void testDelete() {
    QWork work = QWork.work;
    long deleted = jpaQueryFactory.delete(work).where(work.id.eq(2l)).execute();
    System.err.println("delete成功的条数" + deleted);
}

示例github地址

https://github.com/yanjingfan/sakura-boot-demo/tree/master/jpa