Hibernate Community • View topic - Query.setParameter() Bug?

marcelo.melo

Post subject: Query.setParameter() Bug?

Posted: Tue Jul 04, 2006 11:34 am

Newbie

Joined: Wed Jun 28, 2006 4:45 pm
Posts: 3

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:
3.1.3

Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="br.gov.capes.webqualis.persistencia.dto">
<class name="WqlAreaAvaliacaoRVeiculo" table="WQL_AREA_AVALIACAO_R_VEICULO" schema="dbo" catalog="CORPORATIVO">

<id name="idAreaAvalVeiculo" type="java.lang.Integer">
<column name="IdAreaAvalVeiculo" precision="8" scale="0" />
<generator class="identity" />
</id>

<version name="versao" column="Versao" type="java.lang.Integer"></version>

<many-to-one name="wqlEventoClassificacao" class="WqlEventoClassificacao" fetch="select" lazy="false">
<column name="IdEvento" />
</many-to-one>

<many-to-one name="wqlVeiculoPublicacao" class="WqlVeiculoPublicacao" fetch="select" lazy="false">
<column name="IdVeiculoPublicacao" precision="8" scale="0" not-null="true" />
</many-to-one>

<many-to-one name="gAreasAvaliacao" class="GAreasAvaliacao" fetch="select" lazy="false">
<column name="IdAreaAvaliacao" not-null="true" />
</many-to-one>

<property name="idUsuarioAlteracao" type="java.lang.Integer">
<column name="IdUsuarioAlteracao" precision="8" scale="0" />
</property>

<property name="nivel" type="string">
<column name="Nivel" length="1" />
</property>

<property name="circulacao" type="string">
<column name="Circulacao" length="1" />
</property>

<property name="dataAlteracao" type="timestamp">
<column name="DataAlteracao" length="23" />
</property>

<property name="situacao" type="string">
<column name="Situacao" length="1" not-null="true" />
</property>

</class>
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="br.gov.capes.webqualis.persistencia.dto">
<class name="WqlVeiculoPublicacao" table="WQL_VEICULO_PUBLICACAO" schema="dbo" catalog="CORPORATIVO">

<id name="idVeiculoPublicacao" type="java.lang.Integer">
<column name="IdVeiculoPublicacao" precision="8" scale="0" />
<generator class="identity" />
</id>

<many-to-one name="wqlTipoVeiculoPublicacao" class="WqlTipoVeiculoPublicacao" fetch="select" lazy="false">
<column name="IdTipoVeiculoPublicacao" />
</many-to-one>

<property name="tituloPadronizado" type="string">
<column name="TituloPadronizado" not-null="true" />
</property>

<property name="origem" type="string">
<column name="Origem" length="60" />
</property>

<set name="wqlAreaAvaliacaoRVeiculos" inverse="true" lazy="true">
<key>
<column name="IdVeiculoPublicacao" precision="8" scale="0" not-null="true" />
</key>
<one-to-many class="WqlAreaAvaliacaoRVeiculo" />
</set>

<set name="wqlVeiculoRIdentRegistrados" inverse="true" lazy="true">
<key>
<column name="IdVeiculoPublicacao" precision="8" scale="0" />
</key>
<one-to-many class="WqlVeiculoRIdentRegistrado" />
</set>

<set name="wqlBaseIndexadas" inverse="true" lazy="true">
<key>
<column name="IdVeiculoPublicacao" precision="8" scale="0" />
</key>
<one-to-many class="WqlBaseIndexada" />
</set>

<set name="wqlEdicoesAnais" inverse="true" lazy="true">
<key>
<column name="IdVeiculoPublicacao" precision="8" scale="0" />
</key>
<one-to-many class="WqlEdicoesAnais" />

</set>
</class>
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="br.gov.capes.webqualis.persistencia.dto">
<class name="WqlTipoVeiculoPublicacao" table="WQL_TIPO_VEICULO_PUBLICACAO" schema="dbo" catalog="CORPORATIVO">

