HQL and SQL queries

Transfer from http://blog.csdn.net/aaa1117a8w5s6d/article/details/7757097the difference between HQL and SQLTags: sqlhibernatejavasessionuser database2012-07-17 22:03 11614 People read comments (0) favorite reports

Directory (?) [+]

HQL is an object-oriented query, format: from + Class name + Class object + where + object properties

SQL is database-oriented table query, format: from + table name + where + table field

1. Enquiry

It is recommended to use HQL (Hibernate query Language) to query statements when using queries in Hibernate.

When using HQL you need to be aware of:

A, Case sensitive

Because HQL is object-oriented, and the names and properties of object classes are case-sensitive, HQL is also case-sensitive. Therefore, when writing HQL statements, be sure to pay attention to capitalization.

B, FROM clause

The FROM clause is basically similar to SQL, but typically gives the class name an alias (e.g. from Dog D, where D is the object of the Dog class)

The connection to a multi-table query is exactly the same as SQL (e.g. from Dog d,cat c)

1.1 Simple Query examples

Java code

1. List List = Session.createquery ("(select User) from Useras user order by User.loginname"). List ();

Note: The red representation can be omitted, omitting the SELECT keyword equivalent to the SQL SELECT *

1.2 Using placeholder queries

Java code

1. Query query = session.createquery ("Select U.id, u.name from User u where u.name like?");
2. Query.setparameter (0, "% Wang%");
3. List users = Query.list ();

Or

Java code

1. List users = session.createquery ("Select U.id, u.name from User u where u.name like?").  Setparameter (0, "% Wang"). List ();

Note: The subscript position of the 1th placeholder in hibernate starts at 0

1.3 In the form of parameter naming

Single condition:

Java code

1. List users = session.createquery ("Select U.id, u.name from User u where u.name like:myname"). Setparameter ("myname  ", "%% "). List ();

Multi-criteria:

Java code

1. List users = session.createquery ("Select U.id, u.name from User u where u.name like:myname and U.id=:myid"). Setparame  ter ("myname", "% Zhang%"). Setparameter ("myID", 1). List ();

Note: Define named parameter fixed format:: + parameter name (i.e.: myID), assign value, directly write parameter name: Setparameter ("myID", 1), Setparameter method is used to assign a single value to the parameter

1.4 Using in keyword query

Java code

1. List users = session.createquery ("Select U.id, u.name from User u where U.idin (: Myids)"). Setparameterlist ("Myids  ", new object[]{1, 2, 3, 4, 5}). List ();

Note: Because the condition in the in is the form of a collection, the assignment is to use the Setparameterlist method, and the assigned value takes the form of an object array.

1.5 Number of queries

Java code

1. int count = (Integer) session.createquery ("SELECT count (*) from User"). Uniqueresult (). Intvalue ();

1.6 Queries that limit the starting value and number of queries

Java code

1. String hql = "from user as user order by User.loginname";
2. int firstresult= 50;
3. int maxResults = 50;
4. Query query = session.createquery (HQL);
5. query = Query.setfirstresult (Firstresult);
6. Query.setmaxresults (MaxResults);

Note: This is usually required when the records need to be paged, for example, in the above code, the location of the start record of the limit query is 50, the maximum number of query bars is 50.

1.7 Sub-Query

Java code

1. String hql = "From the user user where User.loginname not in (select Ur.user.loginName from userrole ur)";
2. List List = (Session.createquery (HQL)). List ();

Note: In some cases, subqueries also need to be used, for example, in the following example, user object, userrole for the user and the role associated with the object. The following HQL statement finds a user object that does not have a role assigned to it.

1.8 Querying data for a time period

Java code

1. SimpleDateFormat SDF = new SimpleDateFormat ("Yyyy-mm-dd HH:mm:ss");
2. Find employees who entered the workforce from 2010-06-10 to 2010-07-31
3. List users = session.createquery ("Select U.id, u.name from User u where u.create_time between? and? "). Setparameter (0, Sdf.parse ("2010-06-10 00:00:00")). Setparameter (1, Sdf.parse ("2010-07-31 23:59:59")  ). List ();

1.9 Querying----Using database functions is not recommended

Java code

1. String timeunit = "13";
2. String sql = "SELECT COUNT (*) count, CONVERT (VARCHAR (" + Timeunit +"), log.gen_datetime,121) timeunit" + "fro  M log Log ";
3. SQLQuery query = session.createsqlquery (SQL). Addscalar ("Count", Hibernate.integer). Addscalar ("Timeunit",  hibernate.string);
4. List List = Query.list ();

