jpa的CriteriaBuilderFactory+EntityManager的动态SQL，java写法记录

ceshiyuan001

已于 2025-02-06 18:03:54 修改

阅读量1.6k

点赞数 19

CC 4.0 BY-SA版权

文章标签： sql java hibernate

于 2023-11-22 11:25:25 首次发布

本文链接：https://blog.youkuaiyun.com/ceshiyuan001/article/details/134548046

jpa的CriteriaBuilderFactory+EntityManager的SQL，java写法记录

一，文章目的

网上搜索到关于jpa写sql的帖子，写法也是五花八门的。我在这里介绍一下CriteriaBuilderFactory+EntityManager的写法。同时也记录工作中对jpa的使用，希望大家多多留言，将大家遇到的复杂场景和复杂sql记录到留言区。

二，配置CriteriaBuilderFactory

1）引入依赖

<!--        spring-data-jpa -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
<!--   blaze-persistence  -->
        <dependency>
            <groupId>com.blazebit</groupId>
            <artifactId>blaze-persistence-core-api</artifactId>
            <version>1.6.6</version>
        </dependency>
        <dependency>
            <groupId>com.blazebit</groupId>
            <artifactId>blaze-persistence-core-impl</artifactId>
            <version>1.6.6</version>
        </dependency>
<!--        Hibernat -->
        <dependency>
            <groupId>com.blazebit</groupId>
            <artifactId>blaze-persistence-integration-hibernate-5.4</artifactId>
            <version>1.6.6</version>
        </dependency>

2）配置CriteriaBuilderFactory

import com.blazebit.persistence.Criteria;
import com.blazebit.persistence.CriteriaBuilderFactory;
import com.blazebit.persistence.spi.CriteriaBuilderConfiguration;
import org.springframework.beans.factory.config.ConfigurableBeanFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Lazy;
import org.springframework.context.annotation.Scope;

import javax.persistence.EntityManagerFactory;
import javax.persistence.PersistenceUnit;

@Configuration
public class BlazePersistenceConfiguration {

    @PersistenceUnit
    private EntityManagerFactory entityManagerFactory;

    @Bean
    @Scope(ConfigurableBeanFactory.SCOPE_SINGLETON)
    @Lazy(false)
    public CriteriaBuilderFactory createCriteriaBuilderFactory() {
        CriteriaBuilderConfiguration config = Criteria.getDefault();
        return config.createCriteriaBuilderFactory(entityManagerFactory);
    }
}

以下是blaze官网链接，在阅读完本博客后，强烈推荐阅读原文！！
十分推荐阅读：blaze介绍！！

三，SQL的写法

jpa查询的时候，查询结果跟mybatis的不太一样。从mybatis切换到jpa的话对这个查询结果会挺头疼的。
jpa查询返回的结果为object的对象。比如查询单个字段，返回的就是object对象，需要用字段对应的类型去转换。
如果查询的是list，则返回的是List<object[]>，List就不说了，object[]这个就是存储查询的list中单个对象的所有字段。

来呀，互相伤害呀～

实体类

BaseEntity

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;
import java.time.LocalDateTime;

@Data
@AllArgsConstructor
@NoArgsConstructor
@MappedSuperclass // 加了这个注解，子类继承才有效果。
public class BaseEntity {

    // 在Spring jpa 中hibernate 会自动根据实体类建表，但建立的表引擎默认的是MyISAM。那怎么更改默认的表引擎为InnoDB呢？
    // 首先看一下你的MySQL方言是怎么设置的。如果你的方言设置的是MySQL55Dialect，那么无需更改，
    // 默认的就是InnoDB。如果你的方言是MySQL5Dialect， 方言配置，看yml
    // ————————————————
    // 版权声明：本文为优快云博主「SACKings」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
    // 原文链接：https://blog.youkuaiyun.com/SACKings/article/details/120552037

