How does Jpa Specification enable and and or to use queries at the same time

Query using both and and or in the directory JPA Dynamic Query AND, OR Combined Problem Description Code Example

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
@Transactional
public class UserServiceImpl implements UserService {
    @Autowired
    private RongUserRepository rongUserRepository;
     //FriendNumResult   Custom return type 
    //FriendNumParam   Type of custom encapsulation parameter 
    //RongUser   Entity type 
    @Override
    public FriendNumResult friendNum(FriendNumParam friendNumParam) {
        FriendNumResult friendNumResult=new FriendNumResult();
 
        Specification<RongUser> specification = new Specification<RongUser>(){
 
            @Override
            public Predicate toPredicate(Root<RongUser> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                // Encapsulation and Statement 
                List<Predicate> listAnd = new ArrayList<Predicate>();
                // This 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));
 
                // Encapsulation or Statement 
                List<Predicate> listOr = new ArrayList<Predicate>();
                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();
                // Use alone   and  Or   or  Hour   Return 
                //return criteriaBuilder.and(list.toArray());
            }
        };
        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   Your own entity type 
public interface RongUserRepository extends JpaRepository<RongUser,Integer> , JpaSpecificationExecutor<RongUser> {
}

Note: Before using Specification, the RongUserRepository interface must implement JpaSpecificationExecutor < RongUser > , RongUser Entity class for the corresponding table

Combination of AND and OR for JPA Dynamic Query

Now, in the project I am responsible for developing, JPA is used as the ORM framework. With JPA, one line of SQL has not been written. Yesterday, there was a new requirement, which required dynamic query. This is simple. But there is a place to use AND and OR together. Here, I will record my understanding and writing, hoping to help everyone.

Problem description

You need to dynamically query according to the conditions and implement a statement similar to the following:

SELECT *
FROM   table
WHERE  1 = 1
   if (a == 1)
        AND table.column1 = a
   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)

Above are a few lines of pseudo code. It means that several conditions are connected by AND, but some of them are connected by OR.

This scenario is also very common in our actual projects. Here, I will share the specific writing. Take the example in our project as an example.

Code Sample

JPA dynamic query, here we use the way is: Specification interface, custom dynamic query logic. This is also the way I personally recommend. The use of JPA, Specification interface basic knowledge here I will not talk about. Interested friends can consult the official documents to learn.

Next, we first define our database entities:

@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;
    /**
     *  Delete the identity;  0 -  Not deleted, 1 -  Deleted 
     */
    private Integer deleteFlag;
}

Then define the DAO layer interface:

@Repository
public interface UserDAO extends JpaRepository<User, Long> {
    /**
     *  Actually, this function 1 Used as a general  list  Interface uses, 1 Used in conjunction with paging queries. Here, I will not make an introduction, depending on the situation, whether to add a tutorial later 
     */
    List<User> findAll(Specification<User> querySpec);
}

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

@Data
public class UserDTO {
    /**
     *  User name for fuzzy search 
     */
    private String username;
    /**
     *  Users ID , used for  In  Query 
     */
    private List<String> userIdList;
    /**
     *  User age, used for  OR In  Query 
     */
    private List<Integer> 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 point came. We implemented the Specification interface and defined the query logic:

In the actual code operation, I will pull this part of logic into a separate method, which is completed by lambda expression, which is actually an anonymous inner class.

private Specification<User> getListSpec(UserDTO userDTO) {
    return (root, criteriaQuery, criteriaBuilder) -> {
        List<Predicate> predicateList = new ArrayList<>();
        //  Undeleted identity, only undeleted data is queried 
        predicateList.add(criteriaBuilder.equal(root.get("deleteFlag"), 0));
        //  According to   User name   Or   Age List  Query 
        List<Predicate> usernameOrAgePredicate = new ArrayList<>();
        String username = userDTO.getUsername();
        if (!StringUtils.isEmpty(username)) {
            //  User name here, use fuzzy matching 
            usernameOrAgePredicate.add(criteriaBuilder.like(root.get("username"), "%" + username + "%"));
        }
        List<Integer> ageList = userDTO.getAgeList();
        if (!CollectionUtils.isEmpty(ageList)) {
            //  Below is 1 A  IN Query 
            CriteriaBuilder.In<Integer> in = criteriaBuilder.in(root.get("age"));
            ageList.forEach(in::value);
            usernameOrAgePredicate.add(in);
        }
        /*  Here below 1 Lines of code are important. 
         * criteriaBuilder.or(Predicate... restrictions)  Receive a plurality of Predicate Variable parameters; 
         *  These multiple  Predicate Between conditions, is the use of OR Connected; The method eventually returns  1 A Predicate Object; 
         */
        predicateList.add(criteriaBuilder.or(usernameOrAgePredicate.toArray(new Predicate[0])));
        //  Users ID List , IN  Query 
        List<Integer> userIdList = reqDTO.getUserIdList();
        if (!CollectionUtils.isEmpty(userIdList)) {
            CriteriaBuilder.In<Integer> in = criteriaBuilder.in(root.get("id"));
            userIdList.forEach(in::value);
            predicateList.add(in);
        }
        //  Birthday time period inquiry 
        Date birthDayBegin = reqDTO.getBirthDayBegin();
        Date birthDayEnd = reqDTO.getBirthDayEnd();
        if (birthDayBegin != null && birthDayEnd != null) {
            // DateUtils  It's my custom 1 Tool classes 
            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, use the AND  Connect   Multiple  Predicate  Query criteria 
        return criteriaBuilder.and(predicateList.toArray(new Predicate[0]));
    };
}

In this way, our dynamic query part is built. How to use it specifically? As follows:

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

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

The above dynamic query is actually equivalent to the following pseudocode:

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

At present, the corresponding AND condition will be spliced only if the corresponding value is not empty.