Search code examples
javasqlhibernatespring-boothql

Caused by: java.sql.SQLException: Subquery returns more than 1 row on all rows where emailAccess is thesame

I am trying to retrieve all rows where emailAccess is equal to [email protected]

user table is structured this way

id | name | email         | emailAccess
1  | john |[email protected] | [email protected]
2  | jeff |[email protected] | [email protected]

I have a log table like this 

id | userId | message 
1  | 1      | bla bla
2  | 2      | 1234

now I am using the following hql query to retrieve the log based on the userId where emailAccesss from sesssion is [email protected]

String hql = "FROM Chat c WHERE c.user = (FROM User u WHERE u.emailAccess = :emailAccess)";

        return  _sessionFactory.getCurrentSession().createQuery(hql).setParameter("emailAccess", emailAccess).list();

trying to use the above hql query gives me this error

Caused by: org.hibernate.exception.DataException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:135)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)

second stacktrace

Caused by: java.sql.SQLException: Subquery returns more than 1 row
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)

Where am I failing. Kindly assist!

Solution

  • using hql allows you to access objects and their attributes, try this query : 

    String hql = "FROM Chat c WHERE c.user.emailAccess = :emailAccess"