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

This article mainly shows you "Jpa Specification how to achieve and and or query at the same time", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "Jpa Specification how to achieve and and or query at the same time" this article.

Queries that use both and and or

UserServiceImpl class, service implementation class

Import org.springframework.beans.BeanUtils;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.data.jpa.domain.Specification;import org.springframework.stereotype.Service;import org.springframework.transaction.annotation.Transactional; import javax.persistence.criteria.CriteriaBuilder;import javax.persistence.criteria.CriteriaQuery;import javax.persistence.criteria.Predicate;import javax.persistence.criteria.Root;import java.util.ArrayList;import java.util.List; @ Service@Transactionalpublic class UserServiceImpl implements UserService {@ Autowired private RongUserRepository rongUserRepository / / FriendNumResult custom return type / / FriendNumParam custom wrapper parameter type / / RongUser entity type @ Override public FriendNumResult friendNum (FriendNumParam friendNumParam) {FriendNumResult friendNumResult=new FriendNumResult () Specification specification = new Specification () {@ Override public Predicate toPredicate (Root root, CriteriaQuery criteriaQuery, CriteriaBuilder criteriaBuilder) {/ / encapsulates the and statement List listAnd = new ArrayList () / / here is hql, so root.get (), the method must be the corresponding entity attribute listAnd.add (criteriaBuilder.equal (root.get ("perLevel"). As (Integer.class), friendNumParam.getPerLevel ()); Predicate [] array_and=new Predicate [listAnd.size ()]; Predicate Pre_And = criteriaBuilder.and (listAnd.toArray (array_and)) / / encapsulate or statements List listOr = new ArrayList (); listOr.add (criteriaBuilder.equal (root.get ("fId") .as (Integer.class), friendNumParam.getUid ()); listOr.add (criteriaBuilder.equal (root.get ("fId2") .as (Integer.class), friendNumParam.getUid () Predicate [] arrayOr = new Predicate [listOr.size ()]; Predicate Pre_Or = criteriaBuilder.or (listOr.toArray (arrayOr)); return criteriaQuery.where (Pre_And,Pre_Or) .getRestriction (); / / return / / return criteriaBuilder.and (list.toArray ()) when using and or or alone }}; long num=this.rongUserRepository.count (specification); friendNumResult.setFriendNum (Integer.valueOf ((int) num)); return friendNumResult;}}

RongUserRepository interface

Import org.springframework.data.jpa.repository.JpaRepository;import org.springframework.data.jpa.repository.JpaSpecificationExecutor;//RongUser 's own entity type public interface RongUserRepository extends JpaRepository, JpaSpecificationExecutor {}

Note: before using Specification, the RongUserRepository interface must implement the entity class of the JpaSpecificationExecutor,RongUser corresponding table

Combined use of AND and OR for JPA dynamic query

Now, I am responsible for the development of projects that use JPA as the ORM framework. With JPA, not a single line of SQL has been written. Yesterday, there was a new requirement for dynamic query, which is simple. But there is a place where AND and OR need to be used together. Here, I will record my understanding and writing, hoping to help you.

Problem description

You need to make a dynamic query based on the conditions to implement a statement similar to the following:

SELECT * FROM tableWHERE 1 = 1 if (a = = 1) AND table.column1 = an if (b! = null) AND table.column2 = b if (cList! = null & & cList.size () > 0) AND table.column3 IN cList if (d = 2 | | dd = = 2) AND (table.column4 = d OR table.column5 = dd)

There are several lines of pseudo code above. It means that several conditions are AND connected, but some of them are connected using OR.

In our actual project, this scenario is also very common. Here, I will share the specific writing. Take the example in our project.

Code example

JPA dynamic query, the way we use here is to implement the Specification interface and customize the dynamic query logic. This is also a way that I personally recommend. I won't talk about the use of JPA and the basics of Specification interface here. Interested friends can refer to the official documents to learn.

Next, let's first define our database entity:

@ Data@Entity@Table (name = "user") public class User {@ Id @ GeneratedValue (strategy = GenerationType.IDENTITY) private Integer id; / * user name * / private String username; / * * Age * / private Integer age; / * * birthday * / private Date birthDay; / * remove the logo 0-not deleted, 1-deleted * / private Integer deleteFlag;}

Then define the DAO layer interface:

@ Repositorypublic interface UserDAO extends JpaRepository {/ * in fact, this feature is generally used as a list interface and is usually used in conjunction with paging queries. Here, I will not make an introduction. Let's see if we want to add the tutorial * / List findAll (Specification querySpec);} later.

The following is the parameter object of the dynamic query passed from the front end:

@ Datapublic class UserDTO {/ * username for fuzzy search * / private String username; / * user ID, for In query * / private List userIdList; / * * user age, for OR In query * / private List ageList / * birthday, start * / @ JsonFormat (pattern = "yyyy-MM-dd", locale = "zh", timezone = "GMT+8") private Date birthDayBegin; / * birthday, end * / @ JsonFormat (pattern = "yyyy-MM-dd", locale = "zh", timezone = "GMT+8") private Date birthDayEnd;}

Then, the important part comes when we implement the Specification interface and define the query logic:

In the actual code operation, I will extract this part of the logic into a separate method, which is done with lambda expressions, which is actually anonymous inner classes.

Private Specification getListSpec (UserDTO userDTO) {return (root, criteriaQuery, criteriaBuilder)-> {List predicateList = new ArrayList (); / / No identity is deleted, only undeleted data predicateList.add (criteriaBuilder.equal ("deleteFlag"), 0) is queried; / / List usernameOrAgePredicate = new ArrayList () is queried by user name or age List; String username = userDTO.getUsername () If (! StringUtils.isEmpty (username)) {/ / username here, use fuzzy matching usernameOrAgePredicate.add (criteriaBuilder.like (root.get ("username"), "%" + username + "%");} List ageList = userDTO.getAgeList () If (! CollectionUtils.isEmpty (ageList)) {/ / below is an IN query CriteriaBuilder.In in = criteriaBuilder.in (root.get ("age")); ageList.forEach (in::value); usernameOrAgePredicate.add (in);} / * the following line of code is important. * criteriaBuilder.or (Predicate... Restrictions) receives multiple Predicate with variable parameters; * these multiple Predicate conditions are connected using OR; this method finally returns a Predicate object * / predicateList.add (criteriaBuilder.or (usernameOrAgePredicate.toArray (new Predicate [0])); / / user ID List,IN query List userIdList = reqDTO.getUserIdList (); if (! CollectionUtils.isEmpty (userIdList)) {CriteriaBuilder.In in = criteriaBuilder.in (root.get ("id")); userIdList.forEach (in::value); predicateList.add (in) } / / query Date birthDayBegin = reqDTO.getBirthDayBegin (); Date birthDayEnd = reqDTO.getBirthDayEnd (); if (birthDayBegin! = null & & birthDayEnd! = null) {/ / DateUtils is a tool class I customized Date begin = DateUtils.startOfDay (birthDayBegin); Date end = DateUtils.endOfDay (birthDayEnd) PredicateList.add (criteriaBuilder.greaterThanOrEqualTo (root.get ("birthDay"), begin); predicateList.add (criteriaBuilder.lessThanOrEqualTo (root.get ("birthDay"), end));} / / finally, connect multiple Predicate query conditions return criteriaBuilder.and (predicateList.toArray (new Predicate [0])) using AND;};}

In this way, our dynamic query part is built. How do you use it exactly? As follows:

Specification querySpec = this.getListSpec (userDTO); List userList = userDAO.findAll (querySpec)

In this way, we executed a dynamic query and got the results.

The dynamic query above is actually equivalent to the following pseudo code:

SELECT * FROM user WHERE user.deleteFlag = 0 AND (user.username like'% {username}%'OR user.age IN ageList) AND user.id IN userIdListAND user.birthDay > birthDayBegin AND user.birthDay < birthDayEnd

Currently, the corresponding AND condition is stitched only if the corresponding value is not empty.

The above is all the content of the article "how to use and and or query simultaneously by Jpa Specification". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.