Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Eclipselink Query show's different data than resultset
Eclipselink Query show's different data than resultset [message #1034433] Fri, 05 April 2013 08:27 Go to next message
Eduardo Frazão is currently offline Eduardo Frazão
Messages: 118
Registered: January 2012
Senior Member
Hi all.
Im with a weird problem here.

In my DAO, I have an Query that perform a multselect, returning an Entity, and other 3 counts. This query returns an Object[].

The strange is, that the counts change every time, but running the generated query directly on database, the result is ALWAYS consistent. I run the native query hundred times, with the exactly same result, but when eclipselink return the object, the count is sometimes different Shocked .

This is the JPQL Query:


@Override
	public ItemTotalizado<Responsavel, TotalizadorStatusAtividade> getTotaisAtividadesAbertasDoResponsavel(Responsavel r) {
		// Query
				String jpqlTotalAtividades = 	 " (SELECT count(att) FROM Atividade att WHERE att.status = :status AND (att.responsavel = :responsavel OR att.subResponsavel = :responsavel)) total, ";
				String jpqlAtividadesAtrasadas = " (SELECT count(ata) FROM Atividade ata WHERE ata.status = :status AND ata.dataLimite < :dataAtual AND (ata.responsavel = :responsavel OR (ata.subResponsavel IS NOT NULL AND ata.subResponsavel = :responsavel) ) ) atraso,";
				String jpqlAtividadesDelegadas = " (SELECT count(atd) FROM Atividade atd WHERE atd.status = :status AND (atd.responsavel = :responsavel AND atd.subResponsavel IS NOT NULL)) delegadas";
				String jpql = "SELECT a.responsavel, "
						.concat(jpqlTotalAtividades)
						.concat(jpqlAtividadesAtrasadas)
						.concat(jpqlAtividadesDelegadas)
						.concat(" FROM Atividade a WHERE a.responsavel = :responsavel AND a.status = :status GROUP BY a.responsavel");
				
				TypedQuery<Object[]> q = getEntityManager().createQuery(jpql, Object[].class);
				q.setParameter("status", Status.ABERTA);
				q.setParameter("responsavel", r);
				q.setParameter("dataAtual", new Date());
				
				// Resultados da base
				Object[] item = q.getSingleResult();
				
				ItemTotalizado<Responsavel, TotalizadorStatusAtividade> itemTotalizado = new ItemTotalizado<Responsavel, TotalizadorStatusAtividade>();
				itemTotalizado.setItem((Responsavel) item[0]);
				itemTotalizado.addTotal(TotalizadorStatusAtividade.TODAS, (Long) item[1]);
				itemTotalizado.addTotal(TotalizadorStatusAtividade.EM_ATRASO, (Long) item[2]);
				itemTotalizado.addTotal(TotalizadorStatusAtividade.DELEGADAS, (Long) item[3]);
				
				return itemTotalizado;
	}



And this is the generated Query:


SELECT t0.responsavel_id, t0.login, t0.nome, t0.senha, (SELECT COUNT(t2.atividade_id) FROM atividade t2 WHERE ((t2.status = 1) AND ((t2.responsavel_id = 99) OR (t2.sub_responsavel_id = 99)))), (SELECT COUNT(t3.atividade_id) FROM atividade t3 WHERE (((t3.status = 1) AND (t3.data_limite < '2013-04-05 09:11:24.064')) AND ((t3.responsavel_id = 99) OR (NOT ((t3.sub_responsavel_id IS NULL)) AND (t3.sub_responsavel_id = 99))))), (SELECT COUNT(t4.atividade_id) FROM atividade t4 WHERE ((t4.status = 1) AND ((t4.responsavel_id = 99) AND NOT ((t4.sub_responsavel_id IS NULL))))) FROM responsavel t0, atividade t1 WHERE (((t1.responsavel_id = 99) AND (t1.status = 1)) AND (t0.responsavel_id = t1.responsavel_id)) GROUP BY t0.responsavel_id, t0.login, t0.nome, t0.senha



Ive change the parameters with literal data to test it on database. The query never changes, and eclipselink always generate the same query.

Thanks for any help.

// EDIT
Sorry. I forget to say. Im using Eclipselink 2.4.1 and H2 Database 1.3.68

[Updated on: Fri, 05 April 2013 08:37]

Report message to a moderator

Re: Eclipselink Query show's different data than resultset [message #1034479 is a reply to message #1034433] Fri, 05 April 2013 09:35 Go to previous messageGo to next message
Eduardo Frazão is currently offline Eduardo Frazão
Messages: 118
Registered: January 2012
Senior Member
Only as Note, if I run the count as separate querys, it returns the counts in a consistent way:

public ItemTotalizado<Responsavel, TotalizadorStatusAtividade> getTotaisAtividadesAbertasDoResponsavel(Responsavel r) {
		// Query
				String jpqlTotalAtividades = 	 " SELECT count(att) FROM Atividade att WHERE att.status = :status AND (att.responsavel = :responsavel OR att.subResponsavel = :responsavel)";
				String jpqlAtividadesAtrasadas = " SELECT count(ata) FROM Atividade ata WHERE ata.status = :status AND ata.dataLimite < CURRENT_TIMESTAMP AND (ata.responsavel = :responsavel OR (ata.subResponsavel IS NOT NULL AND ata.subResponsavel = :responsavel) )";
				String jpqlAtividadesDelegadas = " SELECT count(atd) FROM Atividade atd WHERE atd.status = :status AND (atd.responsavel = :responsavel AND atd.subResponsavel IS NOT NULL)";
				
				
				
				ItemTotalizado<Responsavel, TotalizadorStatusAtividade> itemTotalizado = new ItemTotalizado<Responsavel, TotalizadorStatusAtividade>();
				itemTotalizado.setItem(r); // no problem return the same object here.
				itemTotalizado.addTotal(TotalizadorStatusAtividade.TODAS, getEntityManager().createQuery(jpqlTotalAtividades, Long.class).setParameter("status", Status.ABERTA).setParameter("responsavel", r).getSingleResult());
				itemTotalizado.addTotal(TotalizadorStatusAtividade.EM_ATRASO, getEntityManager().createQuery(jpqlAtividadesAtrasadas, Long.class).setParameter("status", Status.ABERTA).setParameter("responsavel", r).getSingleResult());
				itemTotalizado.addTotal(TotalizadorStatusAtividade.DELEGADAS, getEntityManager().createQuery(jpqlAtividadesDelegadas, Long.class).setParameter("status", Status.ABERTA).setParameter("responsavel", r).getSingleResult());
				
				return itemTotalizado;
	}


How can I avoid this overhead?
Re: Eclipselink Query show's different data than resultset [message #1034691 is a reply to message #1034479] Fri, 05 April 2013 15:32 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1018
Registered: July 2009
Senior Member
There doesn't seem to be anything in the query or the generated SQL that would explain different results unless the data changes between iterations, such as if you are executing this against transactional data that gets rolled back. Can you give an example of how you get different results and where you are executing the query?
Re: Eclipselink Query show's different data than resultset [message #1034729 is a reply to message #1034691] Fri, 05 April 2013 16:36 Go to previous message
Eduardo Frazão is currently offline Eduardo Frazão
Messages: 118
Registered: January 2012
Senior Member
Hi Chris. Thanks for help.

This is a development database. Theres no transactions or data modification during the tests. Ive noted that my views was presenting diferent information between refresh's, without any modification on database (I was looking in the logs too).

So, I create a small test class to test against the DAO using JPQL, and another to test agains raw database, using plain JDBC, and the generated Query created by Eclipselink.

I've made 50 calls on the DAO, dumping the summarized object returned:

This is the DAO Test Class:

package br.com.h4.controleatividades.domain.service;

import javax.annotation.PostConstruct;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;

import br.com.h4.controleatividades.domain.dao.ResponsavelDao;
import br.com.h4.controleatividades.domain.model.ItemTotalizado;
import br.com.h4.controleatividades.domain.model.Responsavel;
import br.com.h4.controleatividades.domain.model.TotalizadorStatusAtividade;

@Component
@Scope("singleton")
public class Test {

	private ResponsavelDao responsavelDao;

	@Autowired
	public void setResponsavelDao(ResponsavelDao responsavelDao) {
		this.responsavelDao = responsavelDao;
	}

	@PostConstruct
	private void init() {
		
		Responsavel responsavel = responsavelDao.findById(99);
		
		int count = 0;
		while(count < 50) {
			ItemTotalizado<Responsavel, TotalizadorStatusAtividade> totals = responsavelDao.getTotaisAtividadesAbertasDoResponsavel(responsavel);
			
			System.out.println(totals);
			
			System.out.println("=============");
			
			try {
				Thread.sleep(300);
			} catch (Exception e) {}
			
			count++;
		}
		
	}
	
}


Please, note that the "ItemTotalizado<Responsavel, TotalizadorStatusAtividade>" object is only a Wrapper, to put the "Object[]" returned by the JPQL in a typed object before DAO returns it.
This is the code in DAO that do that:

// Resultados da base
Object[] item = q.getSingleResult();
ItemTotalizado<Responsavel, TotalizadorStatusAtividade> itemTotalizado = new ItemTotalizado<Responsavel, TotalizadorStatusAtividade>();
itemTotalizado.setItem((Responsavel) item[0]);
itemTotalizado.addTotal(TotalizadorStatusAtividade.TODAS, (Long) item[1]);
itemTotalizado.addTotal(TotalizadorStatusAtividade.EM_ATRASO, (Long) item[2]);
itemTotalizado.addTotal(TotalizadorStatusAtividade.DELEGADAS, (Long) item[3]);


