Part 7 Hibernate Query Language Insert Query

Now we will see how to use HQL insert query, as i told earlier its little different then remaining query’s, actually the thing is…..

HQL supports only the INSERT INTO……… SELECT……… ; there is no chance to write INSERT INTO………..VALUES, i mean while writing the insert query, we need to select values from other table, we can’t insert our own values manually, you will understand by seeing the following example..

files required…

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

Product.java

1234567891011121314151617181920212223242526272829303132333435package 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;
	}
}

Items.java

12345678910111213141516171819202122232425262728package str;

public class Items{

	private int itemId;
	private String itemName;
	private double itemPrice;	

	public int getItemId() {
		return itemId;
	}
	public void setItemId(int itemtId) {
		this.itemId = itemtId;
	}
	public String getItemName() {
		return itemName;
	}
	public void setItemName(String itemName) {
		this.itemName = itemName;
	}
	public double getItemPrice() {
		return itemPrice;
	}
	public void setItemPrice(double itemPrice) {
		this.itemPrice = itemPrice;
	}

}

Products.hbm.xml

1234567891011121314<?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>

Items.hbm.xml

1234567891011121314<?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.Items" table="items">

<id name="itemId" />
<property name="itemName" length="10"/>
<property name="itemPrice"/>

</class>
</hibernate-mapping>

ForOurLogic.java

1234567891011121314151617181920212223242526272829package 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("insert into Product(productId,proName,price)

select i.itemId,i.itemName,i.itemPrice from Items i where i.itemId= ?");

qry.setParameter(0,600);

int res = qry.executeUpdate();

System.out.println("Command successfully executed...."); System.out.println("Numer of records effected...,"+res); session.close(); factory.close(); } }

Notes:

• see ForOurLogic.java – line number 17, 18 that’s the insert query of HQL, while inserting the values into Product table, it will takes values from Items table
• for executing these DML operations we need to call executeUpdate() method, and it will returns how many records effected due query execution (returns one integer value)