Spring Data Jpa自定义关联查询

Spring Data Jpa自定义关联查询，可以使用原生SQL，也可以使用JPQL语法。
原生SQL返回数组对象List<Object[]>；JPQL返回自定义对象List

ViewUserInfo.java

/**
 * 自定义实体
 */
public class ViewUserInfo {
	//table: user info
	private Integer id;
    private String name;
    private String username;
    //table: role
    private long roleId;
    //table: permission
    private String url;
    private String permission; 
    
    public ViewUserInfo(){
    }
    
    public ViewUserInfo(int userId,String username,String name,
    				long roleid,String url,String permission){
    	this.id=userId;
    	this.username=username;
    	this.name=name;
    	this.roleId= roleid;
    	this.url=url;
    	this.permission=permission;
    }    
	
	set,get...

}

JpaUserRepository.java

//查询方法定义 SQL
@Query(value = "select "
+" a.id as userId,a.username,a.name,b.role_id as roleId,d.url,d.permission "
+" from user_info a, sys_user_role b, sys_role_permission c,sys_permission d "
+" where a.id=b.u_id and b.role_id=c.role_id and c.permission_id=d.id",
	nativeQuery = true)//原生SQL
public List<Object[]> findByNameSQL(String name);
//查询方法定义 JPQL
@Query(value = "select new com.test.demo.model.ViewUserInfo("
+" a.id,a.username,a.name,b.roleid,c.url,c.permission ) "
+" from JpaUser a, SysUserRole b, SysPermission c "
+" where a.id=b.uid and b.roleid=c.id")
public List<ViewUserInfo> findByName(String name);

JpaController.java

//原生SQL
@RequestMapping("/jpa/getSql")
public String getNameSql(@RequestParam String name) {
	List<Object[]> users = userRepository.findByNameSQL(name);
	for(Object[] objs : users){
		System.out.println("----");
		for(int i = 0,j=objs.length; i < j; i++){
			System.out.println("--"+objs[i]);
		}
	}
	return "/";
}
//JPQL
@RequestMapping("/jpa/getJpql")
public String getName(@RequestParam String name) {
	List<ViewUserInfo> users = userRepository.findByName(name);
	for(ViewUserInfo user : users){
		System.out.println("----");
		System.out.println("----"+user.getName());
		System.out.println("----"+user.getUrl());
	}
	return "/";
}

JPQL自定义语法中的对象是对应每个表的JOPO，不再逐一罗列
SysUserRole.java

@Entity
@Table(name = "sys_user_role")
public class SysUserRole {
	@Id
	private Integer id;
	@Column(name="role_id")
	private Long roleid;
	@Column(name="u_id")
	private Long uid;
	
	get,set...
}