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 12:27  |
 Eduardo Frazão
Messages: 123
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  .
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 12: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 13:35  |
Eduardo Frazão
Messages: 123
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 #1034729 is a reply to message #1034691] |
Fri, 05 April 2013 20:36 |
Eduardo Frazão
Messages: 123
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.
|
|
|
Goto Forum:
Current Time: Tue Apr 23 12:01:03 GMT 2024
Powered by FUDForum. Page generated in 0.03819 seconds |
] 
Search
Help
Register
Login
Home