    @Id
    // GeneratedValue https://blog.youkuaiyun.com/sswqzx/article/details/84337921
    // -AUTO主键由程序控制, 是默认选项 ,不设置就是这个
    // -IDENTITY 主键由数据库生成, 采用数据库自增长, Oracle不支持这种方式
    // -SEQUENCE 通过数据库的序列产生主键, MYSQL  不支持
    // -Table 提供特定的数据库产生主键, 该方式更有利于数据库的移植
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(columnDefinition = "BIGINT COMMENT '主键id'")
    private Long id;

    @Column(columnDefinition = "DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'")
    private LocalDateTime timeCreated = LocalDateTime.now();

    @Column(columnDefinition = "DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间'")
    private LocalDateTime timeLastUpdated = LocalDateTime.now();
}

StudentEntity

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;
import java.time.LocalDateTime;


@Data
@Entity
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "t_student",
        uniqueConstraints = @UniqueConstraint(name = "uk_student_no", columnNames = "studentNo"))
public class StudentEntity extends BaseEntity {

    @Column(columnDefinition = "BIGINT NOT NULL COMMENT '班级id'")
    private Long classId;
    @Column(columnDefinition = "VARCHAR(12) NOT NULL COMMENT '学生姓名'")
    private String studentName;
    @Column(columnDefinition = "TINYINT NOT NULL COMMENT '学生性别1-男 2-女'")
    private Integer gender;
    @Column(columnDefinition = "VARCHAR(32) NOT NULL COMMENT '学生学号'")
    private String studentNo;
    @Column(columnDefinition = "VARCHAR(64) NOT NULL COMMENT '家庭地址'")
    private String familyAddress;
    // length 与 columnDefinition 会冲突，用了后者，牵着就不生效了
    @Column(columnDefinition = "VARCHAR(12) NOT NULL COMMENT '联系人姓名'")
    private String contactName;
    @Column(columnDefinition = "VARCHAR(11) NOT NULL COMMENT '联系人电话'")
    private String contactPhone;
    @Column(columnDefinition = "DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间'")
    private LocalDateTime timeOfEnrollment;



    @PreRemove
    public void te() {
        // 原生的删除 deleteById 触发
        // 删除前，会查询一遍。
        // 这里的this，就是当前删除前查询的对象。
        System.out.println("删除回调，删除的对象id" + this.getId());
    }

}

ClassEntity

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Table;
import java.time.LocalDateTime;

@Data
@Entity
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "t_class")
public class ClassEntity extends BaseEntity {

    /**
     * 班级的概念：每一期都新开班级，都建立新的班级记录
     */

    // 班级编号
    @Column(columnDefinition = "VARCHAR(32) NOT NULL COMMENT '班级编号'")
    private String classNo;
    // 班级名称
    @Column(columnDefinition = "VARCHAR(12) NOT NULL COMMENT '班级名称'")
    private String className;
    // 班主任id
    @Column(columnDefinition = "BIGINT NOT NULL COMMENT '班主任id'")
    private Long teacherId;
    // 班主任姓名
    @Column(columnDefinition = "VARCHAR(12) NOT NULL COMMENT '班主任姓名'")
    private String teacherName;
    // 班级年级
    @Column(columnDefinition = "VARCHAR(12) NOT NULL COMMENT '班级年级'")
    private String classGrade;
    // 班级届数
    @Column(columnDefinition = "VARCHAR(12) NOT NULL COMMENT '班级届数'")
    private String classSeason;
    // 开班时间
    @Column(columnDefinition = "DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '开班时间'")
    private LocalDateTime classStartDate;
    // 结束时间
    @Column(columnDefinition = "DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '结束时间'")
    private LocalDateTime classEndDate;
    // 班级计划人数
    @Column(columnDefinition = "INT NOT NULL COMMENT '班级计划人数'")
    private Integer planCount;
    // 班级实际人数
    @Column(columnDefinition = "INT NOT NULL COMMENT '班级实际人数'")
    private Integer actualCount;

}

自定义VO

StudentClassVO

@Data
@AllArgsConstructor
@NoArgsConstructor
public class StudentClassVO {
    private Long studentId;
    private String studentName;
    private String studentNo;
    private Long classId;
    private String classNo;
    private String className;

    public StudentClassVO(Long studentId, String studentName) {
        this.studentId = studentId;
        this.studentName = studentName;
    }

