spring data jpa 关联join查询出自定义实体java bean的坑

ゞ浴缸里的玫瑰 2022-02-17 18:17 423阅读 0赞

repository接口如下：

package cn.detao.repository;
    
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.jpa.repository.Query;
    
    import cn.detao.domain.Role;
    import cn.detao.domain.User;
    import java.lang.String;
    import java.util.List;
    
    public interface UserRepository  extends JpaRepository<User, Integer>  {
    	
    	User findByName(String name);
    
    	User findByUsername(String username);
    	
    	@Query(value = "SELECT new cn.detao.domain.Role(r.id,r.name,r.nameZh) FROM User u INNER JOIN UserRole ur ON u.id = ur.userId INNER JOIN Role r ON ur.roleId = r.id WHERE u.id = ?1")
    	List<Role> findRolesByUser(Integer UserId);
    
    }

role实体：

package cn.detao.domain;
    
    import javax.persistence.Column;
    import javax.persistence.Entity;
    import javax.persistence.GeneratedValue;
    import javax.persistence.GenerationType;
    import javax.persistence.Id;
    
    import lombok.Data;
    
    @Entity
    @Data
    public class Role {
    
    	@Id // 主键
    	@GeneratedValue(strategy = GenerationType.IDENTITY) // 自增长策略
    	private Integer id;
    
    	@Column
    	private String name;
    	@Column
    	private String nameZh;
    
    	public Role(Integer id, String name, String nameZh) {
    		super();
    		this.id = id;
    		this.name = name;
    		this.nameZh = nameZh;
    	}
    	
    	public Role() {
    		
    	}
    
    }

# **注意大坑：** #

SELECT new cn.detao.domain.Role(r.id,r.name,r.nameZh) FROM User u INNER JOIN UserRole ur ON u.id = ur.userId INNER JOIN Role r ON ur.roleId = r.id WHERE u.id = ?1

**查询语句中的所有表名必须是 javaBean 实体名，不能是mysql表名；字段名也必须是javaBean实体里的属性名，不能是mysql表里的字段名。**