Searching...
Saturday, 17 August 2013

Hibernate Query Language (HQL) Example

00:28

Hibernate API provides Query Language for database operations as like SQL but instead of operating on tables and columns, HQL works with persistent objects and their properties. HQL queries are translated by Hibernate into conventional SQL queries and perform action on database. Let us consider Student entity for this example to do basic queries.

1. Select Query

Query query = session.createQuery("from Student where name=:name");
        query.setParameter("name", "Raj");

2. Update Query

Query queryupdate = session
                .createQuery("update Student set standard = :standard where name = :name");
        queryupdate.setParameter("name", "Raj");
        queryupdate.setParameter("standard", "Third Standard");

3. Delete Query

Query querydelete = session
                .createQuery("delete from Student where name = :name");
        querydelete.setParameter("name", "Raj");

4. Insert Query

HQL supports INSERT INTO clause only where records can be inserted from one object to another object.Below is one simple query.

Query queryinsert = session.createQuery(“insert into Student(name,standard) select name, class from Candidate");
int result = queryinsert.executeUpdate()

5. Complete example

Create a Student entity

package com.pretech;
 
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
 
@Entity
@Table(name = "student", catalog = "hibernateschema")
public class Student  {
    @Id
    @Column(name = "name", unique = true, nullable = false, length = 10)
    private String name;
 
    @Column(name = "standard", unique = true, nullable = false, length = 20)
    private String standard;
   
    public String getStandard() {
        return standard;
    }
    public void setStandard(String standard) {
        this.standard = standard;
    }
    public String getName() {
        return name;
    }
    public void setName(String string) {
        name = string;
    }
    public String toString() {
        return name;
    }
}

Create Hibernate Config file (hibernate.cfg.xml)

<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD//EN"
    "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory name="studentFactory">
            <property name="connection.driver_class">
                 com.mysql.jdbc.Driver
            </property>
        <property name="connection.url">
             jdbc:mysql://localhost:3306/hibernateschema
        </property>
        <property name="connection.username">
             root
        </property>
        <property name="connection.password">
            root
        </property>
            <property name="connection.pool_size">5</property>
            <!-- SQL dialect -->
            <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
            <!-- Echo all executed SQL to stdout -->
            <property name="show_sql">true</property>
             <property name="hbm2ddl.auto">update</property>
             <mapping class="com.pretech.Student"></mapping>
             
    </session-factory>
</hibernate-configuration>

Main program to test Queries

package com.pretech;
 
import java.util.Iterator;
import java.util.List;
 
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
 
public class StudentHQLExample {
 
    private static SessionFactory sessionFactory;
 
    public static void main(String[] args) {
        try {
            sessionFactory = new Configuration().configure("hibernate.cfg.xml")
                    .buildSessionFactory();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        Session session = sessionFactory.openSession();
 
        Transaction tx = session.beginTransaction();
        Student student = new Student();
        student.setName("Raj");
        student.setStandard("10th Standard");
        session.save(student);
        tx.commit();
        System.out.println("Record save successfully");
 
        System.out.println("Executing Select Query");
        Query query = session.createQuery("from Student where name=:name");
        query.setParameter("name", "Raj");
        List list = query.list();
        Iterator it = list.iterator();
        while (it.hasNext()) {
            Student stud = (Student) it.next();
            System.out.println(stud.getName());
            System.out.println(stud.getStandard());
        }
 
        System.out.println("Executing Update");
        Transaction tx1 = session.beginTransaction();
 
        Query queryupdate = session
                .createQuery("update Student set standard = :standard where name = :name");
        queryupdate.setParameter("name", "Raj");
        queryupdate.setParameter("standard", "Third Standard");
        int result = queryupdate.executeUpdate();
        tx1.commit();
 
        System.out.println("Student record updated " + result);
       
       
        System.out.println("Executing Delete ");
        Transaction tx2 = session.beginTransaction();
 
        Query querydelete = session
                .createQuery("delete from Student where name = :name");
        querydelete.setParameter("name", "Raj");
        int result1 = queryupdate.executeUpdate();
        tx2.commit();
        System.out.println("Student record deleted " + result1);
    }
}

Output

Hibernate: insert into hibernateschema.student (standard, name) values (?, ?)

Record save successfully
Executing Select Query
Hibernate: select student0_.name as name0_, student0_.standard as standard0_ from hibernateschema.student student0_ where student0_.name=?
Raj
10th Standard
Executing Update
Hibernate: update hibernateschema.student set standard=? where name=?
Student record updated 1
Executing Delete
Hibernate: update hibernateschema.student set standard=? where name=?
Student record deleted 1

 

0 comments: