Newsletter

Part 7 Hibernate Query Language Insert Query

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

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

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

Items.java

package 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

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

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

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("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)

Output:

Database Output Before Execution [PRODUCTS table]

Database Output Before Execution [ITEMS table]

Eclipse Console after execution

:

Database output after execution

​​

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

19 Responses to “Part 7 Hibernate Query Language Insert Query”
  1. chinnasamy says:

    In this tutorial you don’t mention, how to configure two mapping file to configuration file.

  2. anusha says:

    hi,
    how to configure two mapping files in hibernate.cfg.xml?

  3. Hi Anusha,

    Please look over this for config file

    oracle.jdbc.driver.OracleDriver

    jdbc:oracle:thin:@www.java4s.com:1521:XE
    system
    admin

    org.hibernate.dialect.OracleDialect
    true
    update

  4. chetan says:

    while running this program i got my table unmapped why?????

  5. rashid says:

    here you are inserting one table values to another table what if i want add a record into a table from user

  6. Nitya says:

    Hi Sivateja,as you mentioned above while inserting record ,it will take from one table and insert into another another table…why this is hapenning,if there is no data in one table then how it will fetch and insert into another table…and if we enter from UI then how the mapping wil happen…

  7. Revathi says:

    How to write a query if I want to insert my own values instead of retrieving values from some other table?

  8. karunakar kukunoori says:

    hai ,teja i also have same doubt why cant we insert direct values through hql when we are performing select,delete,update operation

  9. palani says:

    sir can you post the configuration file for this two hbm file getting mapped

  10. Palani says:

    sir please post the configuration file for this project….. i wasted two days

  11. Devkaran Singh Rathore says:

    You haven't created object of Item CLASS ,still u are using it in query .how??

  12. Aakash Deep Sharma says:

    Sir same query . how to insert data in Item table ?
    can we insert our own values rather than from different Table ?

  13. mousita says:

    Caused by: line 1:1: unexpected token: INSERT
    the above error is coming…pls help

  14. Narasingha says:

    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);

    Are we not setting the values for productId,proName,price ?

    • Mahboob Ali says:

      Hello,

      Please add the Transaction Interface before the Session Interface….

      Thanks & Regards,
      Mahboob ALi

  15. Shweta says:

    Hi,
    The code is running but changes are not reflected. The data present in items was not copied in products.

  16. Vijaya says:

    Hi,

    With above code, code is running without any errors but changes are not reflected into product table.

    can you any clarify, why are we not able to insert our our data using HQL??

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.