CODEWITHSUNDEEP
javasqloracle-databasehibernate
mvieghofer
mvieghofer

Reputation: 2896

Hibernate returns list of nulls although executed SQL returns values

I'm using hibernate as an ORMapper. I want to execute an actually rather simple hql query:

SELECT a 
FROM Foo a 
WHERE a.status = :A0status 
ORDER BY a.bookingTypeCode ASC, 
         a.priority ASC

This hql query is then converted into a sql query which looks something like this: 

select a.* 
from Foo a 
where a.status='A' 
order by a.bookingtypecode ASC, 
         a.priority ASC

When I execute the sql on the oracle database using the Oracle SQL Developer I get 17 rows returned. However, when I execute the hql query (using the list method of a Query I get a list of 17 elements that are all null. Although the number of elements is correct, not a single one of the elements is actually loaded.

This is the way I create and execute my query:

// the hql query is stored in the hqlQuery variable;
// the parameter are stored in a Map<String, Object> called params
Query hQuery = hibSession.createQuery(hqlQuery);
for (Entry<String, Object> param : params.entrySet()) {
    String key = param.getKey();
    Object value = param.getValue();
    hQuery.setParameter(key, value);
}

List<?> result = hQuery.list();

The result I'm getting after executing query.list();

Does anyone know what might be the problem here?

Update 1

I've recently upgrade from hibernate 3.2 to 4.3.5. Before the upgrade everything worked fine. After the upgrade I get this error.

Upvotes: 12

Views: 17819

Answers (4)

Mitendra
Mitendra

Reputation: 1544

This error showed up for me while upgrading legacy code to Hibernate 5 and fixing unit tests along the way. The primary key (composite key in my case) consisted of two columns (according to the entity class). The test data however wasn't populating data for both the columns. In newer version of Hibernate a primary key (or part of composite key in my case) can never be null. Therefore hibernate always returned null.

I just updated the test data to include the data for both the columnns that formed composite key and hibernate started returning valid results.

Upvotes: 0

Eduardo Cordeiro
Eduardo Cordeiro

Reputation: 351

This error is happening to me. MySQL query browser works, but in hibernate of 7 columns and only one column always came with all null fields. I checked all the ids and they were not null. The error was in the construction of SQL Native. I had to change the way of writing it. Ai worked.

SELECT c.idContratoEmprestimo as idContratoEmprestimo,
c.dtOperacao as dataOperacao,
p.cpf as cpf,
p.nome as nome,
(Select count(p2.idParcelaEmprestimo) from EMP_PARCELA p2 where p2.valorPago > 0 and p2.dtPagamento is not null
and p2.idContratoEmprestimo = c.idContratoEmprestimo and p2.mesCompetencia <= '2014-08-01') as parcelasPagas, c.numeroParcelas as numeroParcelas,
pe.valorPago as valorParcela
FROM EMP_CONTRATO c inner join TB_PARTICIPANTE_DADOS_PLANO AS pp on pp.idParticipantePlano = c.idParticipantePlano
inner join TB_PARTICIPANTE as p on p.id = pp.idParticipante
inner join TB_PARTICIPANTE_INSTITUIDOR as pi on pi.PARTICIPANTE_ID = p.id
inner join EMP_PARCELA as pe on pe.idContratoEmprestimo = c.idContratoEmprestimo
where c.dtInicioContrato <= '2014-08-01' and pi.INSTITUIDOR_ID = 1
and c.avaliado is true
and pe.mesCompetencia = '2014-08-01'
and c.deferido is true
and c.dtQuitacao is null
and c.dtExclusao is null
and pe.valorPago is not null
group by c.idContratoEmprestimo
order by p.nome

Upvotes: 1

mvieghofer
mvieghofer

Reputation: 2896

I've set the Log level of hibernate to TRACE and found the problem. It was actually a mapping/logic/database error. The primary key consisted of two columns (according to the entity class) and one of these columns was nullable. However a primary key can never be nullable. Therefore hibernate always returned null.

Upvotes: 20

Emmanuel Bernard
Emmanuel Bernard

Reputation: 910

If you have not set a custom (and buggy) ResultTransformer, my second best guess is that your debugger is lying to you. Does you code actually receives a list of null?

Also make sure to test with the code you are showing is. Too many times, people simplify things and the devil is in the details.

Upvotes: 3

Related Questions