getting Caused by: org.hibernate.QueryException: cannot dereference scalar collection element: roles

Question

I have this class , and i'am trying to acess mobileNumbers and roles from the user table , but being lazy intialized i keep getting 'lazy intialization error.

I do not wan't to remove the LAZY fetch as complete access of the object is rarely required , but is required. So to overcome it first i tried to add @Transactional but going by this article https://codete.com/blog/5-common-spring-transactional-pitfalls/ it seems a bad approch in my case, i tried using join fetch but it keeps giving multiplebagfetchexception ,hence i tried to fetch them one at a time ( Element Collections i mean)

with this repository class

public interface UserRespository extends JpaRepository<UserDao, Long> {

    Optional<UserDao> getByUserNameIgnoreCase(String userName);
//    Optional<UserDao> findByUserNameIgnoreCase(String userName);

    @Query(value = "select dao.roles.roles from UserDao dao inner join dao.roles r on dao.userName in elements(r.userName) and upper(dao.userName) = upper(?1)")
    Object getByUserNameIgnoreCaseComplete2(String userName);
}

then i get his error

Caused by: org.hibernate.QueryException: cannot dereference scalar collection element: roles
    at org.hibernate.persister.collection.ElementPropertyMapping.toType(ElementPropertyMapping.java:33) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
    at org.hibernate.persister.collection.AbstractCollectionPersister.toType(AbstractCollectionPersister.java:1644) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
    at org.hibernate.hql.internal.ast.tree.FromElementType.getPropertyType(FromElementType.java:396) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
    at org.hibernate.hql.internal.ast.tree.FromElement.getPropertyType(FromElement.java:515) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
    at org.hibernate.hql.internal.ast.tree.DotNode.getDataType(DotNode.java:682) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
    at org.hibernate.hql.internal.ast.tree.DotNode.prepareLhs(DotNode.java:265) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
    at org.hibernate.hql.internal.ast.tree.DotNode.resolve(DotNode.java:205) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
    at org.hibernate.hql.internal.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:114) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
    at org.hibernate.hql.internal.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:109) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
    at org.hibernate.hql.internal.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:104) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
    at org.hibernate.hql.internal.ast.tree.DotNode.resolveSelectExpression(DotNode.java:744) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
    at org.hibernate.hql.internal.ast.HqlSqlWalker.resolveSelectExpression(HqlSqlWalker.java:1057) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectExpr(HqlSqlBaseWalker.java:2295) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectExprList(HqlSqlBaseWalker.java:2232) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectClause(HqlSqlBaseWalker.java:1503) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:585) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:313) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:261) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:271) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:191) ~[hibernate-core-5.3.9.Final.jar:5.3.9.Final]
    ... 112 common frames omitted

I'm not sure why i keep getting this error.Is there any other way i can get the entire object??

Please help.

The actual class

@Entity
@Table(name = "users")
@Data
@NoArgsConstructor
public class UserDao implements Serializable {

    @Id
    @GeneratedValue
    private long id;

    @Column(name = "user_name", nullable = false, unique = true)
    private String userName;

    @Column(name = "password", nullable = false)
    private String password;

    @ElementCollection(targetClass = java.lang.String.class)
    @CollectionTable(name = "mobile_numbers_List",joinColumns = @JoinColumn(name = "user_name",referencedColumnName = "user_name"))
    @Column(name = "mobile_number")
    private List<String> mobileNumbers;

    @ElementCollection(targetClass = java.lang.String.class)
    @CollectionTable(name = "roles_list",joinColumns = @JoinColumn(name = "user_name",referencedColumnName = "user_name"))
    @Column(name = "roles")
    private List<String> roles;
}

Answer 1

This seems like a similar question you previous question , and the answer to the previous one and this one is same.

your query is wrong here

    @Query(value = "select dao.roles.roles from UserDao dao inner join dao.roles r on dao.userName in elements(r.userName) and upper(dao.userName) = upper(?1)")
    Object getByUserNameIgnoreCaseComplete2(String userName);

it's trying to fetch dao.roles.roles which if does not even exist.

here are some changes you should try

1. change dao.roles.roles to r (as you already created an alias , let's use it)
2. remove dao.userName in elements(r.userName) it's not required, spring does this automatically without even specifying it.
3. change the return type to list , it's ElementCollection i.e Collection of elements

this is how your final query would look like

    @Query(value = "select r from UserDao dao inner join dao.roles r on upper(dao.userName) = upper(?1)")
    List<String> getByUserNameIgnoreCaseComplete2(String userName);

Answer 2

Why did u comment out findByUserNameIgnoreCase ? findBy Worked for me Take a peek in this page

Answer 3

The Problem is the dao.roles.roles in your query. Change it to dao.roles. dao.roles in your case is a List<String> and a String does not have a property roles.