Note: For some complex query statements, it is necessary to call a specific function of a particular database to resolve, Hibernate does not recommend the use of native SQL statements to query, because this will undermine the database portability, but hibernate also provides the use of native SQL query method, You just need to get a connection.

1.10 Querying using native SQL statements

Java code

1. List users = session.createsqlquery ("select * from T_user"). List ();

Note: The use of native SQL statements requires the use of: SQLQuery interface; When using native SQL statements, no data is placed into the session (first level) cache, that is, the list () method in the SQLQuery interface does not put data into the session (first level) cache The list () method of the SQLQuery interface stores the array object in the list collection that is returned by the

2. New

New records in the database do not need to use the Insert command in Hibernate, only need to construct the new object, call the Session object's Save (...) Method can be used.

2.1 Adding a single object

An instance of a new single object is added as follows, which adds a record to the user table.

Java code

2. Session session = Hibernatesessionfactory.getsession ();
4. Transaction ts = null;
6. try {
8. ts = session.begintransaction ();
10. User user = new user ();
12. User.setloginname ("Amigo");
14. User.setfullname ("O-Honey Fruit");
16. ......
18. Session.save (user);
20. Ts.commit ();
22. } catch (Exception e) {
24. if (ts! = null) {
26. Ts.rollback ();
28. }
30. } finally {
32. Hibernatesessionfactory.closesession ();
34. }

2.2 New Batch Objects

For the case of bulk additions, you need to flush and clear once the new part of the object is added, for example, manually flush once for each batch of 20 new objects, assuming that the list is a user listing containing many user objects, then you will implement batch additions to these objects. You can use the following methods

Java code

2. Session session = Hibernatesessionfactory.getsession ();
4. Transaction ts = null;
6. try {
8. ts = session.begintransaction ();
10. For (int i = 0; i < list.size (); i++) {
12. User user = (user) list.get (i);
14. Session.save (user);
16. if (i% = = 0) {
18. Session.flush ();
20. Session.clear ();
22. }
24. }
26. Ts.commit ();
28. } catch (Exception e) {
30. if (ts! = null) {
32. Ts.rollback ();
34. }
36. } finally {
38. Hibernatesessionfactory.closesession ();
40. }

3. Update

In hibernate, you do not need to use a query statement before updating an object: Update ..., you typically need to execute the Session object's update (...) after getting the persisted object that needs to be updated. Method. For example:

Java code

2. Session session = Hibernatesessionfactory.getsession ();
4. Transaction ts = null;
6. try {
8. ts = session.begintransaction ();
10. //Get persisted object
12. User user = Session.get (user.   Class, "Amigo");
14. //Modify the properties that need to be modified
16. User.setfullname ("O-Honey Fruit");
18. ......
20. Session.update (user);
22. Ts.commit ();
24. } catch (Exception e) {
26. if (ts! = null) {
28. . Rollback ();
30. }
32. } finally {
34. Hibernatesessionfactory.closesession ();
36. }

4. Delete

4.1 Deleting a single object

Typically, after an object is acquired, the developer can invoke the Delete (...) of the session object. method to delete the object.
Eg. After you get the user Object LoginName (primary key) "Amigo" in the following instance, delete it.

Java code

2. Session session = Hibernatesessionfactory.getsession ();
4. Transaction ts = null;
6. try {
8. ts = session.begintransaction ();
10. //Get persisted object
12. User user = Session.get (user.   Class, "Amigo");
14. Session.delete (user);
16. Ts.commit ();
18. } catch (Exception e) {
20. if (ts! = null) {
22. Ts.rollback ();
24. }
26. } finally {
28. Hibernatesessionfactory.closesession ();
30. }

4.2 Bulk Delete Objects

In the case of bulk deletion of objects, developers can delete objects after they get a list of objects to be deleted, and for each object deletion method, see section 3.4.1. Developers can also do bulk deletions by HQL statements.
Eg. The instance deletes the record through the DELETE statement, except that the object LoginName is "Amigo", and the remainder is deleted, as shown in the following code:

Java code

2. Session session = Hibernatesessionfactory.getsession ();
4. Transaction ts = null;
6. try {
8. ts = session.begintransaction ();
10. String hql = "Delete user as user where user.loginname! = ' Amigo '";
12. Query query = session.createquery (HQL);
14. int count = Query.executeupdate ();
16. Ts.commit ();
18. System.out.println ("Delete count:" + count); //delete number of bars
20. } catch (Exception e) {
22. if (ts! = null) {
24. Ts.rollback ();
26. }
28. } finally {
30. Hibernatesessionfactory.closesession ();
32. }

HQL and SQL queries