I am trying to get the minimum value of a column in a table. This sounds trivial but I experience problems (I am using MySql 5.1 database).
The situation is a little complicated. We have an interface which is implemented from two classes. Each one these two classes is extended from another three classes. Each two of these three extendors have hibernate mapping (the base classes also have mapping) => so we have Interface I implemented by
classA(mapped) extended by classA1(mapped) , classA2(mapped) , classA3(not mapped) classB(mapped) extended by classB1(mapped) , classB2(mapped) , classB3(not mapped)
Here is the problem with an example code:
Criteria c = getSession().createCriteria(samplesType)
.setProjection(Projections.min("pollingTime"));
List resultList= c.list()`enter code here`;
After the .list() is executed there are three values in the resultList: First is null, Second is null, Third contains the correct minimum value I'm looking for.
I was using c.uniqueResult() but it "returns"
org.hibernate.NonUniqueResultException: query did not return a unique result: 3
I've also tried to use hibernate's
createQuery(select min(pollingTime) from tableName).list()
but the result is the same (two nulls and one with the correct result).
I've also tried to add a predefined ResultTransformer to remove the null values from the list but I've failed.
As a workaround I can get the required minimum value using sql query:
createSQLQuery("SELECT MIN(pool_time) FROM tableName")
which is the only solution for the moment.
Do you have any ideas why the returned result is not correct?
Regards, Niki
I found the reason for this nulls returned. As I described there is some inheritance in the system. So, while searching for the minimum value in the base class table, hibernate tries to find the minimums for all the subclasses tables. In our case the tables of the subclasses are empty so there is no minimum, therefore it founds the correct value together with the null values from the subclasses tables.
As a solution to the problem I set the polymorphism to explicit in the hibernate mapping file:
<class name="class" table="TABLE" polymorphism="explicit">
The above disables searching in the specified table while searching a superclass table.
Niki