Spring Jpa多表查询

Spring Jpa多表查询

1.创建两个Entity,SysUserEntity 和SysUserPointEntity，代码如下

     //SysUserEntity  
    @Entity(name = "SysUser")
    @Table(name = "SysUser")
    public class SysUserEntity  implements Serializable {
        private int id;
        private Integer parentUserId;
        private String userCode;
        private String userName;
        private String password;
        private String phone;
        private Boolean active;
        private Integer roleId;
        private Integer clientId;
        private Byte userType;
        private Integer loginNum;
        private Timestamp lastLoginTime;
        private String memo;
        private String modifyMan;
        private Timestamp modifyTime;
        private String phoneMac;
        //省略get/set方法





   //SysUserPointEntity
   @Entity
	@Table(name = "SysUserPoint")
	public class SysUserPointEntity {
	    private int id;
	    private int userId;
	    private String address;
	    private Double longitude;
	    private Double latitude;


 //UserInfoVO 
@Data
@AllArgsConstructor
public class UserInfoVO implements Serializable {
    private SysUserEntity sysUserEntity;
    private SysUserPointEntity sysUserPointEntity;
}

2.创建SysUserRepository，@Query中sql查询的是实体类型，而不是数据库表

@Repository
public interface SysUserRepository extends JpaRepository<SysUserEntity,Integer> {
    @Query(value = "SELECT new com.cy.cloudoa.userservice.entity.sys.user.vo.UserInfoVO(u, a) FROM com.cy.cloudoa.userservice.entity.sys.SysUserEntity u, com.cy.cloudoa.userservice.entity.sys.SysUserPointEntity a WHERE u.id = a.userId")
     List<UserInfoVO> findViewInfo();

}

3.创建SysUserServiceImpl

@Service
public class SysUserServiceImpl implements SysUserService {
    @Autowired
   private SysUserRepository sysUserRepository;

    @Override
    public SysUserEntity getById(Integer id) {

        return sysUserRepository.findById(id).get();
    }

    @Override
    public  List<UserInfoVO> get() {
        List<UserInfoVO> list =  sysUserRepository.findViewInfo();
        list.forEach( k->{
            System.out.print(k.getSysUserEntity().getUserName());
        });
        return list;
    }
}

4.创建SysUserService

public interface SysUserService {
    SysUserEntity getById(Integer id);

    List<UserInfoVO> get();
}

5.创建SysUserController

@RestController
@RequestMapping("/sys/user")
public class SysUserController {
    @Autowired
     private SysUserService sysUserService;
     @GetMapping("/get/{id}")
     public SysUserEntity getById(@PathVariable Integer id){
         return sysUserService.getById(id);
     }

     @GetMapping("/get")
     public List<UserInfoVO> get(){
         return sysUserService.get();
     }
}

6.运行结果