Hibernate HQL, how to retrieve selected fields

Below example code shows how to retrieve selected fields from database. As shown below a List of Object[] is being returned, by session.query(“SELECT Y.firstName, Y.lastName FROM Employee Y WHERE Y.salary >9000”);

Then List need to be iterated, in which each element is an Object array of size 2(since only two fields are being selected). You can download full NetBeans project sour code here.

import org.hibernate.HibernateException; 
import org.hibernate.Transaction;
import org.hibernate.Session;
import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class HQLManageEmployee {
   private static SessionFactory factory; 
   public static void main(String[] args) {
      try{
          //step 1: Create Session Factory
         factory = new Configuration().configure().buildSessionFactory();
      }catch (Throwable ex) { 
         System.err.println("Failed to create sessionFactory object." + ex);
         throw new ExceptionInInitializerError(ex); 
      }
      HQLManageEmployee ME = new HQLManageEmployee();

      /* List down all the employees */
      ME.listEmployees();
   }

   /* Method to  READ all the employees */
   public void listEmployees( ){
      Session session = factory.openSession();
      Transaction tx = null;
      try{
         tx = session.beginTransaction();

         Query qry = session.createQuery("SELECT Y.firstName, Y.lastName FROM Employee Y WHERE Y.salary >9000");

          List<Object []> employees = qry.list(); 
         for (Iterator iterator = 
                           employees.iterator(); iterator.hasNext();){
            Object [] emp =  (Object [])iterator.next(); 

            System.out.println(emp[0]+" --- "+emp[1]);
         }
         tx.commit();
      }catch (HibernateException e) {
         if (tx!=null) tx.rollback();
         e.printStackTrace(); 
      }finally {
         session.close(); 
      }
   }
}

Example HibernateHQL

Java Generate PDF file from database records

Java Generate PDF using Hibernate to read records from database

import java.util.*;
public class Student
{
	private int studentId;
	private String studentName;
	private String studentSex;
	private String studentCourseName;
	private String studentPhno;
	private int studentMarks;
	private String studentAddress;
	public Student( int studentId,String studentName, String studentSex, String studentCourseName, String studentPhno,int studentMarks, String studentAddress) {
		this.studentId=studentId;
		this.studentName=studentName;
		this.studentSex= studentSex;
		this.studentCourseName=studentCourseName;
		this.studentPhno=studentPhno;
		this.studentMarks=studentMarks;
		this.studentAddress=studentAddress;
	}
	
	
	
	@Override
	public String toString() {
		return "" + studentId + "\t\t" + studentName + "\t\t" + studentSex
				+ "\t\t" + studentCourseName + "\t\t" + studentPhno + "\t\t"
				+ studentMarks + "\t\t\t" + studentAddress ;
	}



	public int getStudentId() {
		return studentId;
	}


	public void setStudentId(int studentId) {
		this.studentId = studentId;
	}


	public String getStudentName() {
		return studentName;
	}


	public void setStudentName(String studentName) {
		this.studentName = studentName;
	}


	public String getStudentSex() {
		return studentSex;
	}


	public void setStudentSex(String studentSex) {
		this.studentSex = studentSex;
	}


	public String getStudentCourseName() {
		return studentCourseName;
	}


	public void setStudentCourseName(String studentCourseName) {
		this.studentCourseName = studentCourseName;
	}


	public String getStudentPhno() {
		return studentPhno;
	}


	public void setStudentPhno(String studentPhno) {
		this.studentPhno = studentPhno;
	}


	public int getStudentMarks() {
		return studentMarks;
	}


	public void setStudentMarks(int studentMarks) {
		this.studentMarks = studentMarks;
	}


	public String getStudentAddress() {
		return studentAddress;
	}


	public void setStudentAddress(String studentAddress) {
		this.studentAddress = studentAddress;
	}


	public void display(){
		System.out.print(studentId+"\t\t");
		System.out.print(studentName+"\t\t");
		System.out.print(studentSex+"\t");
		System.out.print(studentCourseName+"\t\t");
		System.out.print(studentPhno+"\t\t");
		System.out.print(studentMarks+"\t\t\t");
		System.out.print(studentAddress+"\t");
		System.out.println();
		
	}
}

