Newsletter

Part 6 Hibernate Query Language, HQL Update,Delete Queries

Hibernate » on Jul 10, 2011 { 23 Comments } By Sivateja

so far we have been executed the programs on Hibernate Query Language (HQL) select only, now we will see the DML operations in HQL like insert, delete, update., you know some thing..? this delete, update query’s are something similar to the select query only but insert query in Hibernate Query Language(HQL) is quite different, i will let you know while we are going to execute the program on HQL insert, for now let us see an example on delete, update queries

Remember.., while we are working with DML operations in HQL we have to call executeUpdate(); to execute the query, which will returns one integer value after the execution, will discuss more on this later..

files required.. (for both delete, update query’s)

  • Product.java (POJO class)
  • Product.hbm.xml
  • hibernate.cfg.xml
  • ForOurLogic.java (Our logic)

 

Let Us See An Example On HQL DELETE

Actually Product.java, Product.hbm.xml and hibernate configuration files are some for both logics

Product.java

package str;

public class Product{

	private int productId;
	private String proName;
	private double price;

	public void setProductId(int productId)
	{
	    this.productId = productId;
	}
	public int getProductId()
	{
	    return productId;
	}

	public void setProName(String proName)
	{
	    this.proName = proName;
	}
	public String getProName()
	{
	    return proName;
	}

	public void setPrice(double price)
	{
	    this.price = price;
	}
	public double getPrice()
	{
	    return price;
	}
}

Product.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="str.Product" table="products">

<id name="productId" column="pid"Β  />
<property name="proName" column="pname" length="10"/>
<property name="price"/>

</class>
</hibernate-mapping>

hibernate.cfg.xml

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

<hibernate-configuration>
<session-factory>
<property name="connection.driver_class">oracle.jdbc.driver.OracleDriver
</property>
<property name="connection.url">jdbc:oracle:thin:@www.java4s.com:1521:XE</property>
<property name="connection.username">system</property>
<property name="connection.password">admin</property>

<property name="dialect">org.hibernate.dialect.OracleDialect</property>
<property name="show_sql">true</property>
<property name="hbm2ddl.auto">update</property>

<mapping resource="Product.hbm.xml"></mapping>
</session-factory>
</hibernate-configuration>

ForOurLogic.java

package str;

import org.hibernate.*;
import org.hibernate.cfg.*;

public class ForOurLogic { 

	public static void main(String[] args)
	{

		Configuration cfg = new Configuration();
		cfg.configure("hibernate.cfg.xml"); 

		SessionFactory factory = cfg.buildSessionFactory();
		Session session = factory.openSession();		

	        Query qry = session.createQuery("delete from Product p
where p.productId=:java4s");
	        qry.setParameter("java4s",110);
	        int res = qry.executeUpdate();

	        System.out.println("Command successfully executed....");
	        System.out.println("Numer of records effected due to delete query"+res);

		session.close();
		factory.close();
	}

}

Notes:

  • see line number 17, i have been used label to pass the value at run time, and nothing to explain more in this…. what do you say πŸ˜‰

Database Output Before Execution

Run the program in eclipse

Eclipse console

Database Output After Execution


Let Us See An Example On HQL UPDATE

Mates, Product.java, Product.hbm.xml, hibernate.cfg.xml are similar to previous hql program (HQL part 5), so am directly writing the code for ForOurLogic.java

ForOurLogic.java

package str;

import org.hibernate.*;
import org.hibernate.cfg.*;

public class ForOurLogic { 

	public static void main(String[] args)
	{

		Configuration cfg = new Configuration();
		cfg.configure("hibernate.cfg.xml"); 

		SessionFactory factory = cfg.buildSessionFactory();
		Session session = factory.openSession();		

	        Query qry = session.createQuery("update Product p set p.proName=?
where p.productId=111");
	        qry.setParameter(0,"updated..");
	        int res = qry.executeUpdate();

	        System.out.println("Command successfully executed....");
	        System.out.println("Numer of records effected due to update query"+res);

		session.close();
		factory.close();
	}

}

Notes:

  • update query written in line number 17,Β  here i am going to set value in run time by using question mark symbol in the query, that’s it.

Database Output Before Execution

Run the program in eclipse

Eclipse Console

Database Output After Execution

And that’s it ………………..Β Β  πŸ˜‰

Source code for HQL delete

download-hibernate-source-code-for-hql-delete-query
Source code for HQL update

download-hibernate-source-code-for-hql-update-query

​ ​​

You Might Also Like

  ::. About the Author .::

Java4s_Author
Sivateja Kandula - Java/J2EE Full Stack Developer
Founder of Java4s - Get It Yourself, A popular Java/J2EE Programming Blog, Love Java and UI frameworks.
You can sign-up for the Email Newsletter for your daily dose of Java tutorials.

Comments

23 Responses to “Part 6 Hibernate Query Language, HQL Update,Delete Queries”
  1. Raju says:

