Hibernate.orgCommunity Documentation
You can also express queries in the native SQL dialect of your database. This is useful if you want to utilize database-specific features such as query hints or the CONNECT
keyword in Oracle. It also provides a clean migration path from a direct SQL/JDBC based application to Hibernate.
Hibernate3 allows you to specify handwritten SQL, including stored procedures, for all create, update, delete, and load operations.
Execution of native SQL queries is controlled via the SQLQuery
interface, which is obtained by calling Session.createSQLQuery()
. The following sections describe how to use this API for querying.
가장 기본적인 SQL 질의는 스칼라들(값들)의 목록을 얻는 것이다.
sess.createSQLQuery("SELECT * FROM CATS").list(); sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").list();
These will return a List of Object arrays (Object[]) with scalar values for each column in the CATS table. Hibernate will use ResultSetMetadata to deduce the actual order and types of the returned scalar values.
To avoid the overhead of using ResultSetMetadata
, or simply to be more explicit in what is returned, one can use addScalar()
:
sess.createSQLQuery("SELECT * FROM CATS") .addScalar("ID", Hibernate.LONG) .addScalar("NAME", Hibernate.STRING) .addScalar("BIRTHDATE", Hibernate.DATE)
이 질의는 다음을 지정했다:
SQL 질의 문자열
반환할 컬럼들과 타입들
This will return Object arrays, but now it will not use ResultSetMetadata
but will instead explicitly get the ID, NAME and BIRTHDATE column as respectively a Long, String and a Short from the underlying resultset. This also means that only these three columns will be returned, even though the query is using *
and could return more than the three listed columns.
스칼라들 중 몇몇 또는 전부에 대한 타입 정보를 남겨두는 것이 가능하다.
sess.createSQLQuery("SELECT * FROM CATS") .addScalar("ID", Hibernate.LONG) .addScalar("NAME") .addScalar("BIRTHDATE")
This is essentially the same query as before, but now ResultSetMetaData
is used to determine the type of NAME and BIRTHDATE, where as the type of ID is explicitly specified.
How the java.sql.Types returned from ResultSetMetaData is mapped to Hibernate types is controlled by the Dialect. If a specific type is not mapped, or does not result in the expected type, it is possible to customize it via calls to registerHibernateType
in the Dialect.
위의 질의들은 스칼라 값들을 반환하는 것, 결과셋들로부터 "원래의" 값들을 기본적으로 반환하는 것에 대한 전부였다. 다음은 addEntity()
를 통해 native sql 질의로부터 엔티티 객체들을 얻는 방법을 보여준다.
sess.createSQLQuery("SELECT * FROM CATS").addEntity(Cat.class); sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").addEntity(Cat.class);
이 질의는 다음을 지정했다:
SQL 질의 문자열
그 질의에 의해 반환되는 엔티티
Cat이 컬럼 ID, NAME 그리고 BIRTHDATE로서 매핑된다고 가정하면, 위의 질의들은 둘다 각각의 요소가 하나의 Cat 엔티티인 하나의 List를 반환할 것이다.
만일 그 엔티티가 또 다른 엔티티에 대해 many-to-one
로 매핑되어 있다면 또한 native 질의를 실행할 때 이것을 반환하는 것이 필수적고, 그 밖의 경우 데이터베이스 지정적인 "컬럼이 발견되지 않았습니다" 오류가 일어날 것이다. 추가적인 컬럼은 * 표기를 사용할 자동적으로 반환될 것이지만, 우리는 다음 Dog
에 대한 many-to-one
예제에서처럼 명시적인 것을 더 선호한다:
sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE, DOG_ID FROM CATS").addEntity(Cat.class);
이것은 cat.getDog()이 고유하게 기능하는 것을 허용한다.
프락시를 초기화 시킴에 있어 가능한 특별한 라운드트립을 피하기 위해서 Dog
에서 eagerly join시키는 것이 간으하다. 이것은 addJoin()
메소드를 통해 행해지는데, 그것은 연관이나 콜렉션 내에서 조인시키는 것을 당신에게 허용해준다.
sess.createSQLQuery("SELECT c.ID, NAME, BIRTHDATE, DOG_ID, D_ID, D_NAME FROM CATS c, DOGS d WHERE c.DOG_ID = d.D_ID") .addEntity("cat", Cat.class) .addJoin("cat.dog");
In this example, the returned Cat
's will have their dog
property fully initialized without any extra roundtrip to the database. Notice that you added an alias name ("cat") to be able to specify the target property path of the join. It is possible to do the same eager joining for collections, e.g. if the Cat
had a one-to-many to Dog
instead.
sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE, D_ID, D_NAME, CAT_ID FROM CATS c, DOGS d WHERE c.ID = d.CAT_ID") .addEntity("cat", Cat.class) .addJoin("cat.dogs");
At this stage you are reaching the limits of what is possible with native queries, without starting to enhance the sql queries to make them usable in Hibernate. Problems can arise when returning multiple entities of the same type or when the default alias/column names are not enough.
Until now, the result set column names are assumed to be the same as the column names specified in the mapping document. This can be problematic for SQL queries that join multiple tables, since the same column names can appear in more than one table.
컬럼 alias 주입은 다음 질의(아마 실패할 것이다)에서 필요하다:
sess.createSQLQuery("SELECT c.*, m.* FROM CATS c, CATS m WHERE c.MOTHER_ID = c.ID") .addEntity("cat", Cat.class) .addEntity("mother", Cat.class)
The query was intended to return two Cat instances per row: a cat and its mother. The query will, however, fail because there is a conflict of names; the instances are mapped to the same column names. Also, on some databases the returned column aliases will most likely be on the form "c.ID", "c.NAME", etc. which are not equal to the columns specified in the mappings ("ID" and "NAME").
다음 형식은 컬럼 이름 중복 취약점을 갖지 않는다:
sess.createSQLQuery("SELECT {cat.*}, {mother.*} FROM CATS c, CATS m WHERE c.MOTHER_ID = c.ID") .addEntity("cat", Cat.class) .addEntity("mother", Cat.class)
이 질의는 다음을 지정했다:
컬럼 alias들을 주입하기 위한 Hibernate용 placeholder들을 가진 SQL 질의 문자열
그 질의에 의해 반환되는 엔티티들
The {cat.*} and {mother.*} notation used above is a shorthand for "all properties". Alternatively, you can list the columns explicitly, but even in this case Hibernate injects the SQL column aliases for each property. The placeholder for a column alias is just the property name qualified by the table alias. In the following example, you retrieve Cats and their mothers from a different table (cat_log) to the one declared in the mapping metadata. You can even use the property aliases in the where clause.
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()
In most cases the above alias injection is needed. For queries relating to more complex mappings, like composite properties, inheritance discriminators, collections etc., you can use specific aliases that allow Hibernate to inject the proper aliases.
The following table shows the different ways you can use the alias injection. Please note that the alias names in the result are simply examples; each alias will have a unique and probably different name when used.
표 16.1. alias 주입 이름들
설명 | 구문 | 예제 |
---|---|---|
간단한 프로퍼티 | {[aliasname].[propertyname] | A_NAME as {item.name} |
composite 프로퍼티 | {[aliasname].[componentname].[propertyname]} | CURRENCY as {item.amount.currency}, VALUE as {item.amount.value} |
엔티티의 판별자(Discriminator) | {[aliasname].class} | DISC as {item.class} |
엔티티의 모든 프로퍼티들 | {[aliasname].*} | {item.*} |
콜렉션 키 | {[aliasname].key} | ORGID as {coll.key} |
콜렉션의 id | {[aliasname].id} | EMPID as {coll.id} |
콜렉션의 요소 | {[aliasname].element} | XID as {coll.element} |
property of the element in the collection | {[aliasname].element.[propertyname]} | NAME as {coll.element.name} |
콜렉션 내에 있는 요소의 모든 프로퍼티들 | {[aliasname].element.*} | {coll.element.*} |
콜렉션의 모든 프로퍼티들 | {[aliasname].*} | {coll.*} |
It is possible to apply a ResultTransformer to native SQL queries, allowing it to return non-managed entities.
sess.createSQLQuery("SELECT NAME, BIRTHDATE FROM CATS") .setResultTransformer(Transformers.aliasToBean(CatDTO.class))
이 질의는 다음을 지정했다:
SQL 질의 문자열
결과 변환자(transformer)
위의 질의는 초기화되어 있고 NAME과 BIRTHNAME의 값들을 CatDTO
의 대응하는 프로퍼티들과 필드들 속으로 주입시킨 CatDTO
의 리스트를 반환할 것이다.
Native SQL queries which query for entities that are mapped as part of an inheritance must include all properties for the baseclass and all its subclasses.
Native SQL queries support positional as well as named parameters:
Query query = sess.createSQLQuery("SELECT * FROM CATS WHERE NAME like ?").addEntity(Cat.class); List pusList = query.setString(0, "Pus%").list(); query = sess.createSQLQuery("SELECT * FROM CATS WHERE NAME like :name").addEntity(Cat.class); List pusList = query.setString("name", "Pus%").list();
Named SQL queries can be defined in the mapping document and called in exactly the same way as a named HQL query. In this case, you do not need to call 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();
The <return-join>
element is use to join associations and the <load-collection>
element is used to define queries which initialize 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}, address.STREET AS {address.street}, address.CITY AS {address.city}, address.STATE AS {address.state}, address.ZIP AS {address.zip} FROM PERSON person JOIN ADDRESS address ON person.ID = address.PERSON_ID AND address.TYPE='MAILING' WHERE person.NAME LIKE :namePattern </sql-query>
명명된 SQL 질의는 스칼라 값을 반환할수도 있다. 당신은 <return-scalar>
요소를 사용하여 컬럼 alias와 Hibernate 타입을 선언해야 한다:
<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>
You can externalize the resultset mapping information in a <resultset>
element which will allow you to either reuse them across several named queries or through the setResultSetMapping()
API.
<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}, address.STREET AS {address.street}, address.CITY AS {address.city}, address.STATE AS {address.state}, address.ZIP AS {address.zip} FROM PERSON person JOIN ADDRESS address ON person.ID = address.PERSON_ID AND address.TYPE='MAILING' WHERE person.NAME LIKE :namePattern </sql-query>
You can, alternatively, use the resultset mapping information in your hbm files directly in java code.
List cats = sess.createSQLQuery( "select {cat.*}, {kitten.*} from cats cat, cats kitten where kitten.mother = cat.id" ) .setResultSetMapping("catAndKitten") .list();
You can explicitly tell Hibernate what column aliases to use with <return-property>
, instead of using the {}
-syntax to let Hibernate inject its own aliases.For example:
<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>
also works with multiple columns. This solves a limitation with the {}
-syntax which cannot allow fine grained control of multi-column properties.
<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>
In this example <return-property>
was used in combination with the {}
-syntax for injection. This allows users to choose how they want to refer column and properties.
만일 당신의 매핑이 한 개의 판별자(discriminator )를 가질 경우 당신은 판별자 컬럼을 지정하는데 <return-discriminator>
를 사용해야 한다.
Hibernate3 provides support for queries via stored procedures and functions. Most of the following documentation is equivalent for both. The stored procedure/function must return a resultset as the first out-parameter to be able to work with Hibernate. An example of such a stored function in Oracle 9 and higher is as follows:
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에서 이 질의를 사용하기 위해 당신은 하나의 명명된 질의(a named query)를 통해 그것을 매핑할 필요가 있다.
<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>
Stored procedures currently only return scalars and entities. <return-join>
and <load-collection>
are not supported.
You cannot use stored procedures with Hibernate unless you follow some procedure/function rules. If they do not follow those rules they are not usable with Hibernate. If you still want to use these procedures you have to execute them via session.connection()
. The rules are different for each database, since database vendors have different stored procedure semantics/syntax.
Stored procedure queries cannot be paged with setFirstResult()/setMaxResults()
.
The recommended call form is standard SQL92: { ? = call functionName(<parameters>) }
or { ? = call procedureName(<parameters>}
. Native call syntax is not supported.
Oracle의 경우 다음 규칙들이 적용된다:
A function must return a result set. The first parameter of a procedure must be an OUT
that returns a result set. This is done by using a SYS_REFCURSOR
type in Oracle 9 or 10. In Oracle you need to define a REF CURSOR
type. See Oracle literature for further information.
Sybase 또는 MS SQL server의 경우 다음 규칙들이 적용된다:
The procedure must return a result set. Note that since these servers can return multiple result sets and update counts, Hibernate will iterate the results and take the first result that is a result set as its return value. Everything else will be discarded.
만일 당신이 당신의 프로시저 내에 SET NOCOUNT ON
을 이용 가능하게 할 수 있다면 그것은 아마 보다 효율적이게 될 것이지만 이것은 필요 조건이 아니다.
Hibernate3는 create, update, delete 오퍼레이션들을 위한 맞춤형 문장들을 사용할 수 있다. Hibernate에서 클래스와 콜렉션 영속자들은 구성 시에 생성된 문자열들의 집합(insertsql, deletesql, updatesql 등)을 이미 포함하고 있다. <sql-insert>
, <sql-delete>
, <sql-update>
매핑 태그들은 이들 문자열들을 오버라이드 시킨다:
<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>
The SQL is directly executed in your database, so you can use any dialect you like. This will reduce the portability of your mapping if you use database specific SQL.
만일 callable
속성이 설정되면 내장 프로시저들이 지원된다:
<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>
The order of the positional parameters is vital, as they must be in the same sequence as Hibernate expects them.
You can view 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 view the expected sequence, do not include your custom SQL in the mapping files, as this will override the Hibernate generated static SQL.
The stored procedures are in most cases required to return the number of rows inserted, updated and deleted, as Hibernate has some runtime checks for the success of the statement. Hibernate always registers the first statement parameter as a numeric output parameter for the CUD operations:
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;
You can also declare your own SQL (or HQL) queries for entity loading:
<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>
This is just a named query declaration, as discussed earlier. You can reference this named query in a class mapping:
<class name="Person"> <id name="id"> <generator class="increment"/> </id> <property name="name" not-null="true"/> <loader query-ref="person"/> </class>
이것은 심지어 내장 프로시저들에 동작한다.
You can even define a query for collection loading:
<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>
You can also define an entity loader that loads a collection by join fetching:
<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>
저작권 © 2004 Red Hat Middleware, LLC.