Hibernate.orgCommunity Documentation

Capítulo 18. SQL Nativo

18.1. Usando um SQLQuery
18.1.1. Consultas Escalares
18.1.2. Consultas de Entidade
18.1.3. Manuseio de associações e coleções
18.1.4. Retorno de entidades múltiplas
18.1.5. Retorno de entidades não gerenciadas
18.1.6. Manuseio de herança
18.1.7. Parâmetros
18.2. Consultas SQL Nomeadas
18.2.1. Utilizando a propriedade retorno para especificar explicitamente os nomes de colunas/alias
18.2.2. Usando procedimentos de armazenamento para consultas
18.3. SQL padronizado para criar, atualizar e deletar
18.4. SQL padronizado para carga

Você também pode expressar consultas no dialeto SQL nativo de seu banco de dados. Isto é bastante útil para usar recursos específicos do banco de dados, assim como dicas de consultas ou a palavra chave em Oracle CONNECT. Ele também oferece um caminho de migração limpo de uma aplicação baseada em SQL/JDBC direta até o Hibernate.

O Hibernate3 permite que você especifique o SQL escrito à mão, incluindo procedimentos armazenados, para todas as operações de criar, atualizar, deletar e carregar.

A execução de consultas SQL nativa é controlada através da interface SQLQuery que é obtido, chamando a Session.createSQLQuery(). As seções abaixo descrevem como usar este API para consultas.

A consulta SQL mais básica é obter uma lista dos escalares (valores).

sess.createSQLQuery("SELECT * FROM CATS").list();

sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").list();

Eles irão retornar uma matriz de Lista de Objeto (Object[]) com valores escalares para cada coluna na tabela CATS. O Hibernate usará o ResultSetMetadata para deduzir a ordem atual e tipos de valores escalares retornados.

Para evitar o uso do ResultSetMetadata ou simplesmente para ser mais explícito em o quê é retornado, você poderá usar o addScalar():

sess.createSQLQuery("SELECT * FROM CATS")

 .addScalar("ID", Hibernate.LONG)
 .addScalar("NAME", Hibernate.STRING)
 .addScalar("BIRTHDATE", Hibernate.DATE)

Esta consulta especificou:

Este ainda irá retornar as matrizes de Objeto, mas desta vez ele não usará o ResultSetMetdata, ao invés disso, obterá explicitamente a coluna de ID, NOME e DATA DE NASCIMENTO como respectivamente uma Longa, String e Curta a partir do conjunto de resultados adjacentes. Isto também significa que somente estas três colunas irão retornar, embora a consulta esteja utilizando * e possa retornar mais do que três colunas listadas.

É possível deixar de fora o tipo de informação para todos ou alguns dos escalares.

sess.createSQLQuery("SELECT * FROM CATS")

 .addScalar("ID", Hibernate.LONG)
 .addScalar("NAME")
 .addScalar("BIRTHDATE")

Esta é a mesma consulta de antes, mas desta vez, o ResultSetMetaData é utilizado para decidir o tipo de NOME e DATA DE NASCIMENTO onde o tipo de ID é explicitamente especificado.

Como o java.sql.Types retornados do ResultSetMetadata é mapeado para os tipos Hibernate, ele é controlado pelo Dialeto. Se um tipo específico não é mapeado ou não resulta no tipo esperado, é possível padronizá-lo através de chamadas para registerHibernateType no Dialeto.

Até aqui, os nomes de colunas do conjunto de resultados são considerados como sendo os mesmos que os nomes de colunas especificados no documento de mapeamento. Isto pode ser problemático para as consultas SQL, que une tabelas múltiplas, uma vez que os mesmos nomes de colunas podem aparecer em mais de uma tabela.

É necessário uma injeção de alias de coluna na seguinte consulta (a qual é bem provável que falhe):

sess.createSQLQuery("SELECT c.*, m.*  FROM CATS c, CATS m WHERE c.MOTHER_ID = m.ID")

 .addEntity("cat", Cat.class)
 .addEntity("mother", Cat.class)

