Spring Data Jpa 返回自定义对象(实体部分属性、多表联查)

ycyin大约 4 分钟Spring Data JpaSpring Data JpaSpring Boot

应用场景

  在Spring data jpa中,一般都是直接返回一个实体或者List<实体>或者Page<实体>,这里的实体一般就是与数据库对应的实体类,就像下面这样:

@Repository
public interface IUserDao extends JpaRepository<User, String>, JpaSpecificationExecutor<User>,
        PagingAndSortingRepository<User, String>, Serializable {
    @Override
    Page<User> findAll(Pageable pageable);
    @Override
    List<User> findAll();
    @Override
    User save(User u);
}    

在某些应用场景下,比如我只返回或者怎么操作呢?

本篇文章以两个关联的实体类(User和Score)多表联查(join)t_user表中的id和user_name字段、t_score表中的score字段为例,这两个实体类在文章末尾提供。

封装字段

首先需要将返回的三个字段(t_user表中的id和user_name字段、t_score表中的score字段)封装为一个对象(TestView),这个对象不需要加任何注解。

public class TestView {
    private String id;
    private String name;
    private Integer score;
    public TestView() {
    }
    public TestView(String id, String name, Integer score) {
        this.id = id;
        this.name = name;
        this.score = score;
    }
    @Override
    public String toString() {
        return "TestView{" +
                "id='" + id + '\'' +
                ", name='" + name + '\'' +
                ", score=" + score +
                '}';
    }
}

实现查询

参考:Spring Data JPA - Reference Documentationopen in new window

Spring Data Jpa 复杂查询总结 (多表关联 以及 自定义分页 )open in new window

Spring Data Jpa多表查询返回自定义实体open in new window

HQL查询实体部分属性open in new window

方法一、使用HQL语句

spring data jpa接口中的方法:

@Query(value="select new com.test.ycyin.entity.TestView(t1.id,t1.userName,t2.score)               from User t1 Left Join Score t2 on t1.id = t2.userId")
Page<TestView> findUserAndScore(Pageable pageable);

从上面的SQL中可以看出我们需要t1.id,t1.userName,t2.score这三个字段,我们还可以只查询TestView类中的两个字段,就像下面这样:

@Query(value="select new com.test.ycyin.entity.TestView(t1.id,t1.userName) from User               t1 Left Join Score t2 on t1.id = t2.userId")
Page<TestView> findUserAndScore(Pageable pageable);

同时需要在TestView类中提供相应字段的构造方法。这样返回的值只有两个,其它字段则是NULL。你可以将两个表的字段都封装在一个类中,然后生成多个构造方法,今后查询的时候需要哪几个字段就使用哪个构造方法。

测试方法:

Sort.Order order = new Sort.Order(Sort.Direction.ASC, "id").nullsLast();// 排序规则
Pageable pageable = PageRequest.of(0,10, Sort.by(order)); // 分页规则
List<TestView> content1 = userDao.findUserAndScore(pageable).getContent(); // 获取List
content1.forEach(u->{ System.out.println(u.toString()); }); 

另外,接口中可以直接返回List<TestView>也是可以的,见下:

@Query(value="select new com.test.ycyin.entity.TestView(t1.id,t1.userName) from User               t1 Left Join Score t2 on t1.id = t2.userId")
List<TestView> findUserAndScore2(Pageable pageable);

方法二、使用原生SQL

多表联查中,使用原生SQL的方式只能返回Object[]类型,需要我们手动去转。

spring data jpa接口中方法:

@Query(value="select t1.id as id,t1.user_name as name ,t2.score as score from t_user                   t1 Left Join t_score t2 on t1.id = t2.user_id", nativeQuery = true)
Page<Object[]> findUserAndScore(Pageable pageable);

从上面的SQL中可以看出我们只需要t_user表中的id和user_name字段、t_score表中的score字段,并且使用了left join来联表查询。

转换方法:

    //转换实体类
    public static <T> List<T> castEntity(List<Object[]> list, Class<T> clazz) throws Exception {
        List<T> returnList = new ArrayList<T>();
        if(CollectionUtils.isEmpty(list)){
            return returnList;
        }
        Object[] co = list.get(0);
        Class[] c2 = new Class[co.length];
        //确定构造方法
        for (int i = 0; i < co.length; i++) {
            if(co[i]!=null){
                c2[i] = co[i].getClass();
            }else {
                c2[i]=String.class;
            }
        }
        for (Object[] o : list) {
            Constructor<T> constructor = clazz.getConstructor(c2);
            returnList.add(constructor.newInstance(o));
        }
        return returnList;
    }

测试方法:

Sort.Order order = new Sort.Order(Sort.Direction.ASC, "id").nullsLast();// 排序规则
Pageable pageable = PageRequest.of(0,10, Sort.by(order)); // 分页规则
Page<Object[]> all = userDao.findUserAndScore(pageable);// 返回Page<Object[]>
List<Object[]> content = all.getContent();// 获取返回的数据
List<TestView> testViews = castEntity(content, TestView.class); //转换objcet[] to TestView
System.out.println(testViews.toString());

附录:

这里贴上两个实体类(省略toString()、getters和setters):

User:

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

	/**
	 * id:代表唯一的记录
	 */
	@Id
	@Column(name = "id", columnDefinition = "varchar(36) comment'ID,UUID生成' ")
	private String id;

	/**
	 * 用户姓名
	 */
	@Column(name = "user_name", columnDefinition = "varchar(255) comment'用户姓名' ", nullable = false)
	private String userName;

	/**
	 * 用户密码
	 */
	@Column(name = "user_pass", columnDefinition = "varchar(255) comment'用户密码' ", nullable = false)
	private String userPass;

	/**
	 * gitlab账号
	 */
	@Column(name = "gitlab_acc", columnDefinition = "varchar(30) comment'gitlab账号' ")
	private String gitlabAcc;

	/**
	 *  电话
	 */
	@Column(name = "phone_number", columnDefinition = "char(11) comment'电话' ")
	private String phoneNumber;

	/**
	 * 邮箱
	 */
	@Column(name = "email_addr", columnDefinition = "varchar(30) comment'邮箱' ")
	private String emailAddr;
}

Score:

@Entity
@Table(name = "t_score")
@DynamicUpdate()
public class Score {
    /**
     * id:代表唯一的记录
     */
    @Id
    @Column(name = "id", columnDefinition = "varchar(36) comment'ID,UUID生成' ")
    private String id;
    /**
     * userId
     */
    @Column(name = "user_id", columnDefinition = "varchar(36) comment'用户表ID' ")
    private String userId;
    /**
     * 分数
     */
    @Column(name = "score", columnDefinition = "int(8) comment'分数' ")
    private Integer score;

    /**
     * 学分
     */
    @Column(name = "credit", columnDefinition = "int(8) comment'学分' ")
    private Integer credit;
}