0%

Spring Data JPA中多表联合查询最佳实践

Spring Data JPA对于单表操作非常方便,采用定义接口的方式,不用写任何实现代码就可以获得常用的数据库操作。但是对于多表联合查询,则不那么方便了,目前公司项目是采用数据库视图的方法,将多表联合查询全部变成了单表查询。数据库视图有众多好处,不失为一种解决方案,但是也存在一些弊端:

  • 当数据库表结构变化需要同步修改视图,维护繁琐;
  • 业务需求变化可能导致频繁修改视图暴露的字段;
  • 有些场景可能只需要2表联合,有些场景需要更多表联合,要么建立一个大视图,要么需要建立多个类似视图,都不太好;
  • 视图的SQL会变得越来越庞大,难以维护;
  • 定义了实体类,JPA自动建表会把视图建成表;
  • SQL SERVER会将视图的查询转换为对基本表的查询,性能不高。

总之将一部分业务逻辑放到数据库层维护,并不是一个特别好的方式。那么Spring Data JPA对多表查询还有哪些方法呢?有没有更好的选择呢?

答案是肯定的,直接上结论。

方案 说明 自定义接收对象 SQL 分页 多表联合 问题
方案一 @Query JPQL:DTO或投影原生SQL:Object[],map(2个字段时) JPQL或原生SQL JpaRepository 实体上配不配关系都可以 1.查询条件要嵌入SQL语句内,一些复杂的情形不好处理,例如某个字段模糊检索,字段是动态的;2.分页查询countQuery把查询语句重复了一遍
方案二 Specification 不支持,只能返回对应PO 无SQL 结合JpaRepository 需要在实体上配置关系,如@OneToOne,否则无法实现左连接,只能联合查询 1.实体需要配置连接关系2.每一个关联对象都是单独的数据库查询
方案三 EntityManager 不支持投影,其他同@Query JPQL或原生SQL 自己封装 实体上配不配关系都可以 相比于@Query好处是,JPQL字符串可以动态拼接,可以处理一些复杂的查询情形。但是分页需要自己封装。
方案四 CriteriaQuery DTO 无SQL 自己封装 需要在实体上配置关系,如@OneToOne否则无法实现左连接,只能联合查询 同Specification,且分页需要自己封装
终极方案 QueryDSL DTOTuple 无SQL 支持 实体上配不配关系都可以 解决以上所有问题

选择一个好的解决方案,需要考虑如下几个方面:

  • 能够自定义对象接收查询结果集;
  • 能够支持复杂的、动态的查询条件;
  • 既然使用JPA,当然最好能够用类型安全的查询方式,并且使用起来比较自然;
  • 能够原生支持分页查询;
  • 能够支持left join,并且对实体定义没有约束。

上表就是从这几个方面进行分析,最后QueryDSL堪称完美,下面详细介绍几种方案。

示例代码:https://github.com/zhongpan/jpa-demo.git

Spring Data JPA

先了解下JPA、Hibernate、Spring Data JPA三者的关系是什么?

JPA是一个接口规范,随着Java EE 5发布,也是EJB3.0的一部分。Hibernate是先于JPA出现的一种历史悠久的ORM框架,它实现了JPA,也是目前用的最多的实现。而Sprint Data JPA是Spring中提供的开箱即用的基于JPA的数据库访问框架,其采用的实现正是Hibernate。Spring Data JPA提供的数据库访问能力如下:

img

从上述接口的名字就可以看出:

  • CrudRepository:最基本的增删改查操作
  • PagingAndSortingRepository:分页查询
  • QueryByExampleExecutor:基于样本查询,避免了传一堆参数,还要判断是否null
  • JpaSpecificationExecutor:基于Specification查询,就是对CriteriaQuery的封装,类型安全的查询方式
  • QuerydslPredicateExecutor:基于QueryDSL的查询,也是类型安全的

上述接口的实现在SimpleJpaRepository和QuerydslJpaPredicateExecutor中,其中就是基于JPA的EntiryManager接口进行封装。如果我们要重写实现,也是通过EntiryManager来完成。

方案一

