Chapitre 16. SQL natif

Vous pouvez aussi écrire vos requêtes dans le dialecte SQL natif de votre base de données. Ceci est utile si vous souhaitez utiliser les fonctionnalités spécifiques de votre base de données comme le mot clé CONNECT d'Oracle. Cette fonctionnalité offre par ailleurs un moyen de migration plus propre et doux d'une application basée sur SQL/JDBC vers une application Hibernate.

Hibernate3 vous permet de spécifier du SQL écrit à la main (incluant les procédures stockées) pour toutes les opérations de création, mise à jour, suppression et chargement.

16.1. Utiliser une SQLQuery

L'exécution des requêtes en SQL natif est contrôlée par l'interface SQLQuery, laquelle est obtenue en appelant Session.createSQLQuery(). Dans des cas extrêmement simples, nous pouvons utiliser la forme suivante :

List cats = sess.createSQLQuery("select * from cats")
    .addEntity(Cat.class)
    .list();

Cette requête a spécifié :

  • la requête SQL

  • l'entité retournée par la requête

Ici, les noms de colonne des résultats sont supposés être les mêmes que les noms de colonne spécifiés dans le document de mapping. Cela peut être problématique pour des requêtes SQL qui joignent de multiple tables, puisque les mêmes noms de colonne peuvent apparaître dans plus d'une table. La forme suivante n'est pas vulnérable à la duplication des noms de colonne :

List cats = sess.createSQLQuery("select {cat.*} from cats cat")
    .addEntity("cat", Cat.class)
    .list();

Cette requête a spécifié :

  • la requête SQL, avec un paramètre fictif pour Hibernate pour injecter les alias de colonne

  • l'entité retournée par la requête, et son alias de table SQL

La méthode addEntity() associe l'alias de la table SQL avec la classe de l'entité retournée, et détermine la forme de l'ensemble des résultats de la requête.

La méthode addJoin() peut être utilisée pour charger des associations vers d'autres entités et collections.

List cats = sess.createSQLQuery(
        "select {cat.*}, {kitten.*} from cats cat, cats kitten where kitten.mother = cat.id"
    )
    .addEntity("cat", Cat.class)
    .addJoin("kitten", "cat.kittens")
    .list();

Une requête SQL native pourrait retourner une simple valeur scalaire ou une combinaison de scalaires et d'entités.

Double max = (Double) sess.createSQLQuery("select max(cat.weight) as maxWeight from cats cat")
        .addScalar("maxWeight", Hibernate.DOUBLE);
        .uniqueResult();

Vous pouvez alternativement décrire les informations de mapping des résultats dans vos fichiers hbm et les utiliser pour vos requêtes.

List cats = sess.createSQLQuery(
        "select {cat.*}, {kitten.*} from cats cat, cats kitten where kitten.mother = cat.id"
    )
    .setResultSetMapping("catAndKitten")
    .list();

16.2. Alias et références de propriété

La notation {cat.*} utilisée au-dessus est un raccourci pour "toutes les propriétés". Alternativement, vous pouvez lister explicitement les colonnes, mais même ce cas que nous laissons à Hibernate injecte des alias de colonne SQL pour chaque propriété. Le remplaçant pour un alias de colonne est juste le nom de la propriété qualifié par l'alias de la table. Dans l'exemple suivant, nous récupérons des Cats à partir d'une table différente (cat_log) de celle déclarée dans les méta-données de mapping. Notez que nous pouvons même utiliser les alias de propriété dans la clause "where" si nous le souhaitons.

La syntaxe {} n'est pas requise pour le requêtes nommées. Voir Section 16.3, « Requêtes SQL nommées ».

String sql = "select cat.originalId as {cat.id}, " +
    "cat.mateid as {cat.mate}, cat.sex as {cat.sex}, " +
    "cat.weight*10 as {cat.weight}, cat.name as {cat.name} " +
    "from cat_log cat where {cat.mate} = :catId"

List loggedCats = sess.createSQLQuery(sql)
    .addEntity("cat", Cat.class)
    .setLong("catId", catId)
    .list();

À noter : si vous listez chaque propriété explicitement, vous devez inclure toutes les propriétés de la classe et ses sous-classes !

La table suivante montre les différentes possibilités d'utilisation de l'injection d'alias. À noter : les noms des alias dans le résultat sont des exemples, chaque alias aura un nom unique et probablement différent lors de l'utilisation.

Tableau 16.1. Noms d'injection d'alias