A intenção para esta consulta é retornar duas instâncias Cat por linha: um cat e sua mãe. Isto irá falhar pois existe um conflito de nomes, são mapeados aos mesmos nomes de colunas e em alguns bancos de dados os aliases de colunas retornadas estarão, muito provavelmente, na forma de "c.ID", "c.NOME", etc., os quais não são iguais às colunas especificadas no mapeamento ("ID" e "NOME").

A seguinte forma não é vulnerável à duplicação do nome de coluna:

sess.createSQLQuery("SELECT {cat.*}, {m.*}  FROM CATS c, CATS m WHERE c.MOTHER_ID = m.ID")

 .addEntity("cat", Cat.class)
 .addEntity("mother", Cat.class)

Esta consulta especificou:

A anotação {cat.*} e {mãe.*} usada acima, é um atalho para "todas as propriedades". De forma alternativa, você pode listar as colunas explicitamente, mas até neste caso nós deixamos o Hibernate injetar os aliases de coluna SQL para cada propriedade. O espaço reservado para um alias de coluna é simplesmente o nome de propriedade qualificado pelo alias de tabela. No seguinte exemplo, recuperamos os Cats e suas mães de uma tabela diferente (cat_log) para aquele declarado no metadado de mapeamentos. Note que podemos até usar os aliases de propriedade na cláusula where se quisermos.

String sql = "SELECT ID as {c.id}, NAME as {c.name}, " +

         "BIRTHDATE as {c.birthDate}, MOTHER_ID as {c.mother}, {mother.*} " +
         "FROM CAT_LOG c, CAT_LOG m WHERE {c.mother} = c.ID";
List loggedCats = sess.createSQLQuery(sql)
        .addEntity("cat", Cat.class)
        .addEntity("mother", Cat.class).list()

Named SQL queries can also be defined in the mapping document and called in exactly the same way as a named HQL query (see Seção 11.4.1.7, “Externando consultas nomeadas”). In this case, you do not need to call addEntity().



Os elementos <return-join> e <load-collection> são usados para unir associações e definir consultas que inicializam coleções,


Uma consulta SQL nomeada pode devolver um valor escalar. Você deve declarar um alias de coluna e um tipo Hibernate usando o elemento <return-scalar>:


Você pode externar as informações de mapeamento de conjunto de resultado em um elemento <resultset> tanto para reusá-los em diversas consultas nomeadas quanto através da API setResultSetMapping().


Você pode também, como forma alternativa, usar a informação de mapeamento de conjunto de resultado em seus arquivos hbm em código de java.


So far we have only looked at externalizing SQL queries using Hibernate mapping files. The same concept is also available with anntations and is called named native queries. You can use @NamedNativeQuery (@NamedNativeQueries) in conjunction with @SqlResultSetMapping (@SqlResultSetMappings). Like @NamedQuery, @NamedNativeQuery and @SqlResultSetMapping can be defined at class level, but their scope is global to the application. Lets look at a view examples.

Exemplo 18.7, “Named SQL query using @NamedNativeQuery together with @SqlResultSetMapping” shows how a resultSetMapping parameter is defined in @NamedNativeQuery. It represents the name of a defined @SqlResultSetMapping. The resultset mapping declares the entities retrieved by this native query. Each field of the entity is bound to an SQL alias (or column name). All fields of the entity including the ones of subclasses and the foreign key columns of related entities have to be present in the SQL query. Field definitions are optional provided that they map to the same column name as the one declared on the class property. In the example 2 entities, Night and Area, are returned and each property is declared and associated to a column name, actually the column name retrieved by the query.

In Exemplo 18.8, “Implicit result set mapping” the result set mapping is implicit. We only describe the entity class of the result set mapping. The property / column mappings is done using the entity mapping values. In this case the model property is bound to the model_txt column.

Finally, if the association to a related entity involve a composite primary key, a @FieldResult element should be used for each foreign key column. The @FieldResult name is composed of the property name for the relationship, followed by a dot ("."), followed by the name or the field or property of the primary key. This can be seen in Exemplo 18.9, “Using dot notation in @FieldResult for specifying associations ”.