首先想到的方法自然是使用@Query注解,直接使用JPQL进行联合查询,自定义接收对象,left join都不在话下。主要的问题是对于一些复杂的、动态的查询条件不好处理,另外分页的countQuery不得不把主查询重写一遍,有点烦人。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@Repository
public interface VmhostDao extends JpaRepository<VmhostPO, String> {

// 方法一:使用@Query注解,可以自定义接收对象
// 问题:查询条件要嵌入SQL语句内,一些复杂的情形不好处理,例如某个字段模糊检索,字段是动态的;分页查询countQuery把查询语句重复了一遍

@Query("select new com.example.demo.entity.VmhostDTO(v, u, t) from VmhostPO v left join AuthUserPO u on v.userid = u.id left join AuthTenantPO t on v.tenantid = t.id where v.name like %?1%")
List<VmhostDTO> findVmhost(String name);

@Query("select new com.example.demo.entity.VmhostInfoDTO(v.id, v.name, u.username, t.name) from VmhostPO v left join AuthUserPO u on v.userid = u.id left join AuthTenantPO t on v.tenantid = t.id where v.name like %:name%")
List<VmhostInfoDTO> findVmhostInfo(String name);

@Query("select v.id as id, v.name as name, u.username as userName, t.name as tname from VmhostPO v left join AuthUserPO u on v.userid = u.id left join AuthTenantPO t on v.tenantid = t.id")
List<VmhostInfoByProjection> findVmhostInfoByProjection();

@Query(value = "select new com.example.demo.entity.VmhostInfoDTO(v.id, v.name, u.username, t.name) from VmhostPO v left join AuthUserPO u on v.userid = u.id left join AuthTenantPO t on v.tenantid = t.id where v.name like %:name%",
countQuery = "select count(*) from VmhostPO v left join AuthUserPO u on v.userid = u.id left join AuthTenantPO t on v.tenantid = t.id where v.name like %:name%")
Page<VmhostInfoDTO> findVmhostInfoByPage(String name, Pageable pageable);

}

方案二

那么SQL的组织能否动态编程控制呢,自然会想到Specification查询,查询条件可以通过CriteriaQuery动态拼装。这也是Spring Data JPA中用的最广泛的查询方式。但是这种方式存在一些限制,首先不能灵活自定义接收对象,只能返回PO,其次要想实现left join,必须在实体上定义关系,最后关联对象不是一次查询回来的,而是单独的查询。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
public interface VmhostSpecWithRelationDao
extends JpaRepository<VmhostWithRelationPO, String>, JpaSpecificationExecutor<VmhostWithRelationPO> {

// 方案二:使用Specification查询
// 问题:实体必须配置关系,否则无法左连接;每个关联对象是单独数据库查询
}
package com.example.demo.service;

@Service
public class VmhostService {


public List<VmhostWithRelationPO> listVmhostSpecWithRelation(String name) {
Specification<VmhostWithRelationPO> spec = (root, cq, cb) -> {
root.join("user", JoinType.LEFT);
root.join("tenant", JoinType.LEFT);
return cb.like(root.get("name"), "%" + name + "%");
};
List<VmhostWithRelationPO> list = vmhostSpecWithRelationDao.findAll(spec);
return list;
}

}

可能大家有两点疑问:

  • @Query和Specification能否混用,@Query定义select的结果,Specification定义查询条件

答案:不行,总是@Query有效,你定义的Specification参数压根就不会理会

1
2
3
4
5
6
7
8
9
10
11
12
13
14
// JpaSpecificationExecutor的参数和JpaRepository不一样,没啥用,SimpleJpaRepository总是用的JpaRepository的参数
public interface VmhostSpecDao extends JpaRepository<VmhostPO, String>, JpaSpecificationExecutor<VmhostInfoDTO> {

// 方案二:@Query和Specification是不能混用的,也无法改变接收结果集对象

// 无法混用,总是query有效,spec参数压根就不会理会
@Query("from VmhostPO")
List<VmhostPO> findVmhost(Specification<VmhostPO> spec);

// 覆盖JpaSpecificationExecutor的方法可以吗?一样的,根本不会走到findAll的默认实现
@Override
@Query("select new com.example.demo.entity.VmhostInfoDTO(v.id, v.name, u.username, t.name) from VmhostPO v left join AuthUserPO u on v.userid = u.id left join AuthTenantPO t on v.tenantid = t.id")
List<VmhostInfoDTO> findAll(Specification<VmhostInfoDTO> spec);
}
  • Specification控制接收结果集对象

答案:对不起,Specification的toPredicate中执行select是无效的,里面只能返回查询条件。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
  // 这样写没有用,生成如下sql
// select vmhostpo0_.id as id1_2_, vmhostpo0_.addresses as addresse2_2_, vmhostpo0_.availablezone as availabl3_2_, vmhostpo0_.baremetal as baremeta4_2_, vmhostpo0_.cpucore as cpucore5_2_, vmhostpo0_.createtime as createti6_2_, vmhostpo0_.disksize as disksize7_2_, vmhostpo0_.floatip as floatip8_2_, vmhostpo0_.hostname as hostname9_2_, vmhostpo0_.locked as locked10_2_, vmhostpo0_.metadata as metadat11_2_, vmhostpo0_.name as name12_2_, vmhostpo0_.privatenetworkid as private13_2_, vmhostpo0_.ramsize as ramsize14_2_, vmhostpo0_.tenantid as tenanti15_2_, vmhostpo0_.tenantname as tenantn16_2_, vmhostpo0_.type as type17_2_, vmhostpo0_.userid as userid18_2_, vmhostpo0_.username as usernam19_2_, vmhostpo0_.vmstatus as vmstatu20_2_ from vmhost vmhostpo0_ cross join auth_user authuserpo1_ cross join auth_tenant authtenant2_ where vmhostpo0_.userid=authuserpo1_.id and vmhostpo0_.tenantid=authtenant2_.id and (vmhostpo0_.name like ?)
public Optional<VmhostInfoDTO> listVmhostSpec(String name) {
Specification<VmhostInfoDTO> spec = (root, cq, cb) -> {
// 只能cross join,要left join需要在实体上建立关系
Root<AuthUserPO> user = cq.from(AuthUserPO.class);
Root<AuthTenantPO> tenant = cq.from(AuthTenantPO.class);
// 这里执行select没有用,这个函数只能返回查询条件,外层会覆盖select
cq.multiselect(root.get("id"), root.get("name"), user.get("username"), tenant.get("name"));
return cb.and(cb.equal(root.get("userid"), user.get("id")), cb.equal(root.get("tenantid"), tenant.get("id")),
cb.like(root.get("name"), "%" + name + "%"));

};
return vmhostSpecDao.findOne(spec);
}

因为SimpleJpaRepository的实现已经固定了select,跟JpaRepository的类型参数相关,跟JpaSpecificationExecutor的类型参数无关

1
2
3
4
5
6
7
8
9
10
11
12
13
14
protected <S extends T> TypedQuery<S> getQuery(@Nullable Specification<S> spec, Class<S> domainClass, Sort sort) {

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<S> query = builder.createQuery(domainClass);

Root<S> root = applySpecificationToCriteria(spec, domainClass, query);
query.select(root);

if (sort.isSorted()) {
query.orderBy(toOrders(sort, root, builder));
}

return applyRepositoryMethodMetadata(em.createQuery(query));
}

方案三

上面两种使用方法是Spring Data JPA用的最多的,接下来只能从底层入手,直接使用EntiryManager。这种方法完全靠自己,所有接口都需要自己实现,丧失了Spring Data JPA的便利性。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
@Repository
public class VmhostEMDao {

@Autowired
@PersistenceContext
private EntityManager entityManager;

// 方案三:使用原生的entityManager,解决@Query的SQL无法动态拼接问题
// 此时分页就需要自己封装了,也没有了JPA自动实现的接口
// 注意这里like后面要引号

@SuppressWarnings("unchecked")
public List<VmhostDTO> findVmhost(String name) {
List<VmhostDTO> list = entityManager.createQuery(
"select new com.example.demo.entity.VmhostDTO(v, u, t) from VmhostPO v left join AuthUserPO u on v.userid = u.id left join AuthTenantPO t on v.tenantid = t.id where v.name like '%"
+ name + "%'")
.getResultList();
return list;
}

@SuppressWarnings("unchecked")
public List<VmhostInfoByProjection> findVmhostInfoByProjection() {
// 此时总是Object[],不支持投影
List<VmhostInfoByProjection> list = entityManager.createQuery(
"select v.id as id, v.name as name, u.username as userName, t.name as tname from VmhostPO v left join AuthUserPO u on v.userid = u.id left join AuthTenantPO t on v.tenantid = t.id")
.getResultList();
return list;
}

}

方案四

