8

I have an association manyToMany between User and Role entities (User >---< Role)

I wanted to perform this query:

createQuery()
        .from(qUser)
        .leftJoin(qUser.roles, qRole)
        .where(qUser.login.eq(login))
        .singleResult(
                Projections.bean(User.class,
                        qUser.id,
                        qUser.login,
                        qUser.password,
                        GroupBy.set(Projections.bean(Role.class,
                                qRole.id,
                                qRole.code
                        )).as(qUser.roles)
                )
        );

The generated query looks like this, for me it's perfect:

SELECT user0_.ID AS col_0_0_,
       user0_.LOGIN AS col_1_0_,
       user0_.PASSWORD AS col_2_0_,
       role2_.ID AS col_4_0_,
       role2_.CODE AS col_5_0_
FROM public.USER user0_
LEFT OUTER JOIN public.USER_ROLE roles1_ ON user0_.ID=roles1_.USER_ID
LEFT OUTER JOIN public.ROLE role2_ ON roles1_.ROLE_ID=role2_.ID
WHERE user0_.LOGIN=? LIMIT ?

But I have a java.lang.IllegalArgumentException: argument type mismatch.

I debugged and I found out that data from database id loaded without problem. This is when QueryDsl/Hibernate did some introspection to create and initialise my entities that the exception is throwed.

The problem is that the User.setRoles(Set<Role>) method has called with a long parameter: The ID of the first Role entity list of the User. Instead of create a Set of Role an then associate these roles to the User.

Is there a problem with the query? Or is it not supported by QueryDsl?

I am using the QueryDsl 3.6.6 (I tested with 3.7.4: same result)

Thibaud Sowa
  • 402
  • 4
  • 19
  • As the error message clearly say - you are comparing mismatched types. Probably one of these conditions : `public.USER_ROLE roles1_ ON user0_.ID=roles1_.USER_ID` or `public.ROLE role2_ ON roles1_.ROLE_ID=role2_.ID` – sagi Oct 26 '16 at 07:43
  • Actually when I run the query, datas are loading perfectly. The problem came from the the entity/data mapping – Thibaud Sowa Oct 26 '16 at 07:55
  • Coming from php,but I don`t think you can parametrize LIMIT – Mihai Nov 16 '16 at 09:28
  • It is JPQL query not pure SQL – Thibaud Sowa Nov 16 '16 at 09:31
  • since you are expecting multiple roles for a given user can you try with .as(set(qUser.roles)). http://www.querydsl.com/static/querydsl/3.1.1/reference/html/ch03s02.html#d0e1634 – Gopi Kolla Nov 22 '16 at 04:58
  • Thank you, but the `.as()` method from the exemple came from `.transform()`. I'm using projection, not transformation. My `.as()` method has not the same signature. ( http://www.querydsl.com/static/querydsl/3.6.6/apidocs/com/mysema/query/group/GroupByBuilder.html#as-com.mysema.query.types.FactoryExpression- vs http://www.querydsl.com/static/querydsl/3.6.6/apidocs/com/mysema/query/group/AbstractGroupExpression.html#as-com.mysema.query.types.Path- ) – Thibaud Sowa Nov 22 '16 at 14:09
  • Not able to understand you statement ** I debugged and I found out that the User.setRoles(Set) method has called with a long parameter: The ID of the first Role entity list of th user** – Sagar Nov 23 '16 at 09:25
  • I have edited the question to clarify, I hope it will be more understandable. – Thibaud Sowa Nov 23 '16 at 10:59

2 Answers2

0

I guess that the java.lang.IllegalArgumentException: argument type mismatch are thrown not by the JOIN comparing and you can verify that by verifing the ID type of the 3 tables UTILISATEUR, ROLE and USER_ROLE.

  1. If there is a difference on type between

UTILISATEUR.ID=USER_ROLE.USER_ID

or

USER_ROLE..ROLE_ID=ROLE.ID

so that's the probleme.

  1. If there is not a problem , the exception is thrown by the test of equality of the login.

But, I suspect that the USER_ROLE join table did not connect with the correct table. You may have two tables USER and UTILISATEUR . Unless you renamed the join table.

bilelovitch
  • 1,975
  • 1
  • 16
  • 24
  • Sorry there is no UTILISATEUR table, I wanted to simplified the example... I corrected the example query. The problem doesn't come from the query but from the data binding between the query data and my entities. – Thibaud Sowa Nov 23 '16 at 08:57
  • because the LEFT JOIN performs a join starting with the first table and then any matching second table records. And according to the association rules of the tables the generated query it's perfect. What is missing is a btree or hash index on the login (if it was not created). – bilelovitch Nov 23 '16 at 10:11
  • I don't see any relation between my problem and the login – Thibaud Sowa Nov 23 '16 at 10:32
0

I have had same error as you, I try different ways and it took a lot of time.Eventually I founded this way, My classes are:

  • Lesson
  • LessonScores for saving user scores
  • LessonScoresModel for returning data and
  • LessonScoresModel.ScoresModel is a static nested class

I need some information about user scores in a class. I use below query for returning data. I use exact related models for retuning data but it's gives the beatiful "argument type mismatch" error. So I developed a static class for returning data.

JPAQuery query = new JPAQuery(em);
    //select from which model you need
        query.from(QLessonScores.lessonScores);
    //use your condition
        query.where(predicate);
    //use query and returning data
        Map<Lesson,List<LessonScoresModel.ScoresModel>> map = (Map<Lesson,List<LessonScoresModel.ScoresModel>>) 
        //use tranform for making group by
                query.transform(GroupBy.groupBy(QLessonScores.lessonScores.lesson).as(
            //return list of data which column we need
                    GroupBy.list(Projections.fields(LessonScoresModel.ScoresModel.class,
                                    QLessonScores.lessonScores.score.as("score"),
                                    QLessonScores.lessonScores.scoresType.as("scoresType"),
                                    QLessonScores.lessonScores.success.as("success")
                ))
            ));

you will find more information in these links, codata programcreek

Mostafa Hosseinzadeh
  • 91
  • 4