Hibernate Reading Notes ----- SQL query

In addition to HQL query, Hibernate also supports native SQL query.
You can use the SQLQuery interface to control the execution of native SQL queries. You can use Session. createSQLQuery () to obtain this interface. This is a subinterface of the Query interface.
The SQL query procedure is as follows:
1. Obtain the Hibernate Session Object
2. Write SQL statements
3. Create a query object using the createSQLQuery method of the Session.
4. Call the addScalar () or addEntity () method of the SQLQuery object to associate the selected results with the scalar value or entity for scalar queries or object queries respectively.
5. If the SQL statement contains parameters, call the setXxxx method of Query to assign values to the parameters.
6. Call the Query list method to return the Query result set.
 
1. scalar Query
The most basic SQL query is to obtain a scalar list:
[SQL]
Session. createSQLQuery ("select * from person_inf"). list ();
 
Session. createSQLQuery ("select id, name, age from person_inf"). list ();
They all return a List composed of an Object array. Each element of the array is a field value in the person_inf table. Hibernate uses ResultSetMetadata to determine the actual sequence and type of returned scalar values.
However, excessive use of ResultSetMetadata in JDBC may reduce program performance. To avoid using ResultSetMetadata too much or to specify a more explicit type of return value, we can use the addScalar () method:
[Java]
Session. createSQLQuery ("select * from person_inf ")
 
. AddScalar ("name", StandardBasicTypes. STRING)
 
. AddScalar ("age", StandardBasicTypes. INT)
 
. List ();
This query specifies:
1. SQL query string.
2. Fields and types to be returned.
It still returns the Object array, but does not use ResultSetMetdata at this time, but explicitly extracts the name and age from the resultset according to the String and int types. At the same time, it also indicates that even if the query uses * for query, the three fields that exceed the list may be obtained, and only the three fields will be returned.
If you only need to select the value of a field and do not need to specify the Data Type of the field, you can use addScalar (String columnAlias ).
Example:
[Java]
Public void scalarQuery (){
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 set element is an array with three values, namely, the array element's person_id, person_name, and person_age.
Object [] 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 ();
}

We can see from the above. In a scalar query, the addScalar () method has two functions:
1. Specify the data columns included in the query results. Columns not selected by addScalar are not included in the query results.
2. Specify the Data Type of the data column in the query result
 
Ii. Object Query
The scalar result set returned by the above scalar query, that is, the "Raw" data returned from the resultset. If the expected result is an object, this can be achieved through the addEntity () method. The addEntity () method converts the result to an object. However, pay attention to the following issues during the conversion process:
1. the query returns all data columns of a data table.
2. The data table has a persistent class ing.
In this case, the query result can be converted to an object using the addEntity () method.
[Java]
Session. createSQLQuery ("select * from perons_inf"). addEntity (Person. class). list;
 
Session. createSQLQuery ("select id, name, age from person_inf"). addEntity (Person. class). list ();
This query specifies:
1. SQL query string
2. entities to be returned
Assume that Person is mapped to a class with three fields: id, name, and age. Both of the preceding queries return a List, and each element is a Person entity.
If the many ing object has a forward-to-one association pointing to another object, the object must also be returned during the query (obtain the ing foreign key column ), otherwise, a "column not found" database error may occur. These additional fields can be automatically returned using the * annotation, but we want to make it clear. See the following example with a pointer-to-one pointing to teacher:
[Java]
Sess. createSQLQuery ("select id, name, age, teacherID from person_inf"). addEntity (Person. class). list ();
In this way, you can use person. getTeacher () to obtain teacher.
Instance:
[Html]
Public void entityQuery (){
Session session = HibernateUtil. getSession ();
Transaction tx = session. beginTransaction ();
String SQL = "select * from person_inf ";
List list = session. createSQLQuery (SQL ).
AddEntity (Person. class). // specify to convert the queried record row to the Person entity.
List ();
For (Iterator iterator = list. iterator (); iterator. hasNext ();){
Person person = (Person) iterator. next (); // each element of the set is a Person object
System. out. println ("name =" + person. getName ());
System. out. println ("age =" + person. getAge ());
 
}
Tx. commit ();
Session. close ();
}
All of the above are single-table queries. If we use multi-table join in SQL statements, the SQL statement can select data from multiple data tables. Hibernate supports converting query results into multiple entities. If you want to convert the query results to multiple entities, you must specify different aliases for different data tables in the SQL string and call the addEntity () method to convert different data tables to different entities. As follows:
[Java]
Public void multiEntityQuery (){
Session session = HibernateUtil. getSession ();
Transaction tx = session. beginTransaction ();
String SQL = "select p. *, e. * from person_inf as p inner join 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 set element is an array composed of Person and MyEvent.
Object [] 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 ());

}
}

 
3. Process Association and inheritance
By capturing the Event connection in advance, it is also possible to avoid additional overhead brought by proxy initialization. This is done through the addJoin () method. This method can be used to convert the correlated object of an object into a query object. As follows:
[Java]
Public void joinQuery (){
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 set element is an array composed of Person and MyEvent.
Object [] 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 ());

}
}
The Person object returned by the above program, its property myEvent property has been fully initialized, no additional database operations are required, and the property is converted to an entity alias e. That is to say, the returned result is the list of Person and Event object arrays.
 
