Pro JPA2读书笔记系列(十)-第九章(条件API)-干货

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
 */
@Service
public 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_id
LEFT OUTER JOIN sys_salesman salesman2_
ON sysuser0_.user_id=salesman2_.user_id
LEFT OUTER JOIN sys_region region3_
ON salesman2_.region_id=region3_.region_id
LEFT OUTER JOIN sys_coordinates region3_1_
ON region3_.region_id=region3_1_.region_id
LEFT OUTER JOIN sys_users_roles sysroles4_
ON sysuser0_.user_id=sysroles4_.user_id
LEFT OUTER JOIN sys_role sysrole5_
ON sysroles4_.role_id                               =sysrole5_.role_id
WHERE 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_id
LEFT OUTER JOIN sys_salesman salesman2_
ON sysuser0_.user_id=salesman2_.user_id
LEFT OUTER JOIN sys_region region3_
ON salesman2_.region_id=region3_.region_id
LEFT OUTER JOIN sys_coordinates region3_1_
ON region3_.region_id=region3_1_.region_id
LEFT OUTER JOIN sys_users_roles sysroles4_
ON sysuser0_.user_id=sysroles4_.user_id
LEFT OUTER JOIN sys_role sysrole5_
ON sysroles4_.role_id                               =sysrole5_.role_id
WHERE 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_id
LEFT OUTER JOIN sys_salesman salesman2_
ON sysuser0_.user_id=salesman2_.user_id
LEFT OUTER JOIN sys_region region3_
ON salesman2_.region_id=region3_.region_id
LEFT OUTER JOIN sys_coordinates region3_1_
ON region3_.region_id=region3_1_.region_id
LEFT OUTER JOIN sys_users_roles sysroles4_
ON sysuser0_.user_id=sysroles4_.user_id
LEFT OUTER JOIN sys_role sysrole5_
ON sysroles4_.role_id                                =sysrole5_.role_id
WHERE (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实体关系管理双向关联的一些思考