Hibernate returns list of nulls although executed SQL returns values

Question

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.

Is it possible that you are querying a view? There must be an unique index column that hibernate can use to map back to the id property of the Foo entity. — skarist
– skarist, Commented Jul 9, 2014 at 16:35
@Zeus I don't think that helps since we don't use standard hibernate entities. It is selfmade (a few years ago) and rather complicated to explain here. Nevertheless this should not be the problem. The reason is in the updated question. Thanks — mvieghofer
– mvieghofer, Commented Jul 10, 2014 at 6:40
@skarist Yes it could be a view. Thanks for the tip I'll check that out. — mvieghofer
– mvieghofer, Commented Jul 10, 2014 at 6:40

mvieghofer · Accepted Answer · 2014-07-10 12:57:11Z

20

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.

answered Jul 10, 2014 at 12:57

mvieghofer

2,9064 gold badges26 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeremy Over a year ago

Worked for me, I had switched the column by which I needed to annotate with @Id in my domain class that hibernate was mapping DB objects into. In my case my table did not have a primary key, but the annotation still caused the nulls because hibernate doesn't want a null ID even in the instances it creates from the DB query results regardless if its a primary key or not.

camilajenny Over a year ago

I had to set the log level to FINEST, it was a problem with the query in my case. For anyone wondering how to set the log level stackoverflow.com/a/847121/7769052

Emmanuel Bernard · Accepted Answer · 2014-07-09 17:20:33Z

3

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.

answered Jul 9, 2014 at 17:20

Emmanuel Bernard

9104 silver badges8 bronze badges

1 Comment

mvieghofer Over a year ago

Hi, no I did not set acustom ResultTransformer and the list returned is actually a list of nulls (I'm getting a NullPointerException because of this). Also the SQL I've posted above is more or less exactly the SQL that is generated by hibernate (I just simplified it by selecting * and changing the alias).

Eduardo Cordeiro · Accepted Answer · 2016-03-01 13:19:38Z

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

Mitendra · Accepted Answer · 2023-10-16 10:25:44Z

0

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.

answered Oct 16, 2023 at 10:25

Mitendra

1,56217 silver badges11 bronze badges

Collectives™ on Stack Overflow

Hibernate returns list of nulls although executed SQL returns values

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related