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

huangapple go评论82阅读模式
英文:

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_fkshop_fkrole_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 = &quot;us_users&quot;)
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 = &quot;First name can not be empty&quot;)
@Column(name = &quot;usFname&quot;)
private String usFname;

@NotBlank(message = &quot;Username can not be left empty&quot;)
@Column(name = &quot;us_lname&quot;)
private String usLname;

@NotBlank(message = &quot;Email field can not be empty&quot;)
@ValidEmail
    @Column(name = &quot;usEmail&quot;, 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 = &quot;shop_fk&quot;)
    @JoinTable(name = &quot;user_shop_role&quot;,
            joinColumns = @JoinColumn(name = &quot;user_fk&quot;), inverseJoinColumns = @JoinColumn(name = &quot;role_fk&quot;))
    private Map&lt;Shop, Role&gt; shopRoleMap = new HashMap&lt;&gt;();
//GETTERS AND SETTERS,
}

Role.java

@Entity
@Table(name = &quot;ro_roles&quot;)
public class Role {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(nullable = false)
private int id;

@Column(name = &quot;rn_role_name&quot;)
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 = &quot;sh_shops&quot;)
public class Shop {
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(nullable = false)
private int id;

@NotBlank(message = &quot;Shop name can not be left blank&quot;)
private String sh_name;

@NotBlank(message = &quot;please provide shop icon&quot;)
private String sh_icon;

@NotBlank(message = &quot;please fill the shop description&quot;)
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(&quot;SELECT u FROM User u join shopRoleMap m where key(m)= usEmail&quot;)
     public HashMap&lt;Shop, Role&gt; 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&lt;User, Integer&gt; {
    
        @Query(&quot;SELECT key(m), value(m) FROM User u join u.shopRoleMap m&quot; +
                &quot; where u.usEmail = :email&quot;)
        List&lt;Object[]&gt; 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文档

示例示例

英文:

Spring Data JPA has a feature called Property Expressions where you can access the child or related entity

For example

List&lt;Shop&gt; findByUser_email(String email);

For more details refer to this

Spring Data JPA doc

Sample example

huangapple
  • 本文由 发表于 2020年7月28日 21:57:30
  • 转载请务必保留本文链接:https://go.coder-hub.com/63135853.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定