    public StudentClassVO(Long studentId, String studentName,  Long classId, String className) {
        this.studentId = studentId;
        this.studentName = studentName;
        this.classId = classId;
        this.className = className;
    }

    public StudentClassVO(Long classId, String classNo, String className) {
        this.classId = classId;
        this.classNo = classNo;
        this.className = className;
    }
}

StudentIdNameVO

@Data
@AllArgsConstructor
@NoArgsConstructor
public class StudentIdNameVO {
    private Long id;
    private String studentName;
    private String studentNo;

    public StudentIdNameVO(Long id, String studentName) {
        this.id = id;
        this.studentName = studentName;
    }

    public StudentIdNameVO(Long id) {
        this.id = id;
    }
}

测试类

@Rollback(value = false)
@SpringBootTest
public class JpaDemo {

    @PersistenceContext
    private EntityManager entityManager;
    @Autowired
    private CriteriaBuilderFactory cbf;

    @Test
    public void te() {
        System.out.println("测试成功");
    }
}

单表查询错误样例

@Test
    public void wrongResultQuery() {
        /**
         * 错误样例，输出：
         * Hibernate: select studentent0_.id as col_0_0_, studentent0_.student_name as col_1_0_ from t_student studentent0_
         *
         * java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to com.lcmgroup.jpademo.dao.StudentEntity
         */
        CriteriaBuilder<StudentEntity> builder = cbf.create(entityManager, StudentEntity.class, "ts")
                .select("ts.id")
                .select("ts.studentName");
        List<StudentEntity> resultList = builder.getResultList();
        StudentEntity studentEntity = resultList.get(0);
        System.out.println(studentEntity);
    }

单字段查询

 @Test
    public void singleResultParamQuery() {

        /**
         * Hibernate: select studentent0_.student_name as col_0_0_ from t_student studentent0_
         * 赵中年
         */
        CriteriaBuilder<String> builder = cbf.create(entityManager, String.class)
                .from(StudentEntity.class, "ts")
                .select("ts.studentName");
        List<String> resultList = builder.getResultList();
        String studentEntity = resultList.get(0);
        System.out.println(studentEntity);
    }

查询返回自定义对象

