1
Good afternoon,
Using HQL, I’m having trouble catching the name of customer price list. Therefore, I decided to try using Native SQL via Hibernate. However, I am not able to do it in Hibernate 2...
Next, I will pass my involved classes and my function where I do the SQL query. I need help to build the HQL query or help to use Createsqlquery from Hibernate 2.
DAO function that pulls data from customers and their 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>
Table.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 didn’t put Java because it exceeded the character limit allowed by the forum, but I believe that everything that needs information can be removed by hbm.xml.
Welcome to Stackoverflow. Is all this code really necessary to explain the problem? Create a minimum, complete and verifiable example.
– Renan Gomes
Actually, my problem was explained in the first paragraph... The codes, I put imagining that it would be easier to show how the relationships between client and price tables work in the bank, to avoid that this was only in the imagination of those who would seek to help me.
– Vitor Carvalho
Thanks for the help, I managed to solve the problem without having to use the native SQL in Hibernate. I took the names of all the bank’s pricing tables and made a hashmap where the key was the table ID. That way I can get the names by just making a query to the bank.
– Vitor Carvalho
@Vitorcarvalho As you have already solved your own problem, create an answer (based on your comment at least) for the good of the community.
– Ismael