Pro JPA2读书笔记系列(十)-第九章(条件API)-干货
来源:互联网 发布:mysql gtid 编辑:程序博客网 时间:2024/06/06 11:36
Pro JPA2 第九章(条件API)-干货
嗯,演示一个使用条件API的例子,代码是公司的,所以实体的具体内容就不展示了- -!抱歉.
实体类:
使用hibernate-jpamodelgen
生成的实体元模型
AssembleEntity:
/** * 描述: TODO: * 包名: spring.data.specification.entity. * 作者: barton. * 日期: 16-7-11. * 项目名称: spring-data * 版本: 1.0 * JDK: since 1.8 */public class AssembleEntity { private String username; private String oname; private String userId; private String truename; private SysUser.UserStatus status; private String rname; private Long rid; public AssembleEntity(String username) { this.username = username; } public AssembleEntity(String username, String oname, String userId, String truename, SysUser.UserStatus status, String rname, Long rid) { this.username = username; this.oname = oname; this.userId = userId; this.truename = truename; this.status = status; this.rname = rname; this.rid = rid; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getOname() { return oname; } public void setOname(String oname) { this.oname = oname; } public String getUserId() { return userId; } public void setUserId(String userId) { this.userId = userId; } public String getTruename() { return truename; } public void setTruename(String truename) { this.truename = truename; } public SysUser.UserStatus getStatus() { return status; } public void setStatus(SysUser.UserStatus status) { this.status = status; } public String getRname() { return rname; } public void setRname(String rname) { this.rname = rname; } public Long getRid() { return rid; } public void setRid(Long rid) { this.rid = rid; } @Override public String toString() { return "AssembleEntity{" + "username='" + username + '\'' + ", oname='" + oname + '\'' + ", userId='" + userId + '\'' + ", truename='" + truename + '\'' + ", status='" + status + '\'' + ", rname='" + rname + '\'' + ", rid='" + rid + '\'' + '}'; }}
AccountService:
import org.apache.commons.lang3.StringUtils;import org.springframework.data.domain.PageRequest;import org.springframework.stereotype.Service;import spring.data.specification.entity.*;import javax.persistence.EntityManager;import javax.persistence.PersistenceContext;import javax.persistence.Tuple;import javax.persistence.TypedQuery;import javax.persistence.criteria.*;import java.util.ArrayList;import java.util.List;/** * 描述: TODO: * 包名: spring.data.specification.service. * 作者: barton. * 日期: 16-7-2. * 项目名称: spring-data * 版本: 1.0 * JDK: since 1.8 */@Servicepublic class AccountService { @PersistenceContext private EntityManager em; /** * 演示如何返回List<Tuple> 类型的数据. * Tuple是一个元组,表示可以通过Object[]数组的形式访问,也可以通过TupleElement的别名进行访问. */ public List<Tuple> selectAccountByPositionAndStatus(String orgName, String status, String regionName, PageRequest page, String searchParam) { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Tuple> cq = cb.createTupleQuery(); // 根(主表) Root<SysUser> user = cq.from(SysUser.class); // 设置表连接关系 Join<SysUser, SysOrganization> organization = user.join(SysUser_.sysOrganization, JoinType.LEFT); Join<SysUser, SalesMan> salesMan = user.join(SysUser_.salseMan, JoinType.LEFT); Join<SalesMan, Region> region = salesMan.join(SalesMan_.region, JoinType.LEFT); Join<SysUser, SysRole> role = user.join(SysUser_.sysRoles, JoinType.LEFT);// 这种方式用在CriteriaQuery<Tuple> 是不可以的.// List<Selection> selections = new ArrayList<>();//// selections.add(user.get(SysUser_.username).alias("username"));// selections.add(user.get(SysUser_.sysOrganization).get(SysOrganization_.name).alias("name")); // 设置要检索的字段 cq.multiselect(user.get(SysUser_.username).alias("username"), organization.get(SysOrganization_.name).alias("oname"), user.get(SysUser_.id).alias("userId"), salesMan.get(SalesMan_.truename).alias("truename"), user.get(SysUser_.status).alias("status"), region.get(Region_.name).alias("rname"), role.get(SysRole_.id).alias("rid") ); // 设置检索条件 List<Predicate> conditions = getConditions(orgName, cb, organization, user, status, searchParam, salesMan); cq.where(conditions.toArray(new Predicate[conditions.size()])); TypedQuery<Tuple> query = em.createQuery(cq); return query.getResultList(); } /** * 演示如何返回List<Object[]> 类型的数据. * 虽然可以在mulselect方法中设置别名,但无法使用. */ public List<Object[]> selectAccountByPositionAndStatus2(String orgName, String status, String regionName, PageRequest page, String searchParam) { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Object[]> cq = cb.createQuery(Object[].class); // 根(主表) Root<SysUser> user = cq.from(SysUser.class); // 设置表连接关系 Join<SysUser, SysOrganization> organization = user.join(SysUser_.sysOrganization, JoinType.LEFT); Join<SysUser, SalesMan> salesMan = user.join(SysUser_.salseMan, JoinType.LEFT); Join<SalesMan, Region> region = salesMan.join(SalesMan_.region, JoinType.LEFT); Join<SysUser, SysRole> role = user.join(SysUser_.sysRoles, JoinType.LEFT);// subquery 不支持出现在select语句中// // 子查询// Subquery<SysUser> sq = cq.subquery(SysUser.class);// Root<SalesMan> sqSm = sq.from(SalesMan.class);// Join<SalesMan, SysUser> sqSmJoin = sqSm.join(SalesMan_.user, JoinType.LEFT);// sq.select(sqSmJoin).where(cb.equal(salesMan.get(SalesMan_.id), "A37018206110")); // 设置要检索的字段 cq.multiselect(user.get(SysUser_.username).alias("username"), organization.get(SysOrganization_.name).alias("oname"), // 链式编程 user.get(SysUser_.id).alias("userId"), salesMan.get(SalesMan_.truename).alias("truename"), user.get(SysUser_.status).alias("status"), region.get(Region_.name).alias("rname"), role.get(SysRole_.id).alias("rid")/*, sq*/ ); // 设置检索条件 List<Predicate> conditions = getConditions(orgName, cb, organization, user, status, searchParam, salesMan); cq.where(conditions.toArray(new Predicate[conditions.size()])); TypedQuery<Object[]> query = em.createQuery(cq); return query.getResultList(); } /** * 演示如何返回List<AssembleEntity> 类型的数据. * 将select方法中设置的字段 直接映射到一个实体类中 * where条件中 使用子查询 * 虽然可以在mulselect方法中设置别名,但无法使用. * 注意点: * 1.AssembleEntity中必须有和select方法中所这是要检索的字段顺序,类型一致的构造方法 * 2.跟别名无关 */ public List<AssembleEntity> selectAccountByPositionAndStatus3(String orgName, String status, String regionName, PageRequest page, String searchParam) { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<AssembleEntity> cq = cb.createQuery(AssembleEntity.class); // 根(主表) Root<SysUser> user = cq.from(SysUser.class);// // 设置表连接关系 Join<SysUser, SysOrganization> organization = user.join(SysUser_.sysOrganization, JoinType.LEFT); Join<SysUser, SalesMan> salesMan = user.join(SysUser_.salseMan, JoinType.LEFT); Join<SalesMan, Region> region = salesMan.join(SalesMan_.region, JoinType.LEFT); Join<SysUser, SysRole> role = user.join(SysUser_.sysRoles, JoinType.LEFT); // 设置要检索的字段 cq.select(cb.construct(AssembleEntity.class, user.get("username"), organization.get(SysOrganization_.name), user.get(SysUser_.id), salesMan.get(SalesMan_.truename), user.get(SysUser_.status), region.get(Region_.name), role.get(SysRole_.id))); // 设置检索条件 List<Predicate> conditions = getConditions(orgName, cb, organization, user, status, searchParam, salesMan); // 子查询 Subquery<SysUser> sq = cq.subquery(SysUser.class); Root<SalesMan> sqSm = sq.from(SalesMan.class); Join<SalesMan, SysUser> sqSmJoin = sqSm.join(SalesMan_.user, JoinType.LEFT); sq.select(sqSmJoin).where(cb.equal(salesMan.get(SalesMan_.id), "A37018206110")); //conditions.add(cb.in(user).value(sq)); conditions.add(cb.exists(sq)); cq.where(conditions.toArray(new Predicate[conditions.size()])); TypedQuery<AssembleEntity> query = em.createQuery(cq); return query.getResultList(); } /** * in的写法 */ private CriteriaBuilder.In getIn(CriteriaBuilder cb, Root<SysUser> root) { CriteriaBuilder.In<String> in = cb.in(root.get(SysUser_.status).as(String.class)); in.value("0"); in.value("1"); return in; } private List<Predicate> getConditions(String orgName, CriteriaBuilder cb, Join<SysUser, SysOrganization> organization, Root<SysUser> user, String status, String searchParam, Join<SysUser, SalesMan> salesMan) { // 设置检索条件 List<Predicate> conditions = new ArrayList<>(); if (StringUtils.isNotBlank(orgName)) { if (!StringUtils.startsWith(orgName, "al")) { Predicate p; if (StringUtils.equals("大区总监", orgName.trim())) { p = cb.and(cb.equal(organization.get(SysOrganization_.name).as(String.class), "大区总监")); } else { p = cb.and(cb.equal(organization.get(SysOrganization_.name).as(String.class), "服务站经理")); } conditions.add(p); conditions.add(getIn(cb, user)); } else { if (!StringUtils.equals("used", status)) { conditions.add(cb.and(cb.equal(user.get(SysUser_.status).as(String.class), status))); } else { conditions.add(getIn(cb, user)); } } if (StringUtils.isNotBlank(searchParam)) { conditions.add(cb.like(salesMan.get(SalesMan_.truename).as(String.class), "%" + searchParam + "%")); } } return conditions; }}
AccounServiceTest:
import org.junit.Test;import org.junit.runner.RunWith;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;import org.springframework.util.Assert;import spring.data.specification.entity.AssembleEntity;import spring.data.specification.service.AccountService;import javax.persistence.Tuple;import java.util.List;/** * 描述: TODO: * 包名: spring.data.specification. * 作者: barton. * 日期: 16-7-2. * 项目名称: spring-data * 版本: 1.0 * JDK: since 1.8 */@RunWith(SpringJUnit4ClassRunner.class)@SpringBootTest(classes = SpecificationApplication.class)public class AccountServiceTest { @Autowired private AccountService accountService; @Test public void testA() { List<Tuple> result = accountService.selectAccountByPositionAndStatus("al", "used", "中国", null, null); Assert.notNull(result); result.forEach(tuple -> System.out.println(tuple.get("userId"))); } @Test public void testB() { List<Object[]> result = accountService.selectAccountByPositionAndStatus2("al", "used", "中国", null, null); Assert.notNull(result); } @Test public void testC() { List<AssembleEntity> result = accountService.selectAccountByPositionAndStatus3("al", "used", "中国", null, null); Assert.notNull(result); result.forEach(assembleEntity -> System.out.println(assembleEntity.getUsername())); }}
testA()生成的sql:
SELECT sysuser0_.username AS col_0_0_, sysorganiz1_.name AS col_1_0_, sysuser0_.user_id AS col_2_0_, salesman2_.truename AS col_3_0_, sysuser0_.status AS col_4_0_, region3_.name AS col_5_0_, sysrole5_.role_id AS col_6_0_FROM sys_user sysuser0_LEFT OUTER JOIN sys_organization sysorganiz1_ON sysuser0_.organization_id=sysorganiz1_.organization_idLEFT OUTER JOIN sys_salesman salesman2_ON sysuser0_.user_id=salesman2_.user_idLEFT OUTER JOIN sys_region region3_ON salesman2_.region_id=region3_.region_idLEFT OUTER JOIN sys_coordinates region3_1_ON region3_.region_id=region3_1_.region_idLEFT OUTER JOIN sys_users_roles sysroles4_ON sysuser0_.user_id=sysroles4_.user_idLEFT OUTER JOIN sys_role sysrole5_ON sysroles4_.role_id =sysrole5_.role_idWHERE CAST(sysuser0_.status AS VARCHAR2(255 CHAR)) IN (? , ?);
testB()生成的sql:
SELECT sysuser0_.username AS col_0_0_, sysorganiz1_.name AS col_1_0_, sysuser0_.user_id AS col_2_0_, salesman2_.truename AS col_3_0_, sysuser0_.status AS col_4_0_, region3_.name AS col_5_0_, sysrole5_.role_id AS col_6_0_FROM sys_user sysuser0_LEFT OUTER JOIN sys_organization sysorganiz1_ON sysuser0_.organization_id=sysorganiz1_.organization_idLEFT OUTER JOIN sys_salesman salesman2_ON sysuser0_.user_id=salesman2_.user_idLEFT OUTER JOIN sys_region region3_ON salesman2_.region_id=region3_.region_idLEFT OUTER JOIN sys_coordinates region3_1_ON region3_.region_id=region3_1_.region_idLEFT OUTER JOIN sys_users_roles sysroles4_ON sysuser0_.user_id=sysroles4_.user_idLEFT OUTER JOIN sys_role sysrole5_ON sysroles4_.role_id =sysrole5_.role_idWHERE CAST(sysuser0_.status AS VARCHAR2(255 CHAR)) IN (? , ?);
testC()称称的sql:
SELECT sysuser0_.username AS col_0_0_, sysorganiz1_.name AS col_1_0_, sysuser0_.user_id AS col_2_0_, salesman2_.truename AS col_3_0_, sysuser0_.status AS col_4_0_, region3_.name AS col_5_0_, sysrole5_.role_id AS col_6_0_FROM sys_user sysuser0_LEFT OUTER JOIN sys_organization sysorganiz1_ON sysuser0_.organization_id=sysorganiz1_.organization_idLEFT OUTER JOIN sys_salesman salesman2_ON sysuser0_.user_id=salesman2_.user_idLEFT OUTER JOIN sys_region region3_ON salesman2_.region_id=region3_.region_idLEFT OUTER JOIN sys_coordinates region3_1_ON region3_.region_id=region3_1_.region_idLEFT OUTER JOIN sys_users_roles sysroles4_ON sysuser0_.user_id=sysroles4_.user_idLEFT OUTER JOIN sys_role sysrole5_ON sysroles4_.role_id =sysrole5_.role_idWHERE (CAST(sysuser0_.status AS VARCHAR2(255 CHAR)) IN (? , ?))AND (EXISTS (SELECT sysuser7_.user_id FROM sys_salesman salesman6_ LEFT OUTER JOIN sys_user sysuser7_ ON salesman6_.user_id =sysuser7_.user_id WHERE salesman2_.user_id=? ));
使用Spring-Boot-JPA的时候的坑:
- 使用
Specification
接口的时候,JPA2.0规范的multiselect()
和select()
方法不起作用.原因是Spring-Boot-JPA会自己调用一次select()
方法,而在JPA2.0中,由于条件API对象的不可变性,同样的方法调用两次,会产生覆盖. - 条件API不支持出现在select,from子句的子查询
使用Spring-Boot-JPA时候的最佳实践(目前总结出的):
- 最好是把上例中AccountService中关于条件API查询的按照Spring-Boot-JPA推荐的自定义repository接口的写法来写,因为这样更规范.
- 如果Spring-Boot-JPA提供的功能满足不了你,或者说我在检索的时候就是只想用部分字段,还是直接用条件API比较好,参考上边的坑.
- 由于实体之间的关系在执行查询的之后序列化的过程中会出现查起来没完的情况(懒加载也没用,因为序列化的时候hibernate代理会执行相应字段属性的getter方法.这些都是基于Java反射机制实现的,如果还不懂,最好自己去看下Java反射的知识),除了基本的增删改查之外,最好从@EntityGraph,@NamedQuery,@Query,条件API,JP QL,序列化忽略(@JsonIgnore)中选择出最合适当前情况的技术来使用.自定义Spring的消息转换器并不能完美的解决问题,因为在JSON序列化的时候,实体对象全部是hibernate代理对象,还是存在session状态的.或者是我水平还不到- -,如果有朋友能够完美解决这个问题,还请告知下,谢谢!
- 对JPA实体关系管理双向关联的一些思考
1 0
- Pro JPA2读书笔记系列(十)-第九章(条件API)-干货
- Pro JPA2读书笔记系列(九)-第九章(条件API)
- Pro JPA2读书笔记系列(十三)-第十一章(高级主题)-缓存-干货
- Pro JPA2读书笔记系列(二)-第二章(入门)
- Pro JPA2读书笔记系列(三)-第三章(企业应用程序)
- Pro JPA2读书笔记系列(四)-第四章(对象-关系映射)
- Pro JPA2读书笔记系列(五)-第五章(集合映射)
- Pro JPA2读书笔记系列(六)-第六章(实体管理器)
- Pro JPA2读书笔记系列(七)-第七章(使用查询)
- Pro JPA2读书笔记系列(八)-第八章(查询语言)
- Pro JPA2读书笔记系列(十二)-第十一章(高级主题)
- Pro JPA2读书笔记系列(一):第一章(简介)
- Pro JPA2读书笔记系列(十一)-第十章(高级对象-关系映射)
- pro JPA2 精通java持久化API 第四章
- pro JPA2 精通java持久化API 第五章
- pro JPA2 精通java持久化API 第六章
- pro JPA2 精通java持久化API 第七章
- pro JPA2 精通java持久化API 第八章
- 指数哥伦布编码
- java中的自加运算符
- C++ STL--stack/queue 的使用方法
- 基于Android的百度地图开发
- 数据结构实验之栈二:一般算术表达式转换成后缀式
- Pro JPA2读书笔记系列(十)-第九章(条件API)-干货
- ecmall 常用标签| 格式化代码 | 变量调节器
- MessageDigest的功能及用法
- 今天太阳了一条狗!大悲咒走起
- Hadoop:NameNode和Secondary NameNode
- #vsftpd.config文件文档
- C语言中比较好的方法
- 汇编指令
- POJ - 3311 Hie with the Pie