Spring JPA generating query with "(? in (.))"

Question

I have two classes mapped many to many: User and Topic. What I wanna do is to get all topics who has the provided user. Sometimes (that's the most weird part to me) it gives me this 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 '))' at line 1

My classes looks like

User.java

@Entity
@Table(name = "user")
public class User {

    /* ... */

    @JsonIgnore
    @LazyCollection(LazyCollectionOption.TRUE)
    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "favorite_topics", 
           joinColumns = @JoinColumn(name = "user_id"), 
           inverseJoinColumns = @JoinColumn(name = "topic_id"))
    private Set<Topic> favoriteTopics;

    /* ... */
}

Topic.java

@Entity
@Table(name = "topic")
public class Topic {

   /* ... */

    @JsonIgnore
    @LazyCollection(LazyCollectionOption.TRUE)
    @ManyToMany(mappedBy = "favoriteTopics", fetch = FetchType.LAZY)
    private Set<User> favoritedBy;

   /* ... */
}

TopicRepository.java

@Repository("topicRepository")
public interface TopicRepository extends JpaRepository<Topic, Integer> {

    /* ... */

    @Query("SELECT t FROM Topic t WHERE :user in t.favoritedBy")
    Set<Topic> favoritedBy(@Param("user") User user);

    /* ... */
}

The returned query is:

select topic0_.* 
  from topic topic0_
  cross join favorite_topics favoritedb1_, user user2_ 
  where topic0_.topic_id = favoritedb1_.topic_id 
     and favoritedb1_.user_id = user2_.user_id 
     and (? in (.))

What am I missing here? The complete code is at github

and where is your code that invokes the JPQL query? including setting the parameters? Post that snippet here not somewhere else — user8558216
– user8558216, Commented Nov 8, 2017 at 8:44
Make it sure that list (or array of values) you pass in in (.) clause is not empty/null. — jarvo69
– jarvo69, Commented Nov 8, 2017 at 10:13

tom01 · Accepted Answer · 2017-11-08 10:10:53Z

1

This query isn't providing anything over just reading the user's favouriteTopic association? Just use the association.

answered Nov 8, 2017 at 10:10

tom01

1253 silver badges11 bronze badges

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

2 Comments

Rômulo M. Farias Over a year ago

But sometimes when I access the association I get an exception about session

tom01 Over a year ago

Mysql cant handle null / empty lists when using in, protected against it e.g. (coalesce(:idParam) is null or entity.id in (:idParam)). But you should probably sort of your session exception as any time you need to lazy load an assoc you wont be able to. There are many answers on how to avoid lazy / proxy type errors depending on your stack (@Transaction) as it looks like you are using Spring Data

Rômulo M. Farias · Accepted Answer · 2017-11-08 23:38:14Z

0

Instead of use IN statement I used JOIN. This is my query now:

SELECT t FROM Topic t JOIN t.favoritedBy u WHERE :user = u

edited Nov 8, 2017 at 23:38

answered Nov 8, 2017 at 10:51

Rômulo M. Farias

1,5231 gold badge17 silver badges29 bronze badges

Collectives™ on Stack Overflow

Spring JPA generating query with "(? in (.))"

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related