I'm using Oracle 10g express with Hibernate 3.2.6GA as implementation of JPA.
I'm having some serious problems with hibernate result of the following native query :
Code:
@SuppressWarnings("unchecked")
public List<Object[]> executarPesquisaSenhasCanceladas(){
String sql = "" +
"select " +
"quantidade, " +
"web_usuario.nome, "+
"case when (externa.dia is null or externa.hora is null) then null else to_timestamp(externa.dia || ' ' || externa.hora || ' .00.00', 'DD/MM/YY HH24:MI:SS') end horario, "+
"externa.id_x_usuario "+
"from ("+
"select "+
"count(*) as quantidade, "+
"intermediaria.id_x_usuario as id_x_usuario, "+
"get_date(intermediaria.horario) as dia, "+
"extract(hour from intermediaria.horario) as hora "+
"from "+
"web_evento_atendimento intermediaria "+
"where "+
"intermediaria.id_web_operacao = :operacaoSenhaCancelada and "+
"intermediaria.horario between :dataInicial and :dataFinal"+
"group by "+ "rollup(get_date(intermediaria.horario), extract (hour from intermediaria.horario), intermediaria.id_x_usuario)+" "+
") "+
") externa "+
"left outer join x_usuario on (x_usuario.id = externa.id_x_usuario) "+
"left outer join web_usuario on (web_usuario.id = x_usuario.id_web_usuario)";
Query q = JPAHelper.currentSession().createNativeQuery(sql);
q.setParameter("dataInicial", this.getDataInicial());
q.setParameter("dataFinal", this.getDataFinal());
q.setParameter("operacaoSenhaCancelada", WebEventoAtendimento.CANCELAR_SENHA);
return q.getResultList();
}
In every database administration tool i tried to run this query the result is the same, including the native apex interface. The result is analytical and the order of the resultset really imports to me. Once this resultset is unordered, it's impossible to order it to achieve the analytical result again (see group by clause with rollup modifier)
Code:
hibernate resultset
1 - 2 - 2009-02-04 17:00:00.0 - 2051
4 - Visual Sistemas Eletrônicos - 2009-02-04 14:00:00.0 - 1150
2 - Visual Sistemas Eletrônicos - 2009-02-04 13:00:00.0 - 1150
9 - Visual Sistemas Eletrônicos - 2009-02-04 11:00:00.0 - 1150
1 - 1 - 2009-02-04 17:00:00.0 - 1151
1 - 1 - 2009-02-04 16:00:00.0 - 1151
18 - null - null - null
18 - null - null - null
2 - null - 2009-02-04 17:00:00.0 - null
1 - null - 2009-02-04 16:00:00.0 - null
4 - null - 2009-02-04 14:00:00.0 - null
2 - null - 2009-02-04 13:00:00.0 - null
9 - null - 2009-02-04 11:00:00.0 - null
expected resultset
9 Visual Sistemas Eletrônicos 04/02/09 11:00:00 1150
9 (null) 04/02/09 11:00:00 (null)
2 Visual Sistemas Eletrônicos 04/02/09 13:00:00 1150
2 (null) 04/02/09 13:00:00 (null)
4 Visual Sistemas Eletrônicos 04/02/09 14:00:00 1150
4 (null) 04/02/09 14:00:00 (null)
1 1 04/02/09 16:00:00 1151
1 (null) 04/02/09 16:00:00 (null)
1 1 04/02/09 17:00:00 1151
1 2 04/02/09 17:00:00 2051
2 (null) 04/02/09 17:00:00 (null)
18 (null) (null) (null)
So, when i populate my List<Object[]> with the return q.getResultList();, the results are shown in a unordered list, with almost all my analytical results show at the bottom of the list(i can't guarantee this order of this unordered list). I have the same program written in c++, and the query works as it should.... Someone have experienced some problem like that?
Thanks and sorry about my english...
Login
FAQ
Search