Hi,
I cannot find out my problem:
I have quite complicated query (lot of outer joins) and I want to filter the result set with a subquery. The problem is that the code does not work (returns zero count) and also I do not see any exceptions.
so first the code:
Code:
//create main criteria object on a class with alias
Criteria criteria = super.getSession().createCriteria(Some.class, "sc");
//create subcriteria, variable value contains the right ID
DetachedCriteria subcriteria = DetachedCriteria.forClass(Other.class).add(Restrictions.eq("id", Long.parseLong(value))).setProjection(Projections.property("localIp"));
//add it to main criteria, so that the property == result from subquery
criteria.add(Subqueries.eq("sc.componentName.propertyName", subcriteria));
//get the count
int count = ((Number) criteria.setProjection(Projections.rowCount()).uniqueResult()).intValue();
//and I get ZERO
I turned on logging and I see this:
select count(*) as y0_ from this_ left outer join this_1_ on this_.seq_number=this_1_.seq_number ... following other joins (not so important to include here)...
where ? = (select this_.local_ip as y0_ from table this_ where this_.client_id=?)
now the binding info:
[org.hibernate.type.StringType] - binding 'sc.componentName.propertyName' to parameter: 1
[org.hibernate.type.LongType] - binding '19392' to parameter: 2
But it returns zero result set without any errors. When I take this SELECT statement and replace the first question mark with 'this_.column_name' and the second question mark with the ID = 19392 so it looks like this:
select count(*) as y0_ from this_ left outer join this_1_ on ... (the same as above) ... WHERE this_.column_name = (select this_.sub_query_column as y0_ from table this_ where this_.client_id=19292)
and execute this query in SQL console (e.g. SQL Server management studio), I get the non-zero result, so it works. But why it does not work in Hibernate? Does Hibernate well replace the "object/property" notation to the table.column (as is in mapping) notation?
What can be seen to me as a strange thing, is that in generated SELECTs, both the outer SELECT and inner subSELECT are using the same alias "this_" for table name and "y0_" for column name. Could it be a problem? In DBMS it works, but I dont know if in Hibernate it is OK.
maybe im just doing some silly thing...
thanks for any help.
Login
FAQ
Search