DescriptionSyntaxeExemple 
Une simple propriété{[aliasname].[propertyname]}A_NAME as {item.name} 
Une propriété composée{[aliasname].[componentname].[propertyname]}CURRENCY as {item.amount.currency}, VALUE as {item.amount.value} 
Discriminant d'une entité{[aliasname].class}DISC as {item.class} 
Toutes les propriétés d'une entité{[aliasname].*}{item.*} 
Une clef de collection{[aliasname].key}ORGID as {coll.key} 
L'identifiant d'une collection{[aliasname].id}EMPID as {coll.id} 
L'élément d'une collection{[aliasname].element}XID as {coll.element} 
Propriété de l'élément dans la collection{[aliasname].element.[propertyname]}NAME as {coll.element.name} 
Toutes les propriétés de l'élément dans la collection{[aliasname].element.*}{coll.element.*} 
Toutes les propriétés de la collection{[aliasname].*}{coll.*} 

16.3. Requêtes SQL nommées

Les requêtes SQL nommées peuvent être définies dans le document de mapping et appelées exactement de la même manière qu'un requête HQL nommée. Dans ce cas, nous n'avons pas besoin d'appeler addEntity().

<sql-query name="persons">
    <return alias="person" class="eg.Person"/>
    SELECT person.NAME AS {person.name},
           person.AGE AS {person.age},
           person.SEX AS {person.sex}
    FROM PERSON person
    WHERE person.NAME LIKE :namePattern
</sql-query>
List people = sess.getNamedQuery("persons")
    .setString("namePattern", namePattern)
    .setMaxResults(50)
    .list();

Les éléments <return-join> et <load-collection> sont respectivement utilisés pour lier des associations et définir des requêtes qui initialisent des collections.

<sql-query name="personsWith">
    <return alias="person" class="eg.Person"/>
    <return-join alias="address" property="person.mailingAddress"/>
    SELECT person.NAME AS {person.name},
           person.AGE AS {person.age},
           person.SEX AS {person.sex},
           adddress.STREET AS {address.street},
           adddress.CITY AS {address.city},
           adddress.STATE AS {address.state},
           adddress.ZIP AS {address.zip}
    FROM PERSON person
    JOIN ADDRESS adddress
        ON person.ID = address.PERSON_ID AND address.TYPE='MAILING'
    WHERE person.NAME LIKE :namePattern
</sql-query>

Une requête SQL nommée peut retourner une valeur scalaire. Vous devez spécifier l'alias de colonne et le type Hibernate utilisant l'élément <return-scalar> :

<sql-query name="mySqlQuery">
    <return-scalar column="name" type="string"/>
    <return-scalar column="age" type="long"/>
    SELECT p.NAME AS name,
           p.AGE AS age,
    FROM PERSON p WHERE p.NAME LIKE 'Hiber%'
</sql-query>

Vous pouvez externaliser les informations de mapping des résultats dans un élément <resultset> pour soit les réutiliser dans différentes requêtes nommées, soit à travers l'API setResultSetMapping().

<resultset name="personAddress">
    <return alias="person" class="eg.Person"/>
    <return-join alias="address" property="person.mailingAddress"/>
</resultset>

<sql-query name="personsWith" resultset-ref="personAddress">
    SELECT person.NAME AS {person.name},
           person.AGE AS {person.age},
           person.SEX AS {person.sex},
           adddress.STREET AS {address.street},
           adddress.CITY AS {address.city},
           adddress.STATE AS {address.state},
           adddress.ZIP AS {address.zip}
    FROM PERSON person
    JOIN ADDRESS adddress
        ON person.ID = address.PERSON_ID AND address.TYPE='MAILING'
    WHERE person.NAME LIKE :namePattern
</sql-query>

16.3.1. Utilisation de return-property pour spécifier explicitement les noms des colonnes/alias

Avec <return-property> vous pouvez explicitement dire à Hibernate quels alias de colonne utiliser, plutot que d'employer la syntaxe {} pour laisser Hibernate injecter ses propres alias.

<sql-query name="mySqlQuery">
    <return alias="person" class="eg.Person">
        <return-property name="name" column="myName"/>
        <return-property name="age" column="myAge"/>
        <return-property name="sex" column="mySex"/>
    </return>
    SELECT person.NAME AS myName,
           person.AGE AS myAge,
           person.SEX AS mySex,
    FROM PERSON person WHERE person.NAME LIKE :name
</sql-query>

<return-property> fonctionne aussi avec de multiple colonnes. Cela résout une limitation de la syntaxe {} qui ne peut pas permettre une bonne granularité des propriétés multi-colonnes.

