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
// ————————————————
// 版权声明:本文为CSDN博主「SACKings」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
// 原文链接:https://blog.csdn.net/SACKings/article/details/120552037
@Id
// GeneratedValue https://blog.csdn.net/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();
}
}
子查询
@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()
.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);
}
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();
}
}
update
在使用update等其他更新语句的时候,注意jpa的entityMapper是有缓存的。比如下面的第一个例子,同事物中,先update 在select 同一条记录,查询的结果却是更新前的。
为了查询到最新结果,可以如下操作:
- 设置@Modifying(clearAutomatically = true)
- 在执行方法后面在调用一次entityManager.clear();因为执行的对象,缓存在了entityMapper
- 注意:entityManager.clear()这个坑,还是推荐第一种写法
@Transactional
@Test
public void updateTestMostScenariosAreWrong() {
// 注意,@Test 类中,默认事物是会回滚的。要在这个类上加 @Rollback(value = false)
/**
* 如下不做任何操作,输出的结果为:
* "name693"
* Hibernate: update t_student set student_name=? where id=?
* 本次更新的name:name228
* "name693"
* 存在问题:
* 更新过后,使用id查询的数据,仍然是未更新前的
*/
Optional<StudentEntity> byId = studentRepository.findById(2L);
System.out.println(JSON.toJSONString(byId.get().getStudentName()));
String updateName = "name" + ThreadLocalRandom.current().nextInt(100, 900);
UpdateCriteriaBuilder<StudentEntity> bd = cbf.update(entityManager, StudentEntity.class)
.set("studentName", updateName)
.where("id").eq(2L);
int i = bd.executeUpdate();
System.out.println("本次更新的name:" + updateName + "是否成功:" + i);
Optional<StudentEntity> byId1 = studentRepository.findById(2L);
System.out.println(JSON.toJSONString(byId1.get().getStudentName()));
}
@Transactional
@Test
public void updateTestFinal() {
// 注意,@Test 类中,默认事物是会回滚的。要在这个类上加 @Rollback(value = false)
/**
* 修复同事物中更新后,查询数据仍是旧数据
* 方法1:@Modifying(clearAutomatically = true),即可刷新
* 方法2:在执行方法后面在调用一次entityManager.clear();因为执行的对象,缓存在了entityMapper
*/
Optional<StudentEntity> byId = studentRepository.findById(2L);
System.out.println(JSON.toJSONString(byId.get().getStudentName()));
String updateName = "name" + ThreadLocalRandom.current().nextInt(100, 900);
UpdateCriteriaBuilder<StudentEntity> bd = cbf.update(entityManager, StudentEntity.class)
.set("studentName", updateName)
.where("id").eq(2L);
int i = bd.executeUpdate();
entityManager.clear();
/**
* @Modifying(clearAutomatically = true)
* @Query("update StudentEntity set studentName = ?1 where id = ?2")
* int updateStudentName(String studentName, Long id);
*
* 调用
* studentRepository.updateStudentName(updateName,2L);
*/
System.out.println("本次更新的name:" + updateName);
Optional<StudentEntity> byId1 = studentRepository.findById(2L);
System.out.println(JSON.toJSONString(byId1.get().getStudentName()));
}
@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
欢迎大家留言,并提出问题进行探讨。