【了然于胸】Spring Data JPA使用Specification动态创设多表查询、复杂查询及排序示例

2. 条件陈设

pom文件引进正视(对此尚不掌握的请自行补充maven相关文化)

 <!-- jpa --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency>

Dao层文件三番两次JpaSpecificationExecutor。那边多再而三了贰个JpaRepository,不必要的能够自行删除。

@Repositorypublic interface IStudentRepository extends JpaRepository<Student, String>,JpaSpecificationExecutor<Student>{}

加上那几个插件是为了让程序自动生成query type(查询实体,命名格局为:"Q"+对应实体名)。上文引进的依据中querydsl-apt正是为此插件服务的。

厂商的体系中相当的大片段属于内部平台,所以对质量的渴求未有那么高,开辟速度反而更首要,由此在搭建基础框架时精选使用JPA,没有使用mybitis,当然个中也会有一部分缘由是在此以前平素采纳hibernate,对mybitis不太熟稔_。

1. 用法

@Servicepublic class StudentService implements IStudentService { @Autowired private IStudentRepository repository; //无关代码略 @Override public List<Student> getStudent(String studentNumber,String name ,String nickName, Date birthday,String courseName,float chineseScore,float mathScore, float englishScore,float performancePoints) { Specification<Student> specification = new Specification<Student>(){ @Override public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> query, CriteriaBuilder cb) { //用于暂时存放查询条件的集合 List<Predicate> predicatesList = new ArrayList<>(); //-------------------------------------------- //查询条件示例 //equal示例 if (!StringUtils.isEmpty{ Predicate namePredicate = cb.equal(root.get, name); predicatesList.add(namePredicate); } //like示例 if (!StringUtils.isEmpty){ Predicate nickNamePredicate = cb.like(root.get("nickName"), '%'+nickName+'%'); predicatesList.add(nickNamePredicate); } //between示例 if (birthday != null) { Predicate birthdayPredicate = cb.between(root.get("birthday"), birthday, new Date; predicatesList.add(birthdayPredicate); } //关联表查询示例 if (!StringUtils.isEmpty(courseName)) { Join<Student,Teacher> joinTeacher = root.join("teachers",JoinType.LEFT); Predicate coursePredicate = cb.equal(joinTeacher.get("courseName"), courseName); predicatesList.add(coursePredicate); } //复杂条件组合示例 if (chineseScore!=0 && mathScore!=0 && englishScore!=0 && performancePoints!=0) { Join<Student,Examination> joinExam = root.join("exams",JoinType.LEFT); Predicate predicateExamChinese = cb.ge(joinExam.get("chineseScore"),chineseScore); Predicate predicateExamMath = cb.ge(joinExam.get("mathScore"),mathScore); Predicate predicateExamEnglish = cb.ge(joinExam.get("englishScore"),englishScore); Predicate predicateExamPerformance = cb.ge(joinExam.get("performancePoints"),performancePoints); //组合 Predicate predicateExam = cb.or(predicateExamChinese,predicateExamEnglish,predicateExamMath); Predicate predicateExamAll = cb.and(predicateExamPerformance,predicateExam); predicatesList.add(predicateExamAll); } //-------------------------------------------- //排序示例(先根据学号排序,后根据姓名排序) query.orderBy(cb.asc(root.get("studentNumber")),cb.asc(root.get; //-------------------------------------------- //最终将查询条件拼好然后return Predicate[] predicates = new Predicate[predicatesList.size()]; return cb.and(predicatesList.toArray(predicates)); } }; return repository.findAll(specification); }}

装配

小结