<id name="idTipoVeiculoPublicacao" type="java.lang.Integer">
<column name="IdTipoVeiculoPublicacao" />
<generator class="identity" />
</id>

<property name="descricao" type="string">
<column name="Descricao" length="60" not-null="true" />
</property>

<set name="wqlAreaEventoRTipoVeiculos" inverse="true" lazy="true">
<key>
<column name="IdTipoVeiculoPublicacao" not-null="true" />
</key>
<one-to-many class="WqlAreaEventoRTipoVeiculo"/>
</set>

<set name="wqlVeiculoPublicacaos" inverse="true" lazy="true">
<key>
<column name="IdTipoVeiculoPublicacao" />
</key>
<one-to-many class="WqlVeiculoPublicacao" />
</set>

</class>
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="br.gov.capes.webqualis.persistencia.dto">
<class name="WqlBaseIndexada" table="WQL_BASE_INDEXADA" schema="dbo" catalog="CORPORATIVO">

<id name="idBaseIndexada" type="java.lang.Integer">
<column name="IdBaseIndexada" precision="8" scale="0" />
<generator class="identity" />
</id>

<many-to-one name="wqlVeiculoPublicacao" class="WqlVeiculoPublicacao" fetch="select" lazy="false">
<column name="IdVeiculoPublicacao" precision="8" scale="0" />
</many-to-one>

<many-to-one name="wqlTipoBaseIndexada" class="WqlTipoBaseIndexada" fetch="select" lazy="false">
<column name="IdTipobaseIndexada" not-null="true" />
</many-to-one>

<property name="classificacao" type="string">
<column name="Classificacao" length="4" />
</property>

<property name="periodoClassificacao" type="string">
<column name="PeriodoClassificacao" length="8" />
</property>
</class>
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="br.gov.capes.webqualis.persistencia.dto">
<class name="WqlVeiculoRIdentRegistrado" table="WQL_VEICULO_R_IDENT_REGISTRADO" schema="dbo" catalog="CORPORATIVO">

<id name="idVeiculoRidentificador" type="java.lang.Integer">
<column name="IdVeiculoRIdentificador" precision="8" scale="0" />
<generator class="identity" />
</id>

<many-to-one name="wqlVeiculoPublicacao" class="WqlVeiculoPublicacao" fetch="select" lazy="false">
<column name="IdVeiculoPublicacao" precision="8" scale="0" />
</many-to-one>

<many-to-one name="gTipoIdentRegistrado" class="GTipoIdentRegistrado" fetch="select">
<column name="IdTipoIdentificador" precision="8" scale="0" />
</many-to-one>

<property name="identificadorRegistrado" type="string" not-null="false">
<column name="IdentificadorRegistrado" />
</property>
</class>
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="br.gov.capes.webqualis.persistencia.dto">
<class name="WqlAreaEventoRTipoVeiculo" table="WQL_AREA_EVENTO_R_TIPO_VEICULO" schema="dbo" catalog="CORPORATIVO">

<id name="idAreaEventoTipo" type="java.lang.Integer">
<column name="IdAreaEventoTipo" precision="8" scale="0" />
<generator class="identity" />
</id>

<version name="versao" type="java.lang.Integer" column="Versao"/>

<many-to-one name="wqlTipoVeiculoPublicacao" class="WqlTipoVeiculoPublicacao" fetch="select" lazy="false">
<column name="IdTipoVeiculoPublicacao" not-null="true" />
</many-to-one>

<many-to-one name="wqlEventoClassificacao" class="WqlEventoClassificacao" fetch="select" lazy="false">
<column name="IdEvento" not-null="true" />
</many-to-one>

<many-to-one name="wqlAreaEventoRTipoVeiculo" class="WqlAreaEventoRTipoVeiculo" fetch="select" lazy="false">
<column name="IdSucessor" precision="8" scale="0" />
</many-to-one>

<many-to-one name="gAreasAvaliacao" class="GAreasAvaliacao" fetch="select" lazy="false">
<column name="IdAreaAvaliacao" not-null="true" />
</many-to-one>

