springDataJpa入门教程
-
springDataJpa入门教程(1)-基于springBoot的基本增删改查
-
springDataJpa入门教程(2)-Specification动态条件查询+排序+分页
-
springDataJpa入门教程(3-1)-基于EntityManager原生sql多表联合查询+动态条件查询+分页
-
springDataJpa入门教程(3-2)-基于EntityManager原生sql多表联合查询+动态条件查询+分页返回自定义实体类对象
-
springDataJpa入门教程(4)-Example单表动态条件查询+分页
-
springDataJpa入门教程(5)-单表动态条件查询+分页
-
springDataJpa入门教程(6)-多表动态条件查询+分页
-
springDataJpa入门教程(7)-基于springDataJpa投影(Projection)返回自定义实体类对象
-
springDataJpa入门教程(8)-JPA EnableJpaAuditing 审计功能
-
springDataJpa入门教程(9)-spring jpa实体属性类型转换器AttributeConverter的用法
-
springDataJpa入门教程(10)-JPA使用过程中遇到的坑及解决方法
springDataJpa入门教程(6)-多表动态条件查询+分页
前面讲到了基于EntityManager原生sql多表联合查询+动态条件查询+分页,但是使用这种方式编码量比较大,分页查询还需要单独查询总记录数。今天来介绍另一种方式实现多表动态条件查询+分页,比较适合动态查询条件较少的场景。由于本人水平有限,教程中难免出现错误,敬请谅解,欢迎大家批评指正。源码地址:源码下载地址。java学习交流群:184998348,欢迎大家一起交流学习。
需要用到的实体类及DTO如下:
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;
}
}
package com.thizgroup.jpa.study.model;
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.Data;
@Entity
@Table(name="tb_address")
@Data//使用lombok生成getter、setter
public class Address {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "country",columnDefinition = "varchar(64)")
private String country;
@Column(name = "province",columnDefinition = "varchar(64)")
private String province;
@Column(name = "city",columnDefinition = "varchar(64)")
private String city;
}
package com.thizgroup.jpa.study.dto;
import java.util.Date;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data//使用lombok生成getter、setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class UserDTO {
private Long id;
private String name;//姓名
private Integer age;//年龄
private String mobile;//手机号
private String email;//邮箱
private Date birthday;//生日
private AddressDTO addressDTO;//地址
private Date createDate;//创建时间
private Date modifyDate;//修改时间
private Date startTime;//开始时间
private Date endTime;//结束时间
}
package com.thizgroup.jpa.study.dto;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data//使用lombok生成getter、setter
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class AddressDTO {
private Long id;
private String country;//国家
private String province;//省份
private String city;//城市
}
首先在IUserService接口中创建一个查询方法,
package com.thizgroup.jpa.study.service;
import com.thizgroup.jpa.study.dto.UserDTO;
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<UserDTO> findUserListByCondition2(UserDTO userDTO,Pageable pageable);
}
然后在UserServiceImpl实现类中实现该方法,
package com.thizgroup.jpa.study.service.impl;
import com.thizgroup.jpa.study.dao.UserDao;
import com.thizgroup.jpa.study.dao.UserRepository;
import com.thizgroup.jpa.study.dto.AddressDTO;
import com.thizgroup.jpa.study.dto.PageRecord;
import com.thizgroup.jpa.study.dto.UserDTO;
import com.thizgroup.jpa.study.dto.UserProjection;
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 javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Order;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Example;
import org.springframework.data.domain.ExampleMatcher;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
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<UserDTO> findUserListByCondition2(UserDTO userDTO,
Pageable pageable) {
String name = null;
String city = null;
if(userDTO != null){
if(StringUtils.isNotBlank(userDTO.getName())){
name = userDTO.getName();
}
if(userDTO.getAddressDTO() != null &&
StringUtils.isNotBlank(userDTO.getAddressDTO().getCity())){
city = userDTO.getAddressDTO().getCity();
}
}
Page<Map<String,Object>> pageList = userRepository
.findUserListByCondition2(name,city,pageable);
Page<UserDTO> userDTOS = pageList.map(data ->
UserDTO.builder()
.id(((BigInteger) data.get("id")).longValue())
.name(data.get("name").toString())
.age(((Short) data.get("age")).intValue())
.birthday((Date) data.get("birthday"))
.email(data.get("email").toString())
.addressDTO(
AddressDTO.builder()
.country(data.get("country").toString())
.city(data.get("city").toString())
.build()
)
.build()
);
return userDTOS;
}
}
接下来在UserRepository接口中创建查询方法,
package com.thizgroup.jpa.study.dao;
import com.thizgroup.jpa.study.model.User;
import java.util.List;
import java.util.Map;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
public interface UserRepository extends JpaRepository<User,Long>, JpaSpecificationExecutor<User> {
/**
* 多表动态查询条件+分页
*/
@Query(nativeQuery = true,
value = "select u.id,u.name,u.age,u.birthday,u.email,"
+"a.country,a.city from tb_user u left join tb_address a "
+ " on u.address_id=a.id where (?1 is null or u.name like %?1%) "
+ " and (?2 is null or a.city = ?2) order by u.create_date ",
countQuery = "select count(*) from tb_user u left join tb_address a "
+ " on u.address_id=a.id where (?1 is null or u.name like %?1%) "
+ " and (?2 is null or a.city = ?2) ")
Page<Map<String,Object>> findUserListByCondition2(String name,
String city,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.dto.AddressDTO;
import com.thizgroup.jpa.study.dto.UserDTO;
import com.thizgroup.jpa.study.model.User;
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 findUserListByCondition2(){
String name = null;
String city = "武汉";
UserDTO userDTO = new UserDTO();
userDTO.setName(name);
AddressDTO addressDTO = new AddressDTO();
addressDTO.setCity(city);
userDTO.setAddressDTO(addressDTO);
//注意:jpa的分页是从0开始的
Page<UserDTO> pageList = userService.findUserListByCondition2(userDTO, PageRequest.of(0, 1));
System.out.println("分页信息:");
System.out.println("总记录数:"+pageList.getTotalElements()+",总页数:"+pageList.getTotalPages());
System.out.println("页码:"+(pageList.getNumber()+1)+",每页条数:"+pageList.getSize());
List<UserDTO> content = pageList.getContent();
content = null == content? new ArrayList<>() : content;
content.forEach(user->System.out.println(user));
}
}
运行一下单元测试,结果如下:
分页信息:
总记录数:2,总页数:2
页码:1,每页条数:1
UserDTO(id=2, name=狄仁杰, age=50, mobile=null, email=di@qq.com, birthday=1988-09-16 08:00:00.0, addressDTO=AddressDTO(id=null, country=中国, province=null, city=武汉), createDate=null, modifyDate=null, startTime=null, endTime=null)
至此,springDataJpa -多表动态条件查询+分页就介绍完了,有需要源码的朋友,请到git上下载源码,源码地址:https://github.com/hgq0916/springdatajpa-study.git。java学习交流群:184998348,欢迎大家一起交流学习。