Problem with Spring Data JPA @Query Annotation (error in SQL syntax)

Question

EDIT: I want to check if a certain user has a privilege in a group. If there is a better solution without querying all his privileges please tell me.

The following query gives me one error I couldn't understand:

@Repository
public interface PrivilegeRepository extends JpaRepository<Privilege, Long> {

    @Query("select p from Privilege p " +
            "inner join p.userTypes " +
            "inner join UserGroup u " +
            "where u.user.id=:uid and u.group.id=:gid")
    List<Privilege> getPrivilegesOfUser(@Param("uid") Long uid, @Param("gid") Long gid);

}

error:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where usergroup1_.user_id=28 and usergroup1_.group_id=18' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_144]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_144]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_144]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_144]

UserGroup entity

@Entity
@Table(name = "user_to_group")
public class UserGroup {

    @EmbeddedId
    private UserGroupId id;

    @ManyToOne(fetch = FetchType.LAZY,
            cascade = {CascadeType.DETACH, CascadeType.MERGE,
                    CascadeType.REFRESH, CascadeType.PERSIST})
    @MapsId("userId")
    @JoinColumn(name = "user_id", insertable = false, updatable = false)
    private User user;

    @ManyToOne(fetch = FetchType.LAZY,
            cascade = {CascadeType.DETACH, CascadeType.MERGE,
                    CascadeType.REFRESH, CascadeType.PERSIST})
    @MapsId("groupId")
    @JoinColumn(name = "group_id", insertable = false, updatable = false)
    private Group group;

    @ManyToOne(fetch = FetchType.LAZY,
            cascade = {CascadeType.DETACH, CascadeType.MERGE,
                    CascadeType.REFRESH, CascadeType.PERSIST})
    @JoinColumn(name = "user_type_id")
    private UserType userType;

    @Column(name = "is_blocked",
    insertable = false)
    private boolean isBlocked = false;

I tried first the equivalent query in the MYSQL Workbench and worked just fine. Nothing with Jpa repository implementation. This is the code that works on MYSQL:

select p.name from privilege p 
    inner join user_type_to_privilege utp 
        on p.id=utp.privilege_id
    natural join user_to_group utg
    where utg.user_id=16 and
        utg.group_id=9;

This is the dialect I use: spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQLDialect Is this outdated or something?

Your JPQL query parses and compiles to an invalid MySQL query, which it shouldn't. Turn on logging of org.hibernate.SQL to trace level to get the generated query and file a bug report at Hibernate's JIRA. If it's an invalid JPQL query (for maybe not having explicit join criteria), then it should fail before reaching the database. — coladict
– coladict, Commented Oct 26, 2018 at 14:03

Robet · Accepted Answer · 2018-10-26 13:07:24Z

1

The problem was on the second inner join. It was ambiguous and didn't know which column to match for the join. Query that works:

@Query("select p from Privilege p " +
            "inner join p.userTypes t " +
            "inner join UserGroup u on t.id=u.userType.id "+
            "where u.user.id=:uid and u.group.id=:gid")
    List<Privilege> getPrivilegesOfUser(@Param("uid") Long uid, @Param("gid") Long gid);

answered Oct 26, 2018 at 13:07

Robet

792 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Problem with Spring Data JPA @Query Annotation (error in SQL syntax)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related