hibernate in general there are three ways of querying: HQL, QBC, and SQL three. However, there are several possible segments:
First, hql Query method
This is one of my most used and favorite, because it is flexible and intuitive to write, and not much worse than the syntax of the familiar SQL. Conditional query, paged query, connection query, nested query, write up and SQL syntax basically consistent, the only difference is to replace the table name with the class or object. Other, including some query functions (count (), sum (), etc.), query conditions, and so on, all the same as SQL syntax.
# # #注意:
keywords are not case-sensitive in hql, but property and class names are case-sensitive
Example 1:
static void query (String name) {
Session S=null;
try{
S=hibernateutil.getsession ();
From after is an object, not a table name
String hql= "from admin as admin where admin.aname=:name";//use named parameters, recommended, easy to read.
Query query=s.createquery (HQL);
Query.setstring ("name", name);
List<admin> list=query.list ();
for (Admin admin:list) {
System.out.println (Admin.getaname ());
}
}finally{
if (s!=null)
S.close ();
}
}
######!!!!!!!!!!!!! For a many-to-one relationship query:
String hql = "from Student where class.classname = ' class Two '";
(The Student entity class contains a reference to the class object .) This is equivalent to two tables of union query )
Example 2 (paged query):
Query query = Session.createquery ("from Customer C
ORDER BY c.name ASC ");
Query.setfirstresult (0);
Query.setmaxresults (10);
List result = Query.list ();
Description
–setfirstresult (int firstresult): Sets the object from which to start the retrieval, the parameter Firstresult represents the index position of the object in the query results, and the starting value of the index position is 0. By default, the query and Criteria interface starts the retrieval from the first object in the query result, which is the object at index position 0.
–setmaxresult (int maxResults): Sets the maximum number of objects retrieved at one time. By default, the query and Criteria interfaces retrieve all of the objects in the results of the search.
Application: Common method, more traditional, similar to JDBC. Cons: New query Language, limited applicability, applies only to hibernate framework.
Second, QBC (query by Criteria) Inquiry method
This approach compares the object-oriented approach, with the emphasis on three objects that describe the condition: restrictions,order,projections. The following three steps are generally required to use QBC queries:
1. Create a Criteria object using the Createcriteria () method of the session instance
2, using the tool class restrictions method for the criteria object set query criteria, Order tool class methods to set the sorting method, projections tool class methods for statistics and grouping.
3. Use the list () method of the criteria object to query and return the results
Common methods of the restrictions class:
Common methods of the restrictions class:
Method name |
Describe |
Restrictions.eq |
Equals |
Restrictions.alleq |
Use Map,key/valu for multiple equals |
Restrictions.gt |
Greater than |
Restrictions.ge |
Greater than or equal |
restrictions.lt |
Less than |
Restrictions.le |
Less than or equal |
Restrictions.between |
The between of the corresponding SQL |
Restrictions.like |
The like of the corresponding SQL |
Restrictions.in |
The in of the corresponding SQL |
Restrictions.and |
and relationship |
Restrictions.or |
or relationship |
Restrictions.sqlrestriction |
SQL qualified Query |
Common methods for order classes:
Method name |
Describe |
Order.asc |
Ascending |
Order.desc |
Descending |
Common methods of projections class
Method name |
Describe |
Projections.avg |
Averaging |
Projections.count |
Count the number of a property |
Projections.countdistinct |
Count the number of different values for a property |
Projections.groupproperty |
Specifies that a property is a grouped property |
Projections.max |
To find the maximum value |
Projections.min |
To find the minimum value |
Projections.projectionlist |
Create a Projectionlist object |
Projections.rowcount |
Number of record bars in the query result set |
Projections.sum |
To find the total of a property |
Example: static void Cri (String name,string password) {
Session S=null;
try{
S=hibernateutil.getsession ();
Criteria C=s.createcriteria (admin.class);
C.add (Restrictions.eq ("Aname", name));//eq is equal to, GT is greater than, LT is less than, or is or
C.add (Restrictions.eq ("Apassword", password));
List<admin> list=c.list ();
for (Admin admin:list) {
System.out.println (Admin.getaname ());
}
}finally{
if (s!=null)
S.close ();
}
}
Example 2 (paged query):
Criteria = Session.createcriteria (Customer.class);
Criteria.addorder (ORDER.ASC ("name")); Sorting method
Criteria.setfirstresult (0);
Criteria.setmaxresults (10);
List result = Criteria.list ()
Application: Object-oriented operation, innovation of the previous database operation mode, easy to read. Disadvantage: The application surface is more limited than HQL.
Third, the QBE (query by Example) Example Query method
Queries a non-empty property of an object as a query condition.
Example: Session session = Sessionfactory.getcurrentsession ();
User user = new user ();
User.setname ("Ijse");
Transaction ts = session.begintransaction ();
try {
Criteria = Session.createcriteria (User.class);
Criteria.add (example.create (user));
User= (User) criteria.list (). get (0);
Session.commit ();
} catch (Hibernateexception ex) {
Ts.rollback ();
Ex.printstacktrace ();
}
System.out.println (User.getname ());
Where applicable: Object-oriented operation. Disadvantage: The applicable surface is more limited than HQL, not recommended.
Four,detachedcriteria: Offline conditional query
Offline query is to set up a Detachedcriteria object, specify the condition of the query, and then pass the object in after Session.begintransaction (). Typically, this object can be established in the presentation layer and then passed into the business layer for querying.
1. Establish Detachedcriteria object
Detachedcriteria dc = Detachedcriteria.forclass (user.class);
int id = 1;
if (id! = 0)
Dc.add (RESTRICTIONS.EQ ("id", id));
Date age = new Date ();
if (age! = null)
Dc.add (Restrictions.le ("Birthday", age));
List users = DC (DC);//Execute Query
SYSTEM.OUT.PRINTLN ("Offline query returns results:" + users);
2. Execute Query
Static List DC (Detachedcriteria DC) {
Session s = hibernateutil.getsession ();
Criteria C = Dc.getexecutablecriteria (s);
List rs = c.list ();
S.close ();
Return RS;
}
Application: Object-oriented operations, separation of business and the underlying, do not require the field attribute ingestion to the DAO implementation layer. Disadvantage: The application surface is more limited than HQL.
Five, named query
1. Configure the following in the data map metafile:
<?xml version= "1.0" encoding= "Utf-8"?>
<! DOCTYPE hibernate-mapping Public "-//hibernate/hibernate mapping DTD 3.0//en"
"Http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<class name= "Com.sy.vo.User" table= "User" catalog= "News" >
....
</class>
<!--named queries: Defining query Conditions--
<query name= "Getuserbyid" >
<! [Cdata[from User where id=:id]]>
</query>
< using SQL in!--named queries, deprecated, affecting cross-database
<sql-query name= "GetUserById2" >
<! [Cdata[select * from User where]]>
</sql-query>
2. Write in Java code:
static List namedquery (int id) {
Session s = hibernateutil.getsession ();
Query q = s.getnamedquery ("Getuserbyid");
Q.setinteger ("id", id);
return Q.list ();
}
Application: The universal method, a bit like the Ibatis lightweight frame operation, convenient maintenance. Cons: Not object-oriented. Based on HQL and SQL, there is a certain flaw.
Vi. SQL query
Example:
static List SQL () {
Session s = hibernateutil.getsession ();
Query q = s.createsqlquery ("SELECT * from User"). Addentity (User.class);
List<user> rs = q.list ();
S.close ();
Return RS;
}
Application: Not familiar with hql friends, and do not intend to transfer database platform friends, omnipotent method shortcomings: the destruction of cross-platform, difficult to maintain, not object-oriented.
Seven,OID Query method
objects are retrieved according to the OID of the object. The get () and load () methods of the session provide this functionality. If you know the OID in advance in your application, you can use this method of retrieving the object.
viii. query.iterator n+1 query (based on a hql, more than one-to-many, many-to-many association mappings)
N + 1 problem, by default, using Query.iterate queries, there can be n+1 problems
The so-called n+1 is a n+1 SQL statement issued at the time of the query.
1: First issue a SQL that queries the list of object IDs
N: According to the ID list to the cache query, if there is no matching data in the cache, then the corresponding SQL statement will be issued according to the ID
* What is the difference between list and iterate?
* The list emits SQL statements each time, and list puts data into the cache without taking advantage of the data in the cache
* Iterate: Iterate utilizes cached data by default, but can be n+1 if no data exists in the cache
Example: Query q=session.createquery ("from UserInfo");
Iterator<userinfo> list=q.iterate ();
while (List.hasnext ()) {
UserInfo st = (UserInfo) it.next ();
System.out.println (St.getname ());
}
To avoid n+1 query resolution:
1. You can change the properties of fetch fetch data to "join" to avoid n+1 queries;
2. Use level Two cache
Nine, review query (based on two:QBC in depth query)
Compound query is based on the original query and then query, you can call the Criteria object's Createcriteria () method in the criteria object based on the query.
Example: Session session = Sessionfactory.getcurrentsession ();
User user = new user ();
Transaction ts = session.begintransaction ();
try {
Criteria criteria1 = Session.createcriteria (Room.class);
Criteria criteria2 =criterial.createcriteria ("User");
Criteria2.add (Restrictions.eq ("name", New String ("Ijse"));
User= (User) criteria.list (). get (0);
Session.commit ();
} catch (Hibernateexception ex) {
Ts.rollback ();
Ex.printstacktrace ();
}
System.out.println (User.getname ());
Hibernate Query Method Summary