Go: Hibernate using SQLQuery

Original: http://hi.baidu.com/luo_qing_long/blog/item/783a15eceb75abdd2f2e21b0.html

The control of the native SQL query execution is performed through the SQLQuery interface, which is obtained by executing session.createsqlquery (). The following describes how to use this API to query.

Scalar query (scalar queries)
The most basic SQL query is to obtain a scalar (numeric) list.

Sess.createsqlquery ("SELECT * from CATS"). List (); Sess.createsqlquery ("Select ID, NAME, BIRTHDATE from CATS"). List (); They all return a list of an Object array (object[]), Each element of the array is a field value of the Cats table. Hibernate uses ResultSetMetaData to determine the actual order and type of scalar values that are returned.

You can use Addscalar () if you want to avoid excessive use of resultsetmetadata, or if you are simply naming the return value for more explicit purposes.

Sess.createsqlquery ("SELECT * from CATS"). Addscalar ("ID", Hibernate.long). Addscalar ("NAME", hibernate.string). Addscalar ("BIRTHDATE", hibernate.date) This query specifies:

SQL query string

fields and types to return

It will still return an object array, but instead of using resultsetmetdata at this point, it is clear that id,name and birthdate are removed from resultset by long,string and short types. It also indicates that even if query is queried using *, it may get more than the three fields listed, and only the three fields will be returned.

It is also possible to not set the type information for all or part of a scalar value.

Sess.createsqlquery ("SELECT * from CATS"). Addscalar ("ID", Hibernate.long). Addscalar ("NAME"). Addscalar ("BIRTHDATE") This is basically the same as the previous query, except that the ResultSetMetaData is used to determine the type of name and birthdate, and the type of the ID is explicitly stated.

about how the Java.sql.Types returned from ResultSetMetaData is mapped to the Hibernate type, which is controlled by the dialect (dialect). If a specified type is not mapped, or if it is not the type you expect, you can define it yourself by Dialet's Registerhibernatetype call.

16.1.2. Entity queries (entities queries)
The above query returns a scalar value, which is the "bare" data returned from resultset. Here's how to get a native query to return an entity object with Addentity ().

Sess.createsqlquery ("SELECT * from CATS"). Addentity (Cat.class); Sess.createsqlquery ("Select ID, NAME, BIRTHDATE from CATS"). Addentity (cat.class); This query specifies:

SQL query string

The entity to return

Assuming that cat is mapped to a class that has three fields of id,name and birthdate, the above two queries return a List, each of which is a cat entity.

If an entity has a many-to-one association to another entity at the time of mapping, it must also be returned at query time, otherwise a "column not found" database error will occur. These additional fields can be returned automatically using the * callout, but we would like to make it clear that the following is an example of a many-to-one that has a point to dog:

Sess.createsqlquery ("Select ID, NAME, BIRTHDATE, dog_id from CATS"). Addentity (Cat.class); so Cat.getdog () will work.

16.1.3. Handling Associations and collection classes (handling associations and collections)
It is possible to get the dog connection by early fetching and avoid the additional overhead of initializing the proxy. This is done through the Addjoin () method, which allows you to connect the associations or collections in.

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"). Add Entity ("Cat", Cat.class). AddJoin ("Cat.dog"); In this example, the returned Cat object, whose dog attribute is fully initialized, no longer requires additional operations from the database. Notice that we added an alias ("Cat") to indicate the target property path of the join. The same advance connection can also be used for collection classes, for example, if Cat has a one-to-many association to dog.

Sess.createsqlquery ("Select ID, NAME, BIRTHDATE, d_id, D_name, cat_id from CATS C, DOGS D WHERE c.id = d.cat_id"). Addenti Ty ("Cat", Cat.class). AddJoin ("Cat.dogs");<p> so far, we hit the ceiling: If SQL queries are not enhanced, These are already the most likely to be done using native SQL queries in Hibernate. The following question is about to occur: What about returning multiple entities of the same type? or the default alias/field is not enough what to do? </p>
16.1.4. Returning multiple entities (returning multiple entities)
So far, the result set field names are assumed to be consistent with the field names specified in the mapping file. If a SQL query joins multiple tables, the same field name may appear multiple times in more than one table, which can cause problems.

The following query requires the use of a field alias injection (This example itself will fail):

Sess.createsqlquery ("Select C.*, m.* from CATS C, CATS m WHERE c.mother_id = c.id"). Addentity ("Cat", Cat.class). Addentit Y ("mother", Cat.class) This query is intended to return two cat instances per row, one for cat and the other for its mother. However, because their field names are mapped to the same, and in some databases, the returned field aliases are in the form of "C.id", "C.name", and they do not match the names in the mapping file ("ID" and "name"), which can cause a failure.

