Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Working with different functions in Hibernate Query Language

This article will discuss about order by clause, average function, Sum function, Min function, Max function, Count function in Hibernate Query Language (HQL).

We will assume all classes and configuration files have been created as mentioned in this post about HQL

We will explain how to work with different functions in HQL (Hibernate Query Language), just like:

  • Order by clause
  • Average function
  • Sum function
  • Min function
  • Max function
  • Count function

Let's start to talk about these functions.

Order by Clause

Listing 1: App2 class (Order by clause)

package Main;

/**
 *
 * @author Anurag
 */
import java.util.Iterator;
import model.Course;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;

public class App2 {

    public static void main(String args[]) {

        SessionFactory sf = Utility.getSessionFactory();
        Session s = sf.getCurrentSession();

        Transaction tx = s.beginTransaction();
        String SQL_QUERY = "from Course course order by course.name";
        Query query = s.createQuery(SQL_QUERY);
        for (Iterator it = query.iterate(); it.hasNext();) {
            Course course = (Course) it.next();

            System.out.println("Course Name: " + course.getName());
        }


        tx.commit();
    }
  • order by clause can be used to retrieve the records in sorted manner .
  • We use from order by .
  • We create a query using the above.
  • Now we iterate over result and display course name one by one.

Average function

Listing 2: App2 class (Avg clause)

package Main;

/**
 *
 * @author Anurag
 */
import java.util.Iterator;
import java.util.List;
import model.Course;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;

public class App2 {

    public static void main(String args[]) {

        SessionFactory sf = Utility.getSessionFactory();
        Session s = sf.getCurrentSession();

        Transaction tx = s.beginTransaction();
        String SQL_QUERY = "select avg(id) from Course course";
        Query query = s.createQuery(SQL_QUERY);
        List list = query.list();
        System.out.println("Average" + list.get(0));


        tx.commit();
    }
}
  • Avg clause can be used to get average of particular column.
  • We use” select avg from .
  • We create a query using the above.
  • Now we show the result.

Sum function

Listing3: App2 class (Sum clause)

package Main;

/**
 *
 * @author Anurag
 */
import java.util.Iterator;
import java.util.List;
import model.Course;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;

public class App2 {

    public static void main(String args[]) {

        SessionFactory sf = Utility.getSessionFactory();
        Session s = sf.getCurrentSession();

        Transaction tx = s.beginTransaction();
        String SQL_QUERY = "select sum(id) from Course course";
        Query query = s.createQuery(SQL_QUERY);
        List list = query.list();
        System.out.println("Sum" + list.get(0));


        tx.commit();
    }
}
  • Sum clause can be used to find sum of particular column.
  • We use” select sum from .
  • We create a query using the above.
  • Now we show the result.

Min function

Listing3: App2 class (Min clause)

package Main;

/**
 *
 * @author Anurag
 */
import java.util.Iterator;
import java.util.List;
import model.Course;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;

public class App2 {

    public static void main(String args[]) {

        SessionFactory sf = Utility.getSessionFactory();
        Session s = sf.getCurrentSession();

        Transaction tx = s.beginTransaction();
        String SQL_QUERY = "select min(id) from Course course";
        Query query = s.createQuery(SQL_QUERY);
        List list = query.list();
        System.out.println("Minimum" + list.get(0));


        tx.commit();
    }
}
  • Min clause can be used to find the minimum of particular column.
  • We use” select min from .
  • We create a query using the above.
  • Now we show the result.

Max function

Listing3: App2 class (Max clause)

package Main;

/**
 *
 * @author Anurag
 */
import java.util.Iterator;
import java.util.List;
import model.Course;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;

public class App2 {

    public static void main(String args[]) {

        SessionFactory sf = Utility.getSessionFactory();
        Session s = sf.getCurrentSession();

        Transaction tx = s.beginTransaction();
        String SQL_QUERY = "select max(id) from Course course";
        Query query = s.createQuery(SQL_QUERY);
        List list = query.list();
        System.out.println("Maximum" + list.get(0));
        tx.commit();
    }
}
  • Max clause can be used to find the maximum of particular column
  • We use” select max from
  • We create a query using the above
  • Now we show the result

Count function

Listing3: App2 class (Count clause)

package Main;

/**
 *
 * @author Anurag
 */
import java.util.Iterator;
import java.util.List;
import model.Course;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;

public class App2 {

    public static void main(String args[]) {

        SessionFactory sf = Utility.getSessionFactory();
        Session s = sf.getCurrentSession();

        Transaction tx = s.beginTransaction();
        String SQL_QUERY = "select count(id) from Course course";
        Query query = s.createQuery(SQL_QUERY);
        List list = query.list();
        System.out.println("Count" + list.get(0));
        tx.commit();
    }
}
  • Count clause can be used to find the count of particular column
  • We use” select count from
  • We create a query using the above.
  • Now we show the result.

This is all for this article. See you next time with some more interesting article.



My main area of specialization is Java and J2EE. I have worked on many international projects like Recorders,Websites,Crawlers etc.Also i am an Oracle Certified java professional as well as DB2 certified

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login