有时我们在查询某个实体的时候,给定的条件是不固定的,这是我们就需要动态构建相应的查询语句,在JPA2.0中我们可以通过Criteria接口查询,JPA criteria查询.相比JPQL,其优势是类型安全,更加的面向对象.而在Spring data JPA中相应的接口是JpaSpecificationExecutor,这个接口基本是围绕着Specification接口来定义的。Specification接口中只定义了如下一个方法:
- Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb);
我们只需要重写这个方法即可,相关知识请自行查阅JPA Criteria查询
过滤条件
1:过滤条件会被应用到SQL语句的FROM子句中。在criteria 查询中,查询条件通过Predicate或Expression实例应用到CriteriaQuery对象上。
2:这些条件使用 CriteriaQuery .where 方法应用到CriteriaQuery 对象上
3:CriteriaBuilder也作为Predicate实例的工厂,通过调用CriteriaBuilder 的条件方法( equal,notEqual, gt, ge,lt, le,between,like等)创建Predicate对象。
4:复合的Predicate 语句可以使用CriteriaBuilder的and, or andnot 方法构建。
相关代码如下,在这个例子中我们定义了2个类Articel和User类
Article:
- @Entity
- @Table(name = "t_article")
- public class Article implements Serializable{
- private static final long serialVersionUID = 6112067846581696118L;
- @Id
- @GeneratedValue
- private Integer aid;
- private String title;
- @Temporal(TemporalType.TIMESTAMP)
- private Date postTime;
- @Temporal(TemporalType.TIMESTAMP)
- private Date lastEditTime;
- private String ip;
- private String tag;
- private boolean forbidComment;//禁止评论
- @ManyToOne
- @JoinColumn(name = "uid")
- private User user;
- private boolean recommend;//是否是推荐
- @Temporal(TemporalType.TIMESTAMP)
- private Date recommendTime;//推荐时间
- //setter/getter略
- }
User:
- @Entity
- @Table(name = "t_user")
- public class User implements Serializable {
- private static final long serialVersionUID = 3703405133265901053L;
- @Id
- @GeneratedValue
- private Integer uid;
- private String nickname;
- private String password;
- //setter/getter略
- }
其中user和article是一对多的关系,是单向的
封装的查询实体SearchArticle
- public class SearchArticle implements Serializable{
- private static final long serialVersionUID = -1082122462716689486L;
- private int page = 1;
- private int limit;
- @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME,pattern = "yyyy-MM-dd HH:mm:ss")
- private Date postTimeStart;
- @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME,pattern = "yyyy-MM-dd HH:mm:ss")
- private Date postTimeEnd;
- @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME,pattern = "yyyy-MM-dd HH:mm:ss")
- private Date recTimeStart;
- @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME,pattern = "yyyy-MM-dd HH:mm:ss")
- private Date recTimeEnd;
- private String nickname;
- }
下面是查询方法
- @Autowired
- private ArticleRepository articleRepository;
- @Override
- public QueryResult<ArticleModel> findArticle(SearchArticle searchArticle) {
- Sort sort = new Sort(Sort.Direction.DESC,"postTime");
- Specification<Article> specification = getWhereClause(searchArticle);
- Page<Article> all = articleRepository.findAll(specification, new PageRequest(searchArticle.getPage() - 1, searchArticle.getLimit(),sort));
- QueryResult<ArticleModel> result = new QueryResult<>();
- List<ArticleModel> list = new ArrayList<>(searchArticle.getLimit());
- for (Article article:all.getContent()){
- ArticleModel model = new ArticleModel(article.getAid(),article.getTitle(),article.getPostTime(),article.isRecommend(),
- article.getRecommendTime(),article.getIp(),article.getUser().getUid(),article.getUser().getNickname());
- list.add(model);
- }
- result.setRows(list);
- result.setTotal(all.getTotalElements());
- return result;
- }
- /**
- * 动态生成where语句
- * @param searchArticle
- * @return
- */
- private Specification<Article> getWhereClause(final SearchArticle searchArticle){
- return new Specification<Article>() {
- @Override
- public Predicate toPredicate(Root<Article> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
- List<Predicate> predicate = new ArrayList<>();
- if(searchArticle.getPostTimeStart()!=null){
- predicate.add(cb.greaterThanOrEqualTo(root.get("postTime").as(Date.class), searchArticle.getPostTimeStart()));
- }
- if(searchArticle.getPostTimeEnd()!=null){
- predicate.add(cb.lessThanOrEqualTo(root.get("postTime").as(Date.class), searchArticle.getPostTimeEnd()));
- }
- if(searchArticle.getRecTimeStart()!=null){
- predicate.add(cb.greaterThanOrEqualTo(root.get("recommendTime").as(Date.class), searchArticle.getRecTimeStart()));
- }
- if (searchArticle.getRecTimeEnd()!=null){
- predicate.add(cb.lessThanOrEqualTo(root.get("recommendTime").as(Date.class), searchArticle.getRecTimeEnd()));
- }
- if (StringUtils.isNotBlank(searchArticle.getNickname())){
- //两张表关联查询
- Join<Article,User> userJoin = root.join(root.getModel().getSingularAttribute("user",User.class),JoinType.LEFT);
- predicate.add(cb.like(userJoin.get("nickname").as(String.class), "%" + searchArticle.getNickname() + "%"));
- }
- Predicate[] pre = new Predicate[predicate.size()];
- return query.where(predicate.toArray(pre)).getRestriction();
- }
- };
- }
其中的 ArticleRepository接口如下,spring data jpa不需要你自己实现dao的接口
- public interface ArticleRepository extends JpaRepository<Article,Integer>,JpaSpecificationExecutor<Article> {}
通过以上的步骤,我们就能构建相应的查询sql了,使用这个接口要对JPA2.0中Criteria查询有一定的了解
文章来源 java分享网 http://www.itjavaer.com/article/35