queryDsl初学

    本文主要介绍springboot + jpa + query dsl的一系列操作，没有query dsl 产品简介。
    是一个通用的查询框架，专注于通过Java API构建类型安全的SQL查询。说的直白点就是用Java 代码写sql 。

附上官网文档链接

http://www.querydsl.com/static/querydsl/4.2.1/reference/html_single/

1、新建一个springboot + jpa 项目

这个不多说，都会的。我使用的是postgresql 数据库，换成mysql 或其他也可以。

2、引入query dsl 的maven依赖

3、各种sql组合尝试

1.简单组合

// 两种写法
// fetchOne查询一条，当匹配到两条数据时会报错
 jpaQueryFactory.selectFrom(qStudent)
         .where(qStudent.studentName.eq(studentName)).fetchOne();
// sql：select student0_.student_id as student_1_0_, student0_.student_address as student_2_0_, student0_.student_age as student_3_0_, student0_.student_name as student_4_0_, student0_.student_sex as student_5_0_ from td_student student0_ where student0_.student_name='小明';

 // fetchFirst 后面会拼接一个 （limit 1），在多个结果中只查询一条
 jpaQueryFactory.selectFrom(qStudent)
         .where(qStudent.studentName.eq(studentName)).fetchFirst();
// sql：select student0_.student_id as student_1_0_, student0_.student_address as student_2_0_, student0_.student_age as student_3_0_, student0_.student_name as student_4_0_, student0_.student_sex as student_5_0_ from td_student student0_ where student0_.student_name='小明' limit 1;

// 查询条件，用逗号也表示and
jpaQueryFactory.selectFrom(qStudent)
                .where(qStudent.studentAge.eq(studentAge), qStudent.studentName.eq(studentName)).fetch();
// sql：select student0_.student_id as student_1_2_, student0_.student_address as student_2_2_, student0_.student_age as student_3_2_, student0_.student_name as student_4_2_, student0_.student_sex as student_5_2_ from td_student student0_ where student0_.student_age=18 and student0_.student_name='张三';

jpaQueryFactory.selectFrom(qStudent)
.where(qStudent.studentAge.eq(studentAge).and(qStudent.studentName.eq(studentName))).fetch();
// sql：select student0_.student_id as student_1_2_, student0_.student_address as student_2_2_, student0_.student_age as student_3_2_, student0_.student_name as student_4_2_, student0_.student_sex as student_5_2_ from td_student student0_ where student0_.student_age=18 and student0_.student_name='张三';

2.动态添加查询条件（where条件）

BooleanBuilder booleanBuilder = new BooleanBuilder();
if (studentAge != null){
    booleanBuilder.and(qStudent.studentAge.eq(studentAge));
}
if (StringUtils.hasText(studentName)){
    booleanBuilder.and(qStudent.studentName.eq(studentName));
}
jpaQueryFactory.selectFrom(qStudent).where(booleanBuilder).fetch();
// sql: select student0_.student_id as student_1_2_, student0_.student_address as student_2_2_, student0_.student_age as student_3_2_, student0_.student_name as student_4_2_, student0_.student_sex as student_5_2_ from td_student student0_ where student0_.student_age=18 and student0_.student_name='张三';

// Predicate 初始化需要赋值 ,给一个 1=1的条件
Predicate predicate = qStudent.studentId.isNotNull();
if (studentAge != null){
    predicate = ExpressionUtils.and(predicate, qStudent.studentAge.eq(studentAge));
}
if (StringUtils.hasText(studentName)){
    predicate = ExpressionUtils.and(predicate, qStudent.studentName.eq(studentName));
}
jpaQueryFactory.selectFrom(qStudent).where(booleanBuilder).fetch();
// sql: select student0_.student_id as student_1_2_, student0_.student_address as student_2_2_, student0_.student_age as student_3_2_, student0_.student_name as student_4_2_, student0_.student_sex as student_5_2_ from td_student student0_ where student0_.student_age=18 and student0_.student_name='张三';

3.动态添加查询条件（排序条件）

// 此处可进行动态处理
/*desc = qStudent.studentName.desc();
 desc = qStudent.studentAge.desc();*/
 // nullsFirst 空值放在第一个, nullsLast()空值放在最后
OrderSpecifier desc = qStudent.studentName.desc().nullsFirst();
jpaQueryFactory.selectFrom(qStudent).orderBy(desc).fetch();
// sql: select student0_.student_id as student_1_2_, student0_.student_address as student_2_2_, student0_.student_age as student_3_2_, student0_.student_name as student_4_2_, student0_.student_sex as student_5_2_ from td_student student0_ order by student0_.student_name desc nulls first;;

// orderBy中可包含多个条件
desc = qStudent.studentName.desc().nullsLast();
jpaQueryFactory.selectFrom(qStudent).orderBy(desc, qStudent.studentId.asc()).fetch();
// sql: select student0_.student_id as student_1_2_, student0_.student_address as student_2_2_, student0_.student_age as student_3_2_, student0_.student_name as student_4_2_, student0_.student_sex as student_5_2_ from td_student student0_ order by student0_.student_name desc nulls last, student0_.student_id asc;

4.动态添加查询条件（使用子查询）

JPQLQuery where = JPAExpressions.select(qStudent.studentId).from(qStudent)
                .where(qStudent.studentName.eq(studentName));
jpaQueryFactory.selectFrom(qStudent)
         .where(qStudent.studentId.in(where)).fetch();
// sql: select student0_.student_id as student_1_2_, student0_.student_address as student_2_2_, student0_.student_age as student_3_2_, student0_.student_name as student_4_2_, student0_.student_sex as student_5_2_ from td_student student0_ where student0_.student_id in (select student1_.student_id from td_student student1_ where student1_.student_name='张三');

查询中的其他操作：

常用操作地址
https://blog.csdn.net/weixin_43826336/article/details/98945400