Test results:
Entity: br.com.h4.controleatividades.domain.model.Responsavel@82, Mapa de totalizacoes: {DELEGADAS=3, TODAS=6, EM_ATRASO=4}
=============
Entity: br.com.h4.controleatividades.domain.model.Responsavel@82, Mapa de totalizacoes: {DELEGADAS=3, TODAS=6, EM_ATRASO=4}
=============
Entity: br.com.h4.controleatividades.domain.model.Responsavel@82, Mapa de totalizacoes: {DELEGADAS=3, TODAS=6, EM_ATRASO=4}
=============
Entity: br.com.h4.controleatividades.domain.model.Responsavel@82, Mapa de totalizacoes: {DELEGADAS=3, TODAS=6, EM_ATRASO=0}
=============
Entity: br.com.h4.controleatividades.domain.model.Responsavel@82, Mapa de totalizacoes: {DELEGADAS=3, TODAS=6, EM_ATRASO=0}
=============
Entity: br.com.h4.controleatividades.domain.model.Responsavel@82, Mapa de totalizacoes: {DELEGADAS=3, TODAS=6, EM_ATRASO=0}
=============
Entity: br.com.h4.controleatividades.domain.model.Responsavel@82, Mapa de totalizacoes: {DELEGADAS=3, TODAS=6, EM_ATRASO=0}


Note that the tree first results takes "EM_ATRASO" COUNT as 4. After, it changes to ZERO. 4 is the right value.


This is the JDBC test code:

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

public class JDBCTest {

	public static void main(String args[]) throws Exception {
		
		Class.forName("org.h2.Driver");
		Connection con = DriverManager.getConnection("jdbc:h2:tcp://localhost:9092/~/Projetos/ca_database", "sa", "");
		
		String query = "SELECT t0.responsavel_id, t0.login, t0.nome, t0.senha, (SELECT COUNT(t2.atividade_id) FROM atividade t2 WHERE ((t2.status = 1) AND ((t2.responsavel_id = 99) OR (t2.sub_responsavel_id = 99)))), (SELECT COUNT(t3.atividade_id) FROM atividade t3 WHERE (((t3.status = 1) AND (t3.data_limite < '2013-04-05 17:30:24.064')) AND ((t3.responsavel_id = 99) OR (NOT ((t3.sub_responsavel_id IS NULL)) AND (t3.sub_responsavel_id = 99))))), (SELECT COUNT(t4.atividade_id) FROM atividade t4 WHERE ((t4.status = 1) AND ((t4.responsavel_id = 99) AND NOT ((t4.sub_responsavel_id IS NULL))))) FROM responsavel t0, atividade t1 WHERE (((t1.responsavel_id = 99) AND (t1.status = 1)) AND (t0.responsavel_id = t1.responsavel_id)) GROUP BY t0.responsavel_id, t0.login, t0.nome, t0.senha";
		
		try {
			int loop = 0;
			while (loop < 50) {
				ResultSet rs = con.createStatement().executeQuery(query);
				while(rs.next()) {
					int columns = rs.getMetaData().getColumnCount();
					for(int i = 1; i<=columns; i++) {
						System.out.println(i + " - " + rs.getString(i));
					}
				}
				System.out.println("==========");
				loop++;
				rs.close();
				Thread.sleep(300);
			}

		} finally {
			con.close();
		}
	}
	
}


Test results:
1 - 99
2 - francisco
3 - francisco
4 - 2e6f9b0d5885b6010f9167787445617f553a735f
5 - 6
6 - 4
7 - 3
==========
1 - 99
2 - francisco
3 - francisco
4 - 2e6f9b0d5885b6010f9167787445617f553a735f
5 - 6
6 - 4
7 - 3
==========
1 - 99
2 - francisco
3 - francisco
4 - 2e6f9b0d5885b6010f9167787445617f553a735f
5 - 6
6 - 4
7 - 3
==========
1 - 99
2 - francisco
3 - francisco
4 - 2e6f9b0d5885b6010f9167787445617f553a735f
5 - 6
6 - 4
7 - 3
==========


The lines 5, 6 and 7 is respectivelly the Counts of the query. Nothing change in the whole test.
If you want, I can send the whole database, and this DAO's and JDBC tests. No problem.
Previous Topic:What happened in "Query"
Next Topic:IN query with composite primary key bug.
Goto Forum:
  


Current Time: Sat Aug 30 04:25:48 EDT 2014

Powered by FUDForum. Page generated in 0.07190 seconds