In this example we are going to see how to use Hibernate Query Language (HQL). This the query language created for Hibernate. It’s syntax is very similar to a normal SQL language but instead of tables it deals with classes and instead of columns it deals with properties or class attributes. And that’s what makes it really suitable to use it alongside a framework like Hibernate.
So these are the tools we are going to use on a Windows 7 platform:
- JDK 1.7
- Maven 3.0.5
- Hibernate 4.2.3.Final
- MySQL JDBC driver 5.1.9
- Eclipse 4.3 Kepler
The basis of this tutorials is going to be this Eclipse project: HibernateMySQLExample.zip. And it’s based in Hibernate 3 with Maven 2 and MySQL 5 Example (XML Mapping and Annotation). All the code snippets displayed here reffer to App.java file of the aforementioned project.
Simple Query Examples
1. HQL Select Query Example
This is a simple “select” query to retrieve a Student with a specific id.
package com.javacodegeeks;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import com.javacodegeeks.utils.HibernateUtil;
public class App
{
public static void main( String[] args )
{
Session session = HibernateUtil.getSessionFactory().openSession();
Query query = session.createQuery("from Student where studentId = :id ");
query.setParameter("id", 5);
// You can replace the above to commands with this one
// Query query = session.createQuery("from Student where studentId = 1 ");
List<?> list = query.list();
Student student = (Student)list.get(0);
System.out.println(student);
}
}Output:
Hibernate: select student0_.STUDENT_ID as STUDENT1_0_, student0_.STUDENT_NAME as STUDENT2_0_, student0_.STUDENT_Age as STUDENT3_0_ from tutorials.student student0_ where student0_.STUDENT_ID=?
Student [studentId=5, studentName=JavaFun, studentAge=19]2. HQL Update Query Example
package com.javacodegeeks;
import org.hibernate.Query;
import org.hibernate.Session;
import com.javacodegeeks.utils.HibernateUtil;
public class App {
public static void main(String[] args) {
Session session = HibernateUtil.getSessionFactory().openSession();
session.getTransaction().begin();
Query query = session.createQuery("update Student set studentName = :studentName" + " where studentId = :studentId");
query.setParameter("studentName", "Jack");
query.setParameter("studentId", 1);
// You can replace the above to commands with this one
//Query query = session.createQuery("update Student set studentName ='Jack' where studentId = 1");
int result = query.executeUpdate();
session.getTransaction().commit();
}
}Output:
Hibernate: update tutorials.student set STUDENT_NAME=? where STUDENT_ID=?3. HQL Delete Query Example
package com.javacodegeeks;
import org.hibernate.Query;
import org.hibernate.Session;
import com.javacodegeeks.utils.HibernateUtil;
public class App {
public static void main(String[] args) {
Session session = HibernateUtil.getSessionFactory().openSession();
session.getTransaction().begin();
Query query = session.createQuery("delete Student where studentId = :studentId");
query.setParameter("studentId", 1);
int result = query.executeUpdate();
session.getTransaction().commit();
}
}Output:
Hibernate: delete from tutorials.student where STUDENT_ID=?4. HQL Insert Query Example
HQL supports INSERT INTO clause only where records can be inserted from one object to another object. Following is the simple syntax of using INSERT INTO clause:
package com.javacodegeeks;
import org.hibernate.Query;
import org.hibernate.Session;
import com.javacodegeeks.utils.HibernateUtil;
public class App {
public static void main(String[] args) {
Session session = HibernateUtil.getSessionFactory().openSession();
session.getTransaction().begin();
Query query = session.createQuery("insert into Student(studentName, studentAge,)" + " select studentName, studentAge from old_student");
int result = query.executeUpdate();
System.out.println("Rows affected: " + result);
session.getTransaction().commit();
}
}That means that you must have an old_student table in you datbases wheere you keep student tuples. This is also called bulk loading or bulk insert. If you simply want to insert a student tuple in the databases you should do it like this:
package com.javacodegeeks;
import org.hibernate.Session;
import com.javacodegeeks.enterprise.hibernate.utils.HibernateUtil;
public class App
{
public static void main( String[] args )
{
Session session = HibernateUtil.getSessionFactory().openSession();
session.beginTransaction();
Student student = new Student();
student.setStudentName("JavaFun");
student.setStudentAge("19");
session.save(student);
session.getTransaction().commit();
}
}The output of the above program would be:
Hibernate: insert into tutorials.student (STUDENT_NAME, STUDENT_Age) values (?, ?)Hibernate parameter binding
We’ve already seen this feature in our previous examples. You can thing of parameter binding like prepared statements in any normal SQL language. Hibernate parameter binding has more or less the same advantages as prepared statements (e.g preventing SQL injection attacks). Without patameter binding a simple select query can be written :
String name="James";
Query query = session.createQuery("from Student where studentName = '"+name+"' ");which is a bit of a fuzz. There are two ways to parameter binding : named parameters or positional.
1. Named parameters
setParameter
Query query = session.createQuery("from Student where studentId = :id ");
query.setParameter("id", 5);setInteger
Query query = session.createQuery("from Student where studentId = :id ");
query.setInteger("id", 5);This tells HQL to insert an object if type Integer in the query. You can also use setString,setBoolean etc for the corresponding types.
setProperties
Student student = new Student("Nikos","12");
Query query = session.createQuery("from Student where studentName = :studentName ");
query.setProperties(student);You may pass an object into the parameter binding. Hibernate can check the Student‘s attribute values and match with the correspondig :studentName parameter. This is a very coool feature!
2. Positional parameters
Positional parameters are syntaxed like this:
Query query = session.createQuery("from Student where studentId = ? and studentName=?");
query.setInteger(0, 1).setString(1, "Jack");The basic disadvantage of this method is that if you change the orders of the parameters on the query you have to change the indexes in setInteger and setString statements.
This was an example on Hibernate Query Language.
please share the hibernate util file also.
how about select where id > 10;