Hibernate Min,Hibernate Min Query,Hibernate Min

Hibernate Min() Function (Aggregate Functions)

In this section, we will show you, how to use the Min() function. Hibernate supports multiple aggregate functions. When they are used in HQL queries, they return an aggregate value ( such as avg(...), sum(...), min(...), max(...) , count(*), count(...), count(distinct ...), count(all...) ) calculated from property values of all objects satisfying other query criteria.

Following is a aggregate function (min() function) with their respective syntax.

min( [ distinct | all ] object.property)

The Min() function aggregates the minimum value of the given column.

Table Name: insurance

insurance_name

invested_amount

investement_date

Life Insurance

25000

0000-00-00 00:00:00

Givan Dhara

20000

2007-07-30 17:29:05

Life Insurance

500

2005-10-15 00:00:00

Car Insurance

2500

2005-01-01 00:00:00

Dental Insurance

500

2004-01-01 00:00:00

Life Insurance

900

2003-01-01 00:00:00

Travel Insurance

2000

2005-02-02 00:00:00

Here is the java code to retrieve the minimum value of "invested_amount" column from insurance table:


package roseindia.tutorial.hibernate;



import java.util.List;



import org.hibernate.Query;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

import org.hibernate.cfg.Configuration;



public class HibernateHQLMinFunction {



  /**

   * @Vinod Kumar

   */

  public static void main(String[] args) {

    // TODO Auto-generated method stub

    Session sess = null;

    try {

      SessionFactory fact = new 

Configuration().configure().buildSessionFactory();

      sess = fact.openSession();

      String SQL_QUERY = "select min

(investementAmount) from Insurance insurance";

      Query query = sess.createQuery(SQL_QUERY);

      List list = query.list();

      System.out.println("Min 

Invested Amount: " + list.get(0));

    }

    catch(Exception e){

      System.out.println(e.getMessage());

    }

  }



}

Output:

log4j:WARN No appenders could be found for logger (org.hibernate.cfg.Environment).

log4j:WARN Please initialize the log4j system properly.

Hibernate: select min(insurance0_.invested_amount) as col_0_0_ from insurance insurance0_

Min Invested Amount: 500

Current Comments

6 comments so far (post your own) View All Comments Latest 10 Comments:

make the following change

String SQL_QUERY = "select min
(insurance.investementAmount) from Insurance insurance";

Posted by vishal on Wednesday, 04.30.08 @ 13:58pm | #58203

View This Comment Separately

String SQL_QUERY = "select avg(insurance.investementAmount)from Insurance insurance";

pgm will execute successfully.

Posted by lakshmi on Saturday, 01.19.08 @ 04:50am | #45551

View This Comment Separately

I also got the same Exception, while running the aggregate functions could pls help me how to fix it
Exception::No data type for node: org.hibernate.hql.ast.AggregateNode
\-[AGGREGATE] AggregateNode: 'min'
\-[ALIAS_REF] IdentNode: 'investmentAmount' {alias=investmentAmount, no from element}

Posted by sateesh on Friday, 01.11.08 @ 16:10pm | #44976

View This Comment Separately

This is due to that u are trying to put in Max attribute to a string/char

Posted by Pradeep Chaitanya Jayanti on Monday, 12.24.07 @ 15:06pm | #43571

View This Comment Separately

an attempt to run the example;DeleteHQLExample.java gave the following error;

Exception in thread "main" java.lang.Error: Unresolved compilation problem:
The method executeUpdate() is undefined for the type Query at roseindia.tutorial.hibernate.DeleteHQLExample.main(DeleteHQLExample.java:21)

I am using MyEclipse, suggestion on the solution would be highly appreciated.

thanks so much.

Posted by dapo on Monday, 11.5.07 @ 15:49pm | #35594

View This Comment Separately

while trying to run package HibernateHQLMinFunction example, l came about an error;

No data type for node: org.hibernate.hql.ast.AggregateNode
\-[AGGREGATE] AggregateNode: 'min'
\-[ALIAS_REF] IdentNode: 'investementAmount' {alias=investementAmount, no from element}

suggestion on how to fix this would be highly appreciated.

thanks in anticipation.

regards.

Posted by dapo on Monday, 11.5.07 @ 15:45pm | #35592

View This Comment Separately