Hibernate statistics Query

Transferred from: http://blog.sina.com.cn/s/blog_5fad23090100fcgr.html

Hibernate statistics Query: Sum take the largest, take the smallest, group

One, the number of query records count (*) keyword

The test method is as follows:

Package com.bjsxt.hibernate;

import java.util.List;

import org.hibernate.Session;

import junit.framework.TestCase;

Public class Statquerytest extends TestCase {

Public void TestQuery1 () {

Session session=null ;

Try {

Session=hibernateutils.getsession ();

Session.begintransaction ();

List Students=session.createquery ("SELECT count (*) from Student"). List ();

Because there is only one number in the list that represents the number of records, the get (0) of the list is executed, and the No. 0 position element is removed.

Long count= (long) students.get (0);

System.out. println (count);

Session.gettransaction (). commit ();

}catch (Exception e) {

E.printstacktrace ();

}finally {

Hibernateutils.closesession (session);

}

}

}

After the method executes, the resulting SQL statement is select COUNT (*) as col_0_0_ from T_student student0_

Execution results are: 110

Second, however, the above way there are shortcomings, the query results are taken out of the list, in fact, the list has only one element. We're going to get the list, then get (0). That's not good.

Hibernate also has one way, if the query returns a single value, such as Count Max Sum, and so on, you can use its uniqueresult, which means the only result. This allows for a single value to be returned. If no value is returned, it is empty.

The test method is as follows:

Public void TestQuery2 () {

Session session=null ;

Try {

Session=hibernateutils.getsession ();

Session.begintransaction ();

Long count= (long) session.createquery ("SELECT count (*) from Student"). Uniqueresult ();

System.out. println (count);

Session.gettransaction (). commit ();

}catch (Exception e) {

E.printstacktrace ();

}finally {

Hibernateutils.closesession (session);

}

}

The resulting query statement is the same as the returned result.

Third, the other query: Sum,max, and so on and count is the same.

Four, group query

The test method is:

Public void TestQuery3 () {

Session session=null ;

Try {

Session=hibernateutils.getsession ();

Session.begintransaction ();

List students=session.createquery ("Select C.name,count (s) from Student S joins s.classes C GROUP by c.name ORDER by C.name Desc "). List ();

for (Iterator iterator=students.iterator (); Iterator.hasnext ();) {

Object[] obj= (object[]) iterator.next ();

System.out. println (obj[0]+ "," +obj[1]);

}

Session.gettransaction (). commit ();

}catch (Exception e) {

E.printstacktrace ();

}finally {

Hibernateutils.closesession (session);

}

}

The resulting query statements are:

Select Classes1_.name as Col_0_0_, Count (student0_.id) as col_1_0_ from T_student student0_ inner join t_classes classes1_ On Student0_.classesid=classes1_.id GROUP by classes1_.name ORDER BY classes1_.name Desc

The result of the execution is:

Class 9,10

Class 8,10

Class 7,10

Class 6,10

Class 5,10

Class 4,10

Class 3,10

Class 2,10

Class 1,10

Class 0,10

2, if the query statement is changed by T_classes connection t_student, its query results are the same

Public void TestQuery3 () {

Session session=null ;

Try {

Session=hibernateutils.getsession ();

Session.begintransaction ();

List students=session.createquery ("Select C.name,count (s) from Classes C joins c.students s GROUP by c.name ORDER by c.na Me desc "). List ();

for (Iterator iterator=students.iterator (); Iterator.hasnext ();) {

Object[] obj= (object[]) iterator.next ();

System.out. println (obj[0]+ "," +obj[1]);

}

Session.gettransaction (). commit ();

}catch (Exception e) {

E.printstacktrace ();

}finally {

Hibernateutils.closesession (session);

}

}

The resulting SQL statement is:

Select Classes0_.name as Col_0_0_, Count (students1_.id) as col_1_0_ from t_classes classes0_ INNER join t_student students 1_ on Classes0_.id=students1_.classesid GROUP by classes0_.name ORDER BY classes0_.name Desc

Query Result:

Class 9,10

Class 8,10

Class 7,10

Class 6,10

Class 5,10

Class 4,10

Class 3,10

Class 2,10

Class 1,10

Class 0,10

3, why the two query method results are the same. Because the first way, the student table on the left, the class table on the right

The second query method, the class table on the left, the student table on the right.

But the display will be: Class 0 students 0

Class 0 Students 1

。。。。

Class 1, Student 0

Class 1, Student 1

。。。。

That is, two tables are connected first, and then the number of students in each class is counted (t_stdent_id).

statistical queries [IMPORTANT]

* But using hibernate to do statistics is still relatively small, usually using statistical middleware. That is, a large number of statistics, a large number of reports, usually using tools to do.