HQL Between clause example

In this tutorial you will learn how to use HQL Between clause to select a range of entity falling between 2 and 6. You can change the values to get entities as per your requirement.

In this example code we are selecting all the customer object having primary key (id) between 2 and 6.

The HQL Between used for this purpose is:

// Create HQL Between clause
String HQL_QUERY = "select c from Customer c where c.id between 2 and 6";

To run the example you should have some records in the Customer table. Following SQL can be used to create table in database.

DROP TABLE IF EXISTS `customers`;

CREATE TABLE `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_name` varchar(50) DEFAULT NULL,
  `customer_phone` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

/*Data for the table `customers` */

insert  into `customers`(`id`,`customer_name`,`customer_phone`)
  values (1,'Deepak','222222222');
insert  into `customers`(`id`,`customer_name`,`customer_phone`) 
  values (2,'Ravi','6565666877878');
insert  into `customers`(`id`,`customer_name`,`customer_phone`) 
  values (3,'John','154545655');
insert  into `customers`(`id`,`customer_name`,`customer_phone`) 
  values (4,'Dermot','455666');
insert  into `customers`(`id`,`customer_name`,`customer_phone`) 
  values (5,'Raju','5454555');
insert  into `customers`(`id`,`customer_name`,`customer_phone`) 
  values (6,'Dinesh','455455454');

Here is the code of Entity class:

package net.roseindia.model;
import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.GeneratedValue;

/**
 * @author Deepak Kumar
 * Hibernate HQL Tutorials
 */
@Entity
@Table(name="customers")
public class Customer {
	
	@Id
	@GeneratedValue
	@Column(name = "id")  
	private Integer id;
	
	@Column(name="customer_name")
	private String customerName;
	
	@Column(name="customer_phone")
	private String customerPhone;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getCustomerName() {
		return customerName;
	}
	public void setCustomerName(String customerName) {
		this.customerName = customerName;
	}
	public String getCustomerPhone() {
		return customerPhone;
	}
	public void setCustomerPhone(String customerPhone) {
		this.customerPhone = customerPhone;
	}
	
}

Code of HQLBetween.java file:

package net.roseindia.hqlexamples;

import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;

import net.roseindia.model.*;

/**
 * HQL Select Example In this example we are selecting all the customers from
 * the datastore
 */

public class HQLBetween {

	public static void main(String[] args) throws Exception {
		/** Getting the Session Factory and session */
		SessionFactory factory = HibernateUtil.getSessionFactory();
		Session session = factory.getCurrentSession();
		/** Starting the Transaction */
		Transaction tx = session.beginTransaction();

		// Create HQL Between clause
		String HQL_QUERY = "select c from Customer c where c.id between 2 and 6";
		Query query = session.createQuery(HQL_QUERY);
		List result = query.list();
		for (Iterator it = result.iterator(); it.hasNext();) {
			Customer customer = (Customer) it.next();
			System.out.println("ID: " + customer.getId());
			System.out.println("Name: " + customer.getCustomerName());
			System.out.println("Phone: " + customer.getCustomerPhone());
		}

		/** Closing Session */
		session.close();
	}
}

You can run the HQL between example code by executing the HQLBetween  class. Following is the output of the code:

Download HQL Select Source Code

Related Tags for HQL Between clause, HQL Between example: