Tutorial - Hibernate native sql insert query example

Sponsored Links

Ad by Google

In my previous post, we have seen an example of "How to use @Formula annotation in Hibernate or Calculated property example in Hibernate" at Hibernate @Formula annotation example
If you are new to Hibernate than please visit Step by Step Hibernate Tutorial here

In this post, I am going to show you how to use native sql queries in Hibernate Query Language, of-course hibernate has powerful query language called HQL, but you can't ignore Structure Query Language(SQL) because core is always core, anyway while using HQL sometimes you may faced a situation where you think oh, In this place SQL will better instead of HQL. Because of that, I am sharing with you guys "How to use Native SQL query in Hibernate".

In this tutorial, I am going to use Product table and inserting records into product table via native query with HQL. Although we have already seen an example of fetching records using Hibernate Query Language here.

Native Sql Query:
Hibernate provides SQLQuery interface to execute Sql Query and SQLQuery can be obtain via createSQLQuery method.

Syntax to obtain SqlQuery:

SQLQuery query = session.createSQLQuery("your query");

Technologies & Tools we are using here:

1. JDK 7
2. Hibernate 4.3.7
3. MySql 5.5
4. Eclipse Juno 4.2
5. Maven 3.2.2

Step 1. Create table script.

CREATE DATABASE `hibernate_tutorial`;

USE `hibernate_tutorial`;

/*Table structure for table `product` */

DROP TABLE IF EXISTS `product`;

CREATE TABLE `product` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `manufactured_date` datetime DEFAULT NULL,
  `price` float DEFAULT NULL,
  `product_code` varchar(255) DEFAULT NULL,
  `product_name` varchar(255) DEFAULT NULL,
  `vat` float DEFAULT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Step 2. Create Product.java annotated class.

package com.javamakeuse.hibernate.poc.pojo;

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

@Entity
@Table(name = "product")
public class Product {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Column(name = "product_id")
	private int id;

	@Column(name = "product_code")
	private String productCode;

	@Column(name = "product_name")
	private String productName;

	@Column(name = "price")
	private float price;

	@Column(name = "vat")
	private float vat;

	@Column(name = "manufactured_date")
	@Temporal(TemporalType.TIMESTAMP)
	private Date manufacturedDate;

	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getProductCode() {
		return productCode;
	}
	public void setProductCode(String productCode) {
		this.productCode = productCode;
	}
	public String getProductName() {
		return productName;
	}
	public void setProductName(String productName) {
		this.productName = productName;
	}
	public float getPrice() {
		return price;
	}
	public void setPrice(float price) {
		this.price = price;
	}
	public float getVat() {
		return vat;
	}
	public void setVat(float vat) {
		this.vat = vat;
	}
	public Date getManufacturedDate() {
		return manufacturedDate;
	}
	public void setManufacturedDate(Date manufacturedDate) {
		this.manufacturedDate = manufacturedDate;
	}

	@Override
	public String toString() {
		return "Product [id=" + id + ", productCode=" + productCode
				+ ", productName=" + productName + ", price=" + price
				+ ", vat=" + vat + ", manufacturedDate=" + manufacturedDate
				+ "]";
	}

}

Step 3. Create HibernateUtility.java class to build SessionFactory.

package com.javamakeuse.hibernate.poc.util;

import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;

public class HibernateUtility {
	private static final SessionFactory sessionFactory = buildSessionFactory();

	private static SessionFactory buildSessionFactory() {
		Configuration configuration = new Configuration();
		configuration.configure();

		ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder()
				.applySettings(configuration.getProperties()).build();
		SessionFactory sessionFactory = configuration
				.buildSessionFactory(serviceRegistry);

		return sessionFactory;
	}

	public static SessionFactory getSessionFactory() {
		return sessionFactory;
	}

}

Step 4. Create hibernate.cfg.xml file to provide mapping class and database related details.

<?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">com.mysql.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://localhost:3306/hibernate_tutorial</property>
        <property name="connection.username">root</property>
        <property name="connection.password">root</property>
         
        <property name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>
        <!-- <property name="format_sql">true</property> -->
        <mapping class="com.javamakeuse.hibernate.poc.pojo.Product"/>
          
    </session-factory>
</hibernate-configuration>

Step 5. Create ProductService.java class to insert/select product into the database.

package com.javamakeuse.hibernate.poc.service;

import java.util.Calendar;
import java.util.List;

import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;

import com.javamakeuse.hibernate.poc.pojo.Product;
import com.javamakeuse.hibernate.poc.util.HibernateUtility;

public class ProductService {

	private static SessionFactory sessionFactory = HibernateUtility.getSessionFactory();
	
	public static List<Product> getProducts() {
		Session session = sessionFactory.openSession();
		SQLQuery sqlQuery = session.createSQLQuery("select * from product");
		return sqlQuery.list();
	}
	
	private static void saveProduct(Product product){
		Session session = sessionFactory.openSession();
		session.beginTransaction();
		SQLQuery insertQuery = session.createSQLQuery("" +
		"INSERT INTO product(manufactured_date,price,product_code,product_name,vat)VALUES(?,?,?,?,?)");
		insertQuery.setParameter(0, product.getManufacturedDate());
		insertQuery.setParameter(1, product.getPrice());
		insertQuery.setParameter(2, product.getProductCode());
		insertQuery.setParameter(3, product.getProductName());
		insertQuery.setParameter(4, product.getVat());
		insertQuery.executeUpdate();
		session.getTransaction().commit();
	}

	public static void main(String[] args) {
		Product product = new Product();
		product.setManufacturedDate(Calendar.getInstance().getTime());
		product.setPrice(20);
		product.setProductCode("TP001");
		product.setProductName("Tooth Paste");
		product.setVat(2);
		saveProduct(product);
		System.out.println("Total product in database - "+getProducts().size());
	}
}

OUTPUT:

Hibernate: INSERT INTO product(manufactured_date,price,product_code,product_name,vat)VALUES(?,?,?,?,?)
Hibernate: select * from product
Total product in database - 1

You can download the complete source code from here

Sponsored Links