HQL Date Between Example
Posted on: November 2, 2010 at 12:00 AM
In this tutorial you will learn how to use HQL date between statement in your program.

HQL Date Between Example

In this tutorial I will show you how you can use the HQL Date Between clause in Hibernate program. With the help of HQL between dates statement you can find the entities based on date field.

In this example we are finding the Invoice for some date rage. With the help of following HQL we are finding all the invoices between 2010/10/25 and 2010/11/02.

String HQL_QUERY = "select i from Invoice i where i.invoiceDate between :start and :end ";

The start date and end date is passed in the following code as parameters:

query.setParameter("start", dateFrom);
query.setParameter("end", dateTo);

Here the code of Invoice entity class:

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

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="invoice")
public class Invoice {
	
	@Id
	@GeneratedValue
	@Column(name = "id")  
	private Integer id;
	
	@Column(name="cust_id")
	private Integer customerId;
	

	@Column(name="invoice_date")
	private Date invoiceDate;
	
	@Column(name="dispatch_status")
	private String dispatchStatus;
	
	
	@Column(name="invoice_amount")
	private Integer invoiceAmount;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public Integer getCustomerId() {
		return customerId;
	}

	public void setCustomerId(Integer customerId) {
		this.customerId = customerId;
	}

	public Date getInvoiceDate() {
		return invoiceDate;
	}

	public void setInvoiceDate(Date invoiceDate) {
		this.invoiceDate = invoiceDate;
	}

	public String getDispatchStatus() {
		return dispatchStatus;
	}

	public void setDispatchStatus(String dispatchStatus) {
		this.dispatchStatus = dispatchStatus;
	}

	public Integer getInvoiceAmount() {
		return invoiceAmount;
	}

	public void setInvoiceAmount(Integer invoiceAmount) {
		this.invoiceAmount = invoiceAmount;
	}	
	
	
}

You can use the following code to create invoice table in your database:

DROP TABLE IF EXISTS `invoice`;

CREATE TABLE `invoice` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cust_id` int(11) DEFAULT NULL,
  `invoice_date` datetime DEFAULT NULL,
  `dispatch_status` varchar(1) DEFAULT NULL,
  `invoice_amount` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

/*Data for the table `invoice` */

insert  into `invoice`(`id`,`cust_id`,`invoice_date`,`dispatch_status`,`invoice_amount`) 
     values (1,1,'2010-10-15 14:28:36','N',150);
insert  into `invoice`(`id`,`cust_id`,`invoice_date`,`dispatch_status`,`invoice_amount`) 
     values (2,2,'2010-10-25 14:30:55','N',160);
insert  into `invoice`(`id`,`cust_id`,`invoice_date`,`dispatch_status`,`invoice_amount`) 
     values (3,3,'2010-10-30 14:33:15','N',380);

Here is the code of hibernate.cfg.xml file:

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<!-- Database connection settings -->
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="connection.url">jdbc:mysql://localhost:3306/hqlinvoice</property>
<property name="connection.username">root</property>
<property name="connection.password"></property>
<!-- JDBC connection pool (use the built-in) -->
<property name="connection.pool_size">1</property>
<!-- SQL dialect -->
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<!-- Enable Hibernate's automatic session context management -->
<property name="current_session_context_class">thread</property>
<!-- Disable the second-level cache -->
<property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
<!-- Echo all executed SQL to stdout -->
<property name="show_sql">true</property>
<!-- Drop and re-create the database schema on startup -->
<property name="hbm2ddl.auto">none</property>
<mapping class="net.roseindia.model.Customer"></mapping>
<mapping class="net.roseindia.model.Invoice"></mapping>

</session-factory>
</hibernate-configuration>

Code to run and test example:

package net.roseindia.hqlexamples;

import java.util.Date;
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 java.text.DateFormat;
import java.text.SimpleDateFormat;

import net.roseindia.model.*;

/**
 * HQL Select Example In this example we will show you how you can use HQL
 * Between dates statement the datastore
 */

public class HQLDateBetween {

	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 i from Invoice i where i.invoiceDate between :start and :end ";

		Query query = session.createQuery(HQL_QUERY);

		String strDateFrom = "2010/10/25";
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd");
		Date dateFrom = formatter.parse(strDateFrom);

		String strDateTo = "2010/11/02";
		Date dateTo = formatter.parse(strDateTo);

		query.setParameter("start", dateFrom);
		query.setParameter("end", dateTo);
		List result = query.list();
		for (Iterator it = result.iterator(); it.hasNext();) {
			Invoice invoice = (Invoice) it.next();
			System.out.println("\nInvoice ID: " + invoice.getId());
			System.out.println("Invoice Date: " + invoice.getInvoiceDate());

		}

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

After executing the program you will get the following output:

Hibernate: select invoice0_.id as id1_, invoice0_.cust_id as cust2_1_, invoice0_.dispatch_status as dispatch3_1_, invoice0_.invoice_amount as invoice4_1_, invoice0_.invoice_date as invoice5_1_ from invoice invoice0_ where invoice0_.invoice_date between ? and ?

Invoice ID: 2

Invoice Date: 2010-10-25 14:30:55.0

Invoice ID: 3

Invoice Date: 2010-10-30 14:33:15.0

Download HQL Example Source Code


Related Tags for HQL Date Between Example:

Advertisements

Ads

 
Advertisement null

Ads