<sql-query name="organizationCurrentEmployments">
    <return alias="emp" class="Employment">
        <return-property name="salary">
            <return-column name="VALUE"/>
            <return-column name="CURRENCY"/>
        </return-property>
        <return-property name="endDate" column="myEndDate"/>
    </return>
        SELECT EMPLOYEE AS {emp.employee}, EMPLOYER AS {emp.employer},
        STARTDATE AS {emp.startDate}, ENDDATE AS {emp.endDate},
        REGIONCODE as {emp.regionCode}, EID AS {emp.id}, VALUE, CURRENCY
        FROM EMPLOYMENT
        WHERE EMPLOYER = :id AND ENDDATE IS NULL
        ORDER BY STARTDATE ASC
</sql-query>

Notez que dans cet exemple nous avons utilisé <return-property> en combinaison avec la syntaxe {} pour l'injection. Cela autorise les utilisateurs à choisir comment ils veulent référencer les colonnes et les propriétés.

Si votre mapping a un discriminant vous devez utiliser <return-discriminator> pour spécifier la colonne discriminante.

16.3.2. Utilisation de procédures stockées pour les requêtes

Hibernate 3 introduit le support des requêtes via procédures stockées et les fonctions. La documentation suivante est valable pour les deux. Les procédures stockées/fonctions doivent retourner l'ensemble de résultats en tant que premier paramètre sortant (NdT: "out-parameter") pour être capable de fonctionner avec Hibernate. Un exemple d'une telle procédure stockée en Oracle 9 et version supérieure :

CREATE OR REPLACE FUNCTION selectAllEmployments
    RETURN SYS_REFCURSOR
AS
    st_cursor SYS_REFCURSOR;
BEGIN
    OPEN st_cursor FOR
 SELECT EMPLOYEE, EMPLOYER,
 STARTDATE, ENDDATE,
 REGIONCODE, EID, VALUE, CURRENCY
 FROM EMPLOYMENT;
      RETURN  st_cursor;
 END;

Pour utiliser cette requête dans Hibernate vous avez besoin de la mapper via une requête nommée.

<sql-query name="selectAllEmployees_SP" callable="true">
    <return alias="emp" class="Employment">
        <return-property name="employee" column="EMPLOYEE"/>
        <return-property name="employer" column="EMPLOYER"/>
        <return-property name="startDate" column="STARTDATE"/>
        <return-property name="endDate" column="ENDDATE"/>
        <return-property name="regionCode" column="REGIONCODE"/>
        <return-property name="id" column="EID"/>
        <return-property name="salary">
            <return-column name="VALUE"/>
            <return-column name="CURRENCY"/>
        </return-property>
    </return>
    { ? = call selectAllEmployments() }
</sql-query>

Notez que les procédures stockées retournent, pour le moment, seulement des scalaires et des entités. <return-join> et <load-collection> ne sont pas supportés.

16.3.2.1. Règles/limitations lors de l'utilisation des procédures stockées

Pur utiliser des procédures stockées avec Hibernate, les procédures doivent suivre certaines règles. Si elles ne suivent pas ces règles, elles ne sont pas utilisables avec Hibernate. Si vous voulez encore utiliser ces procédures vous devez les exécuter via session.connection(). Les règles sont différentes pour chaque base de données, puisque les vendeurs de base de données ont des sémantiques/syntaxes différentes pour les procédures stockées.

Les requêtes de procédures stockées ne peuvent pas être paginées avec setFirstResult()/setMaxResults().

Pour Oracle les règles suivantes s'appliquent :

  • La procédure doit retourner un ensemble de résultats. Le prmeier paramètre d'une procédure doit être un OUT qui retourne un ensemble de résultats. Ceci est fait en retournant un SYS_REFCURSOR dans Oracle 9 ou 10. Dans Oracle vous avez besoin de définir un type REF CURSOR.

Pour Sybase ou MS SQL server les règles suivantes s'appliquent :

  • La procédure doit retourner un ensemble de résultats. Notez que comme ces serveurs peuvent retourner de multiples ensembles de résultats et mettre à jour des compteurs, Hibernate itérera les résultats et prendra le premier résultat qui est un ensemble de résultat comme valeur de retour. Tout le reste sera ignoré.

  • Si vous pouvez activer SET NOCOUNT ON dans votre procédure, elle sera probablement plus efficace, mais ce n'est pas une obligation.

16.4. SQL personnalisé pour créer, mettre à jour et effacer

