Problemas Hibernate

[marceloamigo]

Ola a todos

Estou com problemas no uso do hibernate, espero que alguém possa me ajudar

Tenho uma tabela de produtos e uma de setores no banco de dados da aplicação, onde um produto esta assossiado à um setor.
Segue abaixo o esquema do Hibernate para melhor entendimento

<?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.com.empresa.teste.core.equipment">
    <class name="Sector" table="sector" schema="public">
        <id name="id" column="cd_sector">
            <generator class="sequence">  
                 <param name="sequence">sector_cd_sector_seq</param>  
            </generator>
        </id> 
        <many-to-one name="parent" class="Sector" lazy="false" fetch="join">
            <column name="cd_parent_sector" />
        </many-to-one>
        <many-to-one name="store" class="Store" lazy="false" fetch="join">
            <column name="cd_store" not-null="true" />
        </many-to-one>
        <property name="name">
            <column name="nm_sector" not-null="true" unique="true" />
        </property>
        <property name="creationDate">
            <column name="dt_creat" not-null="true" />
        </property>
        <property name="lastUpdate">
            <column name="dt_upd" not-null="true" />
        </property>
        <property name="idUpdUser">
            <column name="id_upd_user" not-null="true" />
        </property>
        <set name="childs" inverse="true" lazy="false" fetch="join" cascade="none" sort="natural">
            <key>
                <column name="cd_parent_sector" />
            </key>
            <one-to-many class="Sector" />
        </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.com.empresa.teste.core.product">
    <class name="ProductDetail" table="prod_det" schema="public">
        <id name="id" column="cd_prod_det">
            <generator class="sequence">  
                 <param name="sequence">prod_det_cd_prod_det_seq</param>  
            </generator>
        </id>        
        <many-to-one name="sector" class="br.com.empresa.teste.core.equipment.Sector" lazy="false" fetch="join">
            <column name="cd_sector" not-null="true" />
        </many-to-one>
        <many-to-one name="product" class="Product" lazy="false" fetch="join" cascade="all">
            <column name="cd_prod" not-null="true" />
        </many-to-one>
        <property name="price">
            <column name="vl_price" />
        </property>
        <property name="market">
            <column name="tp_mkt" />
        </property>
        
        <property name="valid">
            <column name="nu_valid" />
        </property>
         <property name="validUnit">
            <column name="nm_valid_unit" />
        </property>
        <property name="creationDate">
            <column name="dt_creat"/>
        </property>
        <property name="lastUpdate">
            <column name="dt_upd"/>
        </property>
        <property name="idUpdUser">
            <column name="id_upd_user" />
        </property>
        <set name="productAdditionalInformation" table="prod_add_inform" lazy="false" sort="natural">
            <key column="cd_prod_det" />
            <many-to-many column="cd_add_inform" class="AdditionalInformation" fetch="join"/>
        </set>
        <set name="productSuppliers" table="prod_suppl" lazy="false" sort="natural">
            <key column="cd_prod_det" />
            <many-to-many column="cd_suppl" class="Supplier" fetch="join"/>
        </set>
    </class>
</hibernate-mapping>

Meu problema é com a consulta, segue o código

    	Criteria criteria = HibernateUtil.getSession().createCriteria(this.persistentClass);    	    
    	sql = sqlFilter;
    	criteria.add(Restrictions.sqlRestriction(sql));
    	return criteria.list();

com a seguinte consulta funciona perfeitamente
sql = “{alias}.cd_prod = 1”

minha dúvida é como montar o sql quando a consulta envolve a tabela sector
sql = “[color=red]sector[/color].nm_sector = ‘Geral’ AND {alias}.cd_prod = 1”

no exemplo acima ele não reconhece sector.nm_sector, pois quando o hibernate gera a consulta ele cria outro alias para esse campo, conforme o erro abaixo