Iv. name query
We can place SQL statements in the configuration file instead of programs. This improves program decoupling.
Hibernate uses <SQL-query... /> element to configure named SQL query, configure <SQL-query... /> the element has a required name attribute, which is used to specify the name of the SQL query.
When you use the <SQL-query.../> element to define a name query, it can contain the following elements:
<Return.../>: converts the query result to a persistent object.
<Return-join.../>: preloads the correlated entities of persistent entities.
<Return-scalar.../>: converts the queried data column to a scalar value.
When using a named SQL query, you do not need to call methods such as addEntity () and addScalar. Because the results returned by the query have been specified when naming SQL queries are configured.
[Html]
<! -- Name SQL query -->
<SQL-query name = "sqlquery">
<! -- Convert the p alias to the Person entity -->
<Return alias = "p" class = "Person"/>
<! -- Convert e alias to Event entity -->
<Return alias = "e" class = "MyEvent"/>
<! -- Returns the name attribute column of the person_inf table 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>
You can use the getNamedQuery of the Session to obtain the specified name SQL query.
[Java]
Public void query (){
Session session = HibernateUtil. getSession ();
// Call the name query and directly return the result
List list = session. getNamedQuery ("sqlquery ")
. SetInteger ("age", 30). list ();
For (Iterator iterator = list. iterator (); iterator. hasNext ();){
// Each set element is an array composed of Person and MyEvent.
Object [] 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 put the result set ing information in the <resultset.../> element definition, so that multiple named queries can map the result set in total.
[Html]
<Resultset name = "person_resultSet">
<Return alias = "p" class = "Person"/>
<Return-scalar column = "p. age" type = "int"/>
</Resultset>
By specifying the resultset-ref attribute for the <SQL-query.../> element, you can use an existing result set ing for the named SQL query.
[Html]
<SQL-query name = "sqlquery" resultset-ref = "person_resultSet">
Select p. * from person as p
</SQL-query>

 
5. Call the Stored Procedure
Hibernate can call stored procedures or functions by naming SQL queries. For a function, the function must return a result set. For a stored procedure, the first parameter of the stored procedure must be passed out 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 first define it as a named SQL query, and then use it in the program.
When you use native SQL to call a stored procedure, you should specify callable = "true" for the <SQL-query.../> element ".
[Html]
<SQL-query name = "callProcedure" callable = "true">
<Return class = "Person">
<! -- Convert queried data columns to Object Attributes -->
<Return-property name = "name" column = "name"/>
<Return-property name = "age" column = "age"/>
<Return-property name = "person_id" column = "id"/>
</Return>
</SQL-query>

The program is the same as above.
Pay attention to the following issues when calling stored procedures:
To use stored procedures in Hibernate, you must follow some rules. stored procedures that do not follow these rules will be unavailable. if you still want to use them, you must pass the session. connection () to execute them. these rules apply to different databases. because database providers have different stored procedure syntax and semantics.
You cannot use setFirstResult ()/setMaxResults () to query stored procedures by page.
The recommended call method is standard SQL92 :{? = Call functionName (<parameters>)} or {? = Call procedureName (<parameters>}. Native call syntax is not supported.
Oracle has the following rules:
The function must return a result set. The first parameter of the 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. For more information, see the Oracle manual.
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 set nocount on in the stored procedure, this may be more efficient, but this is not necessary.

Author: chenssy