Hibernate.cfg.xml
<?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>
<property name=”show_sql”>false</property>
<property name=”hbm2ddl.auto”>create</property>
<property name=”hibernate.dialect”>org.hibernate.dialect.MySQLDialect</property>
<property name=”hibernate.connection.driver_class”>com.mysql.jdbc.Driver</property>
<property name=”hibernate.connection.url”>jdbc:mysql://localhost:3306/student1?zeroDateTimeBehavior=convertToNull</property>
<property name=”hibernate.connection.username”>root</property>
<property name=”hibernate.connection.password”></property>

<mapping class=”com.santanu.arrays.Student”/>

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

public class HibernateTest {

	public static void main(String[] args) {
		Student [] student= new Student[10];
		
		student[0]= new Student(1, "Vipin", "M", "Java", "9000000001", 85, "Lucknow");
		student[1]= new Student(2, "Sohel", "M", "C++", "9000000002", 81, "Kolkata");
		student[2]= new Student(3, "Meheraj", "M", "C/C++", "9000000003", 86, "Bengalore");
		student[3]= new Student(4, "Shruti", "F", "J2ee", "9000000004", 55, "Kolkata");
		student[4]= new Student(5, "Shabbu", "M", ".Net", "9000000005", 86, "Bengaluru");
		student[5]= new Student(6, "Sharukh", "M", "Python", "9000000006", 74, "Delhi");
		student[6]= new Student(7, "Deepika", "F", "PHP", "9000000007", 67, "Chandigarh");
		student[7]= new Student(8, "Anuskha", "F", "HTML", "9000000008", 67, "Patna");
		student[8]= new Student(9, "Kejri","M", "Ruby", "9000000009", 71, "Vizag");
		student[9]= new Student(10, "Rahul", "M", "ASP", "9000000010", 51, "Bairely");
		
	    SessionFactory sf = new Configuration().configure().buildSessionFactory();
	    Session sc = sf.openSession();
	    sc.beginTransaction();
	    	  
	    System.out.println("hi");
	    for (Student x:student) {
			sc.save(x);
			}
	    System.out.println("hi");
	    sc.getTransaction().commit();
	    sc.close();
	    
		}

}
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.sql.*;

import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;
public class PDFCreator {

	public static void main(String[] args) throws ClassNotFoundException, SQLException, FileNotFoundException, DocumentException,  IllegalAccessException {
	int rowno=0;
	
	Class.forName("com.mysql.jdbc.Driver");//.newInstance();
	Connection  con= DriverManager.getConnection("jdbc:mysql://localhost:3306/student1?zeroDateTimeBehavior=convertToNull", "root","") ;
	Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	ResultSet rs = st.executeQuery("SELECT * FROM student");
	ResultSetMetaData rsmd = rs.getMetaData();
	int colno = rsmd.getColumnCount();
	while (rs.next()) {
		 rowno= rowno+1;
		
	}
	rs.first();
	System.out.println(""+rs.getString(1));
	
	Document d = new Document();
	PdfWriter.getInstance(d, new FileOutputStream("report.pdf"));
	PdfPTable pt = new PdfPTable(colno);
	d.open();
	d.add(new Paragraph("Student Record"));
	for (int i = 0; i < rowno; i++) {
		pt.addCell(""+rs.getString(1));
		pt.addCell(""+rs.getString(2));
		pt.addCell(""+rs.getString(3));
		pt.addCell(""+rs.getString(4));
		pt.addCell(""+rs.getString(5));
		pt.addCell(""+rs.getString(6));
		pt.addCell(""+rs.getString(7));
		rs.next();
	}
	
	d.add(pt);
	
	d.close();
	}

}

Why Hibernate Query Language(HQL)?

Hibernate Query Language:
Hibernate Query Language (HQL) is same as SQL (Structured Query Language).Hibernate created a new language named Hibernate Query Language (HQL), the syntax is quite similar to database SQL language. The main difference between is HQL uses class name instead of table name, and property names instead of column name.

Advantage of HQL:-
1) database independent:
2) HQL fully supports polymorphic queries. That is, along with the object to be returned as a query result, all child objects (objects of subclasses) of the given object shall be returned.
3) HQL is suitable for executing Static Queries.
4) HQL doesn’t support pagination concept.
5) HQL is to perform both select and non-select operations on the data.

Clauses in the HQL are:
• from
• select
• where
• order by
• group by
FROM Clause:-
• String hql = “FROM Employee”;
• Query query = session.createQuery(hql);
• List results = query.list();
SELECT Clause:
• String hql = “SELECT E.firstName FROM Employee E”;
• Query query = session.createQuery(hql);
• List results = query.list();

