QueryDSL的DEMO示例
来源:互联网 发布:华润网络 百度国际大厦 编辑:程序博客网 时间:2024/05/16 01:53
原文:http://blog.csdn.net/liuchuanhong1/article/details/70244261?utm_source=gold_browser_extension(支持原创)
使用过spring data jpa的同学,都很清楚,对于复杂的sql查询,处理起来还是比较复杂的,而本文中的QueryDSL就是用来简化JPA操作的。
Querydsl定义了一种常用的静态类型语法,用于在持久域模型数据之上进行查询。JDO和JPA是Querydsl的主要集成技术。本文旨在介绍如何使用Querydsl与JPA组合使用。JPA的Querydsl是JPQL和Criteria查询的替代方法。QueryDSL仅仅是一个通用的查询框架,专注于通过Java API构建类型安全的SQL查询。
要想使用QueryDSL,需要做两个前提操作:
1、pom文件中,加入依赖
[html] view plain copy
- <!--query dsl -->
- <dependency>
- <groupId>com.querydsl</groupId>
- <artifactId>querydsl-jpa</artifactId>
- </dependency>
- <dependency>
- <groupId>com.querydsl</groupId>
- <artifactId>querydsl-apt</artifactId>
- <scope>provided</scope>
- </dependency>
[html] view plain copy
- <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>
[java] view plain copy
- package com.chhliu.springboot.jpa.entity;
- import static com.querydsl.core.types.PathMetadataFactory.*;
- import com.querydsl.core.types.dsl.*;
- import com.querydsl.core.types.PathMetadata;
- import javax.annotation.Generated;
- import com.querydsl.core.types.Path;
- /**
- * QUser is a Querydsl query type for User
- */
- @Generated("com.querydsl.codegen.EntitySerializer")
- public class QUser extends EntityPathBase<User> {
- private static final long serialVersionUID = 1153899872L;
- public static final QUser user = new QUser("user");
- public final StringPath address = createString("address");
- public final NumberPath<Integer> age = createNumber("age", Integer.class);
- public final NumberPath<Integer> id = createNumber("id", Integer.class);
- public final StringPath name = createString("name");
- public QUser(String variable) {
- super(User.class, forVariable(variable));
- }
- public QUser(Path<? extends User> path) {
- super(path.getType(), path.getMetadata());
- }
- public QUser(PathMetadata metadata) {
- super(User.class, metadata);
- }
- }
[html] view plain copy
- <outputDirectory>target/generated-sources/java</outputDirectory>
本文涉及到的Entity如下:
[java] view plain copy
- package com.chhliu.springboot.jpa.entity;
- import java.io.Serializable;
- import javax.persistence.Entity;
- import javax.persistence.GeneratedValue;
- import javax.persistence.GenerationType;
- import javax.persistence.Id;
- import javax.persistence.Table;
- @Entity
- @Table(name="t_user")
- public class User implements Serializable{
- /**
- *
- */
- private static final long serialVersionUID = 1L;
- @Id()
- @GeneratedValue(strategy = GenerationType.AUTO)
- private int id;
- private String name;
- private String address;
- private int age;
- …………省略getter,setter方法…………
- /**
- * attention:
- * Details:方便查看测试结果
- * @author chhliu
- */
- @Override
- public String toString() {
- return "User [id=" + id + ", name=" + name + ", address=" + address + ", age=" + age + "]";
- }
- }
[java] view plain copy
- package com.chhliu.springboot.jpa.entity;
- import javax.persistence.CascadeType;
- import javax.persistence.Entity;
- import javax.persistence.GeneratedValue;
- import javax.persistence.Id;
- import javax.persistence.OneToOne;
- import javax.persistence.Table;
- /**
- * 描述:TODO
- * @author chhliu
- */
- @Entity
- @Table(name="PERSON")
- public class Person {
- @Id
- @GeneratedValue
- private Integer id;
- private String name;
- private String address;
- @OneToOne(mappedBy="person", cascade={CascadeType.PERSIST, CascadeType.REMOVE, CascadeType.MERGE})
- private IDCard idCard;
- …………省略getter,setter方法…………
- @Override
- public String toString() {
- return "Person [id=" + id + ", name=" + name + ", address=" + address + ", idCard=" + idCard + "]";
- }
- }
[java] view plain copy
- package com.chhliu.springboot.jpa.entity;
- import javax.persistence.CascadeType;
- import javax.persistence.Entity;
- import javax.persistence.FetchType;
- import javax.persistence.GeneratedValue;
- import javax.persistence.Id;
- import javax.persistence.OneToOne;
- import javax.persistence.Table;
- /**
- * 描述:
- * @author chhliu
- */
- @Entity
- @Table(name="IDCARD")
- public class IDCard {
- @Id
- @GeneratedValue
- private Integer id;
- private String idNo;
- @OneToOne(cascade={CascadeType.MERGE, CascadeType.REMOVE, CascadeType.PERSIST}, fetch=FetchType.EAGER)
- private Person person;
- …………省略getter,setter方法…………
- @Override
- public String toString() {
- return "IDCard [id=" + id + ", idNo=" + idNo + ", person=" + person + "]";
- }
- }
[java] view plain copy
- package com.chhliu.springboot.jpa.entity;
- import java.util.List;
- import javax.persistence.CascadeType;
- import javax.persistence.Column;
- import javax.persistence.Entity;
- import javax.persistence.FetchType;
- import javax.persistence.GeneratedValue;
- import javax.persistence.Id;
- import javax.persistence.OneToMany;
- import javax.persistence.Table;
- /**
- * 描述:Order实体类
- * @author chhliu
- */
- @Entity
- @Table(name="ORDER_C")
- public class Order {
- @Id
- @GeneratedValue
- @Column(name="ID")
- private Integer id;
- @Column(length=20, name="ORDER_NAME")
- private String orderName;
- @Column(name="COUNT")
- private Integer count;
- @OneToMany(mappedBy = "order",cascade={CascadeType.PERSIST,CascadeType.REMOVE},fetch = FetchType.EAGER)
- private List<OrderItem> orderItems;
- …………省略getter,setter方法…………
- }
[java] view plain copy
- package com.chhliu.springboot.jpa.entity;
- import javax.persistence.CascadeType;
- import javax.persistence.Column;
- import javax.persistence.Entity;
- import javax.persistence.FetchType;
- import javax.persistence.GeneratedValue;
- import javax.persistence.Id;
- import javax.persistence.JoinColumn;
- import javax.persistence.ManyToOne;
- import javax.persistence.Table;
- /**
- * 描述:OrderItem实体类
- * @author chhliu
- */
- @Entity
- @Table(name="ORDER_ITEM")
- public class OrderItem {
- @Id
- @GeneratedValue
- @Column(name="ID", nullable=false)
- private Integer id;
- @Column(name="ITEM_NAME", length=20)
- private String itemName;
- @Column(name="PRICE")
- private Integer price;
- @ManyToOne(cascade={CascadeType.PERSIST,CascadeType.REMOVE, CascadeType.MERGE}, fetch=FetchType.EAGER)
- @JoinColumn(name = "ORDER_ID")
- private Order order;
- …………省略getter,setter方法…………
- }
首先,我们来看单表操作
1、使用spring data jpa
要想使用spring data jpa提供的QueryDSL功能,很简单,直接继承接口即可。Spring Data JPA中提供了QueryDslPredicateExecutor接口,用于支持QueryDSL的查询操作接口,如下:
[java] view plain copy
- package com.chhliu.springboot.jpa.repository;
- import org.springframework.data.jpa.repository.JpaRepository;
- import org.springframework.data.querydsl.QueryDslPredicateExecutor;
- import com.chhliu.springboot.jpa.entity.User;
- public interface UserRepositoryDls extends JpaRepository<User, Integer>, QueryDslPredicateExecutor<User>{// 继承<span id="result_box" lang="zh-CN"><span><span id="result_box" lang="zh-CN"><span></span></span></span></span>QueryDslPredicateExecutor<span id="result_box" lang="zh-CN"><span><span id="result_box" lang="zh-CN"></span></span></span>接口
- }
[java] view plain copy
- public interface QueryDslPredicateExecutor<T> {
- T findOne(Predicate predicate);
- Iterable<T> findAll(Predicate predicate);
- Iterable<T> findAll(Predicate predicate, Sort sort);
- Iterable<T> findAll(Predicate predicate, OrderSpecifier<?>... orders);
- Iterable<T> findAll(OrderSpecifier<?>... orders);
- Page<T> findAll(Predicate predicate, Pageable pageable);
- long count(Predicate predicate);
- boolean exists(Predicate predicate);
- }
http://blog.csdn.net/liuchuanhong1/article/details/52042477
测试如下:
[java] view plain copy
- public User findUserByUserName(final String userName){
- /**
- * 该例是使用spring data QueryDSL实现
- */
- QUser quser = QUser.user;
- Predicate predicate = quser.name.eq(userName);// 根据用户名,查询user表
- return repository.findOne(predicate);
- }
[java] view plain copy
- select user0_.id as id1_5_, user0_.address as address2_5_, user0_.age as age3_5_, user0_.name as name4_5_ from t_user user0_ where user0_.name=?
[java] view plain copy
- package com.chhliu.springboot.jpa.repository;
- import java.util.List;
- import javax.persistence.EntityManager;
- import javax.persistence.PersistenceContext;
- import javax.persistence.Query;
- import javax.transaction.Transactional;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.data.domain.Page;
- import org.springframework.data.domain.PageRequest;
- import org.springframework.data.domain.Sort;
- import org.springframework.stereotype.Component;
- import com.chhliu.springboot.jpa.entity.QUser;
- import com.chhliu.springboot.jpa.entity.User;
- import com.querydsl.core.types.Predicate;
- import com.querydsl.jpa.impl.JPAQueryFactory;
- /**
- * 描述:QueryDSL JPA
- * @author chhliu
- */
- @Component
- @Transactional
- public class UserRepositoryManagerDsl {
- @Autowired
- private UserRepositoryDls repository;
- @Autowired
- @PersistenceContext
- private EntityManager entityManager;
- private JPAQueryFactory queryFactory;
- @PostConstruct
- public void init() {
- queryFactory = new JPAQueryFactory(entityManager);
- }
- public User findUserByUserName(final String userName){
- /**
- * 该例是使用spring data QueryDSL实现
- */
- QUser quser = QUser.user;
- Predicate predicate = quser.name.eq(userName);
- return repository.findOne(predicate);
- }
- /**
- * attention:
- * Details:查询user表中的所有记录
- */
- public List<User> findAll(){
- QUser quser = QUser.user;
- return queryFactory.selectFrom(quser)
- .fetch();
- }
- /**
- * Details:单条件查询
- */
- public User findOneByUserName(final String userName){
- QUser quser = QUser.user;
- return queryFactory.selectFrom(quser)
- .where(quser.name.eq(userName))
- .fetchOne();
- }
- /**
- * Details:单表多条件查询
- */
- public User findOneByUserNameAndAddress(final String userName, final String address){
- QUser quser = QUser.user;
- return queryFactory.select(quser)
- .from(quser) // 上面两句代码等价与selectFrom
- .where(quser.name.eq(userName).and(quser.address.eq(address)))// 这句代码等同于where(quser.name.eq(userName), quser.address.eq(address))
- .fetchOne();
- }
- /**
- * Details:使用join查询
- */
- public List<User> findUsersByJoin(){
- QUser quser = QUser.user;
- QUser userName = new QUser("name");
- return queryFactory.selectFrom(quser)
- .innerJoin(quser)
- .on(quser.id.intValue().eq(userName.id.intValue()))
- .fetch();
- }
- /**
- * Details:将查询结果排序
- */
- public List<User> findUserAndOrder(){
- QUser quser = QUser.user;
- return queryFactory.selectFrom(quser)
- .orderBy(quser.id.desc())
- .fetch();
- }
- /**
- * Details:Group By使用
- */
- public List<String> findUserByGroup(){
- QUser quser = QUser.user;
- return queryFactory.select(quser.name)
- .from(quser)
- .groupBy(quser.name)
- .fetch();
- }
- /**
- * Details:删除用户
- */
- public long deleteUser(String userName){
- QUser quser = QUser.user;
- return queryFactory.delete(quser).where(quser.name.eq(userName)).execute();
- }
- /**
- * Details:更新记录
- */
- public long updateUser(final User u, final String userName){
- QUser quser = QUser.user;
- return queryFactory.update(quser).where(quser.name.eq(userName))
- .set(quser.name, u.getName())
- .set(quser.age, u.getAge())
- .set(quser.address, u.getAddress())
- .execute();
- }
- /**
- * Details:使用原生Query
- */
- public User findOneUserByOriginalSql(final String userName){
- QUser quser = QUser.user;
- Query query = queryFactory.selectFrom(quser)
- .where(quser.name.eq(userName)).createQuery();
- return (User) query.getSingleResult();
- }
- /**
- * Details:分页查询单表
- */
- public Page<User> findAllAndPager(final int offset, final int pageSize){
- Predicate predicate = QUser.user.id.lt(10);
- Sort sort = new Sort(new Sort.Order(Sort.Direction.DESC, "id"));
- PageRequest pr = new PageRequest(offset, pageSize, sort);
- return repository.findAll(predicate, pr);
- }
- }
[java] view plain copy
- package com.chhliu.springboot.jpa.repository;
- import java.util.ArrayList;
- import java.util.List;
- import javax.annotation.PostConstruct;
- import javax.persistence.EntityManager;
- import javax.persistence.PersistenceContext;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.stereotype.Component;
- import com.chhliu.springboot.jpa.dto.PersonIDCardDto;
- import com.chhliu.springboot.jpa.entity.QIDCard;
- import com.chhliu.springboot.jpa.entity.QPerson;
- import com.querydsl.core.QueryResults;
- import com.querydsl.core.Tuple;
- import com.querydsl.core.types.Predicate;
- import com.querydsl.jpa.impl.JPAQuery;
- import com.querydsl.jpa.impl.JPAQueryFactory;
- @Component
- public class PersonAndIDCardManager {
- @Autowired
- @PersistenceContext
- private EntityManager entityManager;
- private JPAQueryFactory queryFactory;
- @PostConstruct
- public void init() {
- queryFactory = new JPAQueryFactory(entityManager);
- }
- /**
- * Details:多表动态查询
- */
- public List<Tuple> findAllPersonAndIdCard(){
- Predicate predicate = (QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());
- JPAQuery<Tuple> jpaQuery = queryFactory.select(QIDCard.iDCard.idNo, QPerson.person.address, QPerson.person.name)
- .from(QIDCard.iDCard, QPerson.person)
- .where(predicate);
- return jpaQuery.fetch();
- }
- /**
- * Details:将查询结果以DTO的方式输出
- */
- public List<PersonIDCardDto> findByDTO(){
- Predicate predicate = (QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());
- JPAQuery<Tuple> jpaQuery = queryFactory.select(QIDCard.iDCard.idNo, QPerson.person.address, QPerson.person.name)
- .from(QIDCard.iDCard, QPerson.person)
- .where(predicate);
- List<Tuple> tuples = jpaQuery.fetch();
- List<PersonIDCardDto> dtos = new ArrayList<PersonIDCardDto>();
- if(null != tuples && !tuples.isEmpty()){
- for(Tuple tuple:tuples){
- String address = tuple.get(QPerson.person.address);
- String name = tuple.get(QPerson.person.name);
- String idCard = tuple.get(QIDCard.iDCard.idNo);
- PersonIDCardDto dto = new PersonIDCardDto();
- dto.setAddress(address);
- dto.setIdNo(idCard);
- dto.setName(name);
- dtos.add(dto);
- }
- }
- return dtos;
- }
- /**
- * Details:多表动态查询,并分页
- */
- public QueryResults<Tuple> findByDtoAndPager(int offset, int pageSize){
- Predicate predicate = (QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());
- return queryFactory.select(QIDCard.iDCard.idNo, QPerson.person.address, QPerson.person.name)
- .from(QIDCard.iDCard, QPerson.person)
- .where(predicate)
- .offset(offset)
- .limit(pageSize)
- .fetchResults();
- }
- }
上面将查询结果以DTO的方式输出的示例中,在查询结束后,将查询结果手动的转换成了DTO对象,这种方式其实不太优雅,QueryDSL给我们提供了更好的方式,见下面的示例:
[java] view plain copy
- /**
- * Details:方式一:使用Bean投影
- */
- public List<PersonIDCardDto> findByDTOUseBean(){
- Predicate predicate = (QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());
- return queryFactory.select(
- Projections.bean(PersonIDCardDto.class, QIDCard.iDCard.idNo, QPerson.person.address, QPerson.person.name))
- .from(QIDCard.iDCard, QPerson.person)
- .where(predicate)
- .fetch();
- }
- /**
- * Details:方式二:使用fields来代替setter
- */
- public List<PersonIDCardDto> findByDTOUseFields(){
- Predicate predicate = (QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());
- return queryFactory.select(
- Projections.fields(PersonIDCardDto.class, QIDCard.iDCard.idNo, QPerson.person.address, QPerson.person.name))
- .from(QIDCard.iDCard, QPerson.person)
- .where(predicate)
- .fetch();
- }
- /**
- * Details:方式三:使用构造方法,注意构造方法中属性的顺序必须和构造器中的顺序一致
- */
- public List<PersonIDCardDto> findByDTOUseConstructor(){
- Predicate predicate = (QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());
- return queryFactory.select(
- Projections.constructor(PersonIDCardDto.class, QPerson.person.name, QPerson.person.address, QIDCard.iDCard.idNo))
- .from(QIDCard.iDCard, QPerson.person)
- .where(predicate)
- .fetch();
- }
一对多示例:
[java] view plain copy
- package com.chhliu.springboot.jpa.repository;
- import java.util.List;
- import javax.annotation.PostConstruct;
- import javax.persistence.EntityManager;
- import javax.persistence.PersistenceContext;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.stereotype.Component;
- import com.chhliu.springboot.jpa.entity.QOrder;
- import com.chhliu.springboot.jpa.entity.QOrderItem;
- import com.querydsl.core.Tuple;
- import com.querydsl.core.types.Predicate;
- import com.querydsl.jpa.impl.JPAQuery;
- import com.querydsl.jpa.impl.JPAQueryFactory;
- @Component
- public class OrderAndOrderItemManager {
- @Autowired
- @PersistenceContext
- private EntityManager entityManager;
- private JPAQueryFactory queryFactory;
- @PostConstruct
- public void init() {
- queryFactory = new JPAQueryFactory(entityManager);
- }
- /**
- * Details:一对多,条件查询
- */
- public List<Tuple> findOrderAndOrderItemByOrderName(String orderName){
- //添加查询条件
- Predicate predicate = QOrder.order.orderName.eq(orderName);
- JPAQuery<Tuple> jpaQuery = queryFactory.select(QOrder.order, QOrderItem.orderItem)
- .from(QOrder.order, QOrderItem.orderItem)
- .where(QOrderItem.orderItem.order.id.intValue().eq(QOrder.order.id.intValue()), predicate);
- //拿到结果
- return jpaQuery.fetch();
- }
- /**
- * Details:多表连接查询
- */
- public List<Tuple> findAllByOrderName(String orderName){
- //添加查询条件
- Predicate predicate = QOrder.order.orderName.eq(orderName);
- JPAQuery<Tuple> jpaQuery = queryFactory.select(QOrder.order, QOrderItem.orderItem)
- .from(QOrder.order, QOrderItem.orderItem)
- .rightJoin(QOrder.order)
- .on(QOrderItem.orderItem.order.id.intValue().eq(QOrder.order.id.intValue()));
- jpaQuery.where(predicate);
- //拿到结果
- return jpaQuery.fetch();
- }
- }
从上面的示例中,我们可以看出,QueryDSL大大的简化了我们的操作
阅读全文
0 0
- QueryDSL的DEMO示例
- QueryDSL JPA 查询示例
- Querydsl
- JPA与QueryDsl的学习
- Shiro的Demo示例
- QueryDSL 简单的查询语句应用
- Spring Data JPA的Specifications和Querydsl
- Hibernate的开发demo示例
- Demo示例:Flash读取数据库的值
- Java zTree 的异步加载demo示例
- Java zTree 的异步加载demo示例
- Java zTree 的异步加载demo示例
- 使用Simple-Spring-Memcached的demo示例
- Listview的多选demo示例代码
- QueryDSL介绍
- Querydsl使用
- QueryDSL介绍
- struts2的demo中show case的crud示例
- 2017-10-15离线赛总结
- 洛谷 U3357 C2-走楼梯
- 1821: [JSOI2010]Group 部落划分 Group
- 洛谷 P3797 妖梦斩木棒
- 1093: [ZJOI2007]最大半连通子图
- QueryDSL的DEMO示例
- 1266: [AHOI2006]上学路线route
- 栈和 队列 和 优先队列的 简单操作(初学者)
- [USACO09DEC]牛收费路径Cow Toll Paths
- HTTP请求行、请求头、请求体详解
- 洛谷 P1262 间谍网络
- 实验四:用可重用的链表模块来实现命令行菜单小程序V2.5
- [USACO08OCT]牧场散步Pasture Walking
- P1457 城堡 The Castle