Native SQL query for Hibernate

Hibernate supports native SQL queries in addition to HQL queries.
The control of the native SQL query execution is performed through the SQLQuery interface, which is obtained by executing session.createsqlquery (). The interface is a sub-interface of the query interface.
The steps to execute the SQL query are as follows:
1. Get Hibernate Session Object
2. Writing SQL statements
3. Create a Query object through the Createsqlquery method of the session
4. Call the Addscalar () or Addentity () method of the SQLQuery object to associate the selected result with a scalar value or entity, respectively, for scalar queries or entity queries
5. If the SQL statement contains parameters, call query's Setxxxx method to assign a value to the parameter
6. Call query's list method to return the result set of the query

One, scalar query
The most basic SQL query is to get a scalar list:

Session.createsqlquery ("Select* from person_inf"). List ();  Session.createsqlquery ("select from

They all return a list of object arrays, each of which is a field value of the Person_inf table. Hibernate uses ResultSetMetaData to determine the actual order and type of scalar values that are returned.
However, excessive use of resultsetmetadata in JDBC can degrade the performance of the program. So in order to avoid using resultsetmetadata too much or to specify a more explicit return value type, we can use the Addscalar () method:

Session.createsqlquery ("SELECT * from Person_inf")  . Addscalar ("name", standardbasictypes.string)  . Addscalar ("Age", standardbasictypes.int)  

This query specifies the following:
1. SQL query string.
2. The fields and types to return.
It will still return an object array, but instead of using resultsetmetdata at this point, it is clear that name and age are removed from resultset by string and int 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.
You can use Addscalar (String columnalias) If you only need to select the value of a field, and you do not need to explicitly specify the field's data type.
Examples are as follows:

 Public voidScalarquery () {Session session=hibernateutil.getsession (); Transaction TX=session.begintransaction (); String SQL= "SELECT * FROM Person_inf"; List List=session.createsqlquery (SQL). Addscalar ("PERSON_ID", Standardbasictypes.integer). Addscalar ("Name", standardbasictypes.string). Addscalar ("Age", Standardbasictypes.integer). List ();  for(Iterator Iterator =list.iterator (); Iterator.hasnext ();) {//Each collection element is an array, and the array element division person_id,person_name,person_age three columns of valueObject[] Objects =(object[]) iterator.next (); System.out.println ("Id=" +objects[0]); System.out.println ("Name=" +objects[1]); System.out.println ("Age=" +objects[2]); System.out.println ("----------------------------");         } tx.commit ();     Session.close (); } 

As can be seen from the above. The Addscalar () method in a scalar query has two functions:
1. Specify which data columns the query results contain---columns that are not addscalar selected will not be included in the query results.
2. Specify the data type of the data column in the query result

Second, the entity query
The scalar result set returned by the above scalars query, which is the "bare" data returned from resultset. If the result we want is an entity of an object, it can be implemented by means of the addentity () method. The Addentity () method can tell the result to be converted to an entity. However, there are several issues to be aware of during the conversion process:
1. The query returns all data columns of a data table
2, the data table has a corresponding persistent class map
The query results can then be converted to entities through the addentity () method.

Session.createsqlquery ("SELECT * from Perons_inf"). Addentity (person.  Class). List;  Session.createsqlquery ("Select Id,name,age from Person_inf"). Addentity (person.  Class

This query specifies:
1. SQL query string
2. Entities to return
Assuming that the person is mapped to a class that has id,name and age three fields, the two queries above return a list, each of which is a person entity.
If an entity has a many-to-one association to another entity at the time of the mapping, it must also return that entity at query time (get the mapped foreign key column), or it will cause a "column not found" database error to 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 with points to teacher:

Sess.createsqlquery ("Select ID, Name, age, Teacherid from Person_inf"). Addentity (person.  Class

This allows the teacher to be obtained through Person.getteacher ().
Instance:

 Public voidEntityquery () {Session session=hibernateutil.getsession (); Transaction TX=session.begintransaction (); String SQL= "SELECT * FROM Person_inf"; List List=session.createsqlquery (SQL). Addentity (person.class).//specifies that the row of records for a query is converted to a person entitylist ();  for(Iterator Iterator =list.iterator (); Iterator.hasnext ();) { person person= (person) iterator.next ();//each element of the collection is a person objectSystem.out.println ("name=" +person.getname ()); System.out.println ("Age=" +person.getage ());     } tx.commit (); Session.close (); } 

All of these are single-table queries, and if we use multiple table joins in SQL statements, the SQL statement can select data from multiple data tables. Hibernate supports the conversion of query results into multiple entities. If you want to convert the query results to multiple entities, you should specify different aliases for different data tables in the SQL string, and call the Addentity () method to convert the different data tables to different entities. As follows

 Public voidMultientityquery () {Session session=hibernateutil.getsession (); Transaction TX=session.begintransaction (); String SQL= "Select p.*,e.* from Person_inf as P inner joins Event_inf as E" + "on p.person_id=e.person_id"; List List=session.createsqlquery (SQL). Addentity ("P", person.class). Addentity ("E", MyEvent.class). List ();  for(Iterator Iterator =list.iterator (); Iterator.hasnext ();) {//Each collection element is an array of person, MyEventObject[] Objects =(object[]) iterator.next (); Person Person= (person) objects[0]; MyEvent Event= (MyEvent) objects[1]; System.out.println ("Person_id=" +person.getid () + "Person_name=" +person.getname () + "title=" +event.gettitle ()); } } 

Iii. dealing with associations and inheritance
It is possible to get the event connection by early fetching, and avoid the additional overhead associated with initializing the proxy. This is done through the Addjoin () method, through which the entity's associated entity can be converted into a query object. As follows:

 Public voidJoinquery () {Session session=hibernateutil.getsession (); Transaction TX=session.begintransaction (); String SQL= "Select p.*,e.* from Person_inf as p,event_inf as E where e.person_id=p.person_id"; List List=session.createsqlquery (SQL). Addentity ("P", person.class). AddJoin ("E", "p.myevents"). List ();  for(Iterator Iterator =list.iterator (); Iterator.hasnext ();) {//Each collection element is an array of person, MyEventObject[] Objects =(object[]) iterator.next (); Person Person= (person) objects[0]; MyEvent Event= (MyEvent) objects[1]; System.out.println ("Person_id=" +person.getid () + "Person_name=" +person.getname () + "title=" +event.gettitle ()); } } 

Four, named query
Instead of putting SQL statements in a program, we can put them in a configuration file. This can improve program decoupling better.
Hibernate uses the <sql-query.../> element to configure a named SQL query, and the configuration <sql-query.../> element has a required Name property that specifies the name of the named SQL query.
When you use the <sql-query.../> element to define a named query, you can include several elements:
&LT;RETURN.../&GT;: Converting query results to persisted entities
&LT;RETURN-JOIN.../&GT: Pre-load an associated entity for a persisted entity
&LT;RETURN-SCALAR.../&GT: Converting a query's data column to a scalar value
When you use a named SQL query, you do not need to call addentity (), Addscalar (), and so on. Because when you configure a named SQL query, you have specified the result information returned by the query.
[HTML]
<!--named SQL query-
<sql-query name= "SQLQuery" >
<!--convert P aliases to person entities--
<return alias= "P" class= "person"/>
<!--convert E aliases to event entities--
<return alias= "E" class= "MyEvent"/>
<!--specifies that the Name property column of the Person_inf table be returned as a scalar value-
<return-scalar column= "P.name" type= "string"/>
Select p.*,e.* from Person_inf as p,event_inf as e where p.person_id = e.person_id and P.age=:age
</sql-query>
The specified named SQL query can be obtained using the getnamedquery of the session.

 Public voidQuery () {Session session=hibernateutil.getsession (); //call a named query and return the result directlyList List = Session.getnamedquery ("SQLQuery"). Setinteger ("Age", 30). List ();  for(Iterator Iterator =list.iterator (); Iterator.hasnext ();) {//Each collection element is an array of person, MyEventObject[] Objects =(object[]) iterator.next (); Person Person= (person) objects[0]; MyEvent Event= (MyEvent) objects[1]; System.out.println ("Person_id=" +person.getid () + "Person_name=" +person.getname () + "title=" +event.gettitle ()); } session.close (); } 

Hibernate allows you to place the mapping information for the result set in the <resultset.../> element definition so that multiple named queries can share the result set mappings.

<ResultSetname= "Person_resultset">     <returnalias= "P"class= "Person" />     <Return-scalarcolumn= "P.age"type= "int"/> </ResultSet>by providing<sql-query .../>element to specify the Resultset-ref property, you can have the named SQL query map with an existing result set. [HTML]<Sql-queryname= "SQLQuery"Resultset-ref= "Person_resultset">Select p.* from person as P</Sql-query> 

V. Calling a stored procedure
Hibernate can call a stored procedure or function by naming a SQL query. For a function, the function must return a result set, and for a stored procedure, the first parameter of the stored procedure must be outgoing, and the data type is the result set.
The following is a simple stored procedure:
[SQL]
Create Procedure Select_person ()

ECT * from Person_inf;
If you need to use this stored procedure, you can define it as a named SQL query and then use it in your program.
When using native SQL to invoke a stored procedure, you should specify callable= "true" for the <sql-query.../> element.

<Sql-queryname= "CallProcedure"callable= "true">     <returnclass= "Person">         <!--to convert a query's data column to an entity's properties -         <Return-propertyname= "Name"column= "Name"/>         <Return-propertyname= "Age"column= "Age" />         <Return-propertyname= "person_id"column= "id" />     </return> </Sql-query> 

The program is the same as above.
There are several issues to be aware of when calling 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 Providers have a variety of different syntax and semantics for stored procedures.
Queries against stored procedures cannot be paged using Setfirstresult ()/setmaxresults ().
The recommended method of invocation is standard SQL92: {? = call functionname (<parameters>)} or {? = call procedurename (&LT;PARAMETERS&GT;}. Native invocation syntax is not supported Hold.
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.

Chenssy Original: http://blog.csdn.net/houpengfei111/article/details/10096215

Native SQL query for Hibernate