英文:
How to get a HashMap result from Spring Data Repository using JPQL?
问题
我目前已经使用映射(Map)在用户(User),**商店(Shop)和角色(Role)**之间实现了一个三元关系。所以在我的用户实体(User Entity)中,我有这个映射:
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
}
Role.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;
// Getters and setters
}
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),该表具有user_fk、shop_fk和role_fk,这非常好。困难的部分是使用Spring Data JPA进行查询,并将结果作为HashMap返回,以以下方式获取给定用户的结果:对于登录的用户,返回HashMap<Shop, Role>。
在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)映射的三元关系来获取商店的列表和相应的角色值的结果呢?更多地是希望获得键值(key,value)的结果?
英文:
I've currently implemented a Ternary relationship using Map between User, Shop and Role.
So in my User Entity i have this mapping:
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,
}
Role.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.
}
This mapping gives me another table (user_shop_role) in the database that has user_fk,shop_fk and role_fk which is lovely.
The difficult bit is to make a Query using Spring Data JPA and get the result as a HashMap for a given user as this: HashMap<Shop,Role> for the logged in user.
At the UserRepository layer i have tried this:
interface UserRepository extends JpaRepository and i have this query...
@Query("SELECT u FROM User u join shopRoleMap m where key(m)= usEmail")
public HashMap<Shop, Role> findByUser(String email);
}
How can i get results for a ternary relationship mapped using a Map Collection to get a list of shops and corresponding role value. More of a key,value result ?
答案1
得分: 2
- 仓库方法。
@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
英文:
- Repository method.
@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);
}
- Generated query.
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=?
Update with above code plus another way
Github repo - https://github.com/kavi-kanap/stackoverflow-6313585
答案2
得分: 0
Spring Data JPA有一个名为属性表达式(Property Expressions)的功能,您可以通过它访问子实体或关联实体。
例如:
List<Shop> findByUser_email(String email);
更多详细信息请参阅此链接:
英文:
Spring Data JPA has a feature called Property Expressions where you can access the child or related entity
For example
List<Shop> findByUser_email(String email);
For more details refer to this
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论