@Test
    public void resultParamQuerySelectNew() {

        // 单表
        /**
         * VO:
         * public class StudentClassVO {
         *     private Long studentId;
         *     private String studentName;
         *     private String studentNo;
         *     private Long classId;
         *     private String classNo;
         *     private String className;
         *
         *     public StudentClassVO(Long studentId, String studentName) {
         *         this.studentId = studentId;
         *         this.studentName = studentName;
         *     }
         * }
         * 输出：
         * Hibernate: select studentent0_.id as col_0_0_, studentent0_.student_name as col_1_0_ from t_student studentent0_
         * {"id":1,"studentName":"赵中年"}
         */
        CriteriaBuilder<StudentClassVO> builder = null;
        try {
            builder = (CriteriaBuilder<StudentClassVO>) cbf.create(entityManager, StudentEntity.class, "ts")
                    .selectNew(StudentClassVO.class.getConstructor(Long.class, String.class))
                    .with("ts.id")
                    .with("ts.studentName")
                    .end();
        } catch (NoSuchMethodException e) {
            throw new BizException(500, "转换异常");
        }
        List<StudentClassVO> resultList = builder.getResultList();
        StudentClassVO studentIdNameVO = resultList.get(0);
        System.out.println(JSON.toJSONString(studentIdNameVO));


        /**
         * VO: 增加构造函数就行
         *    public StudentClassVO(Long studentId, String studentName,  Long classId, String className) {
         *         this.studentId = studentId;
         *         this.studentName = studentName;
         *         this.classId = classId;
         *         this.className = className;
         *     }
         * 输出：
         * Hibernate: select studentent0_.id as col_0_0_, studentent0_.student_name as col_1_0_, classentit1_.id as col_2_0_, classentit1_.class_name as col_3_0_
         * from t_student studentent0_
         * inner join t_class classentit1_ on (classentit1_.id=studentent0_.class_id)
         *
         * {"classId":1,"className":"一年级一班","studentId":1,"studentName":"赵中年"}
         */
        // 连表
        CriteriaBuilder<StudentClassVO> builder1 = null;
        try {
            builder1 = (CriteriaBuilder<StudentClassVO>) cbf.create(entityManager, StudentEntity.class, "ts")
                    .innerJoinOn(ClassEntity.class, "cl").on("cl.id").eqExpression("ts.classId").end()
                    .selectNew(StudentClassVO.class.getConstructor(Long.class, String.class, Long.class, String.class))
                    .with("ts.id")
                    .with("ts.studentName")
                    .with("cl.id")
                    .with("cl.className")
                    .end();
        } catch (NoSuchMethodException e) {
            throw new BizException(500, "转换异常");
        }

        List<StudentClassVO> resultList1 = builder1.getResultList();
        StudentClassVO studentIdNameVO1 = resultList1.get(0);
        System.out.println(JSON.toJSONString(studentIdNameVO1));
    }

    @Test
    public void tupleQuery() {

        /**
         * 查询自定义的对象另一种方式
         *
         * Hibernate: select studentent0_.id as col_0_0_, studentent0_.student_name as col_1_0_ from
         * t_student studentent0_ where studentent0_.id in (select max(studentent1_.id) from t_student studentent1_)
         * 6879
         * 郑五少
         */
        CriteriaBuilder<Tuple> end = cbf.create(entityManager, Tuple.class)
                .from(StudentEntity.class, "st")
                .select("st.id", "studentId")
                .select("st.studentName", "studentName")
                .where("st.id").in()
                .from(StudentEntity.class, "stu")
                .select("max(stu.id)")
                .end();
        List<Tuple> resultList = end.getResultList();
        Tuple firstTuple = resultList.get(0);
        // 通过别名取
        Long studentId = firstTuple.get("studentId", Long.class);
        System.out.println(studentId);
        // 通过下标取
        String studentName = firstTuple.get(1, String.class);
        System.out.println(studentName);
    }

group by

@Test
    public void simpleGroupBy() {
        /**
         * 简单的group by
         * Hibernate: select max(studentent0_.id) as col_0_0_
         * from t_student studentent0_ where studentent0_.class_id in (? , ? , ?) group by studentent0_.class_id
         * {"id":75}
         */
        FullQueryBuilder<StudentIdNameVO, ?> bd = null;
        try {
            bd = (FullQueryBuilder<StudentIdNameVO, ?>) cbf.create(entityManager, StudentIdNameVO.class)
                    .select("max(st.id)")
                    .from(StudentEntity.class, "st")
                    .where("st.classId").in(Arrays.asList(1L, 3L, 12L))
                    .groupBy("st.classId")
                    .orderByAsc("st.id")
                    .selectNew(StudentIdNameVO.class.getConstructor(Long.class))
                    .end()
            ;
        } catch (NoSuchMethodException e) {
            throw new BizException(500, "转换异常");
        }
        List<StudentIdNameVO> resultList = bd.getResultList();
        StudentIdNameVO studentIdNameVO = resultList.get(0);
        System.out.println(JSON.toJSONString(studentIdNameVO));
    }

