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入门教程（3-1）-基于EntityManager原生sql多表联合查询+动态条件查询+分页
在JPA实际应用中，很多场景下需要同时对多个表进行操作，这时候，JPA提供的JPQL语句就无法满足实际应用的需求，需要用到原生sql来实现查询，简单的原生sql查询直接使用@Query注解就可以完成，但是要想实现多表并且动态条件查询，则@Query注解也显得力不从心，这时候EntityManager便派上用场了。
下面接着上一节的内容来讲解如何使用EntityManager实现多表联合查询+动态条件查询+分页。有需要源码的朋友，请到git上下载源码，源码地址：源码下载地址。java学习交流群：184998348,欢迎大家一起交流学习。
- 实体类分别是User类、Address类，下面这两个类的代码：
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;
}
- 涉及到DTO及辅助类
package com.thizgroup.jpa.study.dto;
import java.io.Serializable;
import java.util.List;
import lombok.Data;
import lombok.Getter;
import org.springframework.util.Assert;
@Data//使用lombok生成getter、setter
public class PageBean implements Serializable {
@Getter
private long totalCount;//总记录数
@Getter
private int totalPages;//总页数
@Getter
private int pageNumber;//第几页
@Getter
private int pageSize;//每页条数
public PageBean(int pageNumber, int pageSize, long totalCount) {
pageNumber = pageNumber <0 ? 0 :pageNumber;//jpa中页码从0开始
pageSize = pageSize <=0 ? 15 : pageSize;//默认取15条记录
//计算总页数
int totalPages = (int)((totalCount+pageSize-1)/pageSize);
//计算起始页
if(totalPages>0){
pageNumber = totalPages <= pageNumber ? totalPages-1 : pageNumber;
}
this.pageNumber = pageNumber;
this.pageSize = pageSize;
this.totalPages = totalPages;
this.totalCount = totalCount;
}
public PageBean(PageBean pageBean) {
Assert.notNull(pageBean,"pagebean cannot be null");
this.pageNumber = pageBean.getPageNumber();
this.pageSize = pageBean.getPageSize();
this.totalPages = pageBean.getTotalPages();
this.totalCount = pageBean.getTotalCount();
}
}
package com.thizgroup.jpa.study.dto;
import java.util.List;
import lombok.Data;
import lombok.Getter;
@Data
public class PageRecord<T> extends PageBean {
@Getter
private List<T> data;//数据列表
public PageRecord(int pageNumber, int pageSize, long totalCount,List<T> data) {
super(pageNumber, pageSize, totalCount);
this.data = data;
}
public PageRecord(PageBean pageBean, List<T> data){
super(pageBean);
this.data =data;
}
}
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;//城市
}
1.需求：根据用户名、年龄，生日（开始时间-结束时间）、城市名称查询用户分页列表，查询结果按照用户创建时间排序。
在IUserService接口中创建一个查询方法，
package com.thizgroup.jpa.study.service;
import com.thizgroup.jpa.study.dto.PageRecord;
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 {
/**
* 查询用户分页信息
* @param userDTO
* @return
*/
PageRecord<UserDTO> findUserDTOListByPage(UserDTO userDTO, Pageable pageable);
}
然后在UserServiceImpl中实现该方法，
package com.thizgroup.jpa.study.service.impl;
import com.thizgroup.jpa.study.dao.UserDao;
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.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.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 UserDao userDao;
@Override
public PageRecord<UserDTO> findUserDTOListByPage(UserDTO userDTO, Pageable pageable) {
return userDao.findUserListByPage(userDTO,pageable);
}
主要的代码逻辑在dao层，让我们来看下UserDao 的代码，
package com.thizgroup.jpa.study.dao;
import com.thizgroup.jpa.study.dto.AddressDTO;
import com.thizgroup.jpa.study.dto.PageBean;
import com.thizgroup.jpa.study.dto.PageRecord;
import com.thizgroup.jpa.study.dto.UserDTO;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;
/**
* 用户服务
*/
@Repository
public class UserDao {
@Autowired
private EntityManager entityManager;
//使用entityManager实现多表联合带条件带分页查询及排序
public PageRecord<UserDTO> findUserListByPage(UserDTO userDTO, Pageable pageable){
List<Object> args = new ArrayList<>();//用于封装参数
StringBuffer sql = new StringBuffer();
sql.append(
" select u.id,u.name,u.age,u.birthday,u.mobile,u.email,a.country,a.province,a.city,"
+ " u.create_date "
+ " from tb_user u "
+ " left join tb_address a on u.address_id = a.id "
+ " where 1=1 "
);
if(userDTO != null){
if(StringUtils.isNotBlank(userDTO.getName())){
//模糊查询
sql.append(" and u.name like ? ");
args.add("%"+userDTO.getName()+"%");
}
if(null != userDTO.getAge()){
//精确查询
sql.append(" and u.age = ? ");
args.add(userDTO.getAge());
}
//求生日在某个时间段范围内的用户
if(null != userDTO.getStartTime()){
//大于等于
sql.append(" and u.birthday >= ? ");
args.add(userDTO.getStartTime());
}
if(null != userDTO.getEndTime()){
//小于等于
sql.append(" and u.birthday <= ? ");
args.add(userDTO.getEndTime());
}
if(userDTO.getAddressDTO() != null) {
AddressDTO addressDTO = userDTO.getAddressDTO();
//查询某个城市的用户
if(StringUtils.isNotBlank(addressDTO.getCity())) {
sql.append(" and a.city = ? ");
args.add(addressDTO.getCity());
}
}
}
//按照创建时间倒序排序
sql.append(" order by u.create_date desc ");
//创建query对象
Query query = entityManager.createNativeQuery(sql.toString());
//设置查询参数
if(args.size()>0){
for(int i=0;i<args.size();i++){
//注意：jpa的setParameter是从1开始的
query.setParameter(i+1,args.get(i));
}
}
PageBean pageBean = findPageBean(sql.toString(), args, pageable);
//分页查询
query.setFirstResult(pageBean.getPageNumber()*pageBean.getPageSize());
query.setMaxResults(pageBean.getPageNumber()*pageBean.getPageSize()+pageBean.getPageSize());
List<Object[]> resultList = query.getResultList();
//封装查询结果
List<UserDTO> userDTOList = new ArrayList<>();
if(resultList != null && resultList.size() > 0) {
resultList.forEach(objs -> {
UserDTO userDTONew = UserDTO.builder()
.id(((BigInteger)objs[0]).longValue())
.name((String) objs[1])
.age(((Short) objs[2]).intValue())
.birthday((Date) objs[3])
.mobile((String) objs[4])
.email((String) objs[5])
.addressDTO(
AddressDTO.builder()
.country((String) objs[6])
.province((String) objs[7])
.city((String) objs[8])
.build()
)
.createDate((Date) objs[9])
.build();
//添加到列表中
userDTOList.add(userDTONew);
});
}
return new PageRecord<UserDTO>(pageBean,userDTOList);
}
//查询分页信息
PageBean findPageBean(String sql,List<Object> args,Pageable pageRequest){
String countSql = " select count(*) from (" + sql + ") tb ";
Query countQuery = entityManager.createNativeQuery(countSql);
if(args != null && args.size()>0){
for(int i=0;i<args.size();i++){
countQuery.setParameter(i+1,args.get(i));
}
}
//查询总记录数
long totalCount = ((BigInteger)countQuery.getSingleResult()).longValue();
return new PageBean(pageRequest.getPageNumber(),pageRequest.getPageSize(),totalCount);
}
}
说大体思路，首先使用StringBuffer 来动态拼接查询条件，使用List来封装所有的查询参数，根据sql语句查询总记录数，然后计算出分页信息，再通过setFirstResult和setMaxResults设置查询记录的起始位置，最后通过循环将查询的结果集封装到对象中。
下面写个单元测试验证一下，
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.PageRecord;
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 {
@Test
public void findUserListByPage2Test() throws Exception {
UserDTO userDTO = UserDTO.builder()
.name("诸")
//.age(35)
//.startTime(dateFormat.parse("2001-09-16 08:00:00"))
//.endTime(dateFormat.parse("2008-09-16 07:00:00"))
.addressDTO(AddressDTO.builder()
.city("武汉")
.build())
.build();
//注意：jpa的分页是从0开始的
PageRecord<UserDTO> userListByPage2 = userService
.findUserDTOListByPage(userDTO, PageRequest.of(0, 15));
System.out.println("分页信息：");
System.out.println("总记录数："+userListByPage2.getTotalCount()+",总页数："+userListByPage2.getTotalPages());
System.out.println("页码："+(userListByPage2.getPageNumber()+1)+",每页条数："+userListByPage2.getPageSize());
List<UserDTO> content = userListByPage2.getData();
content = null == content? new ArrayList<>() : content;
content.forEach(user->System.out.println(user));
}
}
运行一下单元测试，结果如下：
分页信息：
总记录数：1,总页数：1
页码：1,每页条数：15
UserDTO(id=3, name=诸葛亮, age=54, mobile=158989989, email=zhu@qq.com, birthday=2001-09-16 08:00:00.0, addressDTO=AddressDTO(id=null, country=中国, province=湖北, city=武汉), createDate=2019-09-06 05:50:01.0, modifyDate=null, startTime=null, endTime=null)
至此，springDataJpa基于EntityManager原生sql多表联合查询+动态条件查询+分页就介绍完了，有需要源码的朋友，请到git上下载源码，源码地址：https://github.com/hgq0916/springdatajpa-study.git。java学习交流群：184998348,欢迎大家一起交流学习。
3941
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
