When working with Hibernate and Hibernate HQL, you will find yourself writing again and again the same tedious queries. Not only that, but most of the times these queries are scattered throughout your code making it very hard too read and even harder to maintain. Hibernate Named Queries can provide a way of solving this problem. With Named queries you can define your customized queries written in SQL or HQL and then give them a name.
You can use that specific name any time you want to create and execute that query. Of course you have still the option of binding parameters etc. In some ways Named Queries are like stored procedures or like the public methods of a class. You can use a public method of a class to manipulate, read and write date to your class instance.
There are to basic ways you can declare named queries. One is using the .hbm.xml mapping file of you’re mapped class or by annotations.
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 from Hibernate 3 with Maven 2 and MySQL 5 Example (XML Mapping and Annotation).
1. Named Queries using XML Mapping
Let’s remind ourselves the stucture of the Eclipse project we’re working on:
Open Student.hbm.xml file and paste the following code:
Student.hbm.xml:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="com.javacodegeeks.Student" table="student" catalog="tutorials">
<id name="studentId" type="java.lang.Integer">
<column name="STUDENT_ID" />
<generator class="identity" />
</id>
<property name="studentName" type="string">
<column name="STUDENT_NAME" length="10" not-null="true"
unique="true" />
</property>
<property name="studentAge" type="string">
<column name="STUDENT_Age" length="20" not-null="true" unique="true" />
</property>
</class>
<query name="findStudentByID">
<![CDATA[from Student st where st.studentId = :studentId]]>
</query>
</hibernate-mapping>You should always define your named queries after the class decleration. Notice also, that we are using CDATA to write our query in. This is generally a good practive if you want to safely use special charactes like ‘<‘ or ‘>’ in your queries and not confuse the XML parser.
Now to run the named query change App.java to this
App.java:
package com.javacodegeeks;
import org.hibernate.Query;
import org.hibernate.Session;
import java.util.List;
import com.javacodegeeks.utils.HibernateUtil;
public class App {
public static void main(String[] args) {
Session session = HibernateUtil.getSessionFactory().openSession();
Query query = session.getNamedQuery("findStudentByID");
query.setInteger("studentId", 31);
List<?> list = query.list();
if (!list.isEmpty()) {
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=31, studentName=Jeremny, studentAge=21]Here, you can use all the methods we discussed in Hibernate Query Language Example.
Additionally, you can write native SQL queries in the mapping file. In our case simple MySQL:
Student.hbm.xml:
<?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping> <class name="com.javacodegeeks.Student" table="student" catalog="tutorials"> <id name="studentId" type="java.lang.Integer"> <column name="STUDENT_ID" /> <generator class="identity" /> </id> <property name="studentName" type="string"> <column name="STUDENT_NAME" length="10" not-null="true" unique="true" /> </property> <property name="studentAge" type="string"> <column name="STUDENT_Age" length="20" not-null="true" unique="true" /> </property> </class> <sql-query name="findStudentByID"> <return alias="student" class="com.javacodegeeks.Student" /> <![CDATA[select * from student s where s.STUDENT_ID = :studentId]]> </sql-query> </hibernate-mapping>
Because you are using MySQL to write the query it’s necessary to define a return type that basically maps the result of the database query to the correspoding class. Remember that HQL automatically does that but when using native SQL you have to sort of do it yourself. So here we state that the result of out query are mapped by the Student class in our Java program.
Without changing anything in App.java this is the output of the program :
Output:
Hibernate: select * from student s where s.STUDENT_ID = ?
Student [studentId=31, studentName=Jeremny, studentAge=21]There is a very good article from JBoss documentantion that deeply examined native SQL named queries and all its abilities.
2. Named Queries using Annotation
For this we are going to use the Eclipse project of Hibernate 3 with Maven 2 and MySQL 5 Example (XML Mapping and Annotation) that deals with Annotations. It’s right here : HibernateMySQLAnnot.zip.
Here is how to define an HQL named query in Student.java file:
Student.java
package com.javacodegeeks;
import static javax.persistence.GenerationType.IDENTITY;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
@NamedQueries({ @NamedQuery(name = "findStudentByID", query = "from Student st where st.studentId = :studentId" )})
@Entity
@Table(name = "student", catalog = "tutorials")
public class Student implements java.io.Serializable {
private static final long serialVersionUID = 1L;
private Integer studentId;
private String studentName;
private String studentAge;
public Student() {
}
public Student(String studentName, String studentAge) {
this.studentName = studentName;
this.studentAge = studentAge;
}
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "STUDENT_ID", unique = true, nullable = false)
public Integer getStudentId() {
return this.studentId;
}
public void setStudentId(Integer studentId) {
this.studentId = studentId;
}
@Column(name = "STUDENT_NAME", nullable = false, length = 10)
public String getStudentName() {
return this.studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName;
}
@Column(name = "STUDENT_AGE", nullable = false, length = 20)
public String getStudentAge() {
return this.studentAge;
}
public void setStudentAge(String studentAge) {
this.studentAge = studentAge;
}
@Override
public String toString() {
return "Student [studentId=" + studentId + ", studentName="
+ studentName + ", studentAge=" + studentAge + "]";
}
}Using the same App.java as before the output of the program would be:
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=31, studentName=Jeremny, studentAge=21]You can also write your named query with Native SQL like so:
Student.java
package com.javacodegeeks;
import static javax.persistence.GenerationType.IDENTITY;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
@NamedNativeQueries({ @NamedNativeQuery(name = "findStudentByID", query = "select * from student s where s.STUDENT_ID = :studentId", resultClass = Student.class )})
@Entity
@Table(name = "student", catalog = "tutorials")
public class Student implements java.io.Serializable {
private static final long serialVersionUID = 1L;
private Integer studentId;
private String studentName;
private String studentAge;
public Student() {
}
public Student(String studentName, String studentAge) {
this.studentName = studentName;
this.studentAge = studentAge;
}
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "STUDENT_ID", unique = true, nullable = false)
public Integer getStudentId() {
return this.studentId;
}
public void setStudentId(Integer studentId) {
this.studentId = studentId;
}
@Column(name = "STUDENT_NAME", nullable = false, length = 10)
public String getStudentName() {
return this.studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName;
}
@Column(name = "STUDENT_AGE", nullable = false, length = 20)
public String getStudentAge() {
return this.studentAge;
}
public void setStudentAge(String studentAge) {
this.studentAge = studentAge;
}
@Override
public String toString() {
return "Student [studentId=" + studentId + ", studentName="
+ studentName + ", studentAge=" + studentAge + "]";
}
}As before you have to define the return type of the query for in oder for Hibernate to map the result to the correct class.
Output:
Hibernate: select * from student s where s.STUDENT_ID = ?
Student [studentId=31, studentName=Jeremny, studentAge=21]This was an example on Hibernate Named Queries.
