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.