88107 [main] DEBUG org.hibernate.util.JDBCExceptionReporter - could not execute query [select this_.cd_prod_det as cd1_63_8_, this_.cd_sector as cd2_63_8_, this_.cd_prod as cd3_63_8_, this_.vl_price as vl4_63_8_, this_.tp_mkt as tp5_63_8_, this_.nu_valid as nu6_63_8_, this_.nm_valid_unit as nm7_63_8_, this_.dt_creat as dt8_63_8_, this_.dt_upd as dt9_63_8_, this_.id_upd_user as id10_63_8_, sector2_.cd_sector as cd1_80_0_, sector2_.cd_parent_sector as cd2_80_0_, sector2_.cd_store as cd3_80_0_, sector2_.nm_sector as nm4_80_0_, sector2_.dt_creat as dt5_80_0_, sector2_.dt_upd as dt6_80_0_, sector2_.id_upd_user as id7_80_0_, sector3_.cd_sector as cd1_80_1_, sector3_.cd_parent_sector as cd2_80_1_, sector3_.cd_store as cd3_80_1_, sector3_.nm_sector as nm4_80_1_, sector3_.dt_creat as dt5_80_1_, sector3_.dt_upd as dt6_80_1_, sector3_.id_upd_user as id7_80_1_, store4_.cd_store as cd1_82_2_, store4_.cd_syst_barcode as cd2_82_2_, store4_.nm_company as nm3_82_2_, store4_.nm_fancy_name as nm4_82_2_, store4_.nu_cnpj as nu5_82_2_, store4_.nu_state_regrt as nu6_82_2_, store4_.tp_store as tp7_82_2_, store4_.ds_stand_barcode_unit as ds8_82_2_, store4_.ds_stand_barcode_weight as ds9_82_2_, store4_.dt_creat as dt10_82_2_, store4_.dt_upd as dt11_82_2_, store4_.id_upd_user as id12_82_2_, product5_.cd_prod as cd1_66_3_, product5_.cd_nutrit_inform as cd2_66_3_, product5_.cd_syst_barcode as cd3_66_3_, product5_.fl_in_store as fl4_66_3_, product5_.vl_barcode as vl5_66_3_, product5_.cd_manufct as cd6_66_3_, product5_.nm_prod as nm7_66_3_, product5_.nm_nick_prod as nm8_66_3_, product5_.dt_creat as dt9_66_3_, product5_.dt_upd as dt10_66_3_, product5_.id_upd_user as id11_66_3_, nutritiona6_.cd_nutrit_inform as cd1_62_4_, nutritiona6_.cd_nutrit_catg as cd2_62_4_, nutritiona6_.vl_carbohdt as vl3_62_4_, nutritiona6_.vl_portion as vl4_62_4_, nutritiona6_.vl_energ as vl5_62_4_, nutritiona6_.vl_protein as vl6_62_4_, nutritiona6_.vl_total_fat as vl7_62_4_, nutritiona6_.vl_saturt_fat as vl8_62_4_, nutritiona6_.vl_trans_fat as vl9_62_4_, nutritiona6_.vl_diet_fiber as vl10_62_4_, nutritiona6_.vl_sodium as vl11_62_4_, nutritiona6_.dt_creat as dt12_62_4_, nutritiona6_.dt_upd as dt13_62_4_, nutritiona6_.id_upd_user as id14_62_4_, nutritiona7_.cd_nutrit_catg as cd1_61_5_, nutritiona7_.nm_nutrit_catg as nm2_61_5_, nutritiona7_.dt_creat as dt3_61_5_, nutritiona7_.dt_upd as dt4_61_5_, nutritiona7_.id_upd_user as id5_61_5_, barcodesys8_.cd_syst_barcode as cd1_83_6_, barcodesys8_.nm_barcode as nm2_83_6_, barcodesys8_.dt_creat as dt3_83_6_, barcodesys8_.dt_upd as dt4_83_6_, barcodesys8_.id_upd_user as id5_83_6_, manufactur9_.cd_manufct as cd1_60_7_, manufactur9_.nm_company as nm2_60_7_, manufactur9_.nm_fancy_name as nm3_60_7_, manufactur9_.nu_cnpj as nu4_60_7_, manufactur9_.nu_state_regrt as nu5_60_7_, manufactur9_.nu_phone as nu6_60_7_, manufactur9_.nu_fax as nu7_60_7_, manufactur9_.nm_contc as nm8_60_7_, manufactur9_.nm_contc_email as nm9_60_7_, manufactur9_.nm_addres as nm10_60_7_, manufactur9_.nm_district as nm11_60_7_, manufactur9_.nu_addres_num as nu12_60_7_, manufactur9_.ds_addres_compl as ds13_60_7_, manufactur9_.nu_cep as nu14_60_7_, manufactur9_.nm_city as nm15_60_7_, manufactur9_.nm_uf as nm16_60_7_, manufactur9_.nm_country as nm17_60_7_, manufactur9_.dt_creat as dt18_60_7_, manufactur9_.dt_upd as dt19_60_7_, manufactur9_.id_upd_user as id20_60_7_ from public.prod_det this_ inner join public.sector sector2_ on this_.cd_sector=sector2_.cd_sector left outer join public.sector sector3_ on sector2_.cd_parent_sector=sector3_.cd_sector left outer join public.store store4_ on sector3_.cd_store=store4_.cd_store inner join public.product product5_ on this_.cd_prod=product5_.cd_prod left outer join public.nutrition_inform nutritiona6_ on product5_.cd_nutrit_inform=nutritiona6_.cd_nutrit_inform left outer join public.nutrit_catg nutritiona7_ on nutritiona6_.cd_nutrit_catg=nutritiona7_.cd_nutrit_catg left outer join public.syst_barcode barcodesys8_ on product5_.cd_syst_barcode=barcodesys8_.cd_syst_barcode left outer join public.manufacturer manufactur9_ on product5_.cd_manufct=manufactur9_.cd_manufct where this_.cd_prod = 1 AND sector.nm_sector = ‘Geral’ ]
org.postgresql.util.PSQLException: ERROR: invalid reference to FROM-clause entry for table “sector”
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1608)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1343)