Exemplo 18.9. Using dot notation in @FieldResult for specifying associations

@Entity

@SqlResultSetMapping(name="compositekey",
        entities=@EntityResult(entityClass=SpaceShip.class,
            fields = {
                    @FieldResult(name="name", column = "name"),
                    @FieldResult(name="model", column = "model"),
                    @FieldResult(name="speed", column = "speed"),
                    @FieldResult(name="captain.firstname", column = "firstn"),
                    @FieldResult(name="captain.lastname", column = "lastn"),
                    @FieldResult(name="dimensions.length", column = "length"),
                    @FieldResult(name="dimensions.width", column = "width")
                    }),
        columns = { @ColumnResult(name = "surface"),
                    @ColumnResult(name = "volume") } )
@NamedNativeQuery(name="compositekey",
    query="select name, model, speed, lname as lastn, fname as firstn, length, width, length * width as surface from SpaceShip", 
    resultSetMapping="compositekey")
} )
public class SpaceShip {
    private String name;
    private String model;
    private double speed;
    private Captain captain;
    private Dimensions dimensions;
    @Id
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    @ManyToOne(fetch= FetchType.LAZY)
    @JoinColumns( {
            @JoinColumn(name="fname", referencedColumnName = "firstname"),
            @JoinColumn(name="lname", referencedColumnName = "lastname")
            } )
    public Captain getCaptain() {
        return captain;
    }
    public void setCaptain(Captain captain) {
        this.captain = captain;
    }
    public String getModel() {
        return model;
    }
    public void setModel(String model) {
        this.model = model;
    }
    public double getSpeed() {
        return speed;
    }
    public void setSpeed(double speed) {
        this.speed = speed;
    }
    public Dimensions getDimensions() {
        return dimensions;
    }
    public void setDimensions(Dimensions dimensions) {
        this.dimensions = dimensions;
    }
}
@Entity
@IdClass(Identity.class)
public class Captain implements Serializable {
    private String firstname;
    private String lastname;
    @Id
    public String getFirstname() {
        return firstname;
    }
    public void setFirstname(String firstname) {
        this.firstname = firstname;
    }
    @Id
    public String getLastname() {
        return lastname;
    }
    public void setLastname(String lastname) {
        this.lastname = lastname;
    }
}

Dica

If you retrieve a single entity using the default mapping, you can specify the resultClass attribute instead of resultSetMapping:

@NamedNativeQuery(name="implicitSample", query="select * from SpaceShip", resultClass=SpaceShip.class)