  1. 配备JPA很简短,加多maven信任,配置数据库连接新闻,dao承接上层类就能够在service内注入dao,进行crud等操作
  2. JPA提供了便捷的依照章程名称举行询问的艺术,使用难度异常低
  3. JPA通过@Query申明,帮助类HQL语句询问;也足以使用原生SQL查询,只需求将nativeQuery属性设置为true就能够
  4. 免费分页查询可由此自带的findAll方法就能够
  5. 多规格分页查询,有二种达成情势,当每种条件都以必选时,可应用@query带分页条件来兑现;当有可选条件时,须求使用Specification来贯彻
  6. 为了简化常见的多个可选条件分页查询的代码,在service层提供了二个上层方法,以map的议程设置查询条件,一大半情形下无需程序猿再关怀Specification的语法,降低利用难度

本人搭建好的spring boot web后端开拓框架已上传至GitHub,招待作弄!

正文阅读时间长度5分钟。由小编三汪头阵于简书。

1.1 更新/删除

Update

QMemberDomain qm = QMemberDomain.memberDomain;queryFactory.update.set(qm.status, "0012").where(qm.status.eq.execute();

Delete

QMemberDomain qm = QMemberDomain.memberDomain;queryFactory.delete.where(qm.status.eq.execute();

一、配置JPA

1、添加maven依赖

<!-- jpa --><dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId></dependency><dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId></dependency>

2、 增加数据库配置

spring.jpa.database = MYSQL# Hibernate ddl auto (create, create-drop, update)spring.jpa.hibernate.ddl-auto = update# Naming strategyspring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy# stripped before adding them to the entity manager)spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect# Show or not log for each sql queryspring.jpa.show-sql = truespring.datasource.url=jdbc:mysql://localhost:3306/base?characterEncoding=utf8spring.datasource.username=rootspring.datasource.password=rootspring.datasource.driverClassName=com.mysql.jdbc.Driverspring.datasource.max-active=3spring.datasource.max-idle=1spring.datasource.min-idle=1spring.datasource.initial-size=1

3、dao传承上层类

public interface UserDao extends BaseDao<User, Integer>{ User findByUsernameAndDel(String username, int del);}@NoRepositoryBeanpublic interface BaseDao<T,ID extends Serializable> extends JpaSpecificationExecutor<T>,JpaRepository<T, ID>{}

为了方便使用,提取了一个BaseDao,要求小心的是要在上层类上加多@ NoRepositoryBean申明。BaseDao承袭JpaRepository和JpaSpecificationExecutor,JpaRepository提供了大旨的crud等查询格局,JpaSpecificationExecutor提供了对复杂查询的支撑。

完了了上述三步,已经得以在service内注入dao,通过dao进行数据库curd等操作。

4. 引进阅读

二、JPA查询

//根据用户名和标记删除字段查询对应的用户信息User findByUsernameAndDel(String username, int del);//根据code查询对应的角色Role findByCode(String code);//根据id集合查询对应的角色集合Set<Role> findByIdIn(Set<Integer> roleIds);//根据用户id,查询用户角色关系记录List<UserRole> findByUserId;

简短询问能够因此上述办法有利的达成,简化了数不完dao层的代码,使用着照旧很爽的,具体法则相比较简单,基本上正是findBy开端,后续跟上实体性质,中间配以And、Or、In、like等结合方式名,也便是用艺术名来陈诉查询法规。假若是嵌套对象,能够经过“_"来区分子对象的习性,比方findByCompany_name(String name)正是以子对象company内的name属性为查询条件。常用查询关键字如下:

类HQL语句

@Query("select u from User u where u.username = ?1") public AccountInfo findByAccountId(String username); 

在@Query内向来书写HQL语句就能够,参数可透过"?1,?2"那样的主意设置,下标从1起来。

原生sql查询

@Query(value="select perm.* from role_permission rp left join permission perm on rp.permission_id=perm.id where rp.role_id in;",nativeQuery=true)List<Permission> listByRoleIds(Set<Integer> roles);//根据userId删除用户角色关系@Query(value = "delete from user_role where user_id=?1 ", nativeQuery = true) @Modifyingvoid deleleByUserId;

使用也比较轻松,将@Query内的nativeQuery设置为true就能够。写SQL语句时,可未来天本地mysql顾客端上测量试验号SQL语句的不利,当供给索引时,创立合适的目录。在此基础上看下SQL的性情,如不理想,需调整SQL,可使用explain对SQL语句举办深入分析,查询执行逻辑,针对性优化。

新增、修改 直接调用dao的save方法,援助单个保存和批量封存。

删除 直接调用dao的delete方法,帮忙根据id、对象、对象集结等删除形式,使用时翻看下提醒方法就能够了。

不带条件分页查询

Pageable pageable = new PageRequest(0, 10, new Sort(Direction.DESC, "updateTime"));Page<User> page = userDao.findAll;

多规格复杂分页查询 带条件分页查询有三种艺术:

  1. 行使原生SQL实行分页查询,可是前提是多个查询条件必得同一时候设有,不可能有不奏效的规格,比方顾客列表,顾客姓名能够不作为过滤条件,这种景况原生SQL就不适用了,供给使用上边第二种艺术
  2. 动用Specification实行理并答复杂查询,示例代码如下:
public Page<User> listByPage(final Map<String, String> params,Pageable pageable){ Specification<User> spec = new Specification<User>() { @Override public Predicate toPredicate(Root<User> root,CriteriaQuery<?> query,CriteriaBuilder cb) { List<Predicate> list = new ArrayList<Predicate>(); String type = params.get; String status= params.get; String username = params.get("username"); String name = params.get; if(StringUtils.isNotBlank{ list.add(cb.equal(root.get.as(Integer.class), NumberUtils.toInt; } if(StringUtils.isNotBlank{ list.add(cb.equal(root.get.as(Integer.class), NumberUtils.toInt; } if(StringUtils.isNotBlank){ list.add(cb.like(root.get("username").as(String.class), String.format("%%%s%%", username))); } if(StringUtils.isNotBlank{ list.add(cb.like(root.get.as(String.class), String.format("%%%s%%", name))); } list.add(cb.equal(root.get, Constants.DEL_NO)); Predicate[] p = new Predicate[list.size()]; return cb.and(list.toArray; //in条件查询 /*List<Integer> ids = Lists.newArrayList(); ids.add; ids.add; In<Integer> in = cb.in(root.get.as(Integer.class)); in.value; in.value; return cb.or;*/ } }; Page<User> page = userDao.findAll(spec, pageable);

据他们说上述示例,基本满意了常用的查询供给,更加的多境况可依靠规则尝试一下就可以,也可百度查寻下JPA Specification,有成百上千学科。

简化多条件分页查询 使用Specification需求每一遍都写一大段模板代码,使用起来照旧相比较麻烦,使用入门也可以有个别难度,基于此,在service层的公物代码出对查询举办了一些卷入,简化常见多规格分页查询。

/** * 分页多条件查询 * 注:多个条件间是and关系 & 参数是属性对应的类型 * @author yangwk * @time 2017年8月1日 下午3:50:46 * @param params {"username:like":"test"} 键的格式为字段名:过滤方式,过滤方式见{@code QueryTypeEnum} * @param pageable 分页信息 new PageRequest(page, size,new Sort(Direction.DESC, "updateTime")) * @return */Page<T> list(Map<String, Object> params,Pageable pageable);@Overridepublic Page<T> list(final Map<String, Object> params,Pageable pageable){ Specification<T> spec = new Specification<T>() { @Override public Predicate toPredicate(Root<T> root,CriteriaQuery<?> query,CriteriaBuilder cb) { List<Predicate> list = new ArrayList<Predicate>(); for(Entry<String, Object> entry : params.entrySet{ Object value = entry.getValue(); if(value == null || StringUtils.isBlank(value.toString{ continue; } String key = entry.getKey(); String[] arr = key.split; Predicate predicate = getPredicate(arr,value,root,cb); list.add(predicate); } Predicate[] p = new Predicate[list.size()]; return cb.and(list.toArray; } }; Page<T> page = getDAO().findAll(spec, pageable); return page;}private Predicate getPredicate(String[] arr, Object value, Root<T> root, CriteriaBuilder cb) { if(arr.length == 1){ return cb.equal(root.get.as(value.getClass, value); } if(QueryTypeEnum.like.name().equals{ return cb.like(root.get.as(String.class), String.format("%%%s%%", value)); } if(QueryTypeEnum.ne.name().equals{ return cb.notEqual(root.get.as(value.getClass, value); } if(QueryTypeEnum.lt.name().equals{ return getLessThanPredicate(arr,value,root,cb); } if(QueryTypeEnum.lte.name().equals{ return getLessThanOrEqualToPredicate(arr,value,root,cb); } if(QueryTypeEnum.gt.name().equals{ return getGreaterThanPredicate(arr,value,root,cb); } if(QueryTypeEnum.gte.name().equals{ return getGreaterThanOrEqualToPredicate(arr,value,root,cb); } throw new UnsupportedOperationException(String.format("不支持的查询类型[%s]",arr[1]));}private Predicate getLessThanPredicate(String[] arr, Object value, Root<T> root, CriteriaBuilder cb) { if(Integer.class == value.getClass{ return cb.lessThan(root.get.as(Integer.class), value); } if(Long.class == value.getClass{ return cb.lessThan(root.get.as(Long.class), value); } if(Double.class == value.getClass{ return cb.lessThan(root.get.as(Double.class), value); } if(Float.class == value.getClass{ return cb.lessThan(root.get.as(Float.class), value); } if(Timestamp.class == value.getClass{ return cb.lessThan(root.get.as(Timestamp.class), (Timestamp)value); } if(Date.class == value.getClass{ return cb.lessThan(root.get.as(Date.class), value); } return cb.lessThan(root.get.as(String.class), String.valueOf;}private Predicate getLessThanOrEqualToPredicate(String[] arr, Object value, Root<T> root, CriteriaBuilder cb) { if(Integer.class == value.getClass{ return cb.lessThanOrEqualTo(root.get.as(Integer.class), value); } if(Long.class == value.getClass{ return cb.lessThanOrEqualTo(root.get.as(Long.class), value); } if(Double.class == value.getClass{ return cb.lessThanOrEqualTo(root.get.as(Double.class), value); } if(Float.class == value.getClass{ return cb.lessThanOrEqualTo(root.get.as(Float.class), value); } if(Timestamp.class == value.getClass{ return cb.lessThanOrEqualTo(root.get.as(Timestamp.class), (Timestamp)value); } if(Date.class == value.getClass{ return cb.lessThanOrEqualTo(root.get.as(Date.class), value); } return cb.lessThanOrEqualTo(root.get.as(String.class), String.valueOf;}private Predicate getGreaterThanPredicate(String[] arr, Object value, Root<T> root, CriteriaBuilder cb) { if(Integer.class == value.getClass{ return cb.greaterThan(root.get.as(Integer.class), value); } if(Long.class == value.getClass{ return cb.greaterThan(root.get.as(Long.class), value); } if(Double.class == value.getClass{ return cb.greaterThan(root.get.as(Double.class), value); } if(Float.class == value.getClass{ return cb.greaterThan(root.get.as(Float.class), value); } if(Timestamp.class == value.getClass{ return cb.greaterThan(root.get.as(Timestamp.class), (Timestamp)value); } if(Date.class == value.getClass{ return cb.greaterThan(root.get.as(Date.class), value); } return cb.greaterThan(root.get.as(String.class), String.valueOf;}private Predicate getGreaterThanOrEqualToPredicate(String[] arr,Object value, Root<T> root, CriteriaBuilder cb) { if(Integer.class == value.getClass{ return cb.greaterThanOrEqualTo(root.get.as(Integer.class), value); } if(Long.class == value.getClass{ return cb.greaterThanOrEqualTo(root.get.as(Long.class), value); } if(Double.class == value.getClass{ return cb.greaterThanOrEqualTo(root.get.as(Double.class), value); } if(Float.class == value.getClass{ return cb.greaterThanOrEqualTo(root.get.as(Float.class), value); } if(Timestamp.class == value.getClass{ return cb.greaterThanOrEqualTo(root.get.as(Timestamp.class), (Timestamp)value); } if(Date.class == value.getClass{ return cb.greaterThanOrEqualTo(root.get.as(Date.class), value); } return cb.lessThanOrEqualTo(root.get.as(String.class), String.valueOf;} @ApiModel(value="查询条件支持的过滤方式")public enum QueryTypeEnum { like, equal, ne, lt, lte, gt, gte}//使用示例Map<String, Object> params = Maps.newHashMap();params.put("type", type);params.put("status", status);params.put("username:like", username);params.put("name:like", name);Page<User> rs = this.userService.list(params, new PageRequest(page, size, new Sort(Direction.DESC, "updateTime")));

提前公共list方法,查询条件在map内设置,查询条件在key内安装,那样大部分的询问诉求就足以不再关注Specification的语法,不用写那一大段的复杂代码了

(先贴用法,再贴遭受安顿。有不明了的地方迎接留言研讨。)

正文由小编三汪首发于简书。德姆o已上传github

发这篇的指标是为了提供一篇包涵Specification各类写法的备忘。Specification算是JPA中相比较灵活的询问办法了,也少不了Criteria类型安全和面向对象的亮点。以前都以点到即止的大约利用,刚好近些日子系统地使用了须臾间,遂有本文。

履新日志

3. 二个第三方库

假设您认为每一回达成接口重写toPredicate太难为了,也足以利用那几个第三方库:jpa-spec,能够裁减一些代码量。

演示(更加多示例请移步该库github查看)

public Page<Person> findAll(SearchRequest request) { Specification<Person> specification = Specifications.<Person>and() .eq(StringUtils.isNotBlank(request.getName, "name", request.getName .gt(Objects.nonNull(request.getAge, "age", 18) .between("birthday", new Range<>(new Date(), new Date .like("nickName", "%og%", "%me") .build(); return personRepository.findAll(specification, new PageRequest;}

maven依赖

<dependency> <groupId>com.github.wenhao</groupId> <artifactId>jpa-spec</artifactId> <version>3.1.1</version></dependency>
 @Bean @Autowired public JPAQueryFactory jpaQuery(EntityManager entityManager) { return new JPAQueryFactory(entityManager); }

5. 恢宏阅读

上述。希望本人的稿子对你能具有帮忙。小编不能够保证文中全部说法的百分之百没错,但本人能确定保证它们都以自家的掌握和醒来以及拒绝复制黏贴。有啥样思想、见解或吸引,款待留言商讨。

1.2 查询

询问大约能够玩出花来。

QMemberDomain qm = QMemberDomain.memberDomain;//查询字段-select()List<String> nameList = queryFactory.select.from.fetch();//查询实体-selectFrom()List<MemberDomain> memberList = queryFactory.selectFrom.fetch();//查询并将结果封装至dto中List<MemberFavoriteDto> dtoList = queryFactory.select(Projections.constructor(MemberFavoriteDto.class,qm.name,qf.favoriteStoreCode)).from.leftJoin(qm.favoriteInfoDomains,qf).fetch();//去重查询-selectDistinct()List<String> distinctNameList = queryFactory.selectDistinct.from.fetch();//获取首个查询结果-fetchFirst()MemberDomain firstMember = queryFactory.selectFrom.fetchFirst();//获取唯一查询结果-fetchOne()//当fetchOne()根据查询条件从数据库中查询到多条匹配数据时,会抛`NonUniqueResultException`。MemberDomain anotherFirstMember = queryFactory.selectFrom.fetchOne();

 //查询条件示例 List<MemberDomain> memberConditionList = queryFactory.selectFrom //like示例 .where(qm.name.like('%'+"Jack"+'%') //contain示例 .and(qm.address.contains //equal示例 .and(qm.status.eq //between .and(qm.age.between .fetch();

若是你以为上面的写法相当的矮贵,大家得以应用QueryDSL提供的BooleanBuilder来扩充查询条件管理。如下

BooleanBuilder builder = new BooleanBuilder();//likebuilder.and(qm.name.like('%'+"Jack"+'%'));//containbuilder.and(qm.address.contains;//equal示例builder.and(qm.status.eq;//betweenbuilder.and(qm.age.between;List<MemberDomain> memberConditionList = queryFactory.selectFrom.where.fetch();

使用BooleanBuilder,更复杂的查询关系也不怕。举个例子

BooleanBuilder builder = new BooleanBuilder();builder.and(qm.address.contains;BooleanBuilder builder2 = new BooleanBuilder();builder2.or(qm.status.eq;builder2.or(qm.status.eq;builder.and;List<MemberDomain> memberComplexConditionList = queryFactory.selectFrom.where.fetch();

//以左关联为例-left joinQMemberDomain qm = QMemberDomain.memberDomain;QFavoriteInfoDomain qf= QFavoriteInfoDomain.favoriteInfoDomain;List<MemberDomain> leftJoinList = queryFactory.selectFrom.leftJoin(qm.favoriteInfoDomains,qf).where(qf.favoriteStoreCode.eq.fetch();

//聚合函数-avg()Double averageAge = queryFactory.select(qm.age.avg.from.fetchOne();//聚合函数-concat()String concat = queryFactory.select(qm.name.concat(qm.address)).from.fetchOne();//聚合函数-date_format()String date = queryFactory.select(Expressions.stringTemplate("DATE_FORMAT({0},'%Y-%m-%d')", qm.registerDate)).from.fetchOne();

当用到DATE_FORMAT这类QueryDSL仿佛从未提供支持的Mysql函数时,我们得以手动拼三个String表明式。那样就能够无缝使用Mysql中的函数了。

上边包车型的士用法中子查询未有怎么实际意义,只是充当二个写法示例。

//子查询List<MemberDomain> subList = queryFactory.selectFrom.where(qm.status.in(JPAExpressions.select(qm.status).from.fetch();

//排序List<MemberDomain> orderList = queryFactory.selectFrom.orderBy(qm.name.asc.fetch();

 QMemberDomain qm = QMemberDomain.memberDomain; //写法一 JPAQuery<MemberDomain> query = queryFactory.selectFrom.orderBy(qm.age.asc; long total = query.fetchCount();//hfetchCount的时候上面的orderBy不会被执行 List<MemberDomain> list0= query.offset.limit.fetch(); //写法二 QueryResults<MemberDomain> results = queryFactory.selectFrom.orderBy(qm.age.asc.offset.limit.fetchResults(); List<MemberDomain> list = results.getResults(); logger.debug("total:"+results.getTotal; logger.debug("limit:"+results.getLimit; logger.debug("offset:"+results.getOffset;

写法一和二都会发生两条sql进行查询,一条查询count,一条查询具体数目。写法二的getTotal()等价于写法一的fetchCount。无论是哪个种类写法,在询问count的时候,orderBy、limit、offset那四个都不会被实施。能够大胆采取。

其实Template我们在1.2.4 使用Mysql聚合函数中早就采取过了。QueryDSL并未有对Mysql的有着函数提供援助,辛亏它给大家提供了Template性子。我们得以选择Template来落成各类QueryDSL未直接扶助的语法。示举例下。

 QMemberDomain qm = QMemberDomain.memberDomain; //使用booleanTemplate充当where子句或where子句的一部分 List<MemberDomain> list = queryFactory.selectFrom.where(Expressions.booleanTemplate("{} = \"tofu\"", qm.name)).fetch(); //上面的写法,当booleanTemplate中需要用到多个占位时 List<MemberDomain> list1 = queryFactory.selectFrom.where(Expressions.booleanTemplate("{0} = \"tofu\" and {1} = \"Amoy\"", qm.name,qm.address)).fetch(); //使用stringTemplate充当查询语句的某一部分 String date = queryFactory.select(Expressions.stringTemplate("DATE_FORMAT({0},'%Y-%m-%d')", qm.registerDate)).from.fetchFirst(); //在where子句中使用stringTemplate String id = queryFactory.select.from.where(Expressions.stringTemplate("DATE_FORMAT({0},'%Y-%m-%d')", qm.registerDate).eq("2018-03-19")).fetchFirst();

唯独Template好用归好用,但也许有其局限性。比如当大家须求接纳复杂的正则表明式相配的时候,就有个别衣不蔽体了。那是出于Template中接纳了{}来作为占位符,而正则表明式中也只怕行使了{},由此会时有发生争执。

小编们常常使用Repository来再三再四QueryDslPredicateExecutor<T>接口。通过注入Repository来利用。

继承

@Repositorypublic interface IMemberDomainRepository extends JpaRepository<MemberDomain,String>,QueryDslPredicateExecutor<MemberDomain> {}

注入

@AutowiredIMemberDomainRepository memberRepo;