Andrea Robinson Andrea Robinson - 1 year ago 81
Java Question

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

user table is structured this way

id | name | email | emailAccess
1 | john | |
2 | jeff | |

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

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(
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(

second stacktrace

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

Where am I failing.
Kindly assist!

Answer Source
(FROM User u WHERE u.emailAccess = :emailAccess)

This returns more than one row and you have:

WHERE c.user =

You can't have an equals with multiple results on the right. Either change your query to return a single row or change to be something like:

WHERE c.user in
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download