JPA使用SQL的一些总结
简单的增删改查使用 JPA 非常方便，复杂的 Where 条件我们也在 Specification 和 CriteriaBuilder 的基础上封装了 WhereBuilder，
JPA 支持二种最基本的 SQL 方式，Native SQL 和 JPQL，JPQL 语法非常类似于SQL语法，但是是针对 Java 对象和实体。
先定义一个学生类：
@Entity
@Data
@Table(name = "tb_student")
public class Student {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private Long id;
@Column(name = "code")
private String code;
@Column(name = "gender")
private Integer gender;
@Column(name = "name")
private String name;
@Column(name = "age")
private Integer age;
@Column(name = "register")
private LocalDate register;
@Column(name = "address")
private String address;
}
定义一个StudentDAO接口，结构如下，以下定义的方法就在这个接口中定义
@Repository
public interface StudentDAO extends JpaRepository<Student, Integer>, JpaSpecificationExecutor<Student> {
}
我们可以直接在 DAO 的接口里定义以@Query注解的方式 SQL ，并且需要在@Query注解中添加属性 nativeQuery = true, 动态参数使用 ?1这种问号加数字的方式 ,?后面的数字从1开始
1： 最基本的方式：返回的类型是接口StudentDAO实现的类中定义的的类型， 比如上面红色的部分。
@Query(nativeQuery = true, value = "select * from tb_student where name like %?1%")
List<Student> findEntity(String name);
2：返回的类型不是基础类型，重新定义一个Vo
package com.benwu.dockerdemo.domain.vo;
import lombok.Data;
import java.time.LocalDate;
@Data
public class StudentVO {
private Long id;
private String code;
private String name;
private Integer age;
private LocalDate register;
private String address;
}
定义一个返回CusomerVO的方法：
@Query(nativeQuery = true, value = "select * from tb_student")
List<StudentVO> findList();
但是这样执行时会有错误，会报 Converter 异常，因为我们定义的映射类是Student,
解决方式1：实体类 StudentVO 从class 改为 interface ,
public interface StudentVO {
Long getId();
LocalDate getRegister();
String getCode();
String getName();
Integer getAge();
String getAddress();
}
解决方式2： 不用原生 SQL，使用 JPQL ，可以直接在 select 里面加上自定义实体类的构造函数，注意必须把实体类的完整 package 写全。
这个时候表名和字段名又不能按数据库表的来了，得按 Entity 对象定义的来了
@Query("select new com.benwu.dockerdemo.domain.vo.StudentVO(u.id, u.name,u.code,u.age,u.address,u.register) from Student u")
Optional<StudentVO> findList();
3：返回 Object 的数组
@Query(nativeQuery = true, value = "select * from tb_student")
List<Object[]> findObjects();
然后还需要定义一个转化器来对Object处理，转为我们需要的类型
public class StudentConverter {
public List<StudentVO> convert(List<Object[]> studObjs) {
StudentVO studentVO;
List<StudentVO> studentVOs=new ArrayList<>();
for (Object[] objArr : studObjs ) {
studentVO = new StudentVO();
studentVO.setId(Long.parseLong(objArr[0].toString()));
studentVO.setName(objArr[1].toString());
//从objArr数组中获取对应的元素的顺序是按表里定义字段的顺序，也就是也是sql语句中字段的顺序 ！！！
//其他省略...
studentVOs.add(studentVO);
}
return studentVOs;
}
}
感觉这个有点麻烦，还需要自己提供转化方法！！！
4： 返回 Map 类型的数据
@Query(value = "select new map(u.id, u.code,u.name, u.gender, u.register) from Student u where u.age > 8")
public List<Map<String, Object>> findListMap();
或者直接使用sql语句：
@Query(value = "select * from tb_student u where u.age > 8", nativeQuery = true)
public List<Map<String, Object>> findListMap();
5： 支持分页的方法
jpa中提供了 Pageable 类作为方法的参数来进行分页，最终的分页还是在 sql 后面加上 limit 和 offset 参数，并且会自动生成查询总数的 sql 语句。
@Query(nativeQuery = true, value = "select * from tb_student")
Page<Student> findPage(Pageable pageable);
定义一个Pageable来调用上面定义的方法
Sort sort = new Sort(Sort.Direction.ASC, "age");
Pageable pageable = PageRequest.of(cur, page, sort); //sort为可选参数
List<Student> students = findPage(pageable)
6: 使用Specification 构建查询参数
StudentDAO继承JpaSpecificationExecutor接口，JpaSpecificationExecutor中定义了一些使用Specification 来构建查询参数的方法；
public interface JpaSpecificationExecutor<T> {
Optional<T> findOne(@Nullable Specification<T> spec);
List<T> findAll(@Nullable Specification<T> spec);
Page<T> findAll(@Nullable Specification<T> spec, Pageable pageable);
List<T> findAll(@Nullable Specification<T> spec, Sort sort);
long count(@Nullable Specification<T> spec);
}
定义一个查询测试的方法：
@Resource
StudentDAO studentDAO;
@Test
public void testObject() {
Specification<Student> specification = new Specification<Student>() {
@Override
public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
return query.where(
builder.equal(root.get("id"), 1), //equal()相当于“=”
builder.equal(root.get("name"), "www")
).getRestriction();
}
};
Optional<Student> petOptional = studentDAO.findOne(specification);
if (petOptional.isPresent()) {
System.out.println(JSON.toJSONString(petOptional.get()));
}
}
new 一个Specification会重写它的toPredicate方法，里面参数的含义分别是：
root：得到查询的根，root.get(“变量名”)，根据变量名查询。
query：构建查询的顶层规则（where,from等）
builder：构建查询的底层规则（equal，like，in等）
一个比较复杂的查询构建：
@Test
public void tes2() {
final int age = 10;
final String likeName = "张";
final List<Long> studentIds = Arrays.asList(1L, 2L, 3L);
final Float minScore = 80f; //分数大于80
Specification<Student> spec = new Specification<Student>() {
List<Predicate> list = new ArrayList<>();
@Override
public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
list.add(cb.equal(root.get("dr"), false)); //删除标志
list.add(cb.like(root.get("name").as(String.class), "%" + likeName + "%")); //包含 likeName 的
list.add(cb.in(root.get("id")).value(cb.literal(studentIds))); //id 范围
//子查询： 根据最低分数来查询学生
if (minScore != null && minScore > 0f) {
List<Predicate> subList = new ArrayList<Predicate>();
Subquery<Long> subQuery = query.subquery(Long.class);
Root<SubjectScore> subRoot = subQuery.from(SubjectScore.class); //学科分数
subList.add(cb.ge(subRoot.get("score").as(Float.class), minScore)); //分数要大于最低分数
subQuery.where(cb.and(subList.toArray(new Predicate[subList.size()])));
subQuery.select(subRoot.get("student_id").as(Long.class)); //选择学生ID
list.add(cb.in(root.get("id").as(Long.class)).value(subQuery));
}
return cb.and(list.toArray(new Predicate[list.size()]));
}
};
}
如果语句中出现如下提示：
关闭Idea的语法检查：
其他补充中。。。
浙公网安备 33010602011771号