JPA访问数据库的几种方式
本文为原创,转载请注明出处:https://www.cnblogs.com/supiaopiao/p/10901793.html
1. Repository
1.1. 通过方法名称直接生成查询
Keyword
Sample
JPQL snippet
And
findByLastnameAndFirstname
… where x.lastname = ?1 and x.firstname = ?2
Or
findByLastnameOrFirstname
… where x.lastname = ?1 or x.firstname = ?2
Is,Equals
findByFirstname,findByFirstnameIs,findByFirstnameEquals
… where x.firstname = ?1
Between
findByStartDateBetween
… where x.startDate between ?1 and ?2
LessThan
findByAgeLessThan
… where x.age < ?1
LessThanEqual
findByAgeLessThanEqual
… where x.age <= ?1
GreaterThan
findByAgeGreaterThan
… where x.age > ?1
GreaterThanEqual
findByAgeGreaterThanEqual
… where x.age >= ?1
After
findByStartDateAfter
… where x.startDate > ?1
Before
findByStartDateBefore
… where x.startDate < ?1
IsNull
findByAgeIsNull
… where x.age is null
IsNotNull,NotNull
findByAge(Is)NotNull
… where x.age not null
Like
findByFirstnameLike
… where x.firstname like ?1
NotLike
findByFirstnameNotLike
… where x.firstname not like ?1
StartingWith
findByFirstnameStartingWith
… where x.firstname like ?1(parameter bound with appended %)
EndingWith
findByFirstnameEndingWith
… where x.firstname like ?1(parameter bound with prepended %)
Containing
findByFirstnameContaining
… where x.firstname like ?1(parameter bound wrapped in %)
OrderBy
findByAgeOrderByLastnameDesc
… where x.age = ?1 order by x.lastname desc
Not
findByLastnameNot
… where x.lastname <> ?1
In
findByAgeIn(Collection ages)
… where x.age in ?1
NotIn
findByAgeNotIn(Collection ages)
… where x.age not in ?1
True
findByActiveTrue()
… where x.active = true
False
findByActiveFalse()
… where x.active = false
IgnoreCase
findByFirstnameIgnoreCase
… where UPPER(x.firstame) = UPPER(?1)
In和NotIn可以使用任何Collection的子类作为参数。
案例:
public interfaceUserRepository extendsJpaRepository, JpaSpecificationExecutor {
Page findByName(String name);
List findByNameAndPhone(String name, String phone);
User findUserById(Integer id);
voiddeleteByIdIn(List idList);
}
1.2. @Query注解
可以使用Spring Data JPA 的@Query注解将查询绑定到repository的函数上。
备注:注解到查询方法上的@Query执行顺序优先于下文中的@NamedQuery。
1.2.1. HQL
注意:使用“:属性名”的时候,最好在接口上加上@Param注解,否则会报类似如下异常:
public interfaceUserRepository extendsJpaRepository{
@Query(value = "SELECT u FROM User u WHERE u.namelike %?1%")//不加nativeQuery应使用HQLList findTable2(String name);
@Query(value = "SELECT u FROM User u WHERE u.namelike %:name% and u.sex= :sex")
List findTable3(@Param("name") String name, @Param("sex")String sex);
}
1.2.2. 原生SQL
(1)@Query注解通过设置nativeQuery标识支持执行原生SQL查询语句
public interfaceUserRepository extendsJpaRepository{
@Query(value = "SELECT*FROM user WHERE name like %?1%", nativeQuery = true)
List findTable1(String name);
}
(2)Spring Data JPA通过原生SQL进行查询时,不能满足Page条件进行分页,所以可以通过countQuery标识执行count查询实现Page分页:
public interfaceUserRepository extendsJpaRepository{
@Query(value = "SELECT*FROM user WHERE name like %?1%",
countQuery = "SELECTcount(*) FROM user WHERE name like %?1%",
nativeQuery = true)
Page findTable3(String name, Pageable pageable);
}
1.3. @Modifying注解
在@Query注解中编写JPQL实现DELETE和UPDATE操作的时候必须加上@Modifying注解,以通知Spring Data这是一个DELETE或UPDATE操作。
UPDATE或者DELETE操作需要使用事务,此时需要定义Service层,在Service层的方法上添加事务操作@Transactional。
Modifying查询语句中能用于void/int/Integer 返回类型,不能用于其他类型
注意JPQL不支持INSERT操作。
代码示例:
public interfaceUserRepository extendsJpaRepository{
@Modifying
@Query("UPDATE User u SET u.name= :name WHERE u.id= :id")
voidupdateTable(@Param("name") String name, @Param("id")Integer id);
@Modifying
@Query("delete from User u WHERE u.idin :idList")
voiddeleteByIds(@Param("idList")List idList);
}
2. 实体类中定义,在Repository中使用
2.1. 命名查询@NamedQuery
使用@NamedQuery为实体创建查询适用于定义少量查询。@NamedQuery需要声明在实体类上,@NamedQuery可以实现命名HQL查询(或JPQL)。
备注:注解到查询方法上的@Query(上文中)执行顺序优先于@NamedQuery。
@Entity
@NamedQuery(name = "Two.selectBySex",query = "select t from Two t where t.sex = ?1 group by id")
public classTwo {
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name="id",nullable=false)
privateInteger id;
@Column(name="name",nullable=false)
privateString name;
@Column(name="sex",nullable=false)
privateString sex;
Set、get方法省略
}
Repository层代码展示:
public interfaceTwoRepository extendsJpaRepository{
//实体类上通过@NamedQuery注解实现查询List selectBySex(String sex);
}
Spring Data处理这些方法对命名查询的调用,以实体类名称开始,后接方法名,以点作连接符。所以NameQuery定义在实体上,而不是定义在方法上。
2.2. 一个实体类中有多个命名查询@NamedQueries
上面我们演示了命名查询@NamedQuery的写法,当然JPA也支持多个@NamedQuery,那就是@NamedQueries
@Entity
@NamedQueries(value = {
@NamedQuery(
name = "Two.selectBySex",
query = "select t from Two t where t.sex = ?1 group by id"),
@NamedQuery(
name = "Two.findUserByPrimaryKey",
query = "select t from Two t where t.id = :id")
})
public classTwo {
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name="id",nullable=false)
privateInteger id;
@Column(name="name",nullable=false)
privateString name;
@Column(name="sex",nullable=false)
privateString sex;
Set、get方法省略
}
Repository层代码展示:
public interfaceTwoRepository extendsJpaRepository{
//实体类上通过@NamedQuery注解实现查询List selectBySex(String sex);
Two findUserByPrimaryKey(@Param("id") Integer id);
}
Spring Data处理这些方法对命名查询的调用,以实体类名称开始,后接方法名,以点作连接符。所以NameQuery定义在实体上,而不是定义在方法上。
2.3. 命名原生sql查询@NamedNativeQuery
@NamedNativeQuery可以实现命名原生sql查询
@Entity
@NamedNativeQuery(name = "Three.selectBySex", query = "select*from three where sex = ?1 group by id", resultClass = Three.class)
@Table(name="three")
public classThree {
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name="id",nullable=false)
privateInteger id;
@Column(name="name",nullable=false)
privateString name;
@Column(name="sex",nullable=false)
privateString sex;
Set、get方法省略
}
Repository层代码展示:
public interfaceThreeRepository extendsJpaRepository{
//实体类上通过@NamedNativeQuery注解实现查询List selectBySex(String sex);
}
2.4. 一个实体类中有多个命名查询@NamedNativeQueries
上面我们演示了命名查询@NamedNativeQuery的写法,当然JPA也支持多个@NamedNativeQuery,那就是@NamedNativeQueries
@Entity
@NamedNativeQueries(value = {
@NamedNativeQuery(
name = "Three.selectBySex",
query = "select*from three where sex = ?1 group by id",
resultClass = Three.class),
@NamedNativeQuery(
name = "Three.findUserByPrimaryKey",
query = "select*from three where id = :id",
resultClass = Three.class)//resutlClass用来指定实体类,resutlSetMapping用来指定映射的名称
})
@Table(name="three")
public classThree {
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name="id",nullable=false)
privateInteger id;
@Column(name="name",nullable=false)
privateString name;
@Column(name="sex",nullable=false)
privateString sex;
Set、get方法省略
}
Repository层代码展示:
public interfaceThreeRepository extendsJpaRepository{
//实体类上通过@NamedNativeQuery注解实现查询List selectBySex(String sex);
Three findUserByPrimaryKey(@Param("id") Integer id);
}
3. 外部ORM文件中定义,在Repository中使用
3.1. 命名查询named-query
使用XML配置,向位于resources/META-INF文件夹下的orm.xml配置文件添加必要的元素。
resources/META-INF下orm.xml配置:
SELECT o FROM One o WHERE o.sex = ?1 group by id
SELECT o FROM One o WHERE o.id = :id
Repository层代码展示:
public interfaceOneRepository extendsJpaRepository{
//1.1XML定义命名查询List selectByName(String name);
One findUserByPrimaryKey(@Param("id") Integer id);
}
3.2. 原生SQL查询named-native-query
使用XML配置,向位于resources/META-INF文件夹下的orm.xml配置文件添加必要的元素。
resources/META-INF下orm.xml配置:
SELECT * FROM one WHERE sex = ?1 group by id
SELECT * FROM one WHERE id = :id
Repository层代码展示:
packagecn.com.bmsoft.stormplan.basic.dao;
importcn.com.bmsoft.stormplan.basic.entity.One;
importorg.springframework.data.jpa.repository.JpaRepository;
importorg.springframework.data.jpa.repository.JpaSpecificationExecutor;
importorg.springframework.data.repository.query.Param;
importjava.util.List;
public interfaceOneRepository extendsJpaRepository{
//1.2XML定义 原生sql查询 named-native-queryList selectByName1(String sex);
One findUserByPrimaryKey1(@Param("id")Integer id);
}
4. SpEL表达式
从Spring Data JPA 1.4版本开始,支持在@Query定义的查询中使用SpEL模板表达式。在执行查询的时候,这些表达式通过一个事先定义好的变量集合求值。Spring Data JPA支持一个名为entityName的变量。它的用法是select x from #{#entityName} x。它会将域类型的entityName与给定repository关联起来。entityName按照如下方式处理:如果域类型在@Entity注解上设置了name属性,则使用该属性。否则直接使用域类型的类名。
下例演示了在定义带有查询方法和手工定义查询的repository接口时使用#{#entityName}表达式。
public interfaceUserRepository extendsJpaRepository{
@Query(value = "SELECT u FROM #{#entityName} u WHERE u.name like %:name% and u.sex = :sex")
List findTable4(@Param("name") String name, @Param("sex")String sex);
}
SpEL表达式的好处:
参考网址:https://www.cnblogs.com/tilv37/p/6944182.html
5. Specifications动态构建查询
5.1. 参数介绍
Predicate:单独每一条查询条件的详细描述
Predicate[]:多个查询条件的详细描述
Root:查询哪个表
CriteriaQuery:查询哪些字段,排序是什么
CriteriaBuilder:字段之间是什么关系,如何生成一个查询条件,每个查询条件都是什么方式
(1)CriteriaQuery:主要是构建查询条件
distinct、select、where、groupby、having、orderby等
(2)CriteriaBuilder:主要是用来进行一些函数操作
① and
② or
③ between
④ lt(小于)、le(小于等于)、gt(大于)、ge(大于等于)
⑤ not(非)等...
5.2. 代码案例
//where empname like ? group by wages
@Override
publicPage search1(EmployeeVO employeeVO) throwsException {
Pageable pageable = PageRequest.of(employeeVO.getPage() - 1, employeeVO.getSize(), Sort.Direction.ASC,"empid");
Specification specification = newSpecification() {
@Override
publicPredicate toPredicate(Root root, CriteriaQuery> criteriaQuery, CriteriaBuilder cb) {
List list = newArrayList<>(10);
if(StringUtils.isNotBlank(employeeVO.getEmpname())){
list.add(cb.and(
cb.like(root.get("empname").as(String.class),"%"+ employeeVO.getEmpname()+"%")
));
}
Expression wages = root.get("wages").as(BigDecimal.class);
returncriteriaQuery.where(list.toArray(newPredicate[list.size()])).groupBy(wages).getRestriction();
//where empname like ? group by wages}
};
Page stuPage = employeeRepository.findAll(specification, pageable);
returnstuPage;
}
//where empname like ? or deptid=?
@Override
publicPage search2(EmployeeVO employeeVO) throwsException {
Pageable pageable = PageRequest.of(employeeVO.getPage() - 1, employeeVO.getSize(), Sort.Direction.ASC,"empid");
Specification specification = newSpecification() {
@Override
publicPredicate toPredicate(Root root, CriteriaQuery> criteriaQuery, CriteriaBuilder cb) {
List list = newArrayList<>(10);
if(StringUtils.isNotBlank(employeeVO.getEmpname()) && null!= employeeVO.getDeptid()){
list.add(cb.or(
cb.like(root.get("empname").as(String.class),"%"+ employeeVO.getEmpname()+"%"),
cb.equal(root.get("deptid").as(Integer.class), employeeVO.getDeptid())
));
}//where empname like ? or deptid=?returncriteriaQuery.where(list.toArray(newPredicate[list.size()])).getRestriction();
}
};
Page stuPage = employeeRepository.findAll(specification, pageable);
returnstuPage;
}
//where wages < ?
@Override
publicPage search3(EmployeeVO employeeVO) throwsException {
Pageable pageable = PageRequest.of(employeeVO.getPage() - 1, employeeVO.getSize(), Sort.Direction.ASC,"empid");
Specification specification = newSpecification() {
@Override
publicPredicate toPredicate(Root root, CriteriaQuery> criteriaQuery, CriteriaBuilder cb) {
Predicate wages = cb.lt(root.get("wages").as(BigDecimal.class), employeeVO.getWages());
returncriteriaQuery.where(wages).getRestriction();
}
};//where wages < ?Page stuPage = employeeRepository.findAll(specification, pageable);
returnstuPage;
}
//where wages between ? and ?
@Override
publicPage search4(EmployeeVO employeeVO){
Pageable pageable = PageRequest.of(employeeVO.getPage() - 1, employeeVO.getSize(), Sort.Direction.ASC,"empid");
Specification specification = newSpecification() {
@Override
publicPredicate toPredicate(Root root, CriteriaQuery> criteriaQuery, CriteriaBuilder cb) {
Predicate wages = cb.between(root.get("wages").as(BigDecimal.class), employeeVO.getMinWages(), employeeVO.getMaxWages());
returncriteriaQuery.where(wages).getRestriction();
}
};//where wages between ? and ?Page stuPage = employeeRepository.findAll(specification, pageable);
returnstuPage;
}
6. JPA Join联表查询
构建表关系如下:
实体类代码展示:
@Entity
@Table(name="a")
public classAEntity {
@Id
@GeneratedValue(strategy= GenerationType.IDENTITY)
@Column(name="aid",nullable=false)
privateInteger aid;
@Column(name="aname",nullable=false)
privateString aname;
省略set、get方法
}
@Entity
@Table(name="b")
public classBEntity {
@Id
@GeneratedValue(strategy= GenerationType.IDENTITY)
@Column(name="bid",nullable=false)
privateInteger bid;
@Column(name="bname",nullable=false)
privateString bname;
@OneToOne(cascade=CascadeType.ALL) //B是关系的维护端,当删除 b,会级联删除 a@JoinColumn(name = "aid", referencedColumnName = "aid") //name:关联id,referencedColumnName:A表idprivateAEntity aEntity;//A表
省略set、get方法}
@Entity
@Table(name="c")
public classCEntity {
@Id
@GeneratedValue(strategy= GenerationType.IDENTITY)
@Column(name="cid",nullable=false)
privateInteger cid;
@Column(name="cname",nullable=false)
privateString cname;
省略set、get方法
}
@Entity
@Table(name="d")
public classDEntity {
@Id
@GeneratedValue(strategy= GenerationType.IDENTITY)
@Column(name="did",nullable=false)
privateInteger did;
@Column(name="dname",nullable=false)
privateString dname;
//可选属性optional=false,表示B不能为空。删除d,不影响b@ManyToOne(cascade={CascadeType.MERGE,CascadeType.REFRESH},optional=false)
@JoinColumn(name = "bid", referencedColumnName = "bid")//name:关联id,referencedColumnName:B表idprivateBEntity bEntity;//B表//可选属性optional=false,表示C不能为空。删除d,不影响c@ManyToOne(cascade={CascadeType.MERGE,CascadeType.REFRESH},optional=false)
@JoinColumn(name = "cid", referencedColumnName = "cid")//name:关联id,referencedColumnName:C表id@JsonBackReference
privateCEntity cEntity;//C表省略set、get方法
}
Service层代码展示:
@Override
publicPage search(DVO dVO) {
Pageable pageable = PageRequest.of(dVO.getPage() - 1, dVO.getSize(), Sort.Direction.ASC,"did");
Specification specification = newSpecification() {
@Override
publicPredicate toPredicate(Root root, CriteriaQuery> criteriaQuery, CriteriaBuilder cb) {
List list = newArrayList<>(10);
//A:B:D 1:1:n 根据D的实体类查询anameif(StringUtils.isNotBlank(dVO.getAname())) {
Join ajoin = root.join("bEntity",JoinType.LEFT);
list.add(cb.like(ajoin.get("aEntity").get("aname").as(String.class),"%"+ dVO.getAname()+"%"));
}//select * from d left join b on d.bid=b.bid left join a on b.aid=a.aid where a.aname like "%xxx%"//B:D 1:n 根据D的实体类查询bnameif(StringUtils.isNotBlank(dVO.getBname())) {
Join ajoin = root.join("bEntity",JoinType.LEFT);
list.add(cb.like(ajoin.get("bname").as(String.class),"%"+ dVO.getBname()+"%"));
}//select * from d left join b on d.bid=b.bid where b.bname like "%xxx%"//C:D 1:n 根据D的实体类查询cnameif(StringUtils.isNotBlank(dVO.getCname())) {
Join ajoin = root.join("cEntity",JoinType.LEFT);
list.add(cb.like(ajoin.get("cname").as(String.class),"%"+ dVO.getCname()+"%"));
}//select * from d left join c on d.cid=c.cid where c.cname like "%xxx%"returncriteriaQuery.where(list.toArray(newPredicate[list.size()])).getRestriction();
}
};
Page stuPage = dRepository.findAll(specification, pageable);
returnstuPage;
}