Picture.png

When Spring Data JPA queries the database, if the two tables are related, set a foreign key. When querying, use Specification to create a Join query. But it only supports left connection, not right connection. Although left-right connection can achieve the same effect in turn, it is related to who is whose foreign key. Sometimes foreign keys are really troublesome. So in order to query a view, no better way was found, so we had to query and merge it twice in the service layer.

@Entity
    @Table(name="tb_user")
    public class UserInfo implements Serializable{
     
        @Id
        @GeneratedValue(strategy=GenerationType.IDENTITY)
        private Long userId;
        private String userName;
        private String password;
        private String name;
        private int 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")
    public class Address implements Serializable{
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long 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
    }

public class ViewInfo implements 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
    }

Next, write a custom query statement in the DAO layer:

public interface UserInfoRepository extends CrudRepository<UserInfo, Long>{
        @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<ViewInfo> 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<ViewInfo> 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<ViewInfo> findViewInfoRight();
     
    }

Then query each part in the service layer:

public void summary(){
          System.out.println("=======middle part=======");
          List<ViewInfo> userInfos = userInfoRepository.findViewInfo();
          for(ViewInfo item : userInfos){
              System.out.println(item.getUserInfo().getUserName()+" "+item.getAddress().getCity());
          }
          System.out.println("=======left part=======");
          List<ViewInfo> userInfoLeft = userInfoRepository.findViewInfoLeft();
          for(ViewInfo item : userInfoLeft){
              System.out.println(item.getUserInfo().getUserName()+" "+item.getAddress().getCity());
          }
          System.out.println("=======right part=======");
          List<ViewInfo> userInfoRight = addressRepository.findViewInfoRight();
          for(ViewInfo item : userInfoRight){
              System.out.println(item.getUserInfo().getUserName()+" "+item.getAddress().getCity());
          }
     
        }

In the database, select the Inner Join intersection, Outer Join Union, Left Join select the difference set plus intersection between the left table and the right table, and Right Join select the difference set plus intersection between the right table and the left table. For the moment. If anyone has a better way to read my article, please let me know.