(Hibernate2) How to use createSQLQuery returning unmapped values

1

Good afternoon,

Using HQL, I'm having trouble getting the client price table name . So I decided to try to use Native SQL via Hibernate. But I'm not able to do it in Hibernate 2 ...

Next, I'll spend my classes involved and my function where I do the SQL query. I need help to build the HQL query or help to use Hibernate 2's CreateSQLQuery.

DAO function that pulls customer data and its price tables:

public List findClienteRows(long idLoja, String nome, int firstResult, int fetchSize) {
        StringBuffer whereClauses = new StringBuffer();
        whereClauses.append(" C.ID_LOJA = " + idLoja);
        if(nome != null) {
            whereClauses.append(" AND (UPPER(C.NOME) LIKE " + "%" + nome.toUpperCase() + "%"
                    + " OR C.EMAIL LIKE %" + nome.toUpperCase() + "%)");
        }

        String sql =    " SELECT C.ID AS {id} " +
                " , C.NOME AS {nome} " +
                " , C.EMAIL AS {email} " +
                " , C.CIDADE AS {cidade} " +
                " , C.ST_REVENDA AS {stRevenda} " +
                " , C.ST_PARCEIRO AS {stParceiro} " +
                " , C.DT_CADASTRO AS {dtCadastro} " +
                " , C.USUARIO_INDICA_MAT AS {indicador} " +
                " , TP.NOME AS {tabNome} " +
                " FROM CLIENTE AS C " +
                " LEFT JOIN TAB_PRECO AS TP ON C.ID_TAB_PRECO = TP.ID " +
                " WHERE " + whereClauses + 
                " ORDER BY C.NOME " +
                " LIMIT " + firstResult + " , " + fetchSize;

        String[] returnAlias = new String[] {
                                    "id",
                                    "nome",
                                    "email",
                                    "cidade",
                                    "stRevenda",
                                    "stParceiro",
                                    "dtCadastro",
                                    "indicador",
                                    "tabNome"
                            };

        Class[] returnClasses = new Class[] {
                                    Long.class,
                                    String.class,
                                    String.class,
                                    String.class,
                                    Integer.class,
                                    Integer.class,
                                    Boolean.class,
                                    String.class
                            }; 
        Session s = HibernateUtil.getSession();
        Query query = s.createSQLQuery(sql, returnAlias, returnClasses);

        List list;

        try {
            list = query.list();

            return list;
        } catch (HibernateException e) {
            e.printStackTrace();
            return null;
        }
    }

