Hibernate in Createsqlquery

transferred from: http://blog.csdn.net/jacman/article/details/8591152
One, addentity () and Setresulttransformer () methods

1.

Using SQLQuery
The control of the native SQL query execution is performed through the SQLQuery interface, which is obtained by executing session.createsqlquery (). 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"); 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. Such 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:

The Char field of 1,oracle 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 would like to write a mapping bean, easy to access.

3, if the query SQL is only one field, it can not be used object[] array to receive, can only be received by the object class, directly output object.tostring (), 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, 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!

The arguments in 5,addscalar (String Arg) need to be capitalized!



Second, sqlquery additions and deletions to change

Queries using SQLQuery in Hibernate:

Basehibernatedao dao = new Basehibernatedao ();
Query statements
String strSQL = "SELECT * from Hrrole h where is h.code like?" and H.id <>? ";
Set Query Object
SQLQuery query = Dao.getsession (). Createsqlquery (strSQL);
Set query parameters
Query.setstring (0, (String) This.getcode (). GetText ());
Set query parameters
Query.setstring (1,this.gettextfield1 (). GetText (). toString ());
Add to a class
Query.addentity (Hrrole.class);
Result data
ArraylistDetermine if there is a value
if (idlist.size ()! = 0) {
Error message
This.label2.setText ("Code already exists");
}

Updates using SQLQuery in Hibernate:
Java code

2. Public void UpdateR (String Code, String newcode) {
4. Basehibernatedao DAO = new Basehibernatedao ();
8. try {
10. Transaction tx = Dao.getsession (). BeginTransaction ();
14. String SQL1 = "Update roleandgrant set Rolecode = '" + Code + "'" + "where Rolecode = '" + newcode + "'";
16. String sql2 = "Update emprole set Rolecode = '" + Code + "'" + "where Rolecode = '" + newcode + "'";
20. SQLQuery Query1 = Dao.getsession (). Createsqlquery (SQL1);
22. SQLQuery Query2 = Dao.getsession (). Createsqlquery (SQL2);
26. Query1.addentity (roleandgrant.   Class);
28. Query2.addentity (empvsrole.   Class);
32. Query2.executeupdate ();
34. Query1.executeupdate ();
38. Tx.commit ();
42. Dao.closesession ();
44. } catch (Exception ex) {
46. System.out.println (ex);
48. Dao.closesession ();
50. }
52. }

Delete using SQLQuery in Hibernate:
Java code

2. Public void Deleter (String Code) {
4. Basehibernatedao DAO = new Basehibernatedao ();
8. try {
10. Transaction tx = Dao.getsession (). BeginTransaction ();
14. String SQL1 = "Delete from roleandgrant where Rolecode = '" + Code + "'";
16. String sql2 = "Delete from emprole where Rolecode = '" + Code + "'";
20. SQLQuery Query1 = Dao.getsession (). Createsqlquery (SQL1);
22. SQLQuery Query2 = Dao.getsession (). Createsqlquery (SQL2);
26. Query1.addentity (roleandgrant.   Class);
28. Query2.addentity (empvsrole.   Class);
32. Query2.executeupdate ();
34. Query1.executeupdate ();
38. Tx.commit ();
42. Dao.closesession ();
44. } catch (Exception ex) {
46. System.out.println (ex);
48. Dao.closesession ();
50. }
52. }

Hibernate createsqlquery