How to get a HashMap result from Spring Data Repository using JPQL?

Question

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 ?

trying getting ID of Shop and Role from the mapping table that JPA generates. Then, using these ids, get their object entity and put them in your HashMap.You cannot directly query and add results to HashMap. — Ayush28
If the relationship is map correctly and working. Did you try to do "SELECT m FROM ..." instead of "SELECT u FROM". So you would be returning the relation this way (m), not the user object (u). — Brother
@User-Upvotedon'tsayThanks , you don't know what is ternary relationship... you don't know the use case. IMHO you are just ranting... — Fred Kibuchi
@User-Upvotedon'tsayThanks please find my use case here for the deduced Mapping: [stackoverflow.com/questions/62793067/… — Fred Kibuchi

Kavithakaran Kanapathippillai Kavithakaran Kanapathippillai · Accepted Answer · 2020-07-29T07:13:47

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

How to get a HashMap result from Spring Data Repository using JPQL?

2 Answers