Hibernate.orgCommunity Documentation

第18章 ネイティブ SQL

18.1. Using a SQLQuery
18.1.1. スカラーのクエリ
18.1.2. エンティティのクエリ
18.1.3. 関連とコレクションの操作
18.1.4. 複数エンティティの取得
18.1.5. 管理されていないエンティティの取得
18.1.6. 継承の制御
18.1.7. パラメータ
18.2. 名前付き SQL クエリ
18.2.1. 列と列の別名を明示的に指定するために return-property を使う
18.2.2. 問い合わせするためにストアドプロシージャを使う
18.3. 作成、更新、削除のためのカスタム SQL
18.4. ロードのためのカスタム SQL

データベースのネイティブ SQL 方言を使ってクエリを表現することもできます。クエリヒントや Oracle の CONNECT キーワードのように、データベース独自の機能を利用したいときに使えます。 SQL/JDBC を直接使用しているアプリケーションから Hibernate への移行も容易にしています。

Hibernate3 では、生成、更新、削除、読み込み処理のようなすべての SQL (ストアドプロシージャを含む)を手書きできます。

ネイティブな SQL クエリの実行は SQLQuery インターフェースを通して制御します。 SQLQuery インターフェースは Session.createSQLQuery() を呼び出して取得します。この API を使って問い合わせする方法を以下で説明します。

最も基本的な SQL クエリはスカラー(値)のリストを得ることです。

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

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

これらはどちらも、 CATS テーブルの各カラムのスカラー値を含む Object 配列(Object[])のリストを返します。返すスカラー値の実際の順番と型を推定するために、 Hibernate は ResultSetMetadata を使用します。

ResultSetMetadata を使用するオーバーヘッドを避けるため、もしくは単に何が返されるか明確にするため、 addScalar() を使えます。

sess.createSQLQuery("SELECT * FROM CATS")

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

このクエリで指定されているものを下記に示します:

これはまだ Object 配列を返しますが、 ResultSetMetdata を使用しません。ただし、その代わりに基礎にあるリザルトセットから ID、NAME、BIRTHDATE カラムをそれぞれ Long、String、Short として明示的に取得します。これは3つのカラムを返すのみであることも意味します。たとえ、クエリが * を使用し、列挙した3つより多くのカラムを返せるとしてもです。

スカラーの型情報を省くこともできます。

sess.createSQLQuery("SELECT * FROM CATS")

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

これは本質的に前と同じクエリですが、 NAME と BIRTHDATE の型を決めるために ResultSetMetaData を使用します。一方、 ID の型は明示的に指定されています。

ResultSetMetaData から返される java.sql.Types を Hibernate の型に マッピングすることは、 Dialect が制御します。明示された型がマッピングされていないか、結果の型が期待したものと異なる場合、 Dialect の registerHibernateType を呼び出し、カスタマイズできます。

ここまでのクエリは、すべてスカラー値を返すものでした。基本的に、リザルトセットから「未加工」の値を返します。以降では、 addEntity() により、ネイティブ SQL クエリからエンティティオブジェクトを取得する方法を示します。

sess.createSQLQuery("SELECT * FROM CATS").addEntity(Cat.class);

sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").addEntity(Cat.class);

このクエリで指定されているものを下記に示します:

Cat が ID 、 NAME 、 BIRTHDATE のカラムを使ってクラスにマッピングされる場合、上記のクエリはどちらも、要素が Cat エンティティであるリストを返します。

エンティティを別のエンティティに 多対一 でマッピングしている場合は、ネイティブクエリを実行する際に、この別のエンティティを返すことも要求します。さもなければ、データベース固有の「column not found(カラムが見つかりません)」エラーが発生します。 * 表記を使用した際は、追加のカラムが自動的に返されますが、次の例のように、 Dog多対一 であることを明示することを私たちは好みます。

sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE, DOG_ID FROM CATS").addEntity(Cat.class);

これにより cat.getDog() が正しく機能します。

ここまでは、リザルトセットのカラム名は、マッピングドキュメントで指定されたカラム名と同じであると仮定していました。複数のテーブルが同じカラム名を持つ場合があるため、複数テーブルを結合する SQL クエリで問題となる場合があります。

下記のような(失敗しそうな)クエリでは、カラム別名インジェクション(column alias injection)が必要です:

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

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

このクエリの意図は、1行ごとに2つの Cat インスタンス、つまり猫とその母親を返すということです。同じカラム名にマッピングすることにより名前が衝突するため、このクエリは失敗します。ベータベースによっては、返されるカラムの別名が "c.ID"、"c.NAME" などの形式であり、マッピングで指定されたカラム("ID" と "NAME")と等しくないため、失敗します。

下記の形式は、カラム名が重複しても大丈夫です:

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

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

このクエリで指定されているものを下記に示します:

上記で使用している {cat.*} と {mother.*} という表記は、「すべてのプロパティ」を表す省略形です。代わりに、明示的にカラムを列挙してもよいですが、その場合は、 Hibernate に各プロパティに対応する SQL カラムの別名を挿入させるべきでしょう。カラムの別名のためのプレースホルダは、テーブルの別名によって修飾されたプロパティ名です。下記の例では、別のテーブル cat_log から マッピングメタデータで定義された Cat とその母親を復元します。もし好むなら、 where 節の中でも、プロパティの別名を使えます。

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 「名前付きクエリの外出し」). In this case, you do not need to call addEntity().



The <return-join> element is use to join associations and the <load-collection> element is used to define queries which initialize collections,


名前付き SQL クエリはスカラ値を返すこともできます。 <return-scalar> 要素を使って、列の別名と Hibernate の型を宣言しなければなりません:


リザルトセットのマッピング情報を <resultset> に外部化することができます。複数の名前付きクエリで再利用したり、 setResultSetMapping() API を通して再利用したりできます。


代わりに、 hbm ファイル内のリザルトセットのマッピング情報を直接 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.

例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 例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 例18.9「Using dot notation in @FieldResult for specifying associations 」.



例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;
    }
}

ティップ

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.

別名を挿入するために {} 構文を使う代わりに、 <return-property> を使い、どの列の別名を使うのかを明示できます。


<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> は複数の列も扱えます。これは、複数列のプロパティをきめ細かく制御できないという、 {} 構文の制限を解決します。


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

この例では、挿入のための {} 構文といっしょに、 <return-property> を使っていることに注意してください。列とプロパティをどのように参照するかを選べます。

マッピングに discriminator が含まれている場合、 discriminator の列を指定するために、 <return-discriminator> を使わなければなりません。

Hibernate はバージョン3から、ストアドプロシージャとストアド関数経由の問い合わせがサポートされました。以降の文書の多くは、両方に当てはまります。ストアドプロシージャやストアド関数を Hibernate で使うためには、1番目の出力パラメータとしてリザルトセットを返さなければなりません。 Oracle 9(もしくはそれ以上のバージョン)のストアドプロシージャの例を以下に示します:


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;

Hibernate でこのクエリを使うためには、名前付きクエリでマッピングする必要があります。


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

注記:今のところ、ストアドプロシージャはスカラとエンティティを返すのみです。 <return-join><load-collection> はサポートされていません。

Hibernate でストアドプロシージャや関数を使うためには、そのプロシージャはいくつかのルールに準拠する必要があります。ルールに準拠していないプロシージャは、 Hibernate で使うことはできません。それでも、準拠していないプロシージャを使いたいのであれば、 session.connection() を通じて実行しなければなりません。ルールはデータベースごとに異なります。ストアドプロシージャのセマンティックスとシンタックスは、データベースベンダごとに異なるためです。

setFirstResult()/setMaxResults() を使って、ストアドプロシージャクエリをページ分けすることはできません。

推奨する呼び出し方は、標準である SQL92 に従うことです。 { ? = call functionName(<parameters>) }{ ? = call procedureName(<parameters>} です。ネイティブな呼び出し構文はサポートされていません。

Oracle には下記のルールが適用されます:

Sybase と MS SQL サーバーに適用されるルールを下記に示します:

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 「Column transformers: read and write expressions」. 例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 例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 例18.13「Overriding SQL statements for collections using annotations」.


ティップ

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.

ティップ

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

これは、まさに(以前議論した)名前付きクエリの宣言です。この名前付きクエリをクラスのマッピングから参照できます:


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

これはストアドプロシージャでさえも動作します。

次のように、コレクションをロードするためのクエリさえ定義してよいです:


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

次のように、結合フェッチによりコレクションをロードするエンティティローダーを定義できます:


<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 例18.11「Custom CRUD via annotations」.