Hibernate insert Query
In this tutorial you will learn about how to use HQL insert query.
INSERT query in HQL is used to insert the records into the database table. INSERT query of HQL is just like the INSERT query used in SQL but HQL supports insert into ----- select ---- Statement i.e in HQL insert query only allows to insert from another table. The best thing with the HQL queries are that the predefined words are not case sensitive.
A simple insert query can be written in HQL as :
insert into SalesOrder(stockId, price) select st.stockId, st.price from Stock st
Example :
An example is being given below will demonstrate you how to use HQL insert query in your program. To explain this query I am giving a complete example. To do so at first I have created tables named salesorder(id, price, purchaser, stockId) and stock(stockId, price, stockName) then created the POJO classes correspondence to each table named SalesOrder and Stock with some data members (see in example) of which object will be mapped with corresponding tables. Then created mapping files corresponding to each POJO class that will map these classes object with tables. A configuration file hibernate.cfg.xml is created for providing the Hibernate a required environment setup to work with the database and creating a connection pool. Finally created a class where we have obtained the configuration, created a SessionFactory and obtained a session using SessionFactory, created a Transaction within which we will try to insert data and updates using the executeUpdate() method of Query.
Complete Code
Table salesorder
CREATE TABLE `salesorder` ( `id` int(15) NOT NULL auto_increment, `price` int(25) NOT NULL, `purchaser` varchar(25) default NULL, `stockId` int(15) NOT NULL, PRIMARY KEY (`id`)
Table stock
CREATE TABLE `stock` ( `stockId` int(15) NOT NULL, `price` int(15) NOT NULL, `stockName` varchar(15) default NULL, PRIMARY KEY (`stockId`)
SalesOrder.java
package roseindia;
public class SalesOrder {
private int id;
private int price;
private String purchaser;
private int stockId;
public SalesOrder()
{
}
public SalesOrder(int price, String purchaser, int stockId)
{
this.price = price;
this.purchaser = purchaser;
this.stockId= stockId;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
public String getPurchaser() {
return purchaser;
}
public void setPurchaser(String purchaser) {
this.purchaser = purchaser;
}
public int getStockId() {
return stockId;
}
public void setStockId(int stockId) {
this.stockId = stockId;
}
}
salesorder.hbm.xml
<?xml version='1.0'?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd"> <hibernate-mapping package="roseindia"> <class name="SalesOrder" table="salesorder"> <id name="id" type="int" column="id" > <generator class="native"/> </id> <property name="price"> <column name="price" /> </property> <property name="purchaser"> <column name="purchaser" /> </property> <property name="stockId"> <column name="stockId"/> </property> </class> </hibernate-mapping>
Stock.java
package roseindia;
public class Stock {
private int stockId;
private int price;
private String stockName;
public Stock()
{
super();
}
public Stock(int stockId, int price, String stockName) {
super();
this.stockId = stockId;
this.price = price;
this.stockName = stockName;
}
public int getStockId() {
return stockId;
}
public void setStockId(int stockId) {
this.stockId = stockId;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
public String getStockName() {
return stockName;
}
public void setStockName(String stockName) {
this.stockName = stockName;
}
}
stock.hbm.xml
<?xml version='1.0'?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd"> <hibernate-mapping package="roseindia"> <class name="Stock" table="stock"> <id name="stockId" type="int" column="stockId" > </id> <property name="price"> <column name="price" /> </property> <property name="stockName"> <column name="stockName" /> </property> </class> </hibernate-mapping>
HibernateInsertQuery.java
package roseindia;
import org.hibernate.Transaction;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;
public class HibernateInsertQuery {
private static SessionFactory sessionFactory;
private static ServiceRegistry serviceRegistry;
public static void main(String args[])
{
Session session = null;
try
{
try
{
Configuration cfg = new Configuration();
cfg.addResource("roseindia/salesorder.hbm.xml").addResource(
"roseindia/stock.hbm.xml");
cfg.configure();
serviceRegistry = new ServiceRegistryBuilder().applySettings(
cfg.getProperties()).buildServiceRegistry();
sessionFactory = cfg.buildSessionFactory(serviceRegistry);
}
catch(Throwable th)
{
System.err.println("Failed to create sessionFactory object."
+ th);
throw new ExceptionInInitializerError(th);
}
session = sessionFactory.openSession();
Transaction t = session.beginTransaction();
Query query = session.createQuery("insert into SalesOrder(stockId, price)" +
" select st.stockId, st.price from Stock st where st.stockId = 5");
int update = query.executeUpdate();
if(update == 0 || update == 1)
{
System.out.println(update + " row affected");
}
else
System.out.println(update + " rows affected");
System.out.println("Inserted Records Successfully");
System.out.println("Successfully updated");
t.commit();
}
catch(Exception e)
{
System.out.println(e.getMessage());
}
finally
{
session.close();
}
}
}
hibernate.cfg.xml
<?xml version='1.0' encoding='utf-8'?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD//EN" "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <session-factory> <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property> <property name="hibernate.connection.url">jdbc:mysql://192.168.10.13:3306/data </property> <property name="hibernate.connection.username">root</property> <property name="hibernate.connection.password">root</property> <property name="hibernate.connection.pool_size">10</property> <property name="show_sql">true</property> <property name="dialect">org.hibernate.dialect.MySQLDialect</property> <property name="hibernate.current_session_context_class">thread</property> </session-factory> </hibernate-configuration>
Output :
1. stock table
2. salesorder table before
3. When you will execute the java file HibernateInsertQuery.java (RightClick -> Run As -> Java Application) you will get the output as :
4. salesorder table after executing the program.