Client.hbm.xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN" 
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping
>
    <class
        name="net.alforria.b2c.modelo.Cliente"
        table="CLIENTE"
    >

        <id
            name="id"
            column="ID"
            type="java.lang.Long"
        >
            <generator class="native">
              <!--  
                  To add non XDoclet generator parameters, create a file named 
                  hibernate-generator-params-Cliente.xml 
                  containing the additional parameters and place it in your merge dir. 
              --> 
            </generator>
        </id>

        <discriminator
            column="TIPO_CLIENTE"
            type="string"
            length="1"
        />

        <property
            name="idLoja"
            type="java.lang.Long"
            update="true"
            insert="true"
            column="ID_LOJA"
            not-null="true"
        />
        <property
            name="idTabPreco"
            type="java.lang.Long"
            update="true"
            insert="true"
            column="ID_TAB_PRECO"
            not-null="false"
        />

        <property
            name="nome"
            type="java.lang.String"
            update="true"
            insert="true"
            column="NOME"
            length="200"
            not-null="true"
        />

        <property
            name="email"
            type="java.lang.String"
            update="true"
            insert="true"
            column="EMAIL"
            length="60"
            not-null="true"
        />

        <property
            name="senha"
            type="java.lang.String"
            update="true"
            insert="true"
            column="SENHA"
            length="50"
        />

        <property
            name="lembrete"
            type="java.lang.String"
            update="true"
            insert="true"
            column="LEMBRETE"
            length="255"
        />

        <property
            name="usuarioIndicadorMatriz"
            type="boolean"
            update="true"
            insert="true"
            column="USUARIO_INDICA_MAT"
            not-null="true"
        />

        <property
            name="dddTel"
            type="java.lang.String"
            update="true"
            insert="true"
            column="DDD_TEL"
            length="5"
        />

        <property
            name="tel"
            type="java.lang.String"
            update="true"
            insert="true"
            column="TEL"
            length="15"
        />

        <property
            name="dddCel"
            type="java.lang.String"
            update="true"
            insert="true"
            column="DDD_CEL"
            length="5"
        />

        <property
            name="cel"
            type="java.lang.String"
            update="true"
            insert="true"
            column="CEL"
            length="15"
        />

        <component
            name="endereco"
            class="net.alforria.b2c.modelo.Endereco"
        >

        <property
            name="endereco"
            type="java.lang.String"
            update="true"
            insert="true"
            column="ENDERECO"
            length="255"
            not-null="true"
        />

        <property
            name="bairro"
            type="java.lang.String"
            update="true"
            insert="true"
            column="BAIRRO"
            length="50"
        />

        <property
            name="cidade"
            type="java.lang.String"
            update="true"
            insert="true"
            column="CIDADE"
            length="50"
            not-null="true"
        />

        <property
            name="estado"
            type="java.lang.String"
            update="true"
            insert="true"
            column="UF"
            length="2"
            not-null="true"
        />

        <property
            name="cep"
            type="java.lang.String"
            update="true"
            insert="true"
            column="CEP"
            length="8"
            not-null="true"
        />

        </component>

        <property
            name="stRevenda"
            type="int"
            update="true"
            insert="true"
            column="ST_REVENDA"
            not-null="true"
        />

        <property
            name="stParceiro"
            type="int"
            update="true"
            insert="true"
            column="ST_PARCEIRO"
            not-null="true"
        />

        <property
            name="temCredito"
            type="boolean"
            update="true"
            insert="true"
            column="TEM_CREDITO"
            not-null="true"
        />

        <property
            name="vendedor"
            type="boolean"
            update="true"
            insert="true"
            column="VENDEDOR"
            not-null="true"
        />

        <property
            name="dataCadastro"
            type="date"
            update="false"
            insert="true"
            column="DT_CADASTRO"
            not-null="true"
        />

        <property
            name="tagAlteraSenha"
            type="java.lang.String"
            update="true"
            insert="true"
            column="TAG_ALTERA_SENHA"
            length="250"
        />

        <property
            name="idIndicador"
            type="java.lang.Long"
            update="true"
            insert="true"
            column="ID_INDICADOR"
        />

        <property
            name="site"
            type="java.lang.String"
            update="true"
            insert="true"
            column="SITE"
        />

        <property
            name="facebook"
            type="java.lang.String"
            update="true"
            insert="true"
            column="FACEBOOK"
        />

        <property
            name="ramoAtividade"
            type="java.lang.String"
            update="true"
            insert="true"
            column="RAMO_ATIV"
        />

        <property
            name="codParc"
            type="java.lang.String"
            update="true"
            insert="true"
            column="CODPARC"
        />

        <set
            name="compradores"
            table="COMPRADORES"
            lazy="false"
            cascade="none"
            sort="unsorted"
        >

            <key column="ID_EMPRESA"></key>

            <many-to-many
                class="net.alforria.b2c.modelo.Cliente"
                column="ID_COMPRADOR"
                outer-join="auto"
             />

        </set>

        <set
            name="compradorEmpresas"
            table="COMPRADORES"
            lazy="false"
            cascade="none"
            sort="unsorted"
        >

            <key column="ID_COMPRADOR"></key>

            <many-to-many
                class="net.alforria.b2c.modelo.Cliente"
                column="ID_EMPRESA"
                outer-join="auto"
             />

        </set>

        <!--
            To add non XDoclet property mappings, create a file named
                hibernate-properties-Cliente.xml
            containing the additional properties and place it in your merge dir.
        -->
        <subclass
            name="net.alforria.b2c.modelo.ClientePJ"
            discriminator-value="J"
        >
        <property
            name="contato"
            type="java.lang.String"
            update="true"
            insert="true"
            column="CONTATO"
            length="200"
        />

        <property
            name="cnpj"
            type="java.lang.String"
            update="true"
            insert="true"
            column="CNPJ"
            length="14"
        />

        <property
            name="inscEstadual"
            type="java.lang.String"
            update="true"
            insert="true"
            column="INSC_ESTADUAL"
            length="50"
        />

        <!--
                To add non XDoclet property mappings, create a file named
                hibernate-properties-ClientePJ.xml
        containing the additional properties and place it in your merge dir.
        -->

        </subclass>
        <subclass
            name="net.alforria.b2c.modelo.ClientePF"
            discriminator-value="F"
        >
        <property
            name="cpf"
            type="java.lang.String"
            update="true"
            insert="true"
            column="CPF"
            length="11"
        />

        <property
            name="rg"
            type="java.lang.String"
            update="true"
            insert="true"
            column="RG"
            length="50"
        />

        <property
            name="profissao"
            type="java.lang.String"
            update="true"
            insert="true"
            column="PROFISSAO"
            length="50"
        />

        <property
            name="dtNascimento"
            type="date"
            update="true"
            insert="true"
            column="DT_NASCIMENTO"
        />

        <property
            name="sexo"
            type="char"
            update="true"
            insert="true"
            column="SEXO"
        />

        <!--
                To add non XDoclet property mappings, create a file named
                hibernate-properties-ClientePF.xml
        containing the additional properties and place it in your merge dir.
        -->

        </subclass>

    </class>

</hibernate-mapping>

PreChild.hbm.xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN" 
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping
>
    <class
        name="net.alforria.b2c.modelo.TabelaPreco"
        table="TAB_PRECO"
    >

        <id
            name="id"
            column="ID"
            type="long"
        >
            <generator class="native">
              <!--  
                  To add non XDoclet generator parameters, create a file named 
                  hibernate-generator-params-Area.xml 
                  containing the additional parameters and place it in your merge dir. 
              --> 
            </generator>
        </id>

        <property
            name="idLoja"
            type="long"
            update="true"
            insert="true"
            column="ID_LOJA"
            not-null="true"
        />

        <property
            name="nome"
            type="java.lang.String"
            update="true"
            insert="true"
            column="NOME"
            length="10"
            not-null="false"
        />

        <property
            name="comissao"
            type="java.lang.String"
            update="true"
            insert="true"
            column="COMISSAO"
            length="10"
            not-null="false"
        />

        <property
            name="codInterno"
            type="java.lang.String"
            update="true"
            insert="true"
            column="COD_INTERNO"
            length="20"
            not-null="false"
        />

        <property
            name="valMinCompra"
            type="float"
            update="true"
            insert="true"
            column="VAL_MIN_COMPRA"
            not-null="false"
        />

        <map name="precos" cascade="all">
            <key column="ID_TAB" />
            <index column="ID_PRD" type="long"/>
            <one-to-many class="net.alforria.b2c.modelo.PrecoPrd"/>
        </map>        
        <!--
            To add non XDoclet property mappings, create a file named
                hibernate-properties-Area.xml
            containing the additional properties and place it in your merge dir.
        -->

    </class>

</hibernate-mapping>

I did not put Java because it exceeded the limit of characters allowed by the forum, but I believe that everything that needs information can be removed by hbm.xml.

    
asked by anonymous 23.02.2015 / 12:30

0 answers