版权声明：本文为博主原创文章，转载请注明出处。 https://blog.csdn.net/liuchuanhong1/article/details/70244261
前言
使用过spring data jpa的同学，都很清楚，对于复杂的sql查询，处理起来还是比较复杂的，而本文中的QueryDSL就是用来简化JPA操作的。
Querydsl定义了一种常用的静态类型语法，用于在持久域模型数据之上进行查询。JDO和JPA是Querydsl的主要集成技术。本文旨在介绍如何使用Querydsl与JPA组合使用。JPA的Querydsl是JPQL和Criteria查询的替代方法。QueryDSL仅仅是一个通用的查询框架，专注于通过Java API构建类型安全的SQL查询。
要想使用QueryDSL，需要做两个前提操作：
1、pom文件中，加入依赖
-
<!--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>
-
<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>
-
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);
-
}
-
-
}
-
<outputDirectory>target/generated-sources/java</outputDirectory>
本文涉及到的Entity如下：
-
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 +
"]";
-
}
-
}
-
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 +
"]";
-
}
-
}
-
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 +
"]";
-
}
-
}
-
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方法…………
-
}
-
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的查询操作接口，如下：
-
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>{
// 继承
QueryDslPredicateExecutor
接口
-
-
}
-
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
测试如下：
-
public User findUserByUserName(final String userName){
-
/**
-
* 该例是使用spring data QueryDSL实现
-
*/
-
QUser quser = QUser.user;
-
Predicate predicate = quser.name.eq(userName);
// 根据用户名，查询user表
-
return repository.findOne(predicate);
-
}
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=?
-
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);
-
}
-
}
-
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给我们提供了更好的方式，见下面的示例：
-
/**
-
* 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();
-
}
一对多示例：
-
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大大的简化了我们的操作
扫一扫
731
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
