CODEWITHSUNDEEP
hibernate
kwan_ah
kwan_ah

Reputation: 1091

Subquery returns more than 1 row in hibernate

I am executing this method.

@Override
public Page<User> searchUsers(String name, int userTypeDefId, int accountTypeDefId,
        final PageSetting pageSetting) {
    HibernateCallback<Page<User>> hibernateCB = new HibernateCallback<Page<User>>() {

        @Override
        public Page<User> doInHibernate(Session session) throws HibernateException,
                SQLException {
            Criteria c = session.createCriteria(User.class);
            DetachedCriteria dc = DetachedCriteria.forClass(UserType.class);
            dc.setProjection(Projections.property("user"));
            c.add(Subqueries.propertyEq(UserField.id.name(), dc));
            c.addOrder(Order.asc(UserField.userName.name()));
            Page<User> ret =  getAll(c, pageSetting);
            for (User user : ret.getData()) {
                getHibernateTemplate().initialize(user.getUserAccountTypes());
                getHibernateTemplate().initialize(user.getUserTypes());
            }
            return ret;
        }
    };
    return getHibernateTemplate().execute(hibernateCB);
}

And I am having this error:

java.sql.SQLException: Subquery returns more than 1 row.

I am new to this programming language and I don't have enough idea on how to solve this error.

Upvotes: 1

Views: 3203

Answers (1)

JB Nizet
JB Nizet

Reputation: 692073

I don't know why you're using a Criteria and a subquery for that, because it makes things less readable than a simple HQL query. This is the equivalent HQL query:

select u from User u 
where u.id = (select userType.user from UserType userType)
order by u.name asc

And indeed, you see that this query is invalid:

  • comparing an ID with a user is not valid
  • the subquery returns all the users references by a UserType, so comparing one user ID with N users is not valid.

I don't know what your query should return, so it's hard to fix the code. If the goal of the query is to return all the users referenced by a UserType, I would use this simple HQL query:

select distinct user from UserType userType 
inner join userType.user user
order by user.name

Upvotes: 1

Related Questions