como devo passar o nome da tabela na consulta para que ele monte a query corretamente?

obrigado

[eliangela]

caro marceloamigo!

Não duplique tópicos!
Isso é ruim para o fórum.

[eliangela]

pergunta: vc está usando Linux??
Porque o Linux diferencia maiúsculas de minusculas.

Faz assim:

<class name="Sector" table="Sector" schema="public">

coloca a table com letra maiuscula igual no nome da classe, pra não haver confusão.

Porvavelmente o Hibernate criou a tabela como Sector e está pesquisando como sector.

Tenta aí e posta o resultado.

Outra dica:
Tenta usar Classes com annotations ao invés de XML. O Netbeans é uma ótima ferramenta pra isso. E com classes fica melhor, visualmente falando.

Um forte abraço

[marceloamigo]

Ola,

Obrigado pela sua resposta, mas meu problema esta relacionado ao método sqlRestriction
Estou tendo problemas com os alias das tabelas que fazem join com minha tabela “base”
por exemplo com essa sql restriction

sql = “sector.nm_sector = ‘Geral’ AND {alias}.cd_prod = 1”

o hibernate gera algo assim
select [color=red]sector2_.[/color]nm_sector as nm4_80_0_, this_.cd_prod as cd3_63_8_ WHERE sector.nm_sector = “Padaria” AND this_.cd_prod = 1

seria o mesmo problema aberto nesse tópico, talvez aqui fique mais facil entender

Criteria com sqlRestriction com 2 tabelas

Peço desculpas pelo tópico repetido, quando postei pela primeira vez o navegador enviou uma msg de erro, e acabei postando novamente. Se tiver alguma forma de deletar o outro por favor me informe.