public class SpaceShip {

In some of your native queries, you'll have to return scalar values, for example when building report queries. You can map them in the @SqlResultsetMapping through @ColumnResult. You actually can even mix, entities and scalar returns in the same native query (this is probably not that common though).


An other query hint specific to native queries has been introduced: org.hibernate.callable which can be true or false depending on whether the query is a stored procedure or not.

Com a <return-property> você pode informar explicitamente, quais aliases de coluna utilizar, ao invés de usar a sintáxe {} para deixar o Hibernate injetar seus próprios aliases. Por exemplo:


<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> também funciona com colunas múltiplas. Isto resolve a limitação com a sintáxe {} que não pode permitir controle granulado fino de muitas propriedades de colunas múltiplas.


<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>

Observe que neste exemplo nós usamos <return-property> combinado à síntáxe {} para injeção. Permite que os usuários escolham como eles querem se referir à coluna e às propriedades.

Se seu mapeamento possuir um discriminador, você deve usar <return-discriminator> para especificar a coluna do discriminador.

O Hibernate 3 apresenta o suporte para consultas através de procedimentos e funções armazenadas. A maior parte da documentação a seguir, é equivalente para ambos. Os procedimentos e funções armazenados devem devolver um conjunto de resultados como primeiros parâmetros externos para poder trabalhar com o Hibernate. Um exemplo disto é a função armazenada em Oracle 9 e versões posteriores como se segue:


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;

Para usar esta consulta no Hibernate você vai precisar mapeá-lo através de uma consulta nomeada


<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>

Observe que os procedimentos armazenados somente devolvem escalares e entidades. O <return-join> e <load-collection> não são suportados.

Para usar procedimentos armazenados com Hibernate, os procedimentos e funções precisam seguir a mesma regra. Caso não sigam estas regras, não poderão ser usados com o Hibernate. Se você ainda desejar usar estes procedimentos, terá que executá-los através da session.connection(). As regras são diferentes para cada banco de dados, uma vez que os fabricantes possuem procedimentos de semânticas/sintáxe armazenados.

Consultas de procedimento armazenado não podem ser paginados com o setFirstResult()/setMaxResults().

O formulário de chamada recomedado é o padrão SQL92: { ? = call functionName(<parameters>) } or { ? = call procedureName(<parameters>}. A sintáxe de chamada nativa não é suportada.

As seguintes regras se aplicam para Oracle:

Para servidores Sybase ou MS SQL aplicam-se as seguintes regras:

Hibernate3 can use custom SQL for create, update, and delete operations. The SQL can be overridden at the statement level or inidividual column level. This section describes statement overrides. For columns, see Seção 5.6, “Column transformers: read and write expressions”. Exemplo 18.11, “Custom CRUD via annotations” shows how to define custom SQL operatons using annotations.


@SQLInsert, @SQLUpdate, @SQLDelete, @SQLDeleteAll respectively override the INSERT, UPDATE, DELETE, and DELETE all statement. The same can be achieved using Hibernate mapping files and the <sql-insert>, <sql-update> and <sql-delete> nodes. This can be seen in Exemplo 18.12, “Custom CRUD XML”.


If you expect to call a store procedure, be sure to set the callable attribute to true. In annotations as well as in xml.

To check that the execution happens correctly, Hibernate allows you to define one of those three strategies:

  • none: no check is performed: the store procedure is expected to fail upon issues

  • count: use of rowcount to check that the update is successful

  • param: like COUNT but using an output parameter rather that the standard mechanism

To define the result check style, use the check parameter which is again available in annoations as well as in xml.

You can use the exact same set of annotations respectively xml nodes to override the collection related statements -see Exemplo 18.13, “Overriding SQL statements for collections using annotations”.


Dica

The parameter order is important and is defined by the order Hibernate handles properties. You can see the expected order by enabling debug logging for the org.hibernate.persister.entity level. With this level enabled Hibernate will print out the static SQL that is used to create, update, delete etc. entities. (To see the expected sequence, remember to not include your custom SQL through annotations or mapping files as that will override the Hibernate generated static sql)

Overriding SQL statements for secondary tables is also possible using @org.hibernate.annotations.Table and either (or all) attributes sqlInsert, sqlUpdate, sqlDelete:


The previous example also shows that you can give a comment to a given table (primary or secondary): This comment will be used for DDL generation.

Dica

The SQL is directly executed in your database, so you can use any dialect you like. This will, however, reduce the portability of your mapping if you use database specific SQL.

Last but not least, stored procedures are in most cases required to return the number of rows inserted, updated and deleted. Hibernate always registers the first statement parameter as a numeric output parameter for the CUD operations:


You can also declare your own SQL (or HQL) queries for entity loading. As with inserts, updates, and deletes, this can be done at the individual column level as described in Seção 5.6, “Column transformers: read and write expressions” or at the statement level. Here is an example of a statement level override:


<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>

Este é apenas uma instrução de consulta nomeada, como discutido anteriormente. Você pode referenciar esta consulta nomeada em um mapeamento de classe:


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

Este também funciona com procedimentos armazenados.

Você pode também definir uma consulta para carregar uma coleção:


<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>

Você pode até definir um carregador de entidade que carregue uma coleção por busca de união:


<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>

The annotation equivalent <loader> is the @Loader annotation as seen in Exemplo 18.11, “Custom CRUD via annotations”.