CODEWITHSUNDEEP
javahibernate
beek
beek

Reputation: 3750

SQLSyntaxErrorException in Hibernate Where clause

I'm trying to use the Where clause to filter out empty entities.

i.e.

 @Where(clause = "size(userTracking) > 0 OR size(userResults) > 0")

In 

@OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
@JoinColumn(name = "user_id", insertable = false, updatable = false)
@LazyCollection(LazyCollectionOption.FALSE)
@Where(clause = "size(userTracking) > 0 OR size(userResults) > 0")
private List<UserSession> userSessions  = new ArrayList<UserSession>();

And in UserSessions

@OneToMany(cascade = CascadeType.ALL)
@JoinColumn(name = "user_session_id", updatable = false)
@LazyCollection(LazyCollectionOption.FALSE)
private List<UserResult> userResults = new ArrayList<UserResult>();

@OneToMany(cascade = CascadeType.ALL)
@JoinColumn(name = "user_session_id", updatable = false)
@LazyCollection(LazyCollectionOption.FALSE)
private List<UserTracking> userTracking = new ArrayList<UserTracking>();

However the error I'm getting is 

Caused by: java.sql.SQLSyntaxErrorException: FUNCTION database.size does not exist

Is it possible to use the @Where clause in this way?

It's not a duplicate of

HQL Query to check if size of collection is 0 or empty

I'm explicity saying that .size() doesn't work here.

.size doesn't work either

Java.sql.SQLSyntaxErrorException: Unknown column 'userTracking.size' in 'where clause'

I've also tried 'is not empty' however 

 java.sql.SQLSyntaxErrorException: 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 'usersessio0_.EMPTY OR usersessio0_.userResults IS usersessio0_.EMPTY) and users' at line 1

Upvotes: 0

Views: 149

Answers (1)

Andreas
Andreas

Reputation: 159155

@Where defines a SQL WHERE clause, not an HQL WHERE clause, so you'd need to write:

@Where(clause = "user_session_id IN ( SELECT xxx FROM UserTracking ) OR user_session_id IN ( SELECT xxx FROM UserResult )")

where xxx is whatever those columns are named.

Upvotes: 1

Related Questions