SpringDataJPA笔记(13)-Union查询

SpringDataJPA笔记(13)-Union查询

在JPA中，对Union查询可以通过两种方式，一种是通过Inheritance的注解来实现，一种是通过子查询来实现，个人觉得子查询的方式更加灵活一点

来看具体的demo

首先是第一种通过Inheritance的注解

先设置一个基类，包含了要查询出来的属性，这个类并不会生成实际的表

需要注意一点 如果使用这个注解，id不能使用自增长，因为id在多个表中需要保证唯一性

@Data
@Entity
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
public abstract class UnionBaseEntity implements Serializable {

    private static final long serialVersionUID = 1846463590189371497L;
    @Id
    private Long id;

    @Column(name = "union_name")
    private String unionName;

    @Column(name = "union_age")
    private Integer unionAge;
}

然后是两张实际要union查询的表

@Data
@Entity
@EqualsAndHashCode(callSuper = true)
@Table(name = "union_one_tb")
public class UnionOneEntity extends UnionBaseEntity {

    @Column(name = "union_one")
    private String unionOne;

}

@Data
@Entity
@EqualsAndHashCode(callSuper = true)
@Table(name = "union_two_tb")
public class UnionTwoEntity extends UnionBaseEntity {

    @Column(name = "union_two")
    private String unionTwo;

}

然后配置相关的repository接口类

public interface UnionBaseRepository extends JpaRepository<UnionBaseEntity, Long>, JpaSpecificationExecutor<UnionBaseEntity>, Serializable {
}

public interface UnionOneRepository extends JpaRepository<UnionOneEntity, Long>, JpaSpecificationExecutor<UnionOneEntity>, Serializable {
}

public interface UnionTwoRepository extends JpaRepository<UnionTwoEntity, Long>, JpaSpecificationExecutor<UnionTwoEntity>, Serializable {
}

先添加几条数据，查看数据库，确实只有两张表

再查看两张表的数据

编写controller，查询数据

    @GetMapping("/list/base")
    public List<UnionBaseEntity> listBase() {
        return unionBaseRepository.findAll();
    }

从swagger页面调用listBase接口

这里会发现一个问题，id相同的两条记录被覆盖了，所以使用这个注解的时候，需要保证id的唯一性

第二种通过子查询的模式

@Data
@Entity
@Immutable
@Subselect("select concat(\'one_\', a.id) as id, a.union_name as union_name, a.union_age as union_age from union_one_tb a " +
        "union all select concat(\'two_\', b.id) as id, b.union_name as union_name, b.union_age as union_age from union_two_tb b ")
@Synchronize({"union_one_tb", "union_two_tb"})
public class UnionSubEntity implements Serializable {

    private static final long serialVersionUID = -3795682088296075408L;
    @Id
    private String id;

    @Column(name = "union_name")
    private String unionName;

    @Column(name = "union_age")
    private Integer unionAge;
}

public interface UnionSubRepository extends JpaRepository<UnionSubEntity, String>, JpaSpecificationExecutor<UnionSubEntity>, Serializable {
}

编写controller

    @GetMapping("/list/sub")
    public List<UnionSubEntity> listSub(){
        return unionSubRepository.findAll();
    }

从swagger页面调用listSub接口，查看数据

子查询的方式更为灵活，特别是对于联合查询的数据没有统一的唯一主键时

欢迎关注微信交流