Springdata-JPA是对JPA使用的封装,Querydsl-JPA也是基于各种ORM之上的一个通用查询框架,使用它的API类库可以写出“Java代码的sql”,不用去手动接触sql语句,表达含义却如sql般准确。更重要的一点,它能够构建类型安全的查询,这比起JPA使用原生查询时有很大的不同,我们可以不必再对恶心的“Object[]”进行操作了。当然,我们可以SpringDataJPA +Querydsl-JPA联合使用,它们之间有着完美的相互支持,以达到更高效的编码。
gitub地址: github.com/querydsl/qu…
<dependency> <groupId>com.querydsl</groupId> <artifactId>querydsl-jpa</artifactId> </dependency> <dependency> <groupId>com.querydsl</groupId> <artifactId>querydsl-apt</artifactId> <scope>provided</scope> </dependency> 复制代码
<!-- query dsl 构建Q版实体类的插件--> <plugin> <groupId>com.mysema.maven</groupId> <artifactId>apt-maven-plugin</artifactId> <version>1.1.3</version> <executions> <execution> <goals> <goal>process</goal> </goals> <configuration> <outputDirectory>target/generated-sources/java</outputDirectory> <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor> </configuration> </execution> </executions> </plugin> 复制代码
作用:使用QueryDSL的功能时,会依赖使用到JPAQueryFactory,而JPAQueryFactory在这里依赖使用EntityManager,所以在主类中做如下配置,使得Spring自动帮我们注入EntityManager与自动管理JPAQueryFactory
import javax.persistence.EntityManager; import org.springframework.context.annotation.Configuration; import org.springframework.web.servlet.config.annotation.WebMvcConfigurer; import com.querydsl.jpa.impl.JPAQueryFactory; @Configuration public class WebMvcConfig implements WebMvcConfigurer { @Bean public JPAQueryFactory jpaQuery(EntityManager entityManager) { return new JPAQueryFactory(entityManager); } } 复制代码
@Autowired private JPAQueryFactory jpaQueryFactory; 复制代码
import com.fasterxml.jackson.annotation.JsonIgnoreProperties; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import org.hibernate.annotations.GenericGenerator; import javax.persistence.*; import java.math.BigDecimal; import java.sql.Timestamp; @AllArgsConstructor @NoArgsConstructor @Data @Entity @Table(name = "user_tmw") @JsonIgnoreProperties({"hibernateLazyInitializer", "handler"}) public class User { @Id @GeneratedValue(strategy = GenerationType.AUTO, generator = "custom-uuid") @GenericGenerator(name = "custom-uuid", strategy = "com.springboot.demo.bean.CustomUUIDGenerator") @Column(name = "id", nullable = false, length = 32) private String id; @Column(name = "name", length = 10) private String name; @Column(name = "age") private Integer age; @Column(name = "money") private BigDecimal money; @Column(name = "begin_time") private Timestamp beginTime; @Column(name = "end_time") private Timestamp endTime; } 复制代码
QUser user = QUser.user; //单表获取年龄为10的结果集 List<User> ageList = jpaQueryFactory.selectFrom(user).where(user.age.eq(10)).fetch(); String ageListStr = JSON.toJSONString(ageList); System.out.println("单表获取年龄为10的结果集:"+ageListStr); 复制代码
QUser user = QUser.user; //fetchFirst的用法: 单表获取年龄为10的首个结果 User user1 = jpaQueryFactory.selectFrom(user).where(user.age.eq(10)).fetchFirst(); System.out.println("单表获取年龄为10的首个结果:"+user1.toString()); 复制代码
QUser user = QUser.user; //模糊检索的用法:单表获取名称包含小的用户并且按照年龄倒排序 List<User> nameList = jpaQueryFactory.selectFrom(user).where(user.name.like("%小%")).orderBy(user.age.desc()).fetch(); String nameListStr = JSON.toJSONString(nameList); System.out.println("单表获取名称包含小的用户并且按照年龄倒排序的结果集:"+nameListStr); 复制代码
QUser user = QUser.user; //模糊检索的用法:单表获取名称是以小开头的用户并且按照年龄正序排 List<User> nameAscList = jpaQueryFactory.selectFrom(user).where(user.name.startsWith("小")).orderBy(user.age.asc()).fetch(); String nameAscListStr = JSON.toJSONString(nameAscList); System.out.println("单表获取名称是以小开头的用户并且按照年龄正序排的结果集:"+nameAscListStr); 复制代码
QUser user = QUser.user; //between 区间的用法:单表获取开始时间是XX-XX 区间的用户 String time = "2019-07-22 00:00:00"; SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Date date= df.parse(time); Timestamp nousedate = new Timestamp(date.getTime()); String time1 = "2019-07-23 00:00:00"; Date date1= df.parse(time1); Timestamp nousedate1 = new Timestamp(date1.getTime()); List<User> beginTimeList = jpaQueryFactory.selectFrom(user).where(user.beginTime.between(nousedate, nousedate1)).fetch(); String beginTimeListStr = JSON.toJSONString(beginTimeList); System.out.println("单表获取开始时间是XX-XX 区间的用户的结果集:"+beginTimeListStr); 复制代码
QUser user = QUser.user; //in 的用法:单表获取年龄是10,20的用户 List<Integer> ageLists = new ArrayList<>(); ageLists.add(10); ageLists.add(20); List<User> ages = jpaQueryFactory.selectFrom(user).where(user.age.in(ageLists)).fetch(); String agesStr = JSON.toJSONString(ages); System.out.println("单表获取年龄是10,20的用户的结果集:"+agesStr); 复制代码
QUser user = QUser.user; //聚合函数-concat()的使用:单表查询将用户id,名称,年龄拼接的结果 List<String> concatList = jpaQueryFactory.select(user.id.concat(user.name).concat(user.age.stringValue())).from(user).fetch(); String concatListStr = JSON.toJSONString(concatList); System.out.println("单表查询将用户id,名称,年龄拼接的结果:"+concatListStr); 复制代码
QUser user = QUser.user; List<Map<String,Object>> tupleJPAQuery = jpaQueryFactory.select(user.age, user.count().as("count")).from(user).groupBy(user.age) .fetch().stream().map(x->{ Map<String,Object> resultMap = new HashMap<>(); resultMap.put("age",x.get(0,QUser.class)); resultMap.put("count",x.get(1,QUser.class)); return resultMap; }).collect(Collectors.toList()); String userQueryResultsStr = JSON.toJSONString(tupleJPAQuery); System.out.println("单表分组的结果集:"+userQueryResultsStr); 复制代码
QUser user = QUser.user; //多条件处理 BooleanBuilder booleanBuilder = new BooleanBuilder(); booleanBuilder.and(user.age.eq(10)); booleanBuilder.and(user.name.contains("小")); List<User> mostlist = jpaQueryFactory.selectFrom(user).where(booleanBuilder).fetch(); String mostlistStr = JSON.toJSONString(mostlist); System.out.println("单表查询多条件处理的结果:"+mostlistStr); 复制代码
QUser user = QUser.user; QueryResults<User> userQueryResults = jpaQueryFactory.selectFrom(user).offset(0).limit(2).fetchResults(); String userQueryResultsStr = JSON.toJSONString(userQueryResults); System.out.println("单表分页的结果集:"+userQueryResultsStr); 复制代码