    Actually you are told that every DML operations are performed in presence of session only ok thats fine but when the time of using HQL update,delete you are not used any transaction how it will work please explain me.

    Thank you in advance.

  2. Mohammed Vaseem says:

    @Raju
    Excellently Analyzed…
    We have to use Transaction and has to say transaction.commit().
    then only database modifications will be done.
    In the above program, no deletion of the entities in the database is happening coz we are just querying but not commiting… when commiting only, modifications will be done and be saved into the database.

  3. Java4s says:

    @Raju, @Vaseem

    Raju am very sorry for giving late reply, we thought its better to wait until we know the exact answer.

    Actually am checking the same with my friends as well.
    As we said, modifications wont be effected until we say commit, but i guess it got committed.

    I think transactions is mandatory for DMLs. Even i mailed the same to hibernate forums but no response from them.

    Any ways vaseem can you check with/with out transaction and check the database and conform once ?

  4. Mohammed Vaseem says:

    When i checked with out transaction and did not say tx.commit(), then in the console of the program it has shown that database is updated but when checking in database, database was not updated. This means query is executed but not committed.

    When i checked with Transaction and used tx.commit(),then the value is updated in the database. nothing but query is executed and as well committed.

    I can say that if we want to update any values in the database we must has to use transaction and has to use tx.commit(), then only the values will be updated or else not.

  5. Mohammed Vaseem says:

    Hello java4s,
    Thanks for sharing this much of valuable information with all…
    As a user, I has to get all the updated information very easily. So I request to maintain a separate web page for “What readers are saying” option so that i can easily find the happening updates very easily…
    Thanks again..

  6. Java4s says:

    @ Vaseem
    Oops some thing what we expected πŸ™‚

    Thank for the conformation.

  7. Kiran says:

    Hi all i got the same problem Not updating at database side when i coded without committing transaction for delete or update. i think trnx.commit() ; is needed. I wish you all keep experimenting and letting others know them.

  8. Revan says:

    Good Job !!!!!!!!

  9. Master says:

    In above example we have passed value as updated.. but in screenshot only 1 dot is being displayed. Will there be 2 dots or 1 dot only

  10. Muskan says:

    how to delete record without providing id no. (like here 110)……..

  11. rashid says:

    Hi java4s please help me
    can you please explain me how to insert 5 records or some records at a time by using insert query.
    and in insert query we writing like this insert….table_name + select….table_name both the table name should same or different

  12. srinivas says:

    Good Analysis…!!

  13. Ashok Kumar says:

    DML delete and update not work on my system.

  14. Alok singh says:

    Hi java4s,
    In ForOurLogic.java class two lines are missing, without these lines code is not able to delete or update in database table
    Transaction tx=session.beginTransaction(); // missing
    int res=query.executeUpdate();
    tx.commit(); // missing

    Regards
    Alok singh

  15. Ganesh says:

    Nice example but without transaction we cannot perform any DML operations.

  16. Murali says:

    Hello Friends…!
    I observe..
    Without Transaction and without commit also delete and update are successfully working on database…that I am sure…
    need more explanation on this concept…
    thank you

  17. Mujeeb says:

    HQl Update i’m getting this message in connsole

    Exception in thread “main” java.lang.IllegalArgumentException: node to traverse cannot be null!

    Configuration cfg=new Configuration();
    cfg.configure(“resource/configuration.cfg.xml”);

    SessionFactory sf=cfg.buildSessionFactory();
    Session ses=sf.openSession();
    Transaction ts=ses.beginTransaction();

    String hql = “update Pojo set Name=’ghjf’ where Id=’335′”;

    Query q =ses.createQuery(hql);
    int i = q.executeUpdate();
    ts.commit();
    System.out.println(“Command successfully executed….”);
    System.out.println(“Numer of records effected due to update query “+i);
    ses.close();
    sf.close();

  18. Mujeeb says:

    Showing message in hql update
    Exception in thread “main” java.lang.IllegalArgumentException: node to traverse cannot be null!

  19. something is wrong,,,whichever demo i m trying ,I am getting exception of "connection not open"..please help me

  20. Mahboob Ali says:

    Hello Sir,

    Please add the Transaction Interface in case of "UPDATE" and "DELETE" Operations in Hibernate…..

    Thanks & Regards,
    Mahboob Ali

  21. Deepak Kumar says:

    Dear All & @Java4s your can perform DML operations without transaction if you have defined @Transactional annotation in your services class.

  22. laxmi says:

    yes there must be transaction otherwise changes is not shown on database

Name*
Mail*
Website



By posting your answer, you agree to our comments policy.
Most Recent Posts from Top Categories
Spring Boot Hibernate Spring
Contact | About Us | Privacy Policy | Advertise With Us

© 2010 - 2024 Java4s - Get It Yourself.
The content is copyrighted to Sivateja Kandula and may not be reproduced on other websites.