[Hibernate] Hibernate cluster query, grouping query, sorting and time difference, hibernate Cluster

[Hibernate] Hibernate cluster query, grouping query, sorting and time difference, hibernate Cluster

In Hibernate, HQL statements can basically implement what SQL statements do, just as jQuery does javascript. Although the HQL statement is a query of classes, the HQL does not need to query the query results because of the poor clustering query, grouping query, sorting, and time, the result is obtained by processing the List.

For example, there is a Testtable table as follows:

Like [Mysql], you need to find the item that is different from the last X days, and compare the date with the SQL statement (click to open the link, query the number of items with the date field 30 years from now, and write the SQL statement as follows:

select count(*) from testtable where timestampdiff(year,date,now())<30;

The query result is as follows:

The Java statement in Hibernate is implemented as follows and output as follows:

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);

Here we use pure HQL queries and the returned results are unique. The initialization and configuration of Hibernate are omitted.

You can directly use the uniqueResult () method.

If the query result is multi-row, for example, query the Testtable table,

This query is performed in [Mysql] using group by with having for clustering query (click to open the link). It appears more than once in username, the SQL statement is written as follows:

select username,count(*) from testtablegroup by username having coutn(*)>1order by count(*) desc

The query result is as follows:

In Hibernate, the Java program is written as follows. The key is to convert each row of the query result into an Object array, and forcibly convert each entry of the Object array to complete the processing:

String hql="SELECT t.username,count(*) FROM Testtable t group by t.username having count(*)>1 order by count(*) desc"List<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]);}

Here, pure HQL queries are used, and Hibernate initialization and configuration are also omitted. If you want to use the query results for subsequent work,

You can handle it like this:

String username=(String)obj[0];

It can be seen that the HQL statement is actually no different from the SQL statement. All the keywords of SQL exist in HQL, but there is a slight change,

The key is to add a replacement name in the class to be queried. For example, if Testtable is replaced with t, the operation can be successful.

The entire HQL initialization and arrangement process is omitted above. For details, refer to [Hibernate] Hibernate hierarchy, new method of initialization of Hibernate4.3 (click to open the link.

Here, the directory structure is as follows:

Where,

DbDAO. java, Database Service class, which has not been modified, is reused multiple times:

Import org. hibernate. *; import org. hibernate. cfg. *; import org. hibernate. service. *; import org. hibernate. boot. registry. *; public class dbDAO {private Session session; // The constructor initializes the Session, which is equivalent to connecting to the database public dbDAO () {// The statement Hibernate4.3.8 is used here, here, Hibernate modified the initialization method again, which is very painful: Configuration cfg = new Configuration (). configure (); ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder (). applySettings (cfg. get Properties ()). build (); SessionFactory sessionFactory = cfg. buildSessionFactory (serviceRegistry); this. session = sessionFactory. openSession () ;}// run the public Query query (String hql) {return session. createQuery (hql);} // insert and modify public void save (Object object) {Transaction transaction = session. beginTransaction (); session. save (object); transaction. commit () ;}// execute the delete public void delete (Object object) {Transactio N transaction = session. beginTransaction (); session. delete (object); transaction. commit () ;}// destructor to interrupt the Session, which is equivalent to interrupting the 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"> In Testtable. java, the content of the database persistence class is as follows:


Import javax. persistence. *; @ Entity @ Table (name = "testtable") public class Testtable {private int id; private String username; private String number; private String date; // indicates the primary key and automatically 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 to use dbDAO to query the clustering, grouping, sorting, and time difference of Hibernate described above. java encapsulation:


Import java. util. *; @ SuppressWarnings ("unchecked") public class HQL {public static void main (String [] args) {// create a DAO class dbDAO db = new dbDAO (); // If the returned value is unique, use the uniqueResult () method String result = db. query ("select count (*) from Testtable t where timestampdiff (year, t. date, now () <30 "). uniqueResult (). toString (); System. out. println (result); System. out. println (); // sorting and clustering query (group query) List <Object> resultList = db. query ("SELECT t. username, count (*) FROM Testtable t group by t. username having count (*)> 1 order by count (*) desc "). list (); for (int I = 0; I <resultList. size (); I ++) {Object [] obj = (Object []) resultList. get (I); System. out. println (obj [0] + "," + obj [1]) ;}}
The running result is as follows: