JPA是一个接口规范,随着Java EE 5发布,也是EJB3.0的一部分。Hibernate是先于JPA出现的一种历史悠久的ORM框架,它实现了JPA,也是目前用的最多的实现。而Sprint Data JPA是Spring中提供的开箱即用的基于JPA的数据库访问框架,其采用的实现正是Hibernate。Spring Data JPA提供的数据库访问能力如下:
@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,必须在实体上定义关系,最后关联对象不是一次查询回来的,而是单独的查询。
// 覆盖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); }
// 这样写没有用,生成如下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 + "%"));
@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; }