Hibernate native SQL query, Hibernate native SQL

Hibernate native SQL query, Hibernate native SQL

Recently, a more complex query was made, and the basic query of hibernate could not meet the requirements. Therefore, we had to use the native SQL query provided by hibernate. Make a summary by referring to some information on the Internet.

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:

session.createSQLQuery("select * from person_info").list();    session.createSQLQuery("select id,name,age from person_info").list();

They all return a List composed of an Object array. Each element of the array is a field value in the person_info 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:

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

Public void scalarQuery () {Session session = HibernateUtil. getSession (); Transaction tx = session. beginTransaction (); String SQL = "select * from person_info"; 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. The array element has three values: 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.

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:

session.createSQLQuery("select id, name, age, teacherID from person_info").addEntity(Person.class).list();

In this way, you can use person. getTeacher () to obtain teacher.

Public void entityQuery () {Session session = HibernateUtil. getSession (); Transaction tx = session. beginTransaction (); String SQL = "select * from person_info"; List list = session. createSQLQuery (SQL ). addEntity (Person. class ). // specify to convert the queried record row to the Person Object list (); for (Iterator iterator = list. iterator (); iterator. hasNext ();) {Person 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:

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:

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.

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

Public void query () {Session session = HibernateUtil. getSession (); // call the name query and directly return the result 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.

<resultset name="person_resultSet">      <return alias="p" class="Person" />      <return-scalar column="p.age" type="int"/>  </resultset>  <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:

Create procedure select_person()    select * 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 ".

<SQL-query name = "callProcedure" callable = "true"> <return class = "Person"> <! -- Convert the queried data column to the attribute of the object --> <return-property name = "name" column = "name"/> <return-property name = "age" column = "age"/> <return-property name = "person_id" column = "id"/> </return> </SQL-query>

Reference: http://www.tuicool.com/articles/Iryaum