Using a named native sql query using xml mapping e

问题:

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 '[e-mail]'' at line 7

回答1:

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>