Native SQL query

Chapter 2 native SQL query

You can also use the native SQL language of your database to query data. When you want to use some features of the database (for example, in the query prompt or in the Oracle ConnectKeyword), which is very useful. This will clear the way for you to directly use SQL/jdbcProgramObstacles to migrating to a hibernate-based application.

Hibernate3 allows you to use handwritten SQL to complete all create, update, delete, and load operations (including stored procedures)

17.1. Create an SQL-based Query

SQL query is performed throughSqlqueryIt is obtained by calling the session. createsqlquery () method.

 
List cats = sess. createsqlquery ("select {cat. *} from cats cat "). addentity ("cat", Cat. class );. setmaxresults (50 );. list ();
 

This query specifies:

• An SQL query statement with a placeholder that allows hibernate to use the field alias.

• Queries the returned object and its SQL table alias.

Addentity ()Method to associate the alias of the SQL table with the object class and determine the form of the query result set.

Addjoin ()The todo: Examples!

Native SQL queries may return a simple Scalar Value or a combination of scalar and entity.

Double max = (double) sess. createsqlquery ("select max (cat. weight) as maxweight from cats cat "). addscalar ("maxweight", hibernate. double );. uniqueresult ();
 

17.2. Alias and attribute reference

The{Cat .*}Mark is short for "all attributes. you can explicitly list required fields, but you must have hibernate inject the field alias to each attribute. the positions of these fields are prefixed by the field alias, followed by the attribute name. in the following example (Cat_log).CatObject instead of the table that the cat object originally declared in the ing metadata. note that attribute aliases can be used even in the WHERE clause. for naming queries, the {} syntax is not required. you can get more details in section 17.3 "name SQL query.

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 ();
 

Note:If you explicitly list each attribute, you must include this classAnd its subclass attributes! And its subclasses!

17.3. Name SQL query

You can define the query name in the ing document, and then directly call the named SQL query like calling a named hql query. In this case, weNo Need to callAddentity ()Method.

 
<SQL-query name = "mysqlquery"> <return alias = "person" class = "eg. person "/> select person. name as {person. name}, person. age as {person. age}, person. sex as {person. sex} from person where person. name like 'hiber % '</SQL-query>
 

 
List people = sess. getnamedquery ("mysqlquery"). setmaxresults (50). List ();
 

A named query may return a scalar value. You must use<Return-scalar>Element to specify the field alias and hibernate type

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

<Return-join>And<Load-collection>The element is used as an external connection and the query that defines the initialization set.

17.3.1. Use return-property to explicitly specify the field/alias

Use<Return-property>You can clearly tell hibernate which fields to use.{}-Syntax to allow hibernate to inject its own alias is the opposite.

 
<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 where person. name like: Name </SQL-query>
 

can also be used for multiple fields. It solves the problem of using {}-syntax does not support fine-grained control over multiple fields

 
   
    
     
      
      
     
     
    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 
   

Note that in this example, we use<Return-property>Integration{}To allow users to select how to reference fields and attributes.

If you map a discriminator, you must use <return-discriminator> to specify the fields of the reader.

17.3.2. query using Stored Procedures

Hibernate 3 introduces support for Stored Procedure queries. the stored procedure must return a result set, which is the first external parameter that hibernate can use. the following is an example of a stored procedure for oracle9 and later.

 
Create or replace function ready return ready as st_cursor sys_refcursor; begin open st_cursor for select employee, employer, startdate, enddate, regioncode, Eid, value, currency from employee; return st_cursor; end;
 

To use this query in hibernate, You need to map it through the name 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>
 

 

Note that the stored procedure currently only returns scalar and entity. Currently, this parameter is not supported.<Return-join>And<Load-collection>

17.3.2.1. rules and restrictions on using Stored Procedures

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 mustSession. Connection ()These rules apply to different databases because database providers have different stored procedure syntax and semantics.

Query of stored procedures is not availableSetfirstresult ()/setmaxresults ()Paging.

Oracle has the following rules:

• The stored procedure must return a result set. It is implemented by returning sys_refcursor (in oracle9 or 10). in Oracle, You need to defineRef cursor Type

• The recommended format is {? = Call procname (<parameters> )} Or {? = Call procname}(This is more like an oracle rule than a hibernate rule)

The following rules apply to 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 the first result set as its return value, and others will be discarded.

• If you can setSet nocount on, Which may be more efficient, but this is not necessary.

17.4. Custom SQL is used for create, update, and delete

hibernate3 can use custom SQL statements to execute create, update, and delete operations. In hibernate, persistent classes and collections already contain a set of statements generated during the configuration period (insertsql, deletesql, updatesql, and so on ), these ing tags , , and overload these statements.

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

These SQL statements are executed directly in your database, so you can freely use any syntax you like. However, if you use a specific database syntax, this will certainly reduce the portability of your ing.

If you setCallableTo support the stored procedure.

<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 parameter Location Order is very important. They must be the same as what hibernate expects.

You can set the log debugging levelOrg. hiberante. persister. EntityTo view the sequence that hibernate expects. At this level, Hibernate will print the static SQL statements for the CREATE, update, and delete entities. If you want to see the expected sequence. Remember not to include custom SQL in the ing file, because they will reload the static SQL generated by hibernate.

In most cases (it is best to do this), the stored procedure needs to return the number of rows inserted, updated, and deleted, because hibernate performs some runtime checks on the successful execution of the statement. Hibernate often registers the first parameter of the statement for cud operations as a numeric output parameter.

 
Create or replace function updateperson (UID in number, uname in varchar2) return number isbegin update person set name = uname, where id = uid; return SQL % rowcount; end updateperson;
 

17.5. Custom loading SQL

You may need to declare your own SQL (or hql) to load entities.

 
<SQL-query name = "person"> <return alias = "p" class = "person" lock-mode = "Upgrade"/> select name as {P. name}, ID as {P. id} from person where id =? For update </SQL-query>
 

This is just a name query statement discussed earlier. You can reference this name query in class ing.

<Class name = "person"> <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

Todo: Unfinished example

 
   
    select {empcol. *} from employment empcol where employer =: Id order by startdate ASC, employee ASC 
   
   
    
    select employee as {EMP. employee}, employer as {EMP. employer}, startdate as {EMP. startdate}, enddate as {EMP. enddate}, regioncode as {EMP. regioncode}, ID as {EMP. id} from employment where employer =: ID and enddate is null order by startdate ASC