WHERE Clause:
• String hql = “FROM Employee E WHERE E.id = 10”;
• Query query = session.createQuery(hql);
• List results = query.list();

ORDER BY Clause:

• String hql = “FROM Employee E WHERE E.id > 10 ORDER BY E.salary DESC”;
• Query query = session.createQuery(hql);
• List results = query.list();

GROUP BY Clause:
• String hql = “SELECT SUM(E.salary), E.firtName FROM Employee E ” +
• “GROUP BY E.firstName”;
• Query query = session.createQuery(hql);
• List results = query.list();

Aggregate functions are:
• avg(…), sum(…), min(…), max(…)
• count(*)
• count(…), count(distinct …), count(all…)

HQL Select Query Example:-
Retrieve a stock data where stock code is “7211”.
Query query = session.createQuery(“from Stock where stockCode = :value “);
query.setParameter(“value”, “7211”);
List list = query.list();
Query query = session.createQuery(“from Stock where stockCode = ‘7211’ “);
List list = query.list();
HQL Update Query Example:-

Query query = session.createQuery(“update Stock set stockName = :stockName” +” where stockCode = :stockCode”);
query.setParameter(“stockName”, “DIALOG11”);
query.setParameter(“stockCode”, “7211”);
int result = query.executeUpdate();
Query query = session.createQuery(“update Stock set stockName = ‘DIALOG2′” +” where stockCode = ‘7211’”);
int result = query.executeUpdate();

HQL Delete Query Example:-

Query query = session.createQuery(“delete Stock where stockCode = :stockCode”);
query.setParameter(“stockCode”, “7211”);
int result = query.executeUpdate();
Query query = session.createQuery(“delete Stock where stockCode = ‘7211’”);
int result = query.executeUpdate();

HQL Insert Query Example:-
In HQL, only the INSERT INTO … SELECT … is supported; there is no INSERT INTO … VALUES. HQL only support insert from another table. For example:

1) “insert into Object (id, name) select oo.id, oo.name from OtherObject oo”;
Insert a stock record from another backup_stock table. This can also called bulk-insert statement:
Query query = session.createQuery(“insert into Stock(stock_code, stock_name)” +
“select stock_code, stock_name from backup_stock”);
int result = query.executeUpdate();

You may also like to read:
Hibernate Mapping file
Hibernate Configuration file

Jar file creation

Hibernate Configuration File

What is purpose of Hibernate Configuration File?
Hibernate Configuration File provides below details, which are used by Hibernate Framework. In below description, we are considering connecting to a MySQL database.
hibernate.cfg.xml has below properties.
1. JDBC Driver details
2.Database url
3.Date base credentials
username
password
4.Mapping resource
It is possible to specify one or more mapping resources. Mapping resource are the files with extension hbm.xml

It is also possible to specify annotations for mapping. In such case Annotations will be provided in POJO(Plain Old java Objects) class. In this case, POJO Class name need to be specified as shown below.
<mapping class=”Employee”/>

In addition to above, property such as below are optional, and can be used on need basis
1. hbm2ddl.auto is to automatically create or update tables in database.
2. show_sql is to show SQL queries generated by Hibernate Framework.While debugging, if you want to check SQL queries, then this property need to be set to true.
Below is an example of Hibernate Configuration, 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>
      <property name="hbm2ddl.auto">create</property> 
      <property name="show_sql">true</property><!-- can be used for debugging purpose -->
    <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/zzzz?zeroDateTimeBehavior=convertToNull</property>
    <property name="hibernate.connection.username">root</property>
    <property name="hibernate.connection.password">abcdfe</property>
    <mapping resource="empadd.hbm.xml"/>
  </session-factory>
</hibernate-configuration>

Also note that, it is possible to have different name for Hibernate Configuration file, in this case file name need to be specified in the Application(Java File), as shown below.
SessionFactory factory1 = new Configuration().configure(“hibernate.cfg.xml”).buildSessionFactory();

It is also possible to have multiple Hibernate Configuration files, in an Application.

You may also like to read:
What is Hibernate Mapping File?
Advantages of Hibernate?
What is XML file

Advantages of Hibernate Framework

Traditional way of storing objects in a database table, is to break up object into primitive data types, and store each primitive data in a table column. Application developer need to spend more time on writing mapping source code between class data members and table columns,etc… Situation worsens when relationship exists between objects, which need to be stored in database.

Hibernate provides solution for above problems.

Below is anatomy of Java Application, which uses Hibernate Framework.

hibernate-advantages
hibernate-advantages

1.Reduces size of code, hence development of Application is faster, which in turn controls cost of overall project.
When Hibernate is used, you need not write code to perform below routine activities
Connecting to database, retrieve individual columns from database table and creating object out of it , and vice versa.

2.Transaction Control: Hibernate has built in Transaction Management. A Transaction can be started, commited or rolled back by using Hibernate.
3.Automatic creation and updation of tables or columns. Required tables and columns are automatically created or updated, by provding hbm2ddl property in hbm file. Hibernate gets details of table names, column names, type,etc… from Hibernate Mapping file(i..e hbm.xml)

4.Provides HQL, Hibernate Query language, using which developer need to provide queries in terms of class names, and data members, instead of focusing on tables and columns.

5.Hibernate Caching, caches objects, so that when same object(s) are queried next, object from cache is returned, and database hit is avoided. This improves overall performance of Application.

In you may find, few program statements used again and again, like openSession(), beginTransaction(), commit(), rollBack(), close(),etc…which can be avoided multiple times, by better designing your application. Another alternative to further reduce Hibernate code, is to use combination of Spring and Hibernate.

Other facts:
Hibernate Framework further depends on JDBC, to interact with underlying database. Hence JDBC driver jar file need to be added to projects using Hibernate.

Such Framework is referred as ORM(Object Relational Mapping)

You may also like to read:
Mapping Type in HIbernate Mapping file
DDL with JDBC Statments
How many types of JDBC Drivers exist

How to specify mapping type in Hibernate Mapping file

As known there are two types of XML files in any Application, which is using Hibernate. These two XML files are
1. Configuration File, generally named as hibernate.cfg.xml
2. Mapping File, can be given any name

Note that, Hibernate framework reads contents of above files, to
1. to establish connection to database
2. to create/update tables and columns
3. to select/insert/update records in tables.

Below is an example of hibernate.cfg.xml

<?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>
      <property name="hbm2ddl.auto">create</property> 
      <property name="show_sql">true</property>
    <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/zzzz?zeroDateTimeBehavior=convertToNull</property>
    <property name="hibernate.connection.username">root</property>
    <property name="hibernate.connection.password">abcdfe</property>
    <mapping resource="hibernate.hbm.xml"/>
  </session-factory>
</hibernate-configuration>

As seen above hibernate.cfg.xml file specifies below details
1. Database details
2. JDBC Driver details
3. Database credentials(i..e username and password)
4. list of hibernate mapping files
5. Any other optional property such as below
hbm2ddl.auto – to automatically create or update database tables or columns
show_sql – shows sql queries generated by Hibernate framework. These sql queries are executed in database. This option is generally used for debugging purpose, when Application is in development.

Below is an example of .hbm.xml file

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-mapping PUBLIC
  "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
  "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
   <class name="Trainer" table="ttrainer">
      <meta attribute="class-description">
         This class contains the Trainer details. 
      </meta>
      <id name="id" type="int" column="id">
         <generator class="native"/>
      </id>
      <property name="firstName" column="first_name" type="string"/>
      <property name="lastName" column="last_name" type="string"/>
      <property name="salary" column="salary" type="int"/>
      <many-to-one name="student" column="trn_student" unique="true" 
       class="Student" not-null="true"/>
   </class>

   <class name="Student" table="tstudent">
      <meta attribute="class-description">
         This class contains Student details. 
      </meta>
      <id name="id" type="int" column="id">
         <generator class="native"/>
      </id>
      <property name="sName" column="stdnt_name" type="string"  not-null="true"/>
      <property name="sPhone" column="stdnt_phone" type="string"/>
   </class>

</hibernate-mapping>

As seen above .hbm.xml file specifies below details
1. Mapping between class, and database table
2. Mapping between data members (in each class) and table columns, along with data type
3. Type of relationship between Trainer and Student classes.

Note that he data type specified above, is neither Java type nor SQL Type. It is Hibernate Mapping type. Below shows Hibernate Mapping Type for corresponding Java Type and SQL Type.

hibernate-mapping-types
hibernate-mapping-types

You may also like to read: