如何使用JPQL从Spring Data Repository中获取HashMap结果

Question

我目前已经使用Map在用户、商店和角色之间实现了三元关系。因此，在我的用户实体中，我有这样的映射：

User.java

@Entity
@Table(name = "us_users")
public class User implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(nullable = false)
private int id;
@Basic(fetch = FetchType.LAZY, optional = false)
private String uuid;
@NotBlank(message = "First name can not be empty")
@Column(name = "usFname")
private String usFname;
@NotBlank(message = "Username can not be left empty")
@Column(name = "us_lname")
private String usLname;
@NotBlank(message = "Email field can not be empty")
@ValidEmail
@Column(name = "usEmail", unique = true)
@Basic(fetch = FetchType.LAZY, optional = false)
private String usEmail;
//this is the actual mapping
//a user can have a list of shops, but one role assigned for each shop
@ManyToMany
@MapKeyJoinColumn(name = "shop_fk")
@JoinTable(name = "user_shop_role",
joinColumns = @JoinColumn(name = "user_fk"), inverseJoinColumns = @JoinColumn(name = "role_fk"))
private Map<Shop, Role> shopRoleMap = new HashMap<>();
//GETTERS AND SETTERS,
}

角色.java

@Entity
@Table(name = "ro_roles")
public class Role {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(nullable = false)
private int id;
@Column(name = "rn_role_name")
private String roleName;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getRoleName() {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}

}

Shop.java

@Entity
@Table(name = "sh_shops")
public class Shop {
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(nullable = false)
private int id;
@NotBlank(message = "Shop name can not be left blank")
private String sh_name;
@NotBlank(message = "please provide shop icon")
private String sh_icon;
@NotBlank(message = "please fill the shop description")
private String sh_description;
@NotBlank
private String sh_tag;
//setters, getters, equals, hashCode methods.
}

这个映射为我提供了数据库中的另一个表(user_shop_role(，该表具有username_fk、

shop_fkole_fk在UserRepository层，我尝试过这个：

接口UserRepository扩展了JpaRepository，我有这个查询。。。

@Query("SELECT u FROM User u join shopRoleMap m where key(m)= usEmail")
public HashMap<Shop, Role> findByUser(String email);

}

如何获取使用Map Collection映射的三元关系的结果，以获取商店列表和相应的角色值。更像是一个关键的、有价值的结果？

Answer 1

• 存储库方法

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

@Query("SELECT key(m), value(m) FROM User u join u.shopRoleMap m" +
" where u.usEmail = :email")
List<Object[]> findByUserEmail(String email);

}

• 生成的查询

select
shop3_.id as id1_1_0_,
role2_.id as id1_0_1_,
shop3_.sh_description as sh_descr2_1_0_,
shop3_.sh_icon as sh_icon3_1_0_,
shop3_.sh_name as sh_name4_1_0_,
shop3_.sh_tag as sh_tag5_1_0_,
role2_.rn_role_name as rn_role_2_0_1_ 
from
us_users user0_ 
inner join
user_shop_role shoprolema1_ 
on user0_.id=shoprolema1_.user_fk 
inner join
ro_roles role2_ 
on shoprolema1_.role_fk=role2_.id 
inner join
sh_shops shop3_ 
on shoprolema1_.shop_fk=shop3_.id 
where
user0_.us_email=?

使用上述代码和其他方式更新

Github回购-https://github.com/kavi-kanap/stackoverflow-6313585

Answer 2

Spring Data JPA有一个名为属性表达式的功能，您可以在其中访问子实体或相关实体

例如

List<Shop> findByUser_email(String email);

有关更多详细信息，请参阅本

春季数据JPA-doc

示例