spring jpa @query返回自定义对象

1. 用jpa时，如果遇到多表关联，常需要自定义写sql语句，这时我们经常需要自定义返回对象
   例如用户和部门关系。我们希望查询用户信息，同时返回部门的名称。

案例代码

https://github.com/wengmingdong/wmdspringtest/tree/master/jparepcustomobj

2. 假设我们有以下orm
   部门

@Entity
@Table(name = "T_DEPT")
public class Dept implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    private Integer id;
    private String name;
    private Integer upId;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getUpId() {
        return upId;
    }

    public void setUpId(Integer upId) {
        this.upId = upId;
    }
}

用户

@Entity
@Table(name = "T_USER")
public class User implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    private Integer id;
    private String name;
    private Integer deptId;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getDeptId() {
        return deptId;
    }

    public void setDeptId(Integer deptId) {
        this.deptId = deptId;
    }
}

3. 我们定义了jpa接口DeptRepository和UserRepository
   DeptRepository

@Repository
public interface DeptRepository extends JpaRepository<Dept, Integer> {
}

UserRepository

@Repository
public interface UserRepository extends JpaRepository<User, Integer> {

    @Query("select new com.wmd.springtest.jparepcustomobj.domain.UserDept(" +
            "u.id, u.name, d.id, d.name" +
            ") " +
            "from User u, Dept d " +
            "where u.deptId=d.id")
    List<UserDept> findAllForUserDept();

    @Query("select new map(" +
            "u.id as user_id, u.name as user_name, d.id as dept_id, d.name as dept_name" +
            ") " +
            "from User u, Dept d " +
            "where u.deptId=d.id")
    List<Map<String, Object>> findAllForMap();
}

4. UserRepository 定义了两个方法，一个返回自定义对象，一个返回Map对象

5. UserDept类

public class UserDept implements Serializable {

    @JsonProperty("user_id")
    private Integer userId;

    @JsonProperty("user_name")
    private String userName;

    @JsonProperty("dept_id")
    private Integer deptId;

    @JsonProperty("dept_name")
    private String deptName;

    public UserDept(Integer userId, String userName, Integer deptId, String deptName) {
        this.userId = userId;
        this.userName = userName;
        this.deptId = deptId;
        this.deptName = deptName;
    }

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public Integer getDeptId() {
        return deptId;
    }

    public void setDeptId(Integer deptId) {
        this.deptId = deptId;
    }

    public String getDeptName() {
        return deptName;
    }

    public void setDeptName(String deptName) {
        this.deptName = deptName;
    }
}

6. 定义controller类

@RestController
public class UserDeptController {

    @Autowired
    UserService userService;

    @RequestMapping(value = "/rest/user/custom/rspobj", method = RequestMethod.GET, produces = "application/json")
    List<UserDept> findAllForUserDept() {
        return userService.findAllForUserDept();
    }

    @RequestMapping(value = "/rest/user/custom/map", method = RequestMethod.GET, produces = "application/json")
    List<Map<String, Object>> findAllForMap() {
        return userService.findAllForMap();
    }
}

7. 访问http://localhost:8080/rest/user/custom/rspobj，返回内容如下：

[
    {
        "user_id": 0,
        "user_name": "员工11",
        "dept_id": 1,
        "dept_name": "人事部"
    },
    {
        "user_id": 1,
        "user_name": "员工12",
        "dept_id": 2,
        "dept_name": "财务部"
    },
    {
        "user_id": 2,
        "user_name": "员工21",
        "dept_id": 2,
        "dept_name": "财务部"
    },
    {
        "user_id": 3,
        "user_name": "员工31",
        "dept_id": 3,
        "dept_name": "研发部"
    },
    {
        "user_id": 4,
        "user_name": "员工32",
        "dept_id": 3,
        "dept_name": "研发部"
    }
]

8. 访问http://localhost:8080/rest/user/custom/map，返回内容如下：

[
    {
        "dept_name": "人事部",
        "dept_id": 1,
        "user_id": 0,
        "user_name": "员工11"
    },
    {
        "dept_name": "财务部",
        "dept_id": 2,
        "user_id": 1,
        "user_name": "员工12"
    },
    {
        "dept_name": "财务部",
        "dept_id": 2,
        "user_id": 2,
        "user_name": "员工21"
    },
    {
        "dept_name": "研发部",
        "dept_id": 3,
        "user_id": 3,
        "user_name": "员工31"
    },
    {
        "dept_name": "研发部",
        "dept_id": 3,
        "user_id": 4,
        "user_name": "员工32"
    }
]

9. 源代码地址 https://github.com/wengmingdong/wmdspringtest/tree/master/jparepcustomobj