Hibernate4 doesn't fetch data from database

Question

I'm busy programming an application using hibernate 4.2.2.Final and Spring 3.2.3.RELEASE. I use logging framework log4jdbc-remix (0.2.7) to log all sql calls to the database. I'm using hibernate criteria to generate the SQL statements. In one case, I'm generating a SQL statement that returns results when I execute it on the database itself (DB/2), just copy-paste from the logging output. In hibernate, the resulting list is empty (same query).

The methods (I did change the names of the variables to more 'generic English' names):

@Override
public List<PersonId> getActiveNumbers(SpecialNumber specialumber) {
    final Criteria criteria = this.getCurrentSession().createCriteria(Person.class);
    criteria.add(Restrictions.eq("specialNr", specialNumber));
    criteria.setProjection(Projections.projectionList()
        .add(Projections.property("id.baseNr"))
        .add(Projections.property("id.subNr")));
    criteria.add(Subqueries.propertiesIn(new String[] { "id.baseNr", "id.subNr" },
        isActive()));
    criteria.setResultTransformer(Transformers.aliasToBean(PersonId.class));

    return (List<PersonId>) criteria.list();
}

private DetachedCriteria isActive() {
    DetachedCriteria isActive = DetachedCriteria.forClass(PersonSpecific.class);
    isActive.add(LocalDateComposite.lessThanOrEqual("date", LocalDate.now()));
    isActive.add(Restrictions.or(Restrictions.eq("dateCancel", null),
        Restrictions.ge("dateCancel", LocalDate.now())));
    isActive.setProjection(Projections.projectionList()
        .add(Projections.property("id.baseNr").as("baseNr"))
        .add(Projections.property("id.subNr").as("subNr")));
    return isActive;
}

However, when I remove the line

criteria.add(Restrictions.eq("specialNr", specialNumber));

hibernate does generate a resulting list. The subquery 'isActive' returns the right query with the right values (according to the log, and as well when I remove the above line).

'specialNr' is of type 'specialNumber', for which I have a custom UserType:

public class SpecialNumberUserType extends AbstractUserType implements UserType {
private static final int[] TYPES = { Types.BIGINT };

    [...]

@Override
public int[] sqlTypes() {
    return TYPES;
}

@Override
public Class<?> returnedClass() {
    return SpecialNumber.class;
 }

@Override
public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor session, Object owner)
    throws HibernateException, SQLException {
    if (rs.wasNull()) {
        return null;
    }
    return new specialNumber(rs.getLong(names[0]));
}

@Override
public void nullSafeSet(PreparedStatement st, Object value, int index,
    SessionImplementor session) throws HibernateException, SQLException {

    if (null == value) {
        st.setLong(index, 0L);
    } else {
        st.setLong(index, ((SpecialNumber) value).getValue());
    }

}
}

I can't figure out why hibernate does generate the right sql, but deosn't fetch the right result. I do use 'specialNumber' in other tables and queries, and it does work in the other queries. Only in this particular case, hibernate doesn't fetch the result. Any ideas? It feels as if something is wrong with the custom usertype SpecialNumberUserType, but I can't figure out what, since the resulting sql shows the right value, and it works in other sql-statements using the same 'specialNumber' class.

-- EDIT:

I forgot to mention: when I run the same method against an in-memory test db (hsqldb), than I do get the expected result. So, to summarize:

1. The generated sql is ok, with the right values (checking the logs);
2. Running the generated sql directly on the database (db/2 terminal), I get the expected result;
3. Running the method 'getActiveNumbers' against an in-memory database, I get the expected result;
4. Running the method 'getActiveNumbers' against the real database, I get an empty list, which is not the expected result.
5. Running the same method, but having the line 'criteria.add(Restrictions.eq("specialNr", specialNumber));' removed, I do get the expected result of that query (which is rather a lot of rows);
6. Running other queries (using hibernate criteria) using the same 'SpecialNumber'-class as property of another class against the real database does work - no code shown here, but it's very similar, being having a line like 'criteria.add(Restrictions.eq("specialNr", specialNumber));'.

I'm puzzled...

-- EDIT_II:

I'm trying to get it working with hql-queries. Same very weird problem, and I'm focussing only on 'specialNumber':

final Query query = this.getCurrentSession().createQuery(
        "select * from Person where specialNumber = 436260171");

gives the expected result. (2 records in this case). When I change it to:

final Query query = this.getCurrentSession().createQuery(
        "select * from Person where specialNumber = :specialNumber");
query.setLong("specialNumber", 436260171L);

or

query.setParameter("specialNumber", new SpecialNumber(436260171L));

I get an empty list again. So, using a parameter and suddenly, it doesn't work anymore. I don't get it :p.

Answer 1

Finally, solved it. Apparently on the (legacy) db-side, the data-type was a CHAR. In the in-memory test database, the type was mapped to integer. That's why it worked 'in-memory', but not on the real db. I had to change the nullSafeSet-method of the usertype to make it work properly...

EDIT: It was even worse: 'SpecialNumber' was in different tables defined as different types: on one table, it was a char(N), in another one, it was a bigint. Got everything working now (I think).