The difference between "hibernate" Hibernate's clustering query, group query, sorting and time

The HQL statement in Hibernate can basically implement what SQL statements do, just as jquery does for JavaScript. Although the HQL statement is the query of the class, but hql in the implementation of cluster query, group query, sorting and time of the poor query, do not need to query the query results, and then through the processing of the list to get results.

For example, there is a testtable table as follows:

Like "Mysql" to find the most recent difference x days, SQL statement about the date of the pair (click Open link), the Query Date field is now 30 years of the number of items, the SQL statement writes:

Select COUNT (*) from testtable where Timestampdiff (Year,date,now ()) <30;

Its query results are as follows:

The Java statements in Hibernate are implemented as follows, and this output:

String hql= "SELECT COUNT (*) from TestTable t where Timestampdiff (Year,t.date,now ()) <30" String result= Session.createquery (HQL). Uniqueresult (). toString (); SYSTEM.OUT.PRINTLN (result);

The pure HQL query is used here, and the results are only returned. Omit hibernate initialization and configuration, and so on.

Direct use of the Uniqueresult () method can be.

If the query results are multiple rows, for example, or to query the TestTable table just now,

This query is in the "Mysql" using Group by companion having a cluster query (click Open link) query, in username more than 1 times, not including 1 items, and descending order, then the SQL statement is written as follows:

Select Username,count (*) from Testtablegroup by username have coutn (*) >1order by COUNT (*) desc

Its query results are as follows:

In Hibernate, the Java program is written as follows, the key is to convert each row of query results into an object array, then the object array for each item of the forced type conversion, you can complete processing:

String hql= "Select T.username,count (*) from TestTable T GROUP by T.username have Count (*) >1 ORDER by COUNT (*) desc" Li  st<object> resultlist = Session.createquery (hql). List (); for (int i = 0; i < resultlist.size (); i++) {object[] obj = (object[]) resultlist.get (i); System.out.println (obj[0]+ "," +obj[1]);}

The pure HQL query is used here, and the initialization and configuration of Hibernate are omitted. If you want to use the query results for follow-up work,

Then, you can do this:

String Username= (String) obj[0];

As you can see, the HQL statement is actually not the same as SQL, all the keywords in SQL are present in the HQL, but there is a little change.

The key is that in the class to be queried, a replacement name is appended to it, for example, TestTable is replaced by T, and it can be operated smoothly.

The above omitted the entire HQL initialization and the arrangement process, specifically please see "hibernate" Hibernate's Level division, Hibernate4.3 's initialization new writing "(Click Open link), no longer repeat.

Here, the directory structure is this:

which

Dbdao.java, Database Business class, a word has not changed, multiple reuse:

Import Org.hibernate.*;import Org.hibernate.cfg.*;import Org.hibernate.service.*;import Org.hibernate.boot.registry.*;p Ublic class Dbdao {Private Session session;//constructor, initialize session, equivalent to connect database public Dbdao () {/ /Here is the use of the Hibernate4.3.8, hibernate here and the initialization method has been modified, very painful configuration cfg = new configuration (). Configure (); Serviceregistry serviceregistry = new Standardserviceregistrybuilder (). Applysettings (Cfg.getproperties ()). build (); Sessionfactory sessionfactory = cfg.buildsessionfactory (serviceregistry); this.session = SessionFactory.openSession ( );} Execute Query Public query query (String hql) {return session.createquery (HQL);}  Execute INSERT, modify public void Save (Object object) {Transaction transaction=session.begintransaction (); Session.save (object); Transaction.commit (); }//Execute Delete public void Delete (Object object) {Transaction transaction=session.begintransaction (); Session.delete (object) ; Transaction.commit (); }//destructor, interrupt session, equivalent to interrupt database connection protected void Finalize () throws Exception {if (session.isconnected () | | | session! = null) {Session.close ();}}} 

Hibernate.cfg.xml:

<?xml version= "1.0" encoding= "UTF-8"? ><! DOCTYPE hibernate-configuration Public "-//hibernate/hibernate configuration DTD 3.0//en" "http://www.hibernate.org/ DTD/HIBERNATE-CONFIGURATION-3.0.DTD ">Testtable.java, the contents of the database persistence class are as follows, just map all the things in the database to the TestTable table:
Import javax.persistence.*; @Entity @table (name = "TestTable") public class TestTable {private int id;private String Username;private string Number;private string date;//represents the primary key with the auto-generated item @id@generatedvaluepublic int getId () {return Id;} public void setId (int id) {this.id = ID;} @Column (name = "username") public String GetUserName () {return username;} public void Setusername (String username) {this.username = username;} @Column (name = "number") public String GetNumber () {return number;} public void Setnumber (String number) {this.number = number;} @Column (name = "date") public String getDate () {return date;} public void SetDate (String date) {this.date = date;} @Overridepublic String toString () {return id + "," + Username + "," + number + "," + Date;}}
The most critical is the control layer method implementation class, Hql.java, is the above described Hibernate clustering query, packet query, sorting and time difference query using Dbdao.java package, one by one to achieve:
Import java.util.*; @SuppressWarnings ("Unchecked") public class HQL {public static void main (string[] args) {// Build the DAO class Dbdao db = new Dbdao ();//If the return value is unique, use the Uniqueresult () method String result = Db.query ("SELECT count (*) from testtable t whe Re Timestampdiff (Year,t.date,now ()) <30 "). Uniqueresult (). toString (); SYSTEM.OUT.PRINTLN (result); System.out.println ();//Sort and cluster query (group query) list<object> resultlist = Db.query ("Select T.username,count (*) from TestTable T GROUP by T.username have Count (*) >1 ORDER by COUNT (*) desc "). List (); for (int i = 0; i < Resultlist.siz E (); i++) {object[] obj = (object[]) resultlist.get (i); System.out.println (Obj[0] + "," + obj[1]);}}}
Running results such as:

The difference between "hibernate" Hibernate's clustering query, group query, sorting and time