Hibernate3 peut utiliser des expression SQL personnalisées pour des opérations de création, de mise à jour, et de suppression. Les objets persistants les classes et les collections dans Hibernate contiennent déjà un ensemble de chaînes de caractères générées lors de la configuration (insertsql, deletesql, updatesql, etc). Les tages de mapping <sql-insert>, <sql-delete>, et <sql-update> surchargent ces chaînes de caractères :

<class name="Person">
    <id name="id">
        <generator class="increment"/>
    </id>
    <property name="name" not-null="true"/>
    <sql-insert>INSERT INTO PERSON (NAME, ID) VALUES ( UPPER(?), ? )</sql-insert>
    <sql-update>UPDATE PERSON SET NAME=UPPER(?) WHERE ID=?</sql-update>
    <sql-delete>DELETE FROM PERSON WHERE ID=?</sql-delete>
</class>

Le SQL est directement exécuté dans votre base de données, donc vous êtes libre d'utiliser le dialecte que vous souhaitez. Cela réduira bien sûr la portabilité de votre mapping si vous utilisez du SQL spécifique à votre base de données.

Les procédures stockées sont supportées si l'attribut callable est paramétré :

<class name="Person">
    <id name="id">
        <generator class="increment"/>
    </id>
    <property name="name" not-null="true"/>
    <sql-insert callable="true">{call createPerson (?, ?)}</sql-insert>
    <sql-delete callable="true">{? = call deletePerson (?)}</sql-delete>
    <sql-update callable="true">{? = call updatePerson (?, ?)}</sql-update>
</class>

L'ordre des paramètres positionnels est actuellement vital, car ils doivent être dans la même séquence qu'Hibernate les attend.

Vous pouvez voir l'ordre attendu en activant les journaux de debug pour le niveau org.hibernate.persister.entity level. Avec ce niveau activé, Hibernate imprimera le SQL statique qui est utilisé pour créer, mettre à jour, supprimer, etc. des entités. (Pour voir la séquence attendue, rappelez-vous de ne pas inclure votre SQL personnalisé dans les fichiers de mapping de manière à surcharger le SQL statique généré par Hibernate.)

Les procédures stockées sont dans la plupart des cas (lire : il vaut mieux le faire) requises pour retourner le nombre de lignes insérées/mises à jour/supprimées, puisque Hibernate fait quelques vérifications de succès lors de l'exécution de l'expression. Hibernate inscrit toujours la première expression comme un paramètre de sortie numérique pour les opérations CUD :

CREATE OR REPLACE FUNCTION updatePerson (uid IN NUMBER, uname IN VARCHAR2)
    RETURN NUMBER IS
BEGIN

    update PERSON
    set
        NAME = uname,
    where
        ID = uid;

    return SQL%ROWCOUNT;

END updatePerson;

16.5. SQL personnalisé pour le chargement

Vous pouvez aussi déclarer vos propres requêtes SQL (ou HQL) pour le chargement d'entité :

<sql-query name="person">
    <return alias="pers" class="Person" lock-mode="upgrade"/>
    SELECT NAME AS {pers.name}, ID AS {pers.id}
    FROM PERSON
    WHERE ID=?
    FOR UPDATE
</sql-query>

Ceci est juste une déclaration de requête nommée, comme vu plus tôt. Vous pouvez référencer cette requête nommée dans un mapping de classe :

<class name="Person">
    <id name="id">
        <generator class="increment"/>
    </id>
    <property name="name" not-null="true"/>
    <loader query-ref="person"/>
</class>

Ceci fonctionne même avec des procédures stockées.

Vous pouvez même définir une requête pour le chargement d'une collection :

<set name="employments" inverse="true">
    <key/>
    <one-to-many class="Employment"/>
    <loader query-ref="employments"/>
</set>
<sql-query name="employments">
    <load-collection alias="emp" role="Person.employments"/>
    SELECT {emp.*}
    FROM EMPLOYMENT emp
    WHERE EMPLOYER = :id
    ORDER BY STARTDATE ASC, EMPLOYEE ASC
</sql-query>

Vous pourriez même définir un chargeur d'entité qui charge une collection par jointure :

<sql-query name="person">
    <return alias="pers" class="Person"/>
    <return-join alias="emp" property="pers.employments"/>
    SELECT NAME AS {pers.*}, {emp.*}
    FROM PERSON pers
    LEFT OUTER JOIN EMPLOYMENT emp
        ON pers.ID = emp.PERSON_ID
    WHERE ID=?
</sql-query>