max函数等

    @Test
    public void singleMax() {
        // 单表max
        // 查询学生表中，学号最大,且姓名不等于xx，且入学时间大于等于xx的学生和其班级信息
        /**
         * SELECT st.id,st.student_name,cl.id,cl.class_name
         * FROM t_student st JOIN t_class cl on st.class_id = cl.id
         * WHERE student_no in (
         * SELECT max(student_no)
         * FROM t_student and student_name != xx)
         * and st.time_of_enrollment > xx
         * 输出:
         * Hibernate:
         * select studentent0_.id as col_0_0_, studentent0_.student_name as col_1_0_, classentit1_.id as col_2_0_, classentit1_.class_name as col_3_0_
         * from t_student studentent0_ inner join t_class classentit1_ on (classentit1_.id=studentent0_.class_id)
         * where (select max(studentent2_.student_no) from t_student studentent2_)=studentent0_.student_no
         * {"classId":82,"className":"二年级一十二班","studentId":6739,"studentName":"孙七"}
         */
        try {
            FullQueryBuilder<StudentClassVO, ?> builder =
                    // 创建select，预定需要返回的类型
                    cbf.create(entityManager, StudentClassVO.class)
                            // 构建主体from
                            .from(StudentEntity.class, "st")
                            .innerJoinOn(ClassEntity.class, "cl").on("cl.id").eqExpression("st.classId").end()
                            // 构建需要返回的类型
                            .selectNew(StudentClassVO.class.getConstructor(Long.class, String.class, Long.class, String.class))
                            // 构建查询的字段
                            .with("st.id")
                            .with("st.studentName")
                            // with()是不能写 max(xx),或 st.id as xx
                            // jpa不需要给别名，因为查询出来的数据，最终会对应到构造方法上去。
                            .with("cl.id")
                            .with("cl.className")
                            // end()，表示完结，一段sql的完结，from完结，end()后连接where()等
                            .end()
                            // .whereSubquery() 开启 where的子查询
                            .whereSubquery()
                            .from(StudentEntity.class, "stu")
                            .select("max(stu.studentNo)")
                            .where("stu.studentName").notEq("xx")
                            .end()
                            .eqExpression("st.studentNo")
                            .where("st.timeOfEnrollment").gt(LocalDateTime.now().plus(-10, ChronoUnit.YEARS));

            List<StudentClassVO> resultList = builder.getResultList();
            StudentClassVO studentClassVO = resultList.get(0);
            System.out.println(JSON.toJSONString(studentClassVO));
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }

        // with()与select()的差别
        // with()放在selectNew()后面
        // with() 不能使用函数，比如max(id),avg(id)

        // select()不能放在selectNew()后面
        // select()能使用函数，比如max(id),avg(id)

        try {
            FullQueryBuilder<StudentClassVO, ?> builder =
                    // 创建select，预定需要返回的类型
                    cbf.create(entityManager, StudentClassVO.class)
                            .select("st.id")
                            .select("st.studentName")
                            .select("cl.id")
                            .select("cl.className")
                            .from(StudentEntity.class, "st")
                            .innerJoinOn(ClassEntity.class, "cl").on("cl.id").eqExpression("st.classId").end()
                            .selectNew(StudentClassVO.class.getConstructor(Long.class, String.class, Long.class, String.class))
                            .end()
                            .where("st.timeOfEnrollment").gt(LocalDateTime.now().plus(-10, ChronoUnit.YEARS))
                            .whereSubquery()
                            .from(StudentEntity.class, "stu")
                            .select("max(stu.studentNo)")
                            .where("stu.studentName").notEq("xx")
                            .end()
                            .eqExpression("st.studentNo");

            List<StudentClassVO> resultList = builder.getResultList();
            StudentClassVO studentClassVO = resultList.get(0);
            System.out.println(JSON.toJSONString(studentClassVO));
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }
    }

子查询- 注意有坑

子查询结果集为单条时：.whereSubquery()

    @Test
    public void subquery() {
        // 子查询
        /**
         *
         * SELECT id,class_no,class_name
         * FROM t_class WHERE id in (
         * SELECT max(class_id)
         * FROM t_student where gender = 1)
         *
         * 输出：
         * StudentClassVO(studentId=null, studentName=null, studentNo=null, classId=84, classNo=BH20231007105645785504, className=二年级一十四班)
         *
         */
        FullQueryBuilder<StudentClassVO, ?> cb = null;
        try {
            cb = cbf.create(entityManager, ClassEntity.class, "cl")
                    .selectNew(StudentClassVO.class.getConstructor(Long.class, String.class, String.class))
                    .with("cl.id")
                    .with("cl.classNo")
                    .with("cl.className")
                    .end()
                    .whereSubquery()// 子查询返回多个记录，会报错 提示： Caused by: java.sql.SQLException: Subquery returns more than 1 row
                            // 这不是闹吗?????
                    .from(StudentEntity.class, "ts")
                    .select("max(ts.classId)")
                    .where("ts.gender").eq(1)
                    .end()
                    .eqExpression("cl.id");
        } catch (NoSuchMethodException e) {
            throw new BizException(500, "转换异常");
        }
        List<StudentClassVO> resultList = cb.getResultList();
        StudentClassVO s = resultList.get(0);
        System.out.println(s);
    }

