最近利用了spring data jpa来完成数据会见层的实现。感受较量强大，也较量巨大，中间尚有不少限制。

话说数据库sql的应用有点像打怪进级，一点一点不绝增加难度。

1. 对付一般应用中种种简朴的增删查改，spring data提供了按照名字直接查询的署理要领，啥都不需要做，独一需要编写接口，定名要领，这部门实在是太利便了，并且简朴查询办理了差不多80%的问题。这部门相对简朴，不再赘述，参考用法大全部门。

2. 对付巨大但又相对牢靠的查询，可以利用JPQL和Native Sql，即@Query直接写JPQL语句的方法。这部门也不难，简朴给个例子，需要留意的是返回功效假如是多个值而且返回多组，那应该以Object[][]暗示

@Query(value = "SELECT su.id, su.name_CN, avg(s.rate), count(b.id), count(concat(b.key, '@', s.admin)) "  
            + "FROM  " + CommonConstants.SCHEMA_PREFIX + "Submarket su,  " + CommonConstants.SCHEMA_PREFIX + "Building b,  " + CommonConstants.SCHEMA_PREFIX + "Space s,  " + CommonConstants.SCHEMA_PREFIX + "Market m,  " + CommonConstants.SCHEMA_PREFIX + "City c "  
            + "where b.submarket_id = su.id and s.building_id = b.id and su.market_id = m.id and m.city_id = c.id and c.country_id = ?1 group by su.id", nativeQuery=true)  
    Object[][] findAreaInfoByCountryId(int parentId);

3. 对付巨大且动态的查询，利用Criteria。对付criteria的用法，那就有相当多的内容了。需要利用到criteria的场景凡是是界面上有各类过滤和条件选项。

Criteria由于需要利用类的方法将整个巨大sql语句组织起来，因此有不少类，我们先来领略下这些类的寄义。

3.1大大都环境下，搜索返回的功效是一个数据库中的实体工具，对付这种环境，实际上可以直接利用spring data jpa提供的toPredicate要领，软件开发，该要领界说如下

public interface Specification<T> {  

    Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb);  
}

实际利用时只需要把预界说好的Repository工具担任JpaSpecificationExecutor工具即可

@Repository    
public interface CityDao extends JpaSpecificationExecutor<City>{    
｝

真正挪用时只需要通报如下回调要领，spring会自动帮你完身分页

Page<City> page = cityDao.findAll(new Specification<City>() {    
            @Override    
            public Predicate toPredicate(Root<City> root, CriteriaQuery<?> query, CriteriaBuilder cb) {    
                root = query.from(City.class);    
                Path<String> nameExp = root.get("name");    
                return cb.like(nameExp, "%北京%");    
            }    

        }, new PageRequest(1, 5, new Sort(Direction.DESC, new String[] { "id" })));

对付这种环境，固然动态查询较量巨大，可是要名誉是个中相当简朴的范例了。

3.2 我们来看boss进级难度今后的环境。如果此时你的查询中不是一个简朴实体范例，而是一个巨大的聚合工具，有一堆聚合查询，有一堆a工具的属性，一堆b工具的属性。大概你还试图用toPredicate要领继承，但实际上Page只答允你通报已界说好的数据库中的实体工具，因为其root中界说的泛型实际上限制了后续的行为，好比想在root上join，假如root不是一个数据库实体则编译就报错了。别的由此激发的query.multiselect自界说查询功效无效，因为功效默认就是你界说好的谁人实体。这时候的办理步伐就是自界说dao实现类。首先，界说一个自界说实现接口

@NoRepositoryBean  
public interface SearchJpaRepositoryCustom {  

    public Page<Tuple> searchListing(final ListingSearchContext searchContext, Pageable pageable);  

}

其次，dao接口得担任该自界说接口

public interface BuildingRepository extends PagingAndSortingRepository<Building, Integer>, SearchJpaRepositoryCustom

然后，真正的dao模子实现类如下，需要留意，自界说的实现类必需实现自界说接口，而且，名字是BuildingRepository+impl，留意这里踩过坑

public class BuildingRepositoryImpl extends PagableRepository implements SearchJpaRepositoryCustom {  

    @PersistenceContext  
    private EntityManager em;  

