Part 6 Hibernate Query Language, HQL Update,Delete Queries

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,  hear 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

Source code for HQL update

What you are thinkig....