[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3292) Bug Oracle9iDialect Pagination
Mizael Montenegro (JIRA)
noreply at atlassian.com
Tue May 20 08:08:33 EDT 2008
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3292?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_30210 ]
Mizael Montenegro commented on HHH-3292:
----------------------------------------
I execute the java code to generate consultation for RuaBean:
Session ses = HibernateUtil.getSession();
String hql = "from RuaBean order by tipoRua.nomTipoRua";
Query query = ses.createQuery(hql);
List list = query.list();
for (int i = 0; i < list.size(); i++) {
RuaBean rb = (RuaBean)list.get(i);
System.out.print(rb.getCodRua()+" - ");
}
The result is: 79 - 49 - 77 - 1 - 86 - 85 - 38 - 62 - 88 - 87 - 1 - 68 - 66 - 84 - 70
It's ok. But if use pagination:
query.setFirstResult(0);
query.setMaxResults(10);
This result is: 85 - 86 - 77 - 49 - 1 - 79 - 1 - 84 - 88 - 68
query.setFirstResult(10);
query.setMaxResults(5);
70 - 84 - 88 - 68 - 66
The sequence is totally wrong, it should be:
79 - 49 - 77 - 1 - 86 - 85 - 38 - 62 - 88 - 87 for the first consultation with pagination and:
1 - 68 - 66 - 84 - 70 for second.
In the second, see that three values if they repeat.
This mapping of class RuaBean:
<class name="br.com.erpofm.bean.RuaBean" table="admin.tb_rua" lazy="true">
<comment>Tabela de Ruas</comment>
<composite-id>
<key-property name="codRua" column="cod_rua" type="integer" />
<key-many-to-one name="bairro" class="br.com.erpofm.bean.BairroBean" >
<column name="cod_bairro" />
<column name="cod_cidade" />
<column name="cod_uf" />
</key-many-to-one>
</composite-id>
<many-to-one name="tipoRua" class="br.com.erpofm.bean.TipoRuaBean">
<column name="cod_tipo_rua" />
</many-to-one>
<many-to-one name="usuarioCriacao" class="br.com.erpofm.bean.UsuarioBean">
<column name="cod_usuario_criacao" />
</many-to-one>
<property name="nomRua" column="nom_rua" type="string" />
<property name="dthCriacao" column="dth_criacao" type="string" />
<property name="dthAlteracao" column="dth_alteracao" type="string" />
<property name="codUsuarioAlteracao" column="cod_usuario_alteracao" type="string" />
<property name="indBloqueado" column="ind_bloqueado" type="string" />
</class>
TipoRuaBean:
<class name="br.com.erpofm.bean.TipoRuaBean" table="admin.tb_tipo_rua" lazy="true">
<comment>Tabela de Tipos de Ruas</comment>
<composite-id>
<key-property column="cod_tipo_rua" name="codTipoRua" type="integer"/>
</composite-id>
<many-to-one name="usuarioCriacao" class="br.com.erpofm.bean.UsuarioBean">
<column name="cod_usuario_criacao"></column>
</many-to-one>
<property name="nomTipoRua" column="nom_tipo_rua" type="string" />
<property name="dthCriacao" column="dth_criacao" type="date" />
<property name="dthAlteracao" column="dth_alteracao" type="string" />
<property name="codUsuarioAlteracao" column="cod_usuario_alteracao" type="string" />
<property name="indBloqueado" column="ind_bloqueado" type="string" />
</class>
UsuarioBean:
<class name="br.com.erpofm.bean.UsuarioBean" table="abd.tb_usr" lazy="true">
<comment>Tabela de Usuarios</comment>
<composite-id>
<key-property column="nm_usr" name="nmUsr" type="string"/>
</composite-id>
<property name="pwUsr" column="pw_usr" type="string" />
<property name="nome" column="nome" type="string" />
<property name="cdUniOrc" column="cd_uni_orc" type="string" />
<property name="cargo" column="cargo" type="string" />
<property name="cdUnadm" column="cd_unadm" type="string" />
</class>
BairroBean:
<class name="br.com.erpofm.bean.BairroBean" table="admin.tb_bairro" lazy="true">
<comment>Tabela de Bairros</comment>
<composite-id>
<key-property name="codBairro" column="cod_bairro" type="integer" />
<key-many-to-one name="cidade" class="br.com.erpofm.bean.CidadeBean">
<column name="cod_cidade" />
<column name="cod_uf" />
</key-many-to-one>
</composite-id>
<many-to-one name="usuarioCriacao" class="br.com.erpofm.bean.UsuarioBean">
<column name="cod_usuario_criacao" />
</many-to-one>
<property name="nomBairro" column="nom_bairro" type="string" />
<property name="dthCriacao" column="dth_criacao" type="string" />
<property name="codUsuarioAlteracao" column="cod_usuario_alteracao" type="string" />
<property name="dthAlteracao" column="dth_alteracao" type="string" />
<property name="indBloqueado" column="ind_bloqueado" type="string" />
</class>
it is oh all of the classes and mappings envolved. Thank you.
> Bug Oracle9iDialect Pagination
> ------------------------------
>
> Key: HHH-3292
> URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3292
> Project: Hibernate3
> Issue Type: Bug
> Components: core
> Affects Versions: 3.2.6
> Environment: Java2 6.0, Oracle 10i, Hibernate 3.2.6
> Reporter: Mizael Montenegro
> Priority: Critical
> Original Estimate: 1 hour, 30 minutes
> Remaining Estimate: 1 hour, 30 minutes
>
> Hi, I found a flaw in the dialect of the oracle, the pagination doesn't work correctly, my English is terrible, then I cannot describe the complete problem here, more I will post the string sql that solves the problem.
> select * from ( SELECT x.*, rownum as r FROM ( "CONSULT SQL" ) x ) where r between ? AND ?
> setInt(1, BEGIN);
> setInt(2, END);
> I altered the dialect and it perfectly worked with that solution. The largest problem is when ordination, the order is used using pagination corresponds never to the exact return of the original consultation.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
More information about the hibernate-issues
mailing list