Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to use JPA+querydsl to achieve multi-conditional dynamic query. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

JPA querydsl multi-condition dynamic query

It is believed that many people will use multi-condition retrieval when doing order management, such as querying the completed orders of shops a whose order status is paid and the amount is between 100 and 200.

There are many ways to implement, the core is an if and null. After learning querydsl today, let's review it in detail.

First of all, I do the effect picture, we mainly look at how to achieve the query.

Introduce querydsl.

First of all, QueryDSL is simply a general query framework that focuses on building type-safe SQL queries through Java API.

Secondly, Querydsl can build query statements suitable for different types of ORM frameworks or SQL for users through a set of general query API, that is to say, QueryDSL is a general query framework based on various ORM frameworks and SQL.

With QueryDSL, you can then build queries in a general API way on any supported ORM framework or SQL platform. The platforms currently supported by QueryDSL include JPA,JDO,SQL,Java Collections,RDF,Lucene,Hibernate Search.

Start development, starting with pom dependencies

Here to add two querydsl dependencies, jpa and apt, the version is the same

Com.querydsl querydsl-jpa 4.2.1 com.querydsl querydsl-apt 4.2.1

Then you need to configure a plug-in to generate the Q version of the entity class. Only the Q version of the entity class can participate in the querydsl query.

Com.querydsl querydsl-maven-plugin generate-sources Jpa-export target/ Generated-sources/java com.jerry.gamemarket.entity target/generated-sources/java

This is the target folder where the Q version entity is generated

Com.jerry.gamemarket.entity

This is to generate the Q version of the entities under those packages.

After executing the mvn compile, you can see that the Q version of the entity class is generated.

We need to instantiate the EntityManager object and the JPAQueryFactory object before writing the specific query method, and de-instantiate the JPAQueryFactory object when instantiating the controller, so we introduce a Bean called JPAQueryFactory plus an EntityManager parameter in the startup class, which can be used globally.

@ Bean public JPAQueryFactory queryFactory (EntityManager entityManager) {return new JPAQueryFactory (entityManager);}

Search criteria entity class

