（点击
上方公众号
，可快速关注）
来源：liweisnake ，
blog.csdn.net/liweisnake/article/details/50991485
如有好文章投稿，请点击 → 这里了解详情
最近使用了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
{
Predicate toPredicate(Root
root, CriteriaQuery> query, CriteriaBuilder cb);
}
实际使用时只需要把预定义好的Repository对象继承JpaSpecificationExecutor对象即可
@Repository
public interface CityDao extends JpaSpecificationExecutor
{
｝
真正调用时只需要传递如下回调方法，spring会自动帮你完成分页
Page
page = cityDao.findAll(new Specification
() {
@Override
public Predicate toPredicate(Root
root, CriteriaQuery> query, CriteriaBuilder cb) {
root = query.from(City.class);
Path
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
searchListing(final ListingSearchContext searchContext, Pageable pageable);
}
其次，dao接口得继承该自定义接口
public interface BuildingRepository extends PagingAndSortingRepository
, SearchJpaRepositoryCustom
然后，真正的dao模型实现类如下，需要注意，自定义的实现类必须实现自定义接口，并且，名字是BuildingRepository+impl，注意这里踩过坑
public class BuildingRepositoryImpl extends PagableRepository implements SearchJpaRepositoryCustom {
@PersistenceContext
private EntityManager em;
private Join
getSearchExpression(final CriteriaBuilder cb, final ListingSearchContext searchContext,
final Root
root, final Predicate predicate) {
List
> expressions = predicate.getExpressions();
// 只搜索版本为0的(即当前版本)
expressions.add(cb.equal(root.
get("ver"), 0));
if (searchContext.getSpaceId() > 0) {
expressions.add(cb.equal(root.
get("id"), searchContext.getSpaceId())); // id
}
if (null != searchContext.getMinRate()) {
expressions.add(cb.greaterThanOrEqualTo(root.
get("rate"), searchContext.getMinRate())); // 价格
}
if (null != searchContext.getMaxRate()) {
expressions.add(cb.lessThanOrEqualTo(root.
get("rate"), searchContext.getMaxRate())); // 价格
}
if (null != searchContext.getLCD()) {
expressions.add(cb.lessThanOrEqualTo(root.
get("dateAvailable"), searchContext.getLCD())); // 可用日期
}
// spaceTypeId
if (searchContext.getSpaceTypeId() > 0) {
expressions.add(cb.equal(root.
get("spaceType").get("id"), searchContext.getSpaceTypeId()));
}
// buildingGrade&submarket
Join
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.
get("buildingNameCn"),
"%" + searchContext.getKeyword() + "%");
Predicate like2 = cb.like(buildingJoin.
get("addressCn"), "%" + searchContext.getKeyword() + "%");
expressions.add(cb.or(like1, like2)); // 关键字
}
return buildingJoin;
}
@Override
public Page
searchListing(final ListingSearchContext searchContext, Pageable pageable) {
final CriteriaBuilder cb = em.getCriteriaBuilder();
final CriteriaQuery
query = cb.createTupleQuery();
final Root
root = query.from(Space.class);
Predicate predicate = cb.conjunction();
Join
buildingJoin = getSearchExpression(cb, searchContext, root, predicate);
Join
spaceTypeJoin = root.join(root.getModel().getSingularAttribute("spaceType"), JoinType.INNER);
Join
contiguousJoin = root.join(root.getModel().getSingularAttribute("contiguous"), JoinType.INNER);
Join
assetJoin = buildingJoin.join("asset", JoinType.INNER);
Join
buildingGradeJoin = buildingJoin.join("buildingGrade", JoinType.INNER);
SetJoin
mediaJoin = assetJoin.joinSet("medias");
mediaJoin.on(cb.and(cb.equal(mediaJoin.get("type"), "photo"), cb.equal(mediaJoin.get("subtype"), "main")));
Expression
maxConExp = cb.max(contiguousJoin.
get("maxContiguous"));
Expression
totalConExp = cb.sum(root.
get("size"));
query.multiselect(cb.count(root.
get("id")), root.
get("userByAdmin").get("id"), totalConExp,
maxConExp, cb.min(root.
get("minDivisible")), root.
get("building"),
cb.max(root.
get("stage")), cb.min(root.
get("lcd")),
cb.min(root.
get("led")), cb.min(root.
get("floor")),
cb.max(root.
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.
get("building").get("id"), root.
get("userByAdmin").get("id"));
Predicate minExp = null;
Predicate maxExp = null;
Predicate minMaxResultExp = null;
if (null != searchContext.getMinSize()) {
minExp = cb.greaterThanOrEqualTo(cb.min(root.
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
pagableQuery = em.createQuery(query);
return pageable == null ? new PageImpl
(pagableQuery.getResultList())
: readPage(pagableQuery, pageable);
}
}
3.3 难度又升级了，解决了上面的问题以后，面临新的问题：分页。使用toPredicate时，spring已经实现了分页，而自定义实现类后，分页也需要自己实现。
首当其冲的问题就是count，如果在3.2中用了group by这种操作，而此处分页时候又想统计分页结果，那么实际上就是想count 分组数。常见做法是：
select count(*) from
(select ... group by ...)
但是实际上jpa不支持这种类型的子查询，本人这里试了很久各种方法，实际上都行不通。参考文末“特殊情况的count写法”
最终使用了将 criteria还原为hql，通过translator组装成native的sql，附加上参数，最终执行的方式，通过后的参考代码如下
protected Long executeCountQuery(final ListingSearchContext searchContext, TypedQuery
query) {
String hqlString = query.unwrap(Query.class).getQueryString();
QueryTranslatorFactory translatorFactory = new ASTQueryTranslatorFactory();
