HQL and SQL differences

HQL and SQL differences
The difference between 1.HQL and SQL
SQL database Table query HQL Object-oriented query
Hql:from followed by the class name + class object where you use the properties of the object to do the condition Sql:from followed by the table name where to use the table field to make the conditional query
When you use queries in Hibernate, you typically use HQL query statements.
HQL (Hibernate query Language), Hibernate's query language is very similar to SQL. However, the most fundamental difference between HQL and SQL is that it is object-oriented.

The following points need to be noted when using HQL:
L Case Sensitive
Because HQL is object-oriented, and the name and properties of an object class are case-sensitive, hql is case-sensitive. Eg.
HQL statement: From Cat as Cat where cat.id > 1; with from Cat as Cat where cat.id > 1; is different from SQL.

L FROM clause
Eg. From cat, the sentence returns an instance of the Cat object, which the developer can also alias, eg. From Cat as cat, for multi-table queries, refer to the following:
From Cat as Cat, dog as Dog
Other aspects are similar to SQL and are not to be mentioned here. Next you'll find an example of querying in Hibernate.

1.1 Simple Query
List List = Session.createquery ("From user as user order by User.loginname"). List ();

1.2 Queries with a single parameter
List List = Session.find ("From user as user where user.loginname=?", LoginName, hibernate.string);

Query with more than 1.3 parameters

Eg1. This example uses the "?" Placeholder for the way
String hql = "from user as user where user.loginname=?" and user.orgid=? "; Query query = session.createquery (HQL); Query.setparameter (1, ' amigo '); Query.setparameter (2, New Long (1)) List List = query. List ();
Eg2. This example takes the form of ":p Aramname"
String hql = "from user as user where user.loginname=:loginname and User.orgid=:orgid";
Query query = session.createquery (HQL); Query.setparameter (' loginName ', ' amigo '); Query.setparameter (' OrgId ', New Long (1)) List List = query. List (); 1.4 Number of Queries int
Count
=
(Integer)
Session.createquery ("Select
COUNT (*)
From
User "). Uniqueresult (). Intvalue ();
1.5 queries that limit the starting value and number of queries
This is typically required when a record needs to be paged, for example, in the following code, where the start record for the query is limited to 50 and the maximum number of query bars is 50.

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); 1.6 Sub-query
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.
String hql = "From the user user where user.loginname" + "Not in (select Ur.user.loginName from userrole ur)"; List List = (Session.createquery (HQL)). List (); 1.7 Native SQL queries
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 it will undermine the portability of the database, but hibernate also provides the use of native SQL query method, You just need to get a connection.
Eg. In the following example, a native SQL statement in the SQL Server database is used, as follows: String timeunit = "13";
String sql = "SELECT COUNT (*) count, CONVERT (VARCHAR (" + Timeunit + "), log.gen_datetime,121) Timeunit" + "from log log";
SQLQuery query = session.createsqlquery (SQL). Addscalar ("Count", Hibernate.integer). Addscalar ("Timeunit", hibernate.string); List List = Query.list ();

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.          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 Bulk new objects
For the case of bulk new objects, you need to flush and clear once the new part of the object, for example, do not bulk add 20 objects when manually flush once, if the list is a user lists, contains many user objects, then the implementation of these objects in bulk new

The following methods can be used:
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% 20 = = 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:
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 ("pistachio"); ......
Session.update (user) ts.commit (); } catch (Exception e) {
if (ts! = null) {Ts.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. 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:
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 the number of bars} catch (Exception e) {
if (ts! = null) {Ts.rollback ();}
} finally {
Hibernatesessionfactory.closesession (); }

HQL and SQL differences