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
- 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
- Query query = session.createquery ("Select U.id, u.name from User u where u.name like?");
- Query.setparameter (0, "% Wang%");
- List users = Query.list ();
Or
Java code
- 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
- List users = session.createquery ("Select U.id, u.name from User u where u.name like:myname"). Setparameter ("myname ", "%% "). List ();
Multi-criteria:
Java code
- 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
- 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
- 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
- String hql = "from user as user order by User.loginname";
- int firstresult= 50;
- int maxResults = 50;
- Query query = session.createquery (HQL);
- query = Query.setfirstresult (Firstresult);
- 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
- String hql = "From the user user where User.loginname not in (select Ur.user.loginName from userrole ur)";
- 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
- SimpleDateFormat SDF = new SimpleDateFormat ("Yyyy-mm-dd HH:mm:ss");
- Find employees who entered the workforce from 2010-06-10 to 2010-07-31
- 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
- String timeunit = "13";
- String sql = "SELECT COUNT (*) count, CONVERT (VARCHAR (" + Timeunit +"), log.gen_datetime,121) timeunit" + "fro M log Log ";
- SQLQuery query = session.createsqlquery (SQL). Addscalar ("Count", Hibernate.integer). Addscalar ("Timeunit", hibernate.string);
- 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
- 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
- Session session = Hibernatesessionfactory.getsession ();
- Transaction ts = null;
- try {
- ts = session.begintransaction ();
- User user = new user ();
- User.setloginname ("Amigo");
- User.setfullname ("O-Honey Fruit");
- ......
- Session.save (user);
- Ts.commit ();
- } catch (Exception e) {
- if (ts! = null) {
- Ts.rollback ();
- }
- } finally {
- Hibernatesessionfactory.closesession ();
- }
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
- Session session = Hibernatesessionfactory.getsession ();
- Transaction ts = null;
- try {
- ts = session.begintransaction ();
- For (int i = 0; i < list.size (); i++) {
- User user = (user) list.get (i);
- Session.save (user);
- if (i% = = 0) {
- Session.flush ();
- Session.clear ();
- }
- }
- Ts.commit ();
- } catch (Exception e) {
- if (ts! = null) {
- Ts.rollback ();
- }
- } finally {
- Hibernatesessionfactory.closesession ();
- }
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
- Session session = Hibernatesessionfactory.getsession ();
- Transaction ts = null;
- try {
- ts = session.begintransaction ();
- //Get persisted object
- User user = Session.get (user. Class, "Amigo");
- //Modify the properties that need to be modified
- User.setfullname ("O-Honey Fruit");
- ......
- Session.update (user);
- Ts.commit ();
- } catch (Exception e) {
- if (ts! = null) {
- . Rollback ();
- }
- } finally {
- Hibernatesessionfactory.closesession ();
- }
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
- Session session = Hibernatesessionfactory.getsession ();
- Transaction ts = null;
- try {
- ts = session.begintransaction ();
- //Get persisted object
- User user = Session.get (user. Class, "Amigo");
- Session.delete (user);
- Ts.commit ();
- } catch (Exception e) {
- if (ts! = null) {
- Ts.rollback ();
- }
- } finally {
- Hibernatesessionfactory.closesession ();
- }
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
- Session session = Hibernatesessionfactory.getsession ();
- Transaction ts = null;
- try {
- ts = session.begintransaction ();
- String hql = "Delete user as user where user.loginname! = ' Amigo '";
- Query query = session.createquery (HQL);
- int count = Query.executeupdate ();
- Ts.commit ();
- System.out.println ("Delete count:" + count); //delete number of bars
- } catch (Exception e) {
- if (ts! = null) {
- Ts.rollback ();
- }
- } finally {
- Hibernatesessionfactory.closesession ();
- }
HQL and SQL queries