How to set the count(*)

meileng · **Joined:** Wed Jan 25, 2006 7:32 am **Posts:** 34

Hi All,

I have this statement executed using dao with hibernate

dao.find(select s, count(*) from Student s group by s.gender )

Student table
==========================
ID Name Gender
===========================
1 Kate F
-------------------------------------------
2 John M
--------------------------------------------
3 Kim M
=============================

At file Student.hbm.xml I have defined the hibernate mapping for ID, Name, and Gender.

Also I have defined a Student.java as

public class Student{
private int id = "";
private String name = "";
private String gender = "";

public voit setId(int id){
this.id = id;
}

public int getId(){
return id;
}
..........
......the rest of the name and gender pair of set and get method
}

How can I set the count value return and be able to retrieve it?

Thank you!

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

You can't select objects from a grouping. I'm guessing, from your select statement, that you want to retrieve all students, a count of male students, and a count of female students. You'll need at least two different queries to do that:

Code:

from Student

will select all students.

Code:

select s.gender, count(*) from Student s group by s.gender

will return two Object[2] arrays: each will contain a gender and a count.

meileng · **Joined:** Wed Jan 25, 2006 7:32 am **Posts:** 34

YEs but how can I set the count value of both gender in the persistance entity class?

I mean can I map the persistence entity class with the table return? But my entity class is not defining the pair of set and get mehtod. So how can I get the count value for both the gender?

meileng

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Into what object would you be putting the count of students of each gender? You can't put it in a Student object obviously, because there's one object per student. Do you have a Campus object, Enrollment object, something like that?

If you have a suitable class, I'd recommend mapping that as a formula. Let's say you have a School object, and you wanted to put the count of male and female students in the school in there. You'd want something like this:

Code:

<class name="School" ...>
  ...
  <property name="MaleStudentCount" formula="(select count(*) from StudentTable where Gender = 'M'"/>
  <property name="FemaleStudentCount" formula="(select count(*) from StudentTable where Gender = 'F'"/>
  ...
</class>

Is that what you're looking for?

meileng · **Joined:** Wed Jan 25, 2006 7:32 am **Posts:** 34

Hi

No that's not what am looking for.

Let me contruct a better scenario.

For example I have defined 2 tables:

Student
===============
Id Name
===============
1 Jane
---------------------------
2 John
---------------------------
3 Kim
=================

Course
=================
ID Subject Student_id
=================
1 Maths 1
-------------------------------
2 Bio 1
------------------------------
3 Bio 2
=================

When execute this query :

SELECT *, (SELECT COUNT(*) FROM Course c WHERE c.student_id=s.id) AS number_courses
FROM Student s

it will return

=====================
ID Name number_courses
=====================
1 Jane 2
--------------------------------------
2 John 1
-------------------------------------
3 Kim 0
======================

I need this result returned to be able to print out each student has taken how many courses.

For normal case, for example, select * from Student and the result will be mapped to the persistence Student class and we can just use getID() and and getName() to print out the list of the students details. But how about for those aggregate functions like count(), avg(), min(), etc? how can I print those result out? The getCount() or getAvg() is not defined in those persistence class. Or do I need to defined it and how?

please guide. I hope my scenario problem is clear enough.

Thank you

meileng

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

None of those projects/summations apply to individual objects. For something like the number of courses that a student is on, you can use the formula I suggested earlier. Add a courseCount property in the Student class, and map it in hibernate using a <property formula="(subselect)"/>. Note that the subselect is written in SQL, not HQL, and must use table names, not entity names. As an example:

Code:

<class name="Student" ...>
  ...
  <property name="courseCount" formula="(select count(*) from StudentCourseLinkTable link where link.StudentID = StudentID)"/>
  ...
</class>

Columns in the subselect that are not specified with a table name are from the table specified in the class (in this case, StudentTable or whatever).

meileng · **Joined:** Wed Jan 25, 2006 7:32 am **Posts:** 34

Hi

So, is the query

SELECT *, (SELECT COUNT(*) FROM Course c WHERE c.student_id=s.id) AS courseCount
FROM Student s

still valid to execute for the

<class name="Student" ...>
...
<property name="courseCount" formula="(select count(*) from StudentCourseLinkTable link where link.StudentID = StudentID)"/>
...
</class>

defined?

Also, therefore I need to define a pair of set and getter method for courseCount in Student Table?

please advice. Thank you!

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

That query will work, with mods. Remember, you're writing HQL, not SQL. The query will return a list of Object[2], containing the student and the number of courses the student is in. Here's my guess (I don't have your mapping).

Code:

Query q = session.createQuery("SELECT s, (SELECT COUNT(c) FROM Course c JOIN Student cs WHERE cs = s) FROM Student s");
List<Object[]> rets = q.list();
for (Object[] ret : rets)
{
  Student s = (Student) ret[0];
  int c = (int) ret[1];
  System.out.println("Student " + s.getName() + " is taking " + c + " courses");
}

I haven't tested this, but you should get the idea.

jt_1000 · **Posted:** Thu Feb 16, 2006 1:16 am

tenwit wrote:

That query will work, with mods. Remember, you're writing HQL, not SQL.

why not use SQLQuery and rewrite the SQL to be portable with the same meaning:

Code:

SQLQuery q = session.createSQLQuery("SELECT {s.*}, COUNT(c) a cnt FROM FROM Student left outer join Course c on c.student_id=s.id group by {s.*}");

(off the top of my head I am not sure if the "group by {s.*}" will work, you may have to experiment with the group by...part.)

Then use

Code:

q.addScalar("cnt",Hibernate.DOUBLE);
q.addEntity("s",Student.class);

The {s.*} notation used above is a shorthand for "all properties" from the Student class.

That should get you going...

meileng · **Joined:** Wed Jan 25, 2006 7:32 am **Posts:** 34

Hi

I would like to know why the formula is not defined as

formula="(SELECT COUNT(*) FROM Course c WHERE c.student_id=s.id)"

instead of

formula="(select count(*) from StudentCourseLinkTable link where link.StudentID = StudentID)

?

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

I was guessing your schema. I'm sure that your SQL is correct.

attups · **Joined:** Fri Apr 06, 2007 8:31 pm **Posts:** 7

tenwit wrote:

That query will work, with mods. Remember, you're writing HQL, not SQL. The query will return a list of Object[2], containing the student and the number of courses the student is in. Here's my guess (I don't have your mapping).

Code:

Query q = session.createQuery("SELECT s, (SELECT COUNT(c) FROM Course c JOIN Student cs WHERE cs = s) FROM Student s");
List<Object[]> rets = q.list();
for (Object[] ret : rets)
{
  Student s = (Student) ret[0];
  int c = (int) ret[1];
  System.out.println("Student " + s.getName() + " is taking " + c + " courses");
}

I haven't tested this, but you should get the idea.

attups · **Joined:** Fri Apr 06, 2007 8:31 pm **Posts:** 7

I think when we have aggregate values , hIbernate returns Object[] for each row. We cannot typecast it to the persistent class as I am getting classcast exception

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Yep. That's what my example code that you quoted shows.

attups · **Joined:** Fri Apr 06, 2007 8:31 pm **Posts:** 7

That's my concern.

In your code you have put
Student s = (Student) ret[0];

But this is giving ClassCastException. Is it that I am doing something wrong?