Spring Data JPA 多个实体类表联合视图查询

Spring Data JPA

查询数据库时，如果两个表有关联，那么就设个外键，在查询的时候用Specification创建Join

查询便可。但是只支持左连接，不支持右连接，虽说左右连接反过来就能实现一样的效果，但是这就关系到谁是谁的外键的问题。外键搞起来有时候确实麻烦。所以为了查询个视图，没有找到更好的办法，只好在service层查两次合并起来了。

    @Entity

    @Table(name="tb_user")

    publicclassUserInfoimplements Serializable{

        @Id

        @GeneratedValue(strategy=GenerationType.IDENTITY)

        private Long userId;

        private String userName;

        private String password;

        private String name;

        privateint age;

        private String sex;

        private String email;

        private Date dateOfBirth;

        private String telNumber;

        private String education;

        private String school;

    //    @ManyToOne

    //    @JoinColumn(name="addressId")

    //    private Address address;private Long addressId;

    // getter and setter}

    @Entity

    @Table(name="tb_address")

    publicclassAddressimplements Serializable{

        @Id

        @GeneratedValue(strategy = GenerationType.IDENTITY)

        privateLong addressId;private Long userId

        private String areaCode;

        private String country;

        private String province;

        private String city;

        private String area;

        private String detailAddress;

    // getter and setter

}

创建一个类包含UserInfo和Address中的所有属性：

publicclassViewInfoimplements Serializable{

        private UserInfo userInfo;

        private Address address;

        public ViewInfo(){

        }

        public ViewInfo(UserInfo userInfo){

            Address address =new Address();

            this.userInfo = userInfo;

            this.address = address;

        }

        public ViewInfo(Address address){

            UserInfo userInfo =new UserInfo();

            this.userInfo = userInfo;

            this.address = address;

        }

        public ViewInfo(UserInfo userInfo,Address address){

            this.userInfo = userInfo;

            this.address = address;

        }

    // getter and setter

}

接下来就是在DAO层中写自定义查询语句了：

publicinterfaceUserInfoRepositoryextendsCrudRepository{

        @Query(value="SELECT new com.demo.test.Entity.ViewInfo(u,a)FROM "        + " UserInfo u, com.demo.test.Entity.Address a WHERE u.addressId = a.id) ")

        List findViewInfo();

        @Query("SELECT new com.demo.test.Entity.ViewInfo"        + "(u) FROM UserInfo u WHERE u.addressId IS NULL OR u.addressId NOT IN (SELECT a.id FROM Address a)")

        List findViewInfoLeft();

        @Query("SELECT new com.demo.test.Entity.ViewInfo"        + "(a) FROM Address a WHERE a.id NOT IN (SELECT u.addressId FROM UserInfo u WHERE u.addressId IS NOT NULL)")

        List findViewInfoRight();

    }

作者：一位不著名的普通群众
链接：https://www.jianshu.com/p/31aa998b86ee