I'm using a named native SQL query using xml mapping, I need to use the mysql function AES_ENCRYPT and AES_DECRYPT but marked me syntax error but do not understand why. Now review it all and is well
I'm using a blob field to store the encrypted data of AES_ENCRYPT
Usuario.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<!-- Generated 14/12/2013 03:41:41 AM by Hibernate Tools 3.6.0 -->
<hibernate-mapping>
<class name="Model.TO.Usuario" table="usuario" catalog="sintaxhighlighter">
<id name="correo" type="string">
<column name="Correo" length="50" />
<generator class="assigned" />
</id>
<many-to-one name="roles" class="Model.TO.Roles" fetch="select">
<column name="Roles_idRoles" length="2" not-null="true" />
</many-to-one>
<property name="password" type="java.lang.String">
<column name="Password" not-null="true" />
</property>
<property name="nombres" type="string">
<column name="Nombres" length="50" not-null="true" />
</property>
<property name="apellidoP" type="string">
<column name="ApellidoP" length="25" not-null="true" />
</property>
<property name="apellidoM" type="string">
<column name="ApellidoM" length="25" not-null="true" />
</property>
<set name="codigoses" table="usuarios_has_codigos" inverse="false" lazy="true" fetch="select">
<key>
<column name="Usuarios_Correo" length="50" not-null="true" />
</key>
<many-to-many entity-name="Model.TO.Codigos">
<column name="Codigos_CodName" length="4" not-null="true" />
</many-to-many>
</set>
</class>
<sql-query name="finByCorreo" >
<return alias="Usuario" class="Model.TO.Usuario" />
SELECT use.Correo AS {Usuario.correo},
AES_DECRYPT (use.Password,'escom') AS {Usuario.password},
use.Nombres AS {Usuario.nombres},
use.ApellidoP AS {Usuario.apellidoP},
use.ApellidoM AS {Usuario.apellidoM}
FROM usuario use
WHERE use.Correo LIKE :tags
</sql-query>
</hibernate-mapping>
FindByCorreo
public Usuario finByCorreo(Usuario usuario) {
Usuario model = null;
Session session = HibernateUtil.getSessionFactory().openSession();
System.out.println(usuario.getPassword() + " " + usuario.getCorreo());
try {
session.beginTransaction();
Query query=session.getNamedQuery("finByCorreo");
query.setString("tags", usuario.getCorreo());
model = (Usuario) query.uniqueResult();
session.getTransaction().commit();
} catch (HibernateException e) {
System.out.println(e.getMessage());
session.getTransaction().rollback();
}
return model;
}
Usuario.java
public class Usuario implements java.io.Serializable {
private String correo;
private Roles roles;
private String password;
private String nombres;
private String apellidoP;
private String apellidoM;
private Set codigoses = new HashSet(0);
.
}
resulting error
WARN: SQL Error: 1064, SQLState: 42000
dic 14, 2013 11:13:14 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE use.Correo LIKE 'amezcua92@hotmail.com'' at line 7
One issue I see there, is the alias of your usuario
table. The use
could be a keyword for DB engine. If you are using MySQL, check this 9.3. Reserved Words
<sql-query name="finByCorreo" >
<return alias="Usuario" class="Model.TO.Usuario" />
SELECT ut.Correo AS {Usuario.correo},
AES_DECRYPT (ut.Password,'escom') AS {Usuario.password},
ut.Nombres AS {Usuario.nombres},
ut.ApellidoP AS {Usuario.apellidoP},
ut.ApellidoM AS {Usuario.apellidoM}
FROM usuario ut ------------------- here ut instead of use
WHERE ut.Correo LIKE :tags
</sql-query>