Addentity () and Setresulttransformer () methods for Createsqlquery in Hibernate

1.

Using SQLQuery
The control of native SQL query execution is performed through the SQLQuery interface, by executing the session. Createsqlquery () Gets the interface. In the simplest case, we can take the following form:

List cats = sess.createsqlquery("Select * from Cats"). Addentity (Cat. Class). List ();

This query specifies the following:

SQL query string

Entity returned by the query

Here, the result set field name is assumed to be the same as the field name indicated in the mapping file. For queries that have multiple tables connected, this can cause problems because fields with the same name may appear in more than one table. The following method avoids the problem of duplicate field names:

List cats = sess.createsqlquery("Select {cat.*} from Cats Cat"). Addentity ("Cat", Cat. Class). List ();

This query specifies the following:

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

The query returns an alias for the entity, and its SQL table.

The Addentity () method ties the alias of the SQL table to the entity class and determines the pattern of the query result set.

The AddJoin () method can be used to load the associations of other entities and collections.

List cats = sess.createsqlquery(
"Select {cat.*}, {kitten.*} from Cats cat, cats kitten where kitten.mother = Cat.id")
. Addentity ("Cat", Cat. Class)
. AddJoin ("Kitten", "Cat.kittens")
. List ();

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

In addition, you can also describe the result set mapping information in your HBM files and use it in queries.

List cats = sess.createsqlquery(
"Select {cat.*}, {kitten.*} from Cats cat, cats kitten where kitten.mother = Cat.id")
. setresultsetmapping ("Catandkitten")
. List ();

Named SQL query
You can define the name of the query in the mapping document, and then call the named SQL query directly, just as you would call a named HQL query. In this case, we do not need to call the Addentity () method.

< Sql-query name = "Persons" >
< return alias = "person" class = "eg". Person "/>
Select Person.name as {Person.name},person. Age as {Person.age},person. SEX as {person.sex} from the person person Where person.name Like:namepattern
</sql-query >List people = sess.getnamedquery ("Persons"). SetString ("Namepattern", Namepattern)
. Setmaxresults (50)
. List ();

2.

Problems with the createsqlquery of Hibernate3

In order to speed up the access, the DAO of some hql operations into SQL, in fact, the main reason is: The operation is a number of tables, the returned data is also derived from the fields of multiple tables;
String sql = "Select a.ID ID, a.name name, b.salary salary from employee A, salary B where ...";
Query Query =getsession (). Createsqlquery (SQL)
. Setresulttransformer (Transformers.aliastobean (Returnemployee.class));
Since the returned ID, name, SALARY is not a single bean corresponding to the table, it is necessary to build a returnemployee bean, which includes the ID, name, SALARY, debug under MySQL, and success.
However, in the Oracle environment, the error is:

Org.hibernate.PropertyNotFoundException:Could not the find setter for ID on class com. Returnemployee

After several hours of troubleshooting, debugging, did not find the problem, can only get rid of Google, and finally in a foreign forum found the answer:

This was actually a limitation of some databases which return alias all uppercase instead of using the casing you actually Specified.

Until then use the. Addscalar (..) to workaround it.

The original hibernate support for Oralce has a bug, so the code is modified:
Query query = getsession (). createsqlquery (SQL). Addscalar ("ID")
. Addscalar ("NAME"). Addscalar ("SALARY");
That's all you need to be aware of.
List EmployeeData = Query.list ();
The data in the returned EmployeeData is object[], so the values are:
List Employeebean = new ArrayList ();
for (int i = 0; i < employeedata.size (); i++) {
Employee Employee = new Employee ();//assemble "bare" data into your employee class

Object[] Object = (object[]) employeedata.get (i);
Employee.setid (Object[0].tostring ());
Employee.setname (Object[1].tostring ());
Employee.setorgtype (Object[2].tostring ());

Employeebean.add (employee);
}

You can also return a map object, which means that there are multiple maps in the list, and the code is as follows
Query query = Session.createsqlquery ("Select Id,name from the tree T where PID in (select id from tree)"). Setresulttransforme R (Transformers.alias_to_entity_map); Returns a Map,key: the name in DB is consistent (case-consistent) when you traverse the list, you can

Map map = (map) list.get[i];

map.get ("id"); Map.get ("name"); to fetch the value. Use the field name after the select of your SQL statement as the key for the map, but the key must be exactly the same as the field name in the database.

It can also be used as a function facet. As
Query query = session.createsqlquery ("Select sum (ID) sumid from the tree T where PID in (select ID from tree)
. Addscalar ("Sumid", Hibernate.integer)//conversion type, by Type in db
. Setresulttransformer (Transformers.alias_to_entity_map); Returns a map,key: matches the name in the DB (case-consistent)

Map.get ("Sumid") can be evaluated directly.

Another point is that this method works correctly on the Hibernate3.2 version.

Now, there are some clues, the following summary:

1 , the Oracle char field is mapped to the character type in Hibernate and is a subset of varchar.

2 , complex SQL with Createsqlquery method query no problem, if query multiple fields, traverse with object[] modelling, subscript starting from 0 output value, do not need mapping file; If you wish, you can write a mapping bean for easy access.

3 , if there is only one field in the query SQL, it cannot be received with the object[] array, it can only be received with the object class, and output object.tostring () directly, that is, the value of this field.

4 , you can use the Addscalar (String arg,type type) method to define the type of field to return, such as

s.createsqlquery (SHUIQINGHQL). Addscalar ("Stcd", hibernate.string). Addscalar ("STNM") ;

This solves the problem where the Char field type only has one character.

But you need to get the other fields Addscalar () in too!

5 , the arguments in Addscalar (String Arg) need to be capitalized!

Turn http://langgufu.iteye.com/blog/1565397