类似于方案二之于方案一,我们也可以使用类型安全的查询方式CriteraQuery。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@Repository
public class VmhostCQDao {

@Autowired
@PersistenceContext
private EntityManager entityManager;

// 方案四:相对于方案三,使用了类型安全的CriteriaQuery,其实Specification也是用的CriteriaQuery,所以存在和Specification一样的限制,但是可以控制select了,比Specification灵活一点

public List<VmhostDTO> findVmhost(String name) {
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<VmhostDTO> query = builder.createQuery(VmhostDTO.class);
// 实体上没有配置关系,无法使用left join,只能联合查询(inner join)
Root<VmhostPO> root = query.from(VmhostPO.class);
Root<AuthUserPO> rootUser = query.from(AuthUserPO.class);
Root<AuthTenantPO> rootTenant = query.from(AuthTenantPO.class);
query.multiselect(root, rootUser, rootTenant).where(builder.equal(root.get("userid"), rootUser.get("id")),
builder.equal(root.get("tenantid"), rootTenant.get("id")), builder.like(root.get("name"), "%" + name + "%"));

List<VmhostDTO> list = entityManager.createQuery(query).getResultList();
return list;
}

}

终极方案

到了终极方案了,Spring Data JPA集成了对QueryDSL的支持,官方参考见:http://www.querydsl.com/static/querydsl/latest/reference/html_single

是不是有点像方案二+方案四,单表的时候直接使用JpaRepository和QuerydslPredicateExecutor提供的默认实现。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
package com.example.demo.dao;

import com.example.demo.entity.VmhostPO;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;

public interface VmhostQDSLDao
extends JpaRepository<VmhostPO, String>, QuerydslPredicateExecutor<VmhostPO>, VmhostRepository {

// 方案五:VmhostRepository使用原生的entityManager配合QueryDSL,完美解决所有问题
// 对于单表也可以使用QuerydslPredicateExecutor,自动拥有默认实现

}

多表的时候就基于EntityManager扩展,但是querydsl已经帮我们做了很多工作,不是从头开始。querydsl的书写方式相对于CriteriaQuery也更加自然,易于理解。

1
2
3
4
5
6
7
@NoRepositoryBean
public class BaseRepository {

@PersistenceContext
protected EntityManager em;

}
1
2
3
4
5
6
7
public interface VmhostRepository {

public List<VmhostDTO> findVmhost(Predicate predicate);

public QueryResults<VmhostDTO> findVmhostByPage(Predicate predicate, Pageable pageable);

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
@Repository
public class VmhostRepositoryImpl extends BaseRepository implements VmhostRepository {

// 多表左连接

@Override
public List<VmhostDTO> findVmhost(Predicate predicate) {
JPAQueryFactory queryFactory = new JPAQueryFactory(em);
JPAQuery<VmhostDTO> jpaQuery = queryFactory
.select(Projections.constructor(VmhostDTO.class, QVmhostPO.vmhostPO, QAuthUserPO.authUserPO,
QAuthTenantPO.authTenantPO))
.from(QVmhostPO.vmhostPO).leftJoin(QAuthUserPO.authUserPO)
.on(QVmhostPO.vmhostPO.userid.stringValue().eq(QAuthUserPO.authUserPO.id.stringValue()))
.leftJoin(QAuthTenantPO.authTenantPO)
.on(QVmhostPO.vmhostPO.tenantid.stringValue().eq(QAuthTenantPO.authTenantPO.id.stringValue()));
jpaQuery.where(predicate);
return jpaQuery.fetch();
}

@Override
public QueryResults<VmhostDTO> findVmhostByPage(Predicate predicate, Pageable pageable) {
JPAQueryFactory queryFactory = new JPAQueryFactory(em);
JPAQuery<VmhostDTO> jpaQuery = queryFactory
.select(Projections.constructor(VmhostDTO.class, QVmhostPO.vmhostPO, QAuthUserPO.authUserPO,
QAuthTenantPO.authTenantPO))
.from(QVmhostPO.vmhostPO).leftJoin(QAuthUserPO.authUserPO)
.on(QVmhostPO.vmhostPO.userid.stringValue().eq(QAuthUserPO.authUserPO.id.stringValue()))
.leftJoin(QAuthTenantPO.authTenantPO)
.on(QVmhostPO.vmhostPO.tenantid.stringValue().eq(QAuthTenantPO.authTenantPO.id.stringValue()))
.offset(pageable.getOffset()).limit(pageable.getPageSize());
jpaQuery.where(predicate);
return jpaQuery.fetchResults();
}

}

总结

以上方法都还是在Spring Data JPA框架之内,如果你愿意,你也可以去重写SimpleJpaRepository,重写了注意通过如下注解启用。

1
@EnableJpaRepositories(repositoryBaseClass = XXXXXX.class)

其实QueryDSL已经做了很好的封装,完全没有必要重复造轮子,Spring Data JPA也提供了很多扩展点,在保留其便利性的基础上,根据需要去扩展,不需要全部推倒重来。

-------------本文结束感谢您的阅读-------------