The difference between HQL and SQL
SQL database-oriented table query
HQL Object-oriented query
Hql:from followed by class name + Class object where to use the property of the object to do the condition
Sql:from followed by the table name where to use the field in the table to make the condition
Inquire
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);
1.3 Query with more than 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 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, the native SQL statements in the SQL Server database are 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 batch add 20 objects when manually flush once, assuming that the list is a user lists, contains many user objects, then to implement these objects in batch new, You can use the following methods:
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 properties that need to be modified
User.setfullname ("O-Honey Fruit");
......
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 Number of bars
} catch (Exception e) {
if (ts! = null) {
Ts.rollback ();
}
} finally {
Hibernatesessionfactory.closesession ();
}
The difference between HQL and SQL
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
