JPA复杂查询or和and连用代码示例(一)

正常情况下原生sql的or和and连用一般的语句为：

select * from table where (column1 = '字符串' or column2 = '字符串') and column3 like '%字符串%'

这其实就是两个and条件相连接，只不过其中一个and条件是由or连接条件构成而已。

下面直接讲实际应用

1：新建一个实体类：

@Data
@Entity
@Table(name = "t_user")
@DynamicUpdate
public class TUser {

    @Id
    //long型转成string返回给前端，防止精度丢失
    @JsonSerialize(using = ToStringSerializer.class)
    private Long id;

    @Column(name = "username")
    private String username;

    @Column(name = "phone")
    private String phone;

    @Column(name = "role")
    private String role;

    @JsonIgnore
    @Column(name = "password")
    private String password;

    @Column(name = "department")
    private String department;

    @Column(name = "create_time")
    private Date createTime;

    @Column(name = "update_time")
    private Date updateTime;

    @Version
    @JsonIgnore
    private int version;

    @PreUpdate
    protected void onUpdate() {
        updateTime = new Date();
    }

    @PrePersist
    protected void onCreate() {
        createTime = new Date();
        updateTime = new Date();
    }

}

2：repository

@Repository
public interface UserRepository extends JpaRepository<TUser, Long>, JpaSpecificationExecutor<TUser> {
}

3：service层

@Service
@RequiredArgsConstructor
public class DoctorService extends KeyGenService {
	
	private final UserRepository userRepository;
	
	public Page<TUser> doctorList(DoctorListRes res) {
        Pageable pageable = PageRequest.of(res.getPage(), res.getSize(), Sort.by(Sort.Direction.DESC,
                "createTime"));
        Specification<TUser> specification = (root, query, cb) -> {
            List<Predicate> predicates = Lists.newArrayList();
            //这里就是or连接，role字段等于doctor或者intern
            predicates.add(cb.or(cb.equal(root.get("role"), StateEnum.ecgDoctor.name()), 
            cb.equal(root.get("role"), StateEnum.intern.name())));  
            //添加姓名条件
            if (res.getName() != null) {
                predicates.add(cb.like(root.get("username"), "%" + res.getName().trim() + "%"));
            }
            if (res.getPhoneno() != null) {
                predicates.add(cb.like(root.get("phone"), "%" + res.getPhoneno().trim() + "%"));
            }
            return cb.and(predicates.toArray(new Predicate[predicates.size()]));
        };
        return userRepository.findAll(specification, pageable);
    }
}

我这个翻译成原生sql就是：

select * from t_user where (role = 'doctor' or role = 'intern') and name like '%传递来的参数%' and phone like '%传递来的参数%'
姓名和电话号码只有不为null时才拼写进sql，但角色条件是一定有的，而且是or连接。