Native query example

Instead of using HQL or Hibernate Query Language, hibernate provides us with an annotation where we can use native sql queries i.e., queries that we usually write in our data bases. To achieve this we can make use of "@NamedNativeQuery" annotation. This annotation takes in three parameters. First one being the name of the query with which it should be called in the main program. Second parameter is the native query itself. Third parameter is the name of the class where it needs to store the list of values when the query gets executed. We have not mentioned this parameter in our previous post for the "@NamedQuery" because, for @NamedQuery we have used class name in the query instead of table name. So in this case hibernate knows in which class it needs to store the results. Below is a sample program for the native queries.

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

    <session-factory>

        <!-- Database connection settings -->
        <property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
        <property name="connection.url">jdbc:oracle:thin:@localhost:1521:xe</property>
        <property name="connection.username">hr</property>
        <property name="connection.password">hr</property>

        <!-- JDBC connection pool (use the built-in) -->
        <property name="connection.pool_size">1</property>

        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.OracleDialect</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">create</property>

        <!-- Names the annotated entity class -->
        <mapping class="com.ram.dao.Student"/>

    </session-factory>

</hibernate-configuration>

File: com.ram.dao.Student.java

package com.ram.dao;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.NamedNativeQuery;

@Entity
@NamedNativeQuery(name="Student.byName", query="select * from Student where studentname = ?", resultClass=Student.class)
public class Student {
 @Id @GeneratedValue
 private int studentId;
 private String studentName;
 
 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;
 }
}

File: com.ram.hibernate.HQLParameterExample.java

package com.ram.hibernate;

import java.util.List;

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

import com.ram.dao.Student;

public class NamedNativeQueryExample {
 public static void main(String[] args) {
  SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
  Session session = sessionFactory.openSession();
  session.beginTransaction();
  
  Query query = session.getNamedQuery("Student.byName");
  query.setString(0, "Student 3");
  List students = (List) query.list();
  
  for(Student stud: students){
   System.out.println("Student Name: "+stud.getStudentName());
  }
  
  session.getTransaction().commit();
  session.close();
 }
}

Execute NamedNativeQueryExample.java and you get the output as shown below.