    private Join<Space, ?> getSearchExpression(final CriteriaBuilder cb, final ListingSearchContext searchContext,  
            final Root<Space> root, final Predicate predicate) {  
        List<Expression<Boolean>> expressions = predicate.getExpressions();  
        // 只搜索版本为0的(即当前版本)  
        expressions.add(cb.equal(root.<String> get("ver"), 0));  
        if (searchContext.getSpaceId() > 0) {  
            expressions.add(cb.equal(root.<Integer> get("id"), searchContext.getSpaceId())); // id  
        }  
        if (null != searchContext.getMinRate()) {  
            expressions.add(cb.greaterThanOrEqualTo(root.<BigDecimal> get("rate"), searchContext.getMinRate())); // 价值  
        }  
        if (null != searchContext.getMaxRate()) {  
            expressions.add(cb.lessThanOrEqualTo(root.<BigDecimal> get("rate"), searchContext.getMaxRate())); // 价值  
        }  
        if (null != searchContext.getLCD()) {  
            expressions.add(cb.lessThanOrEqualTo(root.<Date> get("dateAvailable"), searchContext.getLCD())); // 可用日期  
        }  
        // spaceTypeId  
        if (searchContext.getSpaceTypeId() > 0) {  
            expressions.add(cb.equal(root.<String> get("spaceType").get("id"), searchContext.getSpaceTypeId()));  
        }  
        // buildingGrade&submarket  
        Join<Space, ?> buildingJoin = root.join(root.getModel().getSingularAttribute("building"), JoinType.INNER);  
        if (searchContext.getBuildingGradeId() > 0) {  
            expressions.add(cb.equal(buildingJoin.get("buildingGrade").get("id"), searchContext.getBuildingGradeId()));  
        }  
        if (searchContext.getSubmarketId() > 0) {  
            expressions.add(cb.equal(buildingJoin.get("submarket").get("id"), searchContext.getSubmarketId()));  
        }  
        if (StringUtils.isNotEmpty(searchContext.getKeyword())) {  
            Predicate like1 = cb.like(buildingJoin.<String> get("buildingNameCn"),  
                    "%" + searchContext.getKeyword() + "%");  
            Predicate like2 = cb.like(buildingJoin.<String> get("addressCn"), "%" + searchContext.getKeyword() + "%");  
            expressions.add(cb.or(like1, like2)); // 要害字  
        }  
        return buildingJoin;  
    }  

    @Override  
    public Page<Tuple> searchListing(final ListingSearchContext searchContext, Pageable pageable) {  
        final CriteriaBuilder cb = em.getCriteriaBuilder();  
        final CriteriaQuery<Tuple> query = cb.createTupleQuery();  
        final Root<Space> root = query.from(Space.class);  

        Predicate predicate = cb.conjunction();  

        Join<Space, ?> buildingJoin = getSearchExpression(cb, searchContext, root, predicate);  

        Join<Space, ?> spaceTypeJoin = root.join(root.getModel().getSingularAttribute("spaceType"), JoinType.INNER);  

        Join<Space, ?> contiguousJoin = root.join(root.getModel().getSingularAttribute("contiguous"), JoinType.INNER);  

        Join<Building, ?> assetJoin = buildingJoin.join("asset", JoinType.INNER);  

        Join<BuildingGrade, ?> buildingGradeJoin = buildingJoin.join("buildingGrade", JoinType.INNER);  

        SetJoin<Asset, ?> mediaJoin = assetJoin.joinSet("medias");  

        mediaJoin.on(cb.and(cb.equal(mediaJoin.get("type"), "photo"), cb.equal(mediaJoin.get("subtype"), "main")));  

        Expression<BigDecimal> maxConExp = cb.max(contiguousJoin.<BigDecimal> get("maxContiguous"));  
        Expression<BigDecimal> totalConExp = cb.sum(root.<BigDecimal> get("size"));  
        query.multiselect(cb.count(root.<Integer> get("id")), root.<Integer> get("userByAdmin").get("id"), totalConExp,  
                maxConExp, cb.min(root.<BigDecimal> get("minDivisible")), root.<Integer> get("building"),  
                cb.max(root.<Integer> get("stage")), cb.min(root.<Integer> get("lcd")),  
                cb.min(root.<Integer> get("led")), cb.min(root.<Integer> get("floor")),  
                cb.max(root.<Integer> get("floor")), mediaJoin.get("path"), spaceTypeJoin.get("nameEn"),  
                buildingGradeJoin.get("nameEn"));  

        query.where(predicate);  
        query.orderBy(cb.desc(root.get("gmtCreate").as(Date.class)));  
        query.groupBy(root.<Integer> get("building").get("id"), root.<String> get("userByAdmin").get("id"));  

        Predicate minExp = null;  
        Predicate maxExp = null;  
        Predicate minMaxResultExp = null;  
        if (null != searchContext.getMinSize()) {  
            minExp = cb.greaterThanOrEqualTo(cb.min(root.<BigDecimal> get("minDivisible")), searchContext.getMinSize()); // 最小面积  
            minMaxResultExp = minExp;  
        }  
        if (null != searchContext.getMaxSize()) {  
            maxExp = cb.lessThanOrEqualTo(searchContext.isContiguous() ? maxConExp : totalConExp,  
                    searchContext.getMaxSize()); // 最大面积  
            minMaxResultExp = maxExp;  
        }  
        if (null != searchContext.getMinSize() && null != searchContext.getMaxSize()) {  
            minMaxResultExp = cb.or(minExp, maxExp);  
        }  
        if (null != minMaxResultExp) {  
            query.having(minMaxResultExp);  
        }  
        TypedQuery<Tuple> pagableQuery = em.createQuery(query);  
        return pageable == null ? new PageImpl<Tuple>(pagableQuery.getResultList())  
                : readPage(pagableQuery, pageable);  
    }  

}