Shulou(Shulou.com)06/01 Report--

Hibernate supports native SQL queries as well as HQL queries.

Control of native SQL query execution is done through the SQLQuery interface, which is obtained by executing Session.createSQLQuery (). This interface is a subinterface of the Query interface.

The steps to execute the SQL query are as follows:

1. Get the Hibernate Session object

2. Write SQL statements

3. Create a query object through the createSQLQuery method of Session

4. Call the addScalar () or addEntity () method of the SQLQuery object to associate the selected result with a scalar value or entity, which is used for scalar query or entity query, respectively.

5. If the SQL statement contains parameters, call the setXxxx method of Query to assign values to the parameters

6. Call the list method of Query to return the result set of the query

1. Scalar query

The most basic SQL query is to get 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 of an array of Object, with each element of the array being a field value of the person_inf table. Hibernate uses ResultSetMetadata to determine the actual order and type of scalar values returned.

However, excessive use of ResultSetMetadata in JDBC will degrade the performance of the program. So to avoid using ResultSetMetadata too much or to specify a more explicit return type, 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. The field and type to be returned.

It still returns the Object array, but instead of using ResultSetMetdata at this time, it explicitly takes name and age out of the resultset according to the String and int types. At the same time, it also indicates that even if query uses * to query, it may get more than the three fields listed, and only these three fields will be returned.

If you only need to select the value of a field without explicitly specifying the data type of that field, you can use addScalar (String columnAlias).

Examples are as follows:

[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 collection element is an array, and the array element division person_id,person_name,person_ age three-column values

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

}

As you can see from above. The addScalar () method in a scalar query serves two purposes:

1. Specify which data columns are included in the query results-columns that are not selected by addScalar will not be included in the query results.

2. Specify the data type of the data column in the query result

II. Entity query

The scalar result set returned by the scalar query above is the "naked" data returned from resultset. If the result we want is the entity of an object, this can be achieved through the addEntity () method. The addEntity () method converts the result to an entity. However, there are several problems that should be paid attention to in the process of conversion:

1. The query returns all the data columns of a data table

2. The data table has a corresponding persistence class mapping.

Only then can the query result be converted into an entity through 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. The entity to be returned

Assuming that Person is mapped to a class with three fields id,name and age, the above two queries return a List, and each element is a Person entity.

If an entity has an many-to-one association pointing to another entity when mapping, that entity must also be returned when querying (get the mapped foreign key column), otherwise it will result in a "column not found" database error. These additional fields can be returned automatically using * annotations, but we want to specify clearly, take a look at the following example with a many-to-one pointing to teacher:

[java]

Sess.createSQLQuery ("select id, name, age, teacherID from person_inf") .addEntity (Person.class) .list ()

This allows you to get the teacher through person.getTeacher ().

Example:

[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). / / specifies that the record rows of the query are converted into Person entities

List ()

For (Iterator iterator = list.iterator (); iterator.hasNext ();) {

Person person = (Person) iterator.next (); / / each element of the collection 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, and if we use multi-table joins in the SQL statement, the SQL statement can select data from multiple 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 different data tables into different entities. As follows

[java]

Public void multiEntityQuery () {

Session session = HibernateUtil.getSession ()

Transaction tx = session.beginTransaction ()

String sql = "select p. 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 collection element is an array 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 ())

}

}

Third, deal with association and inheritance

The Event connection is obtained by fetching ahead of time, and it is also possible to avoid the extra overhead of initializing proxy. This is done through the addJoin () method, which converts the associated entity of the entity into a query object. As follows:

[java]

Public void joinQuery () {

Session session = HibernateUtil.getSession ()

Transaction tx = session.beginTransaction ()

String sql = "select p. As e where e.person_id=p.person_id. * from person_inf as pome eventual 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 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 myEvent property of the Person object returned by the above program has been fully initialized, no additional operation of the database is required, and the attribute is converted to an entity aliased as e. That is, the returned result is a list of Person and Event object arrays.

IV. Named query

We can put the SQL statement not in the program, but in the configuration file. This can better improve program decoupling.

Hibernate uses an element to configure a named SQL query, which has a required name attribute that specifies the name of the named SQL query.

When you use elements to define named queries, you can include the following elements:

Convert query results to persistent entities

Preload the associated entity of the persistent entity

Convert the data columns of the query to scalar values

When using named SQL queries, there is no need to call methods such as addEntity (), addScalar (), and so on. Because when you configure a named SQL query, you have specified the result information returned by the query.

[html]

Select p.fugma e.* from person_inf as prem eventdeciinf as e where p.person_id = e.person_id and p.age=:age

You can get the specified named sql query by using Session's getNamedQuery.

[java]

Public void query () {

Session session = HibernateUtil.getSession ()

/ / call the named query and return the result directly

List list = session.getNamedQuery ("sqlquery")

.setInteger ("age", 30) .list ()

For (Iterator iterator = list.iterator (); iterator.hasNext ();) {

/ / each collection element is an array 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 mapping information of the result set in the element definition, which allows multiple named queries to share the result set mapping.

[html]

By specifying the resultset-ref attribute for the element, you can have the named SQL query use an existing result set mapping.

[html]

Select p.* from person as p

Fifth, call stored procedures

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 emit a parameter, and the data type is a result set.

Here 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 call stored procedures, you should specify callable= "true" for the element.

[html]

The procedure is the same as above.

There are several issues to pay attention to when calling a stored procedure:

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 must execute them through session.connection (). These rules apply to different databases. Because database providers have different stored procedure syntax and semantics.

Queries to stored procedures cannot be paged using setFirstResult () / setMaxResults ().

The recommended calling method is the standard SQL92: {? = call functionName ()} or {? = call procedureName (}). Native call syntax is not supported.

There are the following rules for Oracle:

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. You need to define a REF CURSOR type in Oracle, see the Oracle manual.

There are the following rules 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 the first result set as its return value, and the rest will be discarded.

This may be more efficient if you can set SET NOCOUNT ON in a stored procedure, but this is not necessary.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.