Hibernate Query Language Example

Hibernate Query Language Example

Hibernate Query Language was created in Hibernate to support SQL as both looks similar but HQL much more powerful than SQL as it understands object structure i.e. polymorphism, inheritance, association and fully object oriented. You could use this in your java program and get data directly map to an object. Be-careful while using as its case sensitive.

Here I will show different real time example of HQL query. Let’s first create one sample table with some data:

• Create table script:

create table OTC.dbo.Test (
	test_id int identity not null,
	name varchar (20) null,
	eventDate date null,
	createTime datetime default getdate() null,
	updateTime datetime null,
	primary key (test_id) 
)

• Insert data script and repeat this three four time with different data:

INSERT INTO OTC.dbo.Test(
   name
  ,eventDate
  ,updateTime
) VALUES (
   'Java Honk'  -- name - IN varchar(20)
   ,'20151226' -- eventDate - IN date
   ,'12/28/2015 12:00:00 AM' -- updateTime - IN datetime
)

• Java model class mapped with Test table:

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

@Entity
@Table(name = "OTC.dbo.Test")
public class TestModel {
	
	@Id
	@Column(name="test_id")
	private String test_id;
	
	@Column(name="name")
	private String name;
	
	@Column(name="eventDate")
	private java.sql.Date eventDate;
	
	@Column(name="createTime")
	private Date createTime;
	
	@Column(name="updateTime")
	@Temporal(TemporalType.TIMESTAMP)
	private Date updateTime;
	
	//Getter and Setters...
	
}

• Select all table data:

@Override
@SuppressWarnings("unchecked")
@Transactional(readOnly = true, propagation=Propagation.NOT_SUPPORTED)
public List<T> getDataBySelectAll() {
	
	Session session = sessionFactory.getCurrentSession();
	Query query = session.createQuery("from TestModel");				
	
	List<T> dataList = query.list();
	return dataList;
}

• Select data with where clause:

@Override
@SuppressWarnings("unchecked")
@Transactional(readOnly = true, propagation=Propagation.NOT_SUPPORTED)
public List<T> getDataBySelectWhereClause() {
	
	Session session = sessionFactory.getCurrentSession();
	Query query = session.createQuery("from TestModel where name = :name");		
	query.setParameter("name", "Java Honk");
	
	List<T> dataList = query.list();
	return dataList;		
}

• Select data by like clause:

@Override
@SuppressWarnings("unchecked")
@Transactional(readOnly = true, propagation=Propagation.NOT_SUPPORTED)
public List<T> getDataBySelectLikeClause() {
	
	Session session = sessionFactory.getCurrentSession();
	Query query = session.createQuery("from TestModel where name like :name");
	query.setParameter("name", "%" + "Java" + "%");
	
	List<T> dataList = query.list();
	return dataList;
}

• Select data by not like clause:

@Override
@SuppressWarnings("unchecked")
@Transactional(readOnly = true, propagation=Propagation.NOT_SUPPORTED)
public List<T> getDataBySelectNotLikeClause() {
	
	Session session = sessionFactory.getCurrentSession();
	Query query = session.createQuery("from TestModel where name not like :name");
	query.setParameter("name", "%" + "Java Honk2" + "%");
	
	List<T> dataList = query.list();
	return dataList;	
}

• Select data by in clause:

@Override
	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true, propagation=Propagation.NOT_SUPPORTED)
	public List<T> getDataBySelectInClause() {
		
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("from TestModel where name in (:name)");
		List<String> nameList =  new ArrayList<String>();
		nameList.add("Java Honk");
		nameList.add("Java Honk2");
		query.setParameterList("name",nameList);
		
		List<T> dataList = query.list();
		return dataList;
	}

• Delete data from table:

@Override
@Transactional(readOnly = false, propagation=Propagation.NOT_SUPPORTED)
public int deleteDataFormTable() {
	
	Session session = sessionFactory.getCurrentSession();
	Query query = session.createQuery("delete TestModel where name = :name");
	query.setParameter("name", "Java Honk");
	int result = query.executeUpdate();
	
	return result;
}

• Update data to the table:

@Override
@Transactional(readOnly = false, propagation=Propagation.NOT_SUPPORTED)
public int updateDataToTable() {
	
	Session session = sessionFactory.getCurrentSession();
	Query query = session.createQuery("update TestModel set name = :name" +" where test_id = :test_id");
	query.setParameter("name", "Java Honk Updated");
	query.setInteger("test_id", 3);
	int result = query.executeUpdate();
	
	return result;
}

• Insert data to the table: Hibernate does not support into values as we do using SQL query and her you will have to insert data from another table which is bulk insert:

@Override
@Transactional(readOnly = false, propagation=Propagation.NOT_SUPPORTED)
public int InsertDataToTable() {
	
	Session session = sessionFactory.getCurrentSession();
	Query query = session.createQuery("insert into TestModel(name, eventDate, updateTime)" +
			" select name, eventDate, updateTime from JavaHonkModel ");
	int result = query.executeUpdate();
	
	return result;
}

 Download Project: HibernateSelectQueryExample

Reference:

• HQL: The Hibernate Query Language