Duplicate field names can be resolved in the following form:

Sess.createsqlquery ("Select {cat.*}, {mother.*} from CATS C, CATS m WHERE c.mother_id = c.id"). Addentity ("Cat", Cat.class ). Addentity ("Mother", Cat.class) This query indicates:

SQL query statement with placeholder attached to hibernate to inject field aliases

Entity returned by the query

The {cat.*} and {mother.*} tags used above appear as shorthand for all properties. Of course you can also explicitly list the field names, but in this example we let Hibernate inject SQL field aliases for each attribute. The placeholder for the field alias is the prefix of the property name plus the table alias. In the following example, we get the cat and its mother from another table (Cat_log) by mapping the specified in the metadata. Note that we can even use property aliases in the WHERE clause if we want to.

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 () 16.1.4.1. Aliases and attribute references (alias and property references)
In most cases, the above attribute injections are required, but in the case of more complex mappings such as composite attributes, inheritance trees through identifiers, and collection classes, there are also special aliases that allow hibernate to inject appropriate aliases.

The following table lists the different possibilities for using an alias injection parameter. Note: Aliases in the following results are examples only, and each alias requires a unique and different name when it is useful.

Table 16.1. Aliases (alias injection names)

Description    Syntax    example  
Simple Property   {[aliasname].[ Propertyname]  a_name as {item.name} 
Composite Property   {[aliasname].[ ComponentName]. [PropertyName]}   currency as {item.amount.currency}, VALUE as {item.amount.value} 
Entity discernment (discriminator of an entity )   {[aliasname].class}  disc as {item.class} 
All properties of the entity   {[aliasname].*}   {item.*} 
Collection key (collection key)   {[aliasname].key}  orgid as {Coll.key}  
Collection id  {[aliasname].id}  empid as {coll.id} 
collection element   {[aliasname]. Element}  xid as {coll.element}  
property of the collection element   {[aliasname].element.[ Propertyname]}  name as {coll.element.name}  
All properties of the collection element   {[aliasname].element.* }  {coll.element.*}  
All properties of the collection   {[aliasname].*}  {coll.*}   

16.1.5. Returning an unmanaged entity (returning non-managed entities)
You can use Resulttransformer for native SQL queries. This returns entities that are not managed by hibernate.

Sess.createsqlquery ("Select NAME, BIRTHDATE from CATS"). Setresulttransformer
(Transformers.aliastobean (Catdto.class))
This query specifies:

SQL query string

Results converter (Result transformer)

The above query will return a list of catdto, which will be instantiated and inject the value of name and birthday into the corresponding attribute or field.

16.1.6. Handling inheritance (Handling inheritance)
A native SQL query, if its query result entity is part of an inheritance tree, must contain all the properties of the base class and all subclasses.

16.1.7. Parameters (Parameters)
Native queries support positional parameters and named parameters:

Query query = sess.createsqlquery ("select * from CATS WHERE NAME?"). 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 (); 16.2. Named SQL query
You can define the name of the query in the mapping document and then call the named SQL query directly, just as you would call a named HQL query. In this case, we addentity the () method.

<sql-query > <return alias= "person" class= "eg. Person "/> Select Person.name as {person.name}. Age as {person.age}, person. SEX as {person.sex} from the person person WHERE person.name like:namepattern </sql-query>list people = Sess.getnamedqu ery ("Persons"). SetString ("Namepattern", Namepattern). Setmaxresults (). List ();<return-join> and < The load-collection> element is used to connect associations and to define the query as pre-initialized for each collection.

<sql-query > <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. The ZIP as {address.zip} from person to JOIN address adddress on person.id = address. person_id and address. Type= ' mailing ' WHERE person.name like:namepattern </sql-query> A named query may return a scalar value. You have to use <return-scalar> element to specify the alias and hibernate type of the field

<sql-query > <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 put the result set mapping information outside the < resultset> element, this can be accessed between multiple named queries, or through the setresultsetmapping () API. (Here the original is doubtful.) The original: You can externalize the resultset mapping informations in a <resultset> element to either reuse them accross SEv eral named queries or through the setresultsetmapping () API.)

<resultset > <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. The ZIP as {address.zip} from person to JOIN address adddress on person.id = address. person_id and address. Type= ' mailing ' WHERE person.name like:namepattern </sql-query> In addition, you can use the result set in the HBM file to define the information directly in the Java code.

List cats = Sess.createsqlquery ("Select {cat.*}, {kitten.*} from Cats cat, cats kitten where kitten.mother = Cat.id"). S Etresultsetmapping ("Catandkitten"). List (); 16.2.1. Use Return-property to explicitly specify fields/aliases
Using <return-property> you can explicitly tell hibernate which field aliases to use, instead of using {}-syntax to inject hibernate into its own alias.

<sql-query > <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 the person person WHERE person.name Like:name </sql-query><return-property> can also be used with multiple fields, which resolves the use of {} -syntax cannot fine-grained control over the limits of multiple fields
<sql-query > <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 F ROM EMPLOYMENT WHERE employer =: ID and ENDDATE are NULL ORDER by StartDate ASC </sql-query> Note In this example, we used the <return- Property> the injection syntax of the union {}. Allows the user to select how fields and properties are referenced.

If you map a recognizer (discriminator), you must use <return-discriminator> to specify the recognizer field

16.2.2. Using stored procedures to query
Hibernate 3 introduces support for stored procedure queries (stored procedure) and functions. In the following descriptions, these are generally applicable. The stored procedure/function must return a result set as the first external parameter that hibernate can use. The following is an example of a Oracle9 and later stored procedure.

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; To use this query in hibernate, you need to map it by using a named query.

<sql-query 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> Note stored procedures currently only return scalars and entities. <return-join> and < are not supported now Load-collection>

16.2.2.1. Rules and restrictions for using stored procedures
In order to use stored procedures in Hibernate, you must follow some rules. Stored procedures that do not follow these rules will not be available. If you still want to use them, you have to execute them through session.connection (). These rules are for different databases. Because the database provider has a variety of different stored procedure syntax and semantics.

Queries against stored procedures cannot be paged using Setfirstresult ()/setmaxresults ().

The recommended method of invocation is standard SQL92: {? = call functionname (<parameters>)} or {? = call procedurename (<PARAMETERS>}. Native invocation syntax is not Support.

The following rules are available for Oracle:

The function must return a result set. The first parameter of a stored procedure must be out, which returns a result set. This is done through the Sys_refcursor type of Oracle 9 or 10. In Oracle you need to define a ref CURSOR type, see the Manual for Oracle.

The following rules are available for Sybase or MS SQL Server:

The stored procedure must return a result set: Note that these servers may return multiple result sets and the number of updates. Hibernate will take out the first result set as its return value, and the others will be discarded.

This may be more efficient if you can set the set NOCOUNT on in the stored procedure, but this is not required.

16.3. Custom SQL for Create,update and delete
Hibernate3 can use custom SQL statements to perform create,update and delete operations. In hibernate, persisted classes and collections already contain a set of statements generated by the configuration period (Insertsql, Deletesql, Updatesql, etc.), these mapping markers <sql-insert>, <sql-delete >, and <sql-update> overloaded these statements.

<class > <id name= "id" > <generator class= "increment"/> </id> <property name= "name" not-null= "True"/> <sql-insert>insert into (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> These SQL are executed directly in your database, so you are free to use any syntax you like. But if you use database-specific syntax, this will of course reduce the portability of your mappings.

If you set callable, you can support the stored procedure.

<class > <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 (?,?)} The position order of </sql-update> </class> parameters is very important and they must be the same order as hibernate expects.

You can see the order that hibernate expects by setting the log debug level to Org.hiberante.persister.entity. At this level, hibernate will print the static SQL for the Create,update and delete entities. (If you want to see the expected order.) Remember not to include custom SQL in the mapping file because they will reload the static SQL generated by Hibernate. )

In most cases (preferably), the stored procedure needs to return the number of inserted/updated/deleted rows because hibernate performs some run-time checks on the success of the statement. Hibernate often registers the first parameter of the statement that carries out the cud operation as a numeric output parameter.

CREATE OR REPLACE FUNCTION Updateperson (uid in number, uname in VARCHAR2) RETURN number is the BEGIN update person Set NAME = uname, where ID = UID; return sql%rowcount; END updateperson;16.4. Custom Load SQL
You may need to declare your own SQL (or HQL) to mount the entity

<sql-query > <return alias= "pers" class= "person" lock-mode= "Upgrade"/> Select NAME as {pers.name}, ID as {pers . ID} from the person WHERE id=? For UPDATE </sql-query> This is just a named query declaration discussed earlier, and you can refer to this named query in the class map.

<class > <id name= "id" > <generator class= "increment"/> </id> <property name= "name" not-null= "True"/> <loader query-ref= "person"/> </class> This can also be used for stored procedures

You can even set a query for the collection mount:

<set inverse= "true" > <key/> <one-to-many class= "Employment"/> <loader query-ref= "employments"/ > </set><sql-query > <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 even define an entity loader that loads a collection by connection fetching:

<sql-query > <return alias= "pers" class= "person"/> <return-join alias= "emp" property= "pers.employments "/> Select NAME as {pers

This article from Csdn Blog, reproduced please indicate the source: http://blog.csdn.net/dknypxt/archive/2010/07/18/5744389.aspx

Go: Hibernate using SQLQuery