子查询结果集为多条时：.whereExists()

使用场景之一：
TaskGroup分组表，1 对多TaskGroupStaffRel 分组成员关系。通过分组成员名称，且是负责人，反向搜索分组，并查询列表。
select join 整了半天，没写出来，用这个替代了。

实现的sql效果：

SELECT
	count(*) AS col_0_0_
FROM
	t_task_group tk
WHERE
	(
	tk.task_group_name LIKE ?)
	AND (
	EXISTS ( SELECT tksr.task_group_id FROM t_task_group_staff_rel tksr
	WHERE tksr.is_main =? AND ( tksr.staff_name LIKE ?) AND tksr.task_group_id = tk.id ))

查询的语句为：


 try {
            CriteriaBuilder<TaskGroupListResponse> cb = (CriteriaBuilder<TaskGroupListResponse>) cbf.create(entityManager, TaskGroupListResponse.class)
                    .from(TaskGroup.class, "tg")
                    .selectNew(TaskGroupListResponse.class.getConstructor(Long.class, String.class))
                    .with("tg.id")
                    .with("tg.taskGroupName")
                    .end();

            // 处理排序
            cb.orderByDesc("tg.timeLastUpdated");

            if (StringUtils.isNotEmpty(taskGroupName)) {
                cb.where("tg.taskGroupName").like().value("%" + taskGroupName + "%").noEscape();
            }

            // 子查询
            // 当存在主要任务组名称时，创建子查询 -ok
            if (StringUtils.isNotEmpty(taskGroupMajorName)) {
                CriteriaBuilder<TaskGroupListResponse> sqb = cb.whereExists()
                        .from(TaskGroupStaffRel.class, "tgsr")
                        .select("tgsr.taskGroupId")
                        .where("tgsr.isMain").eq(true)
                        .where("tgsr.staffName").like().value("%" + taskGroupMajorName + "%").noEscape()
                        .where("tgsr.taskGroupId").eqExpression("tg.id") // 将子查询与主查询连接
                        .end();
// 如果sqb 还要继续添加条件，则先不要.end()，
              // 如下:SubqueryBuilder<CriteriaBuilder<TaskListResponse>> sqb = cb.whereExists()
              //                    .from(TaskStaffRel.class, "tksr")
              //                    .select("tksr.taskId")
              //                    .where("tksr.staffId").eq(currentStaffId)
              //                    .where("tksr.taskId").eqExpression("tk.id");
              // String creatorName = request.getCreatorName();
              //            if (StringUtils.isNotEmpty(creatorName)) {
              //                sqb.where("tksr.staffName").like().value("%" + creatorName + "%").noEscape();
              //                sqb.where("tksr.taskStaffType").eq(TaskStaffType.TASK_CREATOR);
              //            }
              //            sqb.end();
            }


            long count = cb.getCountQuery().getSingleResult();

            int page = request.getPage();
            int pageSize = request.getPageSize();

            if (count <= 0L) {
                return new PageImpl<>(Collections.emptyList(), PageRequest.of(page, pageSize), 0);
            }

            //设置分页参数
            cb.setFirstResult(page * pageSize);
            cb.setMaxResults(pageSize);

            return new PageImpl<>(cb.getResultList(), PageRequest.of(page, pageSize), count);


        } catch (NoSuchMethodException e) {
            throw new RuntimeException(e);
        }