Package com.jerry.gamemarket.dto;import com.jerry.gamemarket.enums.OrderStatusEnums;import com.jerry.gamemarket.enums.PayStatusEnums;import lombok.Data;import org.springframework.context.annotation.Bean;import java.math.BigDecimal;/** * author by Li Zhaojie * Date 2018-11-28 * / @ Datapublic class SearchOrderDTO {private String orderId; / / private String id; private String buyerName; private String buyerPhone; private String buyerAddress; private String canteenName; private BigDecimal maxAmount; private BigDecimal minAmount Private Integer orderStatus; / / default unpaid private Integer payStatus; private Integer pageNum = 1; private Integer pageSize=10;}

Dynamically search for methods in the implementation class. Here the return type is QueryResults. Let's take a look at this special return type.

Look at the source code

Results is an array of returned list data

Total is the total

Where did offset start?

Limit is a limit on the number of messages.

/ Source code recreated from a .class file by IntelliJ IDEA// (powered by Fernflower decompiler) / / package com.querydsl.core;import com.google.common.collect.ImmutableList;import java.io.Serializable;import java.util.List;import javax.annotation.Nullable;public final class QueryResults implements Serializable {private static final long serialVersionUID =-4591506147471300909L; private static final QueryResults EMPTY = new QueryResults (ImmutableList.of (), 9223372036854775807L, 0L, 0L); private final long limit; private final long offset; private final long total Private final List results; public static QueryResults emptyResults () {return EMPTY;} public QueryResults (List results, @ Nullable Long limit, @ Nullable Long offset, long total) {this.limit = limit! = null? Limit.longValue (): 9223372036854775807L; this.offset = offset! = null? Offset.longValue (): 0L; this.total = total; this.results = results;} public QueryResults (List results, QueryModifiers mod, long total) {this (results, mod.getLimit (), mod.getOffset (), total);} public List getResults () {return this.results;} public long getTotal () {return this.total } public boolean isEmpty () {return this.results.isEmpty ();} public long getLimit () {return this.limit;} public long getOffset () {return this.offset;}} @ Override public QueryResults dymamicQuery (SearchOrderDTO searchOrderDTO) {QOrderMaster o = QOrderMaster.orderMaster; JPAQuery query = jpaQueryFactory.select (o) .from (o) If (! StringUtils.isEmpty (searchOrderDTO.getOrderId () {query.where (o.orderId.like (searchOrderDTO.getOrderId ();} if (! StringUtils.isEmpty (searchOrderDTO.getBuyerName () {query.where (o.buyerName.like ("%" + searchOrderDTO.getBuyerName () + "%")) } if (! StringUtils.isEmpty (searchOrderDTO.getBuyerPhone () {query.where (o.buyerPhone.eq (searchOrderDTO.getBuyerPhone ();} if (searchOrderDTO.getMaxAmount ()! = null & & searchOrderDTO.getMinAmount ()! = null) {query.where (o.orderAmount.goe (searchOrderDTO.getMinAmount () } if (searchOrderDTO.getMaxAmount ()! = null & & searchOrderDTO.getMinAmount ()! = null) {query.where (o.orderAmount.loe (searchOrderDTO.getMaxAmount ();} if (searchOrderDTO.getOrderStatus ()! = null) {query.where (o.orderStatus.eq (searchOrderDTO.getOrderStatus () } if (searchOrderDTO.getPayStatus ()! = null) {query.where (o.payStatus.eq (searchOrderDTO.getPayStatus ();} return query.orderBy (o.createTime.desc ()) .offset ((searchOrderDTO.getPageNum ()-1) * searchOrderDTO.getPageSize ()) .limit (searchOrderDTO.getPageSize ()) .fetchResults ();}

These queries include fuzzy queries, dynamic queries and paging.

Finally, there are references in Controller.

@ PostMapping ("/ searchorder") public QueryResults findByCase (@ RequestBody SearchOrderDTO searchOrderDTO) {QueryResults queryResults=orderService.dymamicQuery (searchOrderDTO); System.out.println (searchOrderDTO); System.out.println (queryResults.getResults ()); return queryResults;}

The whole query is completed, how to render the data depends on what you like.

SpringdataJPA and querydsl. What is SpringDataJPA? What is QueryDSL?

SpringDataJPA is the encapsulation used for JPA (JPA is the java persistence layer api)

QueryDSL is a general framework based on various ORM (object-relational mapping). Using its API class library, you can write the sql of java code

@ Mapper entity-model mapping

Use the annotation @ Mapper on mapper (componentModel = "spring", uses = {})

It is used to map dto and entity to automatically generate mapper to complete the conversion.

If the attribute names in dto and entity do not match, you need to add comments

@ Mappings ({@ Mapping (source = "entity.name", target = "dto property name")}) overall project flow

In the service backend, the restful interface is provided for external exposure under the rest package, and the proxy layer is introduced into the concrete class. The proxy layer implements the processing of dto and dao layer (injection of service and automatically generated mapper mapping). Mapper handles the conversion between dto and entity, and the service layer operates db (the mode of operation is jpa).

Doubt

In a proxy class, why initialize mapper and service by adding @ Autowired annotations to constructors instead of adding @ Autowired annotations to member variables to be injected? what are the advantages of using constructors?

Through the relevant data to find the answer: java variable initialization order is: static variable or static statement block-> instance variable or initialization statement block-> construction method-> @ Autowired if there is an added constructor in this class, when the constructor is executed, the member variable has not been initialized, and an error will be reported at this time. If there is no constructor, you can add @ Autowired annotation on the member variable to initialize the variable. In order to avoid initializing member variables when the constructor is initialized, it is recommended to add the @ Autowired annotation to the constructor.

QueryDSL in the project is only used to generate Q class, and there is no sql in java code format? Why only use spring-data-jpa?

JpaRepository

Introduction to spring-data-jpa, spring integrates a variety of third-party frameworks, and the naming format is spring-data-*,spring, which integrates jpa to create spring-data-jpa.

Repository is the persistence layer, which is equivalent to dao, mapper, etc. Basic CRUD can be used by defining various repository inheritance JpaRepository in the project.

CrudRepository this API is the second-level interface of spring integrating jpa. This API provides ordinary CRUD operation. PagingAndSortingRepository API is added to provide overload method of findAll method (supporting paging). QueryByExampleExecutor gracefully solves the null pointer problem, and then optimizes it to JpaRepository API. This API optimizes the last API method and returns a wider range of values.

SimpleJpaRepository

This class is the concrete implementation of the JpaRepository interface, and the CRUD operation is provided by this class. Including four member variables JpaEntityInformation, PersistenceProvider, CrudMethodMetadata, EntityManager. The first three member variables are used to get the spliced sql,EntityManager to execute the sql. Equivalent to session, sqlSession, etc.

After writing a Repository that inherits JpaRepository

You can write its implementation class, but you don't need the implements keyword to implement it. Spring-data-jpa will automatically recognize the relationship, or you can not write the implementation class. At run time, the SimpleJpaRepository class is its implementation class. If you write a custom implementation class, the logic in the implementation class will be executed.

Related Grammar of spring-data-jpa

For multi-table queries, you need to use the Specification anonymous inner class, override its methods. It is more intuitive to write sql when you encounter a multi-table query.

On "how to use JPA+querydsl to achieve multi-conditional dynamic query" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.