Count always return 1 when there is no data to match

Dr Pompeii · **Joined:** Fri Jun 23, 2006 6:40 pm **Posts:** 25

Dear Members

I have these two DAO method implementation

The first is this, based in List<> collection because is closely related with FROM Entidad for the HQL

Quote:

@SuppressWarnings("unchecked")
@Transactional(readOnly = true)
public List<Entidad> getAllEntidadByTipo(String tipo) {
return this.sessionFactory.getCurrentSession()
.createQuery("FROM Entidad e WHERE e.tipoEntidad=:tipo")
.setParameter("tipo", tipo).list();
}

and the second, based with SELECT COUNT(*) and Integer
(I tried even with createQuery)

Quote:

public Integer getCountAllEntidadByApellidoPaterno(String apellidoPaterno){
return this.sessionFactory.getCurrentSession().
createSQLQuery("SELECT COUNT(*) FROM Entidad e WHERE e.apellidosPaternoEntidad=:apellidoPaterno").
setParameter("apellidoPaterno", apellidoPaterno.trim()).list().size();
}

This is my Test method

Quote:

@Test
public void getCountAllEntidadByApellidoPaternoFailTest(){

@SuppressWarnings("static-access")
Entidad entidad = this.entidadFactory.createEntidadManolito();
this.entidadDaoService.insertarEntidad(entidad);

@SuppressWarnings("static-access")
Entidad entidad01 = this.entidadFactory.createEntidadAlumno01();
this.entidadDaoService.insertarEntidad(entidad01);

@SuppressWarnings("static-access")
Entidad entidad02 = this.entidadFactory.createEntidadAlumno02();
this.entidadDaoService.insertarEntidad(entidad02);

List<Entidad> entidades = this.entidadDaoService.getAllEntidadByTipo("alumno");//First DAO Method
System.out.println("******getCountAllEntidadByApellidoPaternoFailTest() entidades.size(): "+ entidades.size());

Integer count = this.entidadDaoService.getCountAllEntidadByApellidoPaterno("XXX");//Second dao method
System.out.println("+++++++++++++++count: "+count);
assertTrue("Debo ser 0", count == 0 );
}

After to exceture the Test Class, I receive these values for this method

Quote:

******getCountAllEntidadByApellidoPaternoFailTest() entidades.size(): 3
Hibernate: SELECT COUNT(*) FROM Entidad e WHERE e.apellidosPaternoEntidad=?
+++++++++++++++count: 1

The color red work fine like I expected, because I used entidadFactory three times to insert the values, BTW these three entities has the same last name 'Jordan', later in the test method I am using XXX (color orange) where is an invalid data or no exists

The blue part is weird and wrong, why return 1?, it must return 0, not 1!, even Hibernate show the sql output and is correct, what is wrong?

Even worst, if I change

Quote:

Integer count = this.entidadDaoService.getCountAllEntidadByApellidoPaterno("XXX");//Second dao method

(should return 0)

to

Quote:

Integer count = this.entidadDaoService.getCountAllEntidadByApellidoPaterno("Jordan");//Second dao method

(should return 3, with List<> approach return 3)

I receive the same
+++++++++++++++count: 1

What is wrong?

Here arise two questions

1) Why work fine with List<> approach??, I mean From Entidad .. and not with
Select Count(*) From ...., I mean without List<> approach

2) I am trying to use Select Count(*) because I think is better for performance reasons because it is returning a single number(Integer) where instead I think if I use FROM Entidad .. it load a collection of Entities, where consume more resources, knowing that each row item is an independent Java class instance and furthermore even bear in mind considering other factor, the amount of properties defined for any this class, 10 ... 40, 50 fields etc

Am I correct with this idea? about (2) or not exists difference?, and I am complicating unnecessarily myself?

Thanks in advanced

nordborg · **Posted:** Sat May 14, 2011 6:02 am

Your code is not correct.

The query SELECT COUNT(*) FROM Entidad e WHERE e.apellidosPaternoEntidad=:apellidoPaterno return a single value. So the size of the list will always be 1. The count is found in the first element of the list. So, createSQLQuery(....).list().get(0) should return the count. Also note that the count is a Long and not an Integer.

Dr Pompeii · **Joined:** Fri Jun 23, 2006 6:40 pm **Posts:** 25

Hello nordborg

Thanks a lot for the reply

Well you was right, thank you

nordborg wrote:

The query SELECT COUNT(*) FROM Entidad e WHERE e.apellidosPaternoEntidad=:apellidoPaterno return a single value. So the size of the list will always be 1.The count is found in the first element of the list

Now I understand, interesting and confuse this behavior, I thought the number or count value was returned directly, not inside of a collection like the first and unique element, now I did realize why list().size(); always return 1

BTW is it indicated on Hibernate documentation?

nordborg wrote:

So, createSQLQuery(....).list().get(0) should return the count. Also note that the count is a Long and not an Integer.

Thanks, below my correction for the rest of the community

Quote:

public Integer getCountAllEntidadByApellidoMaterno(String apellidoMaterno){
return Integer.parseInt( this.sessionFactory.getCurrentSession().
createQuery("SELECT COUNT(e) FROM Entidad e WHERE e.apellidosMaternoEntidad=:apellidoMaterno").
setParameter("apellidoMaterno", apellidoMaterno.trim()).list().get(0).toString());
}

Since your signature says you are an expert, making the question again

In my first post for the second question, exists difference of perfomance?, Was I correct?

Thanks a lot

nordborg · **Posted:** Mon May 16, 2011 1:40 am

Yes, select count(*) will be quicker than loading all entities in a list.