<property name="situacao" type="string">
<column name="Situacao" length="1" />
</property>

<property name="idPessoa" type="java.lang.Integer">
<column name="IdPessoa" precision="8" scale="0" not-null="true" />
</property>

<property name="dataSituacao" type="timestamp">
<column name="DataSituacao" length="23" not-null="true" />
</property>

</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
private final static String QUERY_PERIODICO =
"select new br.gov.capes.webqualis.to.PeriodicoTO("
+ " vep.idVeiculoPublicacao,"
+ " tpv.idTipoVeiculoPublicacao,"
+ " aav.gAreasAvaliacao.identificador,"
+ " aav.gAreasAvaliacao.nomeArea,"
+ " aav.circulacao,"
+ " aav.nivel,"
+ " vep.tituloPadronizado,"
+ " vid.identificadorRegistrado,"
+ " bas.classificacao,"
+ " vep.origem)"
+ " from WqlAreaAvaliacaoRVeiculo aav"
+ " join aav.wqlVeiculoPublicacao vep"
+ " join vep.wqlTipoVeiculoPublicacao tpv"
+ " left join vep.wqlBaseIndexadas bas"
+ " with bas.periodoClassificacao = :periodoClassificacao"
+ " and bas.wqlTipoBaseIndexada.idTipobaseIndexada = :idTipobaseIndexada"
+ " left join vep.wqlVeiculoRIdentRegistrados vid"
+ " with vid.gTipoIdentRegistrado.idTipoIdentificador = :idTipoIdentificador,"
+ " WqlAreaEventoRTipoVeiculo aetv"
+ " where aetv.gAreasAvaliacao.identificador = aav.gAreasAvaliacao.identificador"
+ " and aetv.wqlEventoClassificacao.idEvento = aav.wqlEventoClassificacao.idEvento"
+ " and tpv.idTipoVeiculoPublicacao = :idTipoVeiculoPublicacao"
+ " and aetv.situacao = :situacao";

public List findWhereTipoVeiculoIdentificadorRegistradoEquals(String status,
Integer tipoVeiculo, Integer tipoIdentificador, String identificador)
throws DaoException {

List lista = new ArrayList();

if (tipoVeiculo.equals(WqlTipoVeiculoPublicacao.PERIODICO)) {

//buscando o periodo
WqlBaseIndexadaDAO baseDao = WqlBaseIndexadaDAOImpl.getInstance();
String periodo = baseDao.findByTipoBaseMaxPeriodo(WqlTipoBaseIndexada.JCR);

String hql = QUERY_PERIODICO +
" and vid.identificadorRegistrado = :identificadorRegistrado";

Query query = getSession()
.createQuery(hql)
.setInteger(WqlTipoBaseIndexada.PROP_ID_TIPOBASE_INDEXADA,
WqlTipoBaseIndexada.JCR.intValue())
.setInteger(GTipoIdentRegistrado.PROP_ID_TIPO_IDENTIFICADOR,
GTipoIdentRegistrado.ISSN.intValue())
.setInteger(WqlTipoVeiculoPublicacao.PROP_ID_TIPO_VEICULO_PUBLICACAO,
tipoVeiculo.intValue())
.setString(WqlVeiculoRIdentRegistrado.PROP_IDENTIFICADOR_REGISTRADO,
identificador)
.setString(WqlAreaEventoRTipoVeiculo.PROP_SITUACAO,
status)
.setString(WqlBaseIndexada.PROP_PERIODO_CLASSIFICACAO,
periodo);

Calendar inicio = Calendar.getInstance();

lista = query.list();

Calendar fim = Calendar.getInstance();

System.out.println("Tempo decorrido: [" + (fim.getTimeInMillis() - inicio.getTimeInMillis()) + "]ms");

} else if(tipoVeiculo.equals(WqlTipoVeiculoPublicacao.ANAIS)){
String hql = QUERY_ANAIS;

lista = getSession()
.createQuery(hql)
.setInteger(GTipoIdentRegistrado.PROP_ID_TIPO_IDENTIFICADOR,
GTipoIdentRegistrado.ISBN.intValue())
.setInteger(WqlTipoVeiculoPublicacao.PROP_ID_TIPO_VEICULO_PUBLICACAO,
tipoVeiculo.intValue())
.setString(WqlVeiculoRIdentRegistrado.PROP_IDENTIFICADOR_REGISTRADO,
identificador)
.setString(WqlAreaEventoRTipoVeiculo.PROP_SITUACAO,
status)
.list();
}
return lista;
}

Full stack trace of any exception that occurs:

Name and version of the database you are using:
Sybase ASE 12.5

The generated SQL (show_sql=true):
select wqlveiculo1_.IdVeiculoPublicacao as col_0_0_, wqltipovei2_.IdTipoVeiculoPublicacao as col_1_0_, wqlareaava0_.IdAreaAvaliacao as col_2_0_, gareasaval6_.NomeArea as col_3_0_, wqlareaava0_.Circulacao as col_4_0_, wqlareaava0_.Nivel as col_5_0_, wqlveiculo1_.TituloPadronizado as col_6_0_, wqlveiculo4_.IdentificadorRegistrado as col_7_0_, wqlbaseind3_.Classificacao as col_8_0_, wqlveiculo1_.Origem as col_9_0_ from CORPORATIVO.dbo.WQL_AREA_AVALIACAO_R_VEICULO wqlareaava0_ inner join CORPORATIVO.dbo.WQL_VEICULO_PUBLICACAO wqlveiculo1_ on wqlareaava0_.IdVeiculoPublicacao=wqlveiculo1_.IdVeiculoPublicacao inner join CORPORATIVO.dbo.WQL_TIPO_VEICULO_PUBLICACAO wqltipovei2_ on wqlveiculo1_.IdTipoVeiculoPublicacao=wqltipovei2_.IdTipoVeiculoPublicacao left outer join CORPORATIVO.dbo.WQL_BASE_INDEXADA wqlbaseind3_ on wqlveiculo1_.IdVeiculoPublicacao=wqlbaseind3_.IdVeiculoPublicacao and (wqlbaseind3_.PeriodoClassificacao=? and wqlbaseind3_.IdTipobaseIndexada=?) left outer join CORPORATIVO.dbo.WQL_VEICULO_R_IDENT_REGISTRADO wqlveiculo4_ on wqlveiculo1_.IdVeiculoPublicacao=wqlveiculo4_.IdVeiculoPublicacao and (wqlveiculo4_.IdTipoIdentificador=?), CORPORATIVO.dbo.G_AREAS_AVALIACAO gareasaval6_, CORPORATIVO.dbo.WQL_AREA_EVENTO_R_TIPO_VEICULO wqlareaeve5_ where wqlareaava0_.IdAreaAvaliacao=gareasaval6_.Identificador and wqlareaeve5_.IdAreaAvaliacao=wqlareaava0_.IdAreaAvaliacao and wqlareaeve5_.IdEvento=wqlareaava0_.IdEvento and wqltipovei2_.IdTipoVeiculoPublicacao=? and wqlareaeve5_.Situacao=? and wqlveiculo4_.IdentificadorRegistrado=?

