jpa中批量更新和插入实现及复杂查询.md

批量更新和插入

@NoRepositoryBean
public interface BaseRepository<T, ID extends Serializable> extends JpaRepository<T, ID>, JpaSpecificationExecutor<T> {
    <S extends T> Iterable<S> batchSave(Iterable<S> var1);
    <S extends T> Iterable<S> batchUpdate(Iterable<S> var1);
}

public class BaseRepositoryImpl<T, ID extends Serializable> extends SimpleJpaRepository<T, ID> implements BaseRepository<T, ID> {
    //500一批
    private static final int BATCH_SIZE = 500;

    private EntityManager em;

    public BaseRepositoryImpl(JpaEntityInformation<T, ?> entityInformation, EntityManager em) {
        super(entityInformation, em);
        this.em = em;

    }

    public BaseRepositoryImpl(Class<T> domainClass, EntityManager em) {
        super(domainClass, em);
        this.em = em;
    }

    @Override
    @Transactional
    public <S extends T> Iterable<S> batchSave(Iterable<S> var1) {
        Iterator<S> iterator = var1.iterator();
        int index = 0;
        while (iterator.hasNext()) {
            em.persist(iterator.next());
            index++;
            if (index % BATCH_SIZE == 0) {
                em.flush();
                em.clear();
            }
        }
        if (index % BATCH_SIZE != 0) {
            em.flush();
            em.clear();
        }
        return var1;
    }

    @Override
    @Transactional
    public <S extends T> Iterable<S> batchUpdate(Iterable<S> var1) {
        Iterator<S> iterator = var1.iterator();
        int index = 0;
        while (iterator.hasNext()) {
            em.merge(iterator.next());
            index++;
            if (index % BATCH_SIZE == 0) {
                em.flush();
                em.clear();
            }
        }
        if (index % BATCH_SIZE != 0) {
            em.flush();
            em.clear();
        }
        return var1;
    }
}

//业务中使用
public interface QuestionRepository extends BaseRepository<Question, Integer> {

    Question findQuestionByQuestionIdAndDeleteState(String questionId, Short deleteState) throws Exception;
    
    @Modifying
    @Query("update Question set deleteState=1,deleteTime=:deleteTime where categoryId=:categoryId and createUserId=:userId")
    int deleteAllByCategoryId(String categoryId, Integer userId, Long deleteTime) throws Exception;

}

jpa复杂查询

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
　　　　 //查询结果所需要的类型(Entity相对应)
CriteriaQuery<Entity> criteriaQuery = criteriaBuilder.createQuery(Entity.class);
　　　　 //查询所需要的主体类（Entity0相对应）
Root<Entity0> root = criteriaQuery.from(Entity0.class);
　　　　 //查询结果-select（此处查询所有符合条件的主体类）
criteriaQuery.select(root);
　　　　 //过滤条件用Predicate方法拼接
Predicate restrictions = criteriaBuilder.conjunction();
　　　　 //过滤条件——equal（当Entity0关联member类时，Entity0：member=m：1）
restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member));
//过滤条件——like
restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.like(root.<String>get("str"), "%" + str + "%"));
//用户名查询（member里面的username匹配） ———— 多层查询 ———— 子查询的一种：适用于m:1或1:1（即多对一或一对一）
restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.like(root.get("member").<String>get("username"), "%" + username + "%"));
//子查询（规范写法，先判断查询内容是否存在）（适用于1：m）（即一对多）
if (searchType != null || searchValue != null || hasExpired != null || status != null || type != null || isPendingReceive != null
        || isPendingRefunds != null || isAllocatedStock != null || businessType != null) {
    //建立子查询
　　　　　　Subquery<Order> orderSubquery = criteriaQuery.subquery(Order.class);
    Root<Order> orderSubqueryRoot = orderSubquery.from(Order.class);
    orderSubquery.select(orderSubqueryRoot);
　　　　　　　//子查询和父查询相关联
    Predicate orderRestrictions = criteriaBuilder.equal(orderSubqueryRoot.<MergeOrder>get("mergeOrder"), root);
    //子查询过滤条件拼接
    if (searchType != null && searchValue != null) {
        if ("phone".equals(searchType)) {
            orderRestrictions = criteriaBuilder.and(orderRestrictions, criteriaBuilder.like(orderSubqueryRoot.<String>get("phone"), "%" + searchValue + "%"));
        }
    }
    if (type != null) {
        CriteriaBuilder.In<Order.Type> in = criteriaBuilder.in(orderSubqueryRoot.<Order.Type>get("type"));
        in.value(type);
        orderRestrictions = criteriaBuilder.and(orderRestrictions, in);
    }
    //and、or以及判断是否为null，比较（>）的使用（比较可以用于日期比较）
    if (hasExpired != null) {
        orderRestrictions = criteriaBuilder.and(orderRestrictions, criteriaBuilder.or(orderSubqueryRoot.get("expire").isNull(), criteriaBuilder.greaterThan(orderSubqueryRoot.<Date>get("expire"), new Date())));
    }
    // not的使用方法（不符合上述过滤条件），notEqual的使用，<（小于）的使用
    if (isPendingReceive != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("paymentMethodType"), PaymentMethod.Type.cashOnDelivery));
        Predicate predicate = criteriaBuilder.and(criteriaBuilder.or(orderSubqueryRoot.get("expire").isNull()
                , criteriaBuilder.greaterThan(orderSubqueryRoot.<Date>get("expire"), new Date()))
                , criteriaBuilder.notEqual(orderSubqueryRoot.get("status"), Order.Status.completed)
                , criteriaBuilder.lessThan(orderSubqueryRoot.<BigDecimal>get("amountPaid"), orderSubqueryRoot.<BigDecimal>get("amount")));
        if (isPendingReceive) {
            orderRestrictions = criteriaBuilder.and(orderRestrictions, criteriaBuilder.not(predicate));
        }
    }
    // 多层查询使用
    if (businessType != null) {
        orderRestrictions = criteriaBuilder.and(orderRestrictions, criteriaBuilder.equal(orderSubqueryRoot.get("store").get("business").get("businessType"), businessType));
    }
　　　　// 拼接过滤条件
    orderSubquery.where(orderRestrictions);
    // 和总条件拼接（exists的使用）
    restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(orderSubquery));
}
criteriaQuery.where(restrictions);
　　　　
TypedQuery<Entity> query = entityManager.createQuery(criteriaQuery);
　　　　
Entity singleResult = query.getSingleResult();