前面有几篇是介绍动态条件查询+分页的，比如Specification，EntityManager,Example,但是这几种查询各有优缺点，Specification适合于复杂的动态条件查询，编码量较大，如果查询条件比较少，用Specification感觉有点杀鸡用牛刀的感觉，EntityManager比较适合复杂的sql语句，多表联合查询的场景，如果是单表没必要用EntityManager，Example对字符串的查询支持比较好，但是不够灵活，比如判断某个字段的值是否大于某个值或者小于某个值，这种还是自己写sql来的方便，下面来介绍一种简单的动态条件查询。

下面是用到的实体类User类的代码，

package com.thizgroup.jpa.study.model;

import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@Entity
@Table(name = "tb_user")
@Data//使用lombok生成getter、setter
@NoArgsConstructor//生成无参构造方法
public class User {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  @Column(name = "name",columnDefinition = "varchar(64)")
  private String name;

  @Column(name = "mobile",columnDefinition = "varchar(64)")
  private String mobile;

  @Column(name = "email",columnDefinition = "varchar(64)")
  private String email;

  @Column(name = "age",columnDefinition = "smallint(64)")
  private Integer age;

  @Column(name = "birthday",columnDefinition = "timestamp")
  private Date birthday;

  //地址
  @Column(name = "address_id",columnDefinition = "bigint(20)")
  private Long addressId;

  @Column(name = "create_date",columnDefinition = "timestamp")
  private Date createDate;

  @Column(name = "modify_date",columnDefinition = "timestamp")
  private Date modifyDate;

  @Builder(toBuilder = true)
  public User(Long id,String name, String mobile, String email, Integer age, Date birthday,
      Long addressId) {
    this.id = id;
    this.name = name;
    this.mobile = mobile;
    this.email = email;
    this.age = age;
    this.birthday = birthday;
    this.addressId = addressId;
  }
}
1.需求：根据姓名、邮箱模糊查询用户列表，姓名和邮箱为null则查询所有。

首先，在IUserService中，添加一个查询方法，

package com.thizgroup.jpa.study.service;

import com.thizgroup.jpa.study.model.User;
import java.util.List;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;

/**
 * 用户服务
 */
public interface IUserService {
	  /**
   * 单表动态查询条件+分页
   */
  Page<User> findUserListByCondition(String name ,String email,Pageable pageable);
}

然后在UserServiceImpl中实现这个方法，

package com.thizgroup.jpa.study.service.impl;

import com.thizgroup.jpa.study.dao.UserRepository;
import com.thizgroup.jpa.study.model.User;
import com.thizgroup.jpa.study.service.IUserService;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.stream.Collectors;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;

@Service
@Transactional(readOnly = false,propagation = Propagation.REQUIRED)
public class UserServiceImpl implements IUserService {

  @Autowired
  private UserRepository userRepository;
    @Override
  public Page<User> findUserListByCondition(String name, String email, Pageable pageable) {
    return userRepository.findUserListByCondition(name,email,pageable);
  }
}

接下来看userRepository的findUserListByCondition方法，

/**
   * 单表动态查询条件+分页
   */
  @Query(nativeQuery = true,value = "select * from tb_user where (?1 is null or name like %?1%) "
      + " and (?2 is null or email like %?2%) ",
      countQuery = "select count(*) from tb_user where (?1 is null or name like %?1%) "
          + " and (?2 is null or email like %?2%) ")
  Page<User> findUserListByCondition(String name ,String email,Pageable pageable);

上面是采用原生sql来实现的查询，动态查询条件是通过"?1 is null or name like %?1%",如果参数的值为null，则这个条件一定成立，如果不为null，则按照or关键词后的条件查询。
注意：这种写法一定不要忘了外面的括号。

下面，写个单元测试验证上述代码，

package com.thizgroup.jpa.study.service;

import com.thizgroup.jpa.study.JpaApplication;
import com.thizgroup.jpa.study.model.User;
import com.thizgroup.jpa.study.utils.DateUtils;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

@SpringBootTest(classes={JpaApplication.class})
@RunWith(SpringJUnit4ClassRunner.class)
@Transactional(readOnly = false,propagation = Propagation.REQUIRED)
public class UserServiceImplTest {

  @Autowired
  private IUserService userService;

   @Test
  public void findUserListByCondition(){
    String name = null;
    String email = "@qq.com";
    //注意：jpa的分页是从0开始的
    Page<User> pageList = userService.findUserListByCondition(name,email, PageRequest.of(0, 15));
    System.out.println("分页信息：");
    System.out.println("总记录数："+pageList.getTotalElements()+",总页数："+pageList.getTotalPages());
    System.out.println("页码："+(pageList.getNumber()+1)+",每页条数："+pageList.getSize());
    List<User> content = pageList.getContent();
    content = null == content? new ArrayList<>() : content;
    content.forEach(user->System.out.println(user));
  }

}

运行一下单元测试，结果如下：

分页信息：
总记录数：3,总页数：1
页码：1,每页条数：15
User(id=1, name=张三, mobile=156989989, email=hu@qq.com, age=35, birthday=2008-09-16 08:00:00.0, addressId=11, createDate=2019-08-06 05:50:01.0, modifyDate=2019-08-08 05:46:11.0)
User(id=2, name=狄仁杰, mobile=158789989, email=di@qq.com, age=50, birthday=1988-09-16 08:00:00.0, addressId=22, createDate=2019-07-06 05:50:01.0, modifyDate=2019-08-06 06:20:48.0)
User(id=3, name=诸葛亮, mobile=158989989, email=zhu@qq.com, age=54, birthday=2001-09-16 08:00:00.0, addressId=22, createDate=2019-09-06 05:50:01.0, modifyDate=2019-08-08 05:46:17.0)