最近使用了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);
}
}
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<Tuple> query) {
String hqlString = query.unwrap(Query.class).getQueryString();
QueryTranslatorFactory translatorFactory = new ASTQueryTranslatorFactory();
Query hibernateQuery = ((HibernateQuery)query).getHibernateQuery();
QueryImpl hImpl = (QueryImpl)hibernateQuery;
Map<String, TypedValue> paramMap = (Map<String, TypedValue>)ReflectionUtils.getFieldValue(hImpl,"namedParameters");
QueryTranslator translator = translatorFactory.createQueryTranslator(hqlString, hqlString, paramMap,
(SessionFactoryImplementor) emf.unwrap(SessionFactory.class), null);
translator.compile(paramMap, false);
javax.persistence.Query nativeQuery = em
.createNativeQuery("select count(*) from (" + translator.getSQLString() + ") x");
ParameterTranslations parameterTranslations = translator.getParameterTranslations();
for(String name : paramMap.keySet()){
for (int position : parameterTranslations.getNamedParameterSqlLocations(name)) {
nativeQuery.setParameter(position + 1, paramMap.get(name).getValue());
}
}
Long cnt = ((Number) nativeQuery.getSingleResult()).longValue();
return cnt;
}
通过利用已有的分页类，实现自定义的分页
protected Page<Tuple> readPage(final ListingSearchContext searchContext, TypedQuery<Tuple> query, Pageable pageable) {
query.setFirstResult(pageable.getOffset());
query.setMaxResults(pageable.getPageSize());
Long total = executeCountQuery(searchContext, query);
List<Tuple> content = total > pageable.getOffset() ? query.getResultList() : Collections.<Tuple> emptyList();
return new PageImpl<Tuple>(content, pageable, total);
}
4. 复杂sql的简化。对于一些明显复杂的sql（比如需要经过多次嵌套子查询的sql），建议将该查询简化，简化的方式无非是修改模型，增加适应简化查询的的新方法。
spring data reference http://docs.spring.io/spring-data/data-jpa/docs/current/reference/html/#specifications
jpa api用法汇总 http://www.cnblogs.com/xingqi/p/3929386.html
http://www.objectdb.com/Java/jpa/query/jpql/structure
jpa subquery例子大全 http://www.programcreek.com/java-api-examples/index.PHP?api=javax.persistence.criteria.Subquery
specification例子http://www.programcreek.com/java-api-examples/index.php?api=org.springframework.data.jpa.domain.Specification
spring hql ASTQueryTranslatorFactory例子 http://www.programcreek.com/java-api-examples/index.php?api=org.hibernate.hql.internal.ast.ASTQueryTranslatorFactory
spring Query 例子 http://www.programcreek.com/java-api-examples/org.hibernate.Query
JPA动态类型安全查询 https://www.ibm.com/developerworks/cn/java/j-typesafejpa/
mybatis问题集 http://www.diguage.com/archives/47.html
http://m.oschina.NET/blog/133500
http://www.altuure.com/2010/09/23/jpa-criteria-api-by-samples-%E2%80%93-part-ii/
http://info.michael-simons.eu/2012/09/25/jpa-criteria-query-or-plain-sql/
http://stackoverflow.com/questions/4483576/jpa-2-0-criteria-api-subqueries-in-expressions
http://jdevelopment.nl/counting-rows-returned-jpa-query/