Preface
Students who have used spring data jpa are very clear that it is still quite complicated to process complex SQL queries, and the QueryDSL in this article is used to simplify JPA operations.
Querydsl defines a commonly used static type syntax for querying on persistent domain model data. JDO and JPA are the main integration technologies of Querydsl. This article aims to explain how to use Querydsl in combination with JPA. JPA's Querydsl is an alternative to JPQL and Criteria queries. QueryDSL is just a general query framework that focuses on building type-safe SQL queries through the Java API.
To use QueryDSL, two prerequisite operations are required:
1. Add dependencies to the pom file
<!--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>
2. Add the compilation plug-in to the pom file
<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>
The plugin looks for domain types annotated with javax.persistence.Entity and generates corresponding query types for them. The following is the User entity class to illustrate, and the generated query types are as follows:
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); } } After we have created the entity class, then run the mvn clean compli command, and it will be
<outputDirectory>target/generated-sources/java</outputDirectory>
Generate the corresponding query type in the directory. Then copy all the generated classes to the project, just do it.
The Entity involved in this article is as follows:
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; …………Omit getter, setter method…………/** * attention: * Details: Convenient to view test results* @author chhliu */ @Override public String toString() { return "User [id=" + id + ", name=" + name + ", address=" + address + ", age=" + age + "]"; } }The entity class above is mainly used for single table operations.
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; /** * Description: 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; …………Omit getter, setter method………… @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; /** * Description: * @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; …………Omit getter, setter method………… @Override public String toString() { return "IDCard [id=" + id + ", idNo=" + idNo + ", person=" + person + "]"; } }The above two Entity is mainly used for example operations of one-to-one relationships
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; /** * Description: Order entity class* @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; ………Omit getter, setter method…… } 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; /** * Description: OrderItem Entity Class* @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; ………… omit getter, setter method……… } The above two Entity is used to show example operations of a one-to-many relationship.
First, let's look at the single table operation
1. Use spring data jpa
To use the QueryDSL function provided by spring data jpa, it is very simple, just inherit the interface directly. Spring Data JPA provides the QueryDslPredicateExecutor interface, which is used to support QueryDSL query operation interface, as follows:
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>{// Inheritance interface}The QueryDslPredicateExecutor interface provides the following methods:
public interface QueryDslPredicateExecutor<T> { T findOne(Predicate predict); Iterable<T> findAll(Predicate predict, Sort sort); Iterable<T> findAll(Predicate predict, OrderSpecifier<?>... orders); Iterable<T> findAll(OrderSpecifier<?>... orders); Page<T> findAll(Predicate predict, Pageable pageable); long count(Predicate predict); boolean exists(Predicate predicate); } The use of the above method is similar to other interface usage methods in spring data jpa. For details, please refer to: http://www.VeVB.COM/article/137757.htm
The tests are as follows:
public User findUserByUserName(final String userName){ /** * This example is to use spring data QueryDSL to implement */ QUser quser = QUser.user; Predicate predicate = quser.name.eq(userName);// According to the user name, query the user table return repository.findOne(predicate); }The corresponding sql is as follows:
The code copy is as follows: 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=?
The single table operation example code is as follows:
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.PageRequest; import org.springframework.data.domain.Sort; import org.springframework.steretype.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; /** * Description: 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){ /** * This example is to use spring data QueryDSL to implement */ QUser quser = QUser.user; Predicate predict = quser.name.eq(userName); return repository.findOne(predicate); } /** * attention: * Details: Query all records in the user table*/ public List<User> findAll(){ QUser quser = QUser.user; return queryFactory.selectFrom(quser) .fetch(); } /** * Details: Single-condition query*/ public User findOneByUserName(final String userName){ QUser quser = QUser.user; return queryFactory.selectFrom(quser) .where(quser.name.eq(userName)) .fetchOne(); } /** * Details: Single table multi-condition query*/ public User findOneByUserNameAndAddress(final String userName, final String address){ QUser quser = QUser.user; return queryFactory.select(quser) .from(quser) // The above two sentences are equivalent to selectFrom .where(quser.name.eq(userName).and(quser.address.eq(address)))// This code is equivalent to where(quser.name.eq(userName), quser.address.eq(address)) .fetchOne(); } /** * Details: use join query*/ 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: Sort query results */ public List<User> findUserAndOrder(){ QUser quser = QUser.user; return queryFactory.selectFrom(quser) .orderBy(quser.id.desc()) .fetch(); } /** * Details: Group By using */ public List<String> findUserByGroup(){ QUser quser = QUser.user; return queryFactory.select(quser.name) .from(quser) .groupBy(quser.name) .fetch(); } /** * Details: delete the user*/ public long deleteUser(String userName){ QUser quser = QUser.user; return queryFactory.delete(quser).where(quser.name.eq(userName)).execute(); } /** * Details: update the record*/ 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: Use native 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: Pagination query single table*/ 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); } }Multi-table operation examples (one-to-one) are as follows:
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.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: Multi-table dynamic query*/ 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: output query results in DTO*/ public List<PersonIDCardDto> findByDTO(){ Predicate predict = (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: Multi-table dynamic query and pagination*/ 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(); } }In the example above where the query results are output in DTO mode, after the query is finished, the query results are manually converted into DTO objects. This method is actually not very elegant. QueryDSL provides us with a better way, see the following example:
/** * Details: Method 1: Use Bean projection*/ 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: Method 2: Use fields instead of setter */ public List<PersonIDCardDto> findByDTOUseFields(){ Predicate predict = (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: Method 3: Use the constructor method, note that the order of attributes in the constructor must be consistent with the order in the constructor*/ public List<PersonIDCardDto> findByDTOUseConstructor(){ Predicate predict = (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(); } The above only provides several ideas. Of course, you can also use @QueryProjection to implement it, which is very flexible.
One-to-many example:
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: one-to-many, conditional query*/ public List<Tuple> findOrderAndOrderItemByOrderName(String orderName){ //Add query conditions 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()), predict); //Get the result return jpaQuery.fetch(); } /** * Details: Multi-table join query*/ public List<Tuple> findAllByOrderName(String orderName){ //Add query conditions 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); //Get the result return jpaQuery.fetch(); } } From the above example, we can see that QueryDSL has greatly simplified our operations
The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.