select case when

    @Test
    public void caseWhenSelect() {

        /**
         * Hibernate: select studentent1_.id as col_0_0_,
         * case when studentent1_.student_name is null then '姓名为空'
         * when length(studentent1_.student_name)>? then '名字大于2个字'
         * else 'st.studentName' end as col_1_0_,
         * classentit0_.id as col_2_0_, classentit0_.class_name as col_3_0_
         * from t_class classentit0_ inner join t_student studentent1_ on (studentent1_.class_id=classentit0_.id)
         * where studentent1_.id=?
         * {"classId":1,"className":"一年级一班","studentId":1,"studentName":"名字大于2个字"}
         */
        try {
            FullQueryBuilder<StudentClassVO, ?> bd = cbf.create(entityManager, StudentClassVO.class)
                    .from(ClassEntity.class, "cl")
                    .innerJoinOn(StudentEntity.class, "st").on("st.classId").eqExpression("cl.id")
                    .end()
                    .select("st.id")
                    .selectCase()
                    .when("st.studentName").isNull()
                    .then("姓名为空")
                    .when("length(st.studentName)").gt().value(2)
                    .then("名字大于2个字")
                    .otherwise("st.studentName")
                    .select("cl.id")
                    .select("cl.className")
                    .selectNew(StudentClassVO.class.getConstructor(Long.class, String.class, Long.class, String.class))
                    .end()
                    .where("st.id").eq(1L);

            List<StudentClassVO> resultList = bd.getResultList();
            StudentClassVO studentClassVO = resultList.get(0);
            System.out.println(JSON.toJSONString(studentClassVO));
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }
    }

union

    @Test
    public void simpleUnion() {

        /**
         * Hibernate: select studentent0_.id as col_0_0_, studentent0_.student_name as col_1_0_
         * from t_student studentent0_ where studentent0_.id in
         * ((select studentent1_.id from t_student studentent1_ where studentent1_.id in (? , ? , ?)
         * UNION
         * (select studentent2_.id from t_student studentent2_ where studentent2_.student_name like ?)))
         */
        try {
            FullQueryBuilder<StudentIdNameVO, ?> bd = (FullQueryBuilder<StudentIdNameVO, ?>) cbf.create(entityManager, StudentIdNameVO.class)
                    .selectNew(StudentIdNameVO.class.getConstructor(Long.class, String.class))
                    .end()
                    .from(StudentEntity.class, "st")
                    .select("st.id")
                    .select("st.studentName")
                    .where("st.id")
                    .in().from(StudentEntity.class, "stu")
                    .select("stu.id")
                    .where("stu.id").in(1L, 3L, 4L)
                    .union()
                    .from(StudentEntity.class, "stt")
                    .select("stt.id")
                    .where("stt.studentName").like().value("%张%").noEscape()
                    .endSet()
                    .end();
            List<StudentIdNameVO> resultList = bd.getResultList();

        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }
    }

@query写法

@query动态参数写法。场景例子：
项目上使用的jpa操作数据库，当使用@query注解时，需要根据姓名模糊查询记录，但是这个姓名用户可以不输入

写法参考：

@Query(" select new com.xxx.WikiIdDTO(wi.id) from table1 wc " +
            " join table2  wcr on wc.id = wcr.channelId join table3  wi on wcr.knowledgeId = wi.id " +
            " where (:wikiAccountId is null or wi.author = :wikiAccountId) and wc.status in :statusList ")
    List<WikiIdDTO> findChannelRelWikiIdsByAccountIdAndStatus(@Param("wikiAccountId") Long wikiAccountId, @Param("statusList") List<ChannelStatus> statusList);

whereOr

where (genId = xx and categoryName = xx) or (genId = xx and categoryName = xx) or …

写法参考：

@Test
    public void whereOrTe() {


        CriteriaBuilder<StudentEntity> cb = cbf.create(entityManager, StudentEntity.class, "st");

        WhereOrBuilder<CriteriaBuilder<StudentEntity>> whereOr = cb.whereOr();
        for (int i = 0; i < 2; i++) {

            whereOr.whereAnd()
                    .where("st.studentName").eq(i + "")
                    .where("st.gender").eq(1)
                    .endAnd();
        }
        whereOr.endOr();

        System.out.println(JSON.toJSONString(cb.getResultList()));
    }

四，项目地址

项目地址

项目地址：
https://gitee.com/lcmgroup/jpa-demo.git

项目手册

表数据位置：
resource -> sql

sql示例位置：
com.lcmgroup.jpademo.JpaDemo

欢迎大家留言，并提出问题进行探讨。