Wednesday , June 28 2017
Home / Hibernate / HQL update, delete Query Example

HQL update, delete Query Example

In the previous tutorials, we had a long discussion about Hibernate select query and how it works. Now we are going to discuss about HQL update,delete Queries.

If we want to updated the multiple records at a time, we can go with HQL update. Or else If our requirement is to update a single record then we can go with hibernate update query.

As we all know that, HQL commands are very similar to SQL Commands. Lets compare the SQL update commands with HQL commands.

HQL update Query Example :

Simple HQL update :

SQL : update student set marks=50 where id=10;

HQL : update Student s set e=s.marks=50 where s.studentId=10;

HQL update with query Parameters

SQL : update student set marks=50 where id=?;

HQL : update Student s set e=s.marks=50 where s.studentId=?;

HQL update with named Parameters

HQL : update Student s set e=s.marks=50 where s.studentId=sId;

To execute the non-select operations (update,delete,insert) also, we need to have the Hibernate Query object. By calling the executeUpdate() method on query object, we can fire the HQL non-select commands.

Here is the complete example for Hibernate Update.

HQL Update Example :

Project Structure  :

HQL update Delete Example-min


import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;

import com.otp.hibernate.pojo.HibernateUtility;

public class Main {

    public static void main(String[] args) {

        SessionFactory factory = HibernateUtility.getSessionFactory();

        Session session = factory.openSession();
        Transaction transaction = session.beginTransaction();

        // Reading complete Entity
        System.out.println("Simple update");

        String qryString = "update Student s set s.address='Hyderabad' where s.studentId=5";
        Query query = session.createQuery(qryString);
        int count = query.executeUpdate();

        System.out.println(count + " Record(s) Updated.");

        System.out.println("Updating with Query Parameters ");

        String qryString2 = "update Student s set s.address='Pune' where s.studentId=?";
        Query query2 = session.createQuery(qryString2);
        query2.setParameter(0, 1);
        query2.executeUpdate();

        int count2 = query2.executeUpdate();

        System.out.println(count2 + " Record(s) Updated.");

        System.out.println("Updating with Named Parameters");

        String qryString3 = "update Student s set s.address='Goa' where s.studentId=:sId";
        Query query3 = session.createQuery(qryString3);
        query3.setParameter("sId", 3);
        query3.executeUpdate();

        int count3 = query3.executeUpdate();

        System.out.println(count3 + " Record(s) Updated.");

        transaction.commit();
        session.clear();
        session.close();
        System.out.println("Transaction Completed !");

    }

}

The executeUpdate() method returns the rows count. By using the count, we can come to know that, how many records are updated in database.

For all non-select operations (insert,update and delete), it is mandatory to begin and commit the transaction.

 

Simple update
Hibernate: update onlinetutorialspoint.student set address=’Hyderabad’ where id=5
1 Record(s) Updated.
Updating with Query Parameters
Hibernate: update onlinetutorialspoint.student set address=’Pune’ where id=?
1 Record(s) Updated.
Updating with Named Parameters
Hibernate: update onlinetutorialspoint.student set address=’Goa’ where id=?
1 Record(s) Updated.
Transaction Completed !

HQL delete query :

HQL delete query is as same as the update. The HQL delete is used to delete the bulk records from the database. If our requirement is to delete a single record, we can go with hibernate delete operation.

HQL Delete Query Examples :

Simple HQL delete :

SQL : delete from student where id=10;

HQL : delete from Student s  where s.studentId=10;

HQL delete with query parameters :

SQL : delete from student where id=?;

HQL : delete from Student s  where s.studentId=?;

HQL delete with named parameters :

HQL : delete from Student s  where s.studentId=:sId;

Example :

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;

import com.otp.hibernate.pojo.HibernateUtility;


public class Deleting {

    public static void main(String[] args) {

        SessionFactory factory = HibernateUtility.getSessionFactory();

        Session session = factory.openSession();
        Transaction transaction = session.beginTransaction();

        // Reading complete Entity
        System.out.println("Simple Delete");

        String qryString = "delete from Student s where s.studentId=1";
        Query query = session.createQuery(qryString);
        int count = query.executeUpdate();
        System.out.println(count + " Record(s) Deleted.");

        System.out.println("Deleting with Query Parameters ");

        String qryString2 = "delete from Student s where s.studentId=?";
        Query query2 = session.createQuery(qryString2);
        query2.setParameter(0, 2);
        int count2 = query2.executeUpdate();

        System.out.println(count2 + " Record(s) Deleted.");

        System.out.println("Deleting with Named Parameters");

        String qryString3 = "delete from Student s where s.studentId=:sId";
        Query query3 = session.createQuery(qryString3);
        query3.setParameter("sId", 4);
        int count3 = query3.executeUpdate();

        System.out.println(count3 + " Record(s) Deleted.");

        transaction.commit();
        session.clear();
        session.close();
        System.out.println("Transaction Completed !");

    }

}
 

Output :

Simple Delete
Hibernate: delete from onlinetutorialspoint.student where id=1
1 Record(s) Deleted.
Deleting with Query Parameters
Hibernate: delete from onlinetutorialspoint.student where id=?
1 Record(s) Deleted.
Deleting with Named Parameters
Hibernate: delete from onlinetutorialspoint.student where id=?
1 Record(s) Deleted.
Transaction Completed !

The complete example is available for download.

Happy learning 🙂

Download Example

About chandrashekhar

Hi Folks, you have reach this so far, that shows you like what you are learning. Then why don't you support us to improve for bettor tutorials by leaving your valuable comments and why not you keep in touch with us for latest updates on your favorite blog @ facebook , twitter , Or Google+ ,

Check Also

Hibernate Native SQL Query Example

In this tutorials, we are going to learn about Hibernate Native SQL with example. Hibernate …

Leave a Reply

Your email address will not be published. Required fields are marked *