Debug level Hibernate log excerpt:
#webqualis# DEBUG [Loader ] result set row: 17 11:58:35,229 [main]
#webqualis# DEBUG [Loader ] result row: 11:58:35,229 [main]
#webqualis# DEBUG [IntegerType] returning '72' as column: col_0_0_ 11:58:35,229 [main]
#webqualis# DEBUG [IntegerType] returning '1' as column: col_1_0_ 11:58:35,229 [main]
#webqualis# DEBUG [ShortType ] returning '6' as column: col_2_0_ 11:58:35,229 [main]
#webqualis# DEBUG [StringType] returning 'CIÊNCIAS BIOLÓGICAS I ' as column: col_3_0_ 11:58:35,229 [main]
#webqualis# DEBUG [StringType] returning 'N' as column: col_4_0_ 11:58:35,229 [main]
#webqualis# DEBUG [StringType] returning 'B' as column: col_5_0_ 11:58:35,229 [main]
#webqualis# DEBUG [StringType] returning 'Acta Amazonica' as column: col_6_0_ 11:58:35,229 [main]
#webqualis# DEBUG [StringType] returning '0044-5967' as column: col_7_0_ 11:58:35,229 [main]
#webqualis# DEBUG [StringType] returning null as column: col_8_0_ 11:58:35,229 [main]
#webqualis# DEBUG [StringType] returning 'azul' as column: col_9_0_ 11:58:35,229 [main]
#webqualis# DEBUG [Loader ] result set row: 18 11:59:51,896 [main]
#webqualis# DEBUG [Loader ] result row: 11:59:51,896 [main]
#webqualis# DEBUG [IntegerType] returning '72' as column: col_0_0_ 11:59:51,896 [main]
#webqualis# DEBUG [IntegerType] returning '1' as column: col_1_0_ 11:59:51,896 [main]
#webqualis# DEBUG [ShortType ] returning '6' as column: col_2_0_ 11:59:51,896 [main]
#webqualis# DEBUG [StringType] returning 'CIÊNCIAS BIOLÓGICAS I ' as column: col_3_0_ 11:59:51,896 [main]
#webqualis# DEBUG [StringType] returning 'N' as column: col_4_0_ 11:59:51,896 [main]
#webqualis# DEBUG [StringType] returning 'B' as column: col_5_0_ 11:59:51,896 [main]
#webqualis# DEBUG [StringType] returning 'Acta Amazonica' as column: col_6_0_ 11:59:51,896 [main]
#webqualis# DEBUG [StringType] returning '0044-5967' as column: col_7_0_ 11:59:51,896 [main]
#webqualis# DEBUG [StringType] returning null as column: col_8_0_ 11:59:51,896 [main]
#webqualis# DEBUG [StringType] returning 'azul' as column: col_9_0_ 11:59:51,896 [main]
#webqualis# DEBUG [Loader ] result set row: 19 11:59:51,896 [main]
#webqualis# DEBUG [Loader ] result row: 11:59:51,896 [main]
#webqualis# DEBUG [IntegerType] returning '72' as column: col_0_0_ 11:59:51,896 [main]
#webqualis# DEBUG [IntegerType] returning '1' as column: col_1_0_ 11:59:51,896 [main]
#webqualis# DEBUG [ShortType ] returning '5' as column: col_2_0_ 11:59:51,896 [main]
#webqualis# DEBUG [StringType] returning 'GEOCIÊNCIAS ' as column: col_3_0_ 11:59:51,896 [main]
#webqualis# DEBUG [StringType] returning 'N' as column: col_4_0_ 11:59:51,896 [main]
#webqualis# DEBUG [StringType] returning 'A' as column: col_5_0_ 11:59:51,896 [main]
#webqualis# DEBUG [StringType] returning 'Acta Amazonica' as column: col_6_0_ 11:59:51,896 [main]
#webqualis# DEBUG [StringType] returning '0044-5967' as column: col_7_0_ 11:59:51,896 [main]
#webqualis# DEBUG [StringType] returning null as column: col_8_0_ 11:59:51,896 [main]
#webqualis# DEBUG [StringType] returning 'azul' as column: col_9_0_ 11:59:51,896 [main]
#webqualis# DEBUG [Loader ] result set row: 20 11:59:51,896 [main]

Sorry for the really big post, but I've already had this problem with Hibernate 2.7.1 and still got no answers, I'd really like if someone could help me.
What happens is that, between rows 17 and 18 there's a pause of more than one minute, and that doesn't happen if I hard code the paramenters into the hql query instead of using the setInteger and setString methods.
I can't really see anything wrong, and I've already tried to use a different driver version (I'm now using jTDS 1.2, when I was using jConnect 5.5 the pause lasted for almost 10 minutes!), and still get the same results.
Can someone please help me? I'd like to remember that when the query is run anywhere else (say, SQLAdvantage), there is no lag or pause, it runs extremely fast.

Thanks in advance

Top