Hibernate Native SQL Query Example | Named SQL Queries Hibernate

 Hibernate Native SQL Query Example

For explanation watch the video :

Directory structure

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

<modelVersion>4.0.0</modelVersion>

<groupId>com.test</groupId>

<artifactId>NamedSqlQueries</artifactId>

<version>0.0.1-SNAPSHOT</version>

<packaging>jar</packaging>

<name>NamedSqlQueries</name>

<url>http://maven.apache.org</url>

<properties>

<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

<maven.compiler.source>1.9</maven.compiler.source>

<maven.compiler.target>1.9</maven.compiler.target>

</properties>

<dependencies>

<dependency>

<groupId>junit</groupId>

<artifactId>junit</artifactId>

<version>3.8.1</version>

<scope>test</scope>

</dependency>

<dependency>

<groupId>org.hibernate</groupId>

<artifactId>hibernate-core</artifactId>

<version>5.6.8.Final</version>

</dependency>

<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->

<dependency>

<groupId>org.projectlombok</groupId>

<artifactId>lombok</artifactId>

<version>1.18.20</version>

<scope>provided</scope>

</dependency>

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->

<dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

<version>8.0.22</version>

</dependency>

</dependencies>

</project>

Employee

package com.test.entity;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import javax.persistence.Table;

import lombok.AllArgsConstructor;

import lombok.Data;

import lombok.NoArgsConstructor;

@Data

@AllArgsConstructor

@NoArgsConstructor

@Entity

@Table(name = "emptab")

public class Employee {

@Id

@GeneratedValue(strategy = GenerationType.IDENTITY)

private Integer id;

private String name;

private String dept;

private Double sal;

public Employee(String name,Double sal,String dept) {

super();

this.name = name;

this.dept = dept;

this.sal = sal;

}

}

HibernateUtil

package com.test.util;

import java.util.Properties;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

import org.hibernate.cfg.Configuration;

import org.hibernate.cfg.Environment;

import com.test.entity.Employee;

public class HibernateUtil {

static SessionFactory factory;

static {

Configuration cfg = new Configuration();

Properties prop = new Properties();

prop.put(Environment.URL, "jdbc:mysql://localhost:3306/new");

prop.put(Environment.USER, "root");

prop.put(Environment.PASS, "root");

prop.put(Environment.SHOW_SQL, true);

prop.put(Environment.FORMAT_SQL, true);

prop.put(Environment.HBM2DDL_AUTO, "update");

cfg.setProperties(prop);

cfg.addAnnotatedClass(Employee.class);

factory = cfg.buildSessionFactory();

}

public static SessionFactory getSessionFactory() {

return factory;

}

public static Session getSession() {

return factory.openSession();

}

}

App

package com.test;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

import com.test.entity.Employee;

import com.test.util.HibernateUtil;

public class App 

{

    public static void main( String[] args )

    {

    SessionFactory factory = HibernateUtil.getSessionFactory();

    Session ses = HibernateUtil.getSession();

    try(factory;ses){

    //create emp objects

    Employee e1 = new Employee("sam", 5000.0, "QA");

Employee e2 = new Employee("Jhon", 6000.0, "BA");

Employee e3 = new Employee("Randy", 7000.0, "DEV");

Employee e4 = new Employee("Brock", 5000.0, "QA");

Employee e5 = new Employee("Angela", 6000.0, "BA");

Employee e6 = new Employee("Carla", 7000.0, "DEV");

Employee e7 = new Employee("Raja", 5000.0, "QA");

Employee e8 = new Employee("Rani", 6000.0, "BA");

Employee e9 = new Employee("Ram", 7000.0, "DEV");

Employee e10 = new Employee("Sandy", 5000.0, "QA");

ses.beginTransaction();

ses.save(e1);

ses.save(e2);

ses.save(e3);

ses.save(e4);

ses.save(e5);

ses.save(e6);

ses.save(e7);

ses.save(e8);

ses.save(e9);

ses.save(e10);

ses.getTransaction().commit();

    }catch(Exception e) {

    e.printStackTrace();

    }

    }

}

Test

package com.test;

import java.util.List;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

import org.hibernate.query.NativeQuery;

import org.hibernate.type.StandardBasicTypes;

import com.test.entity.Employee;

import com.test.util.HibernateUtil;

public class Test {

public static void main(String[] args) {

SessionFactory factory = HibernateUtil.getSessionFactory();

Session ses = HibernateUtil.getSession();

try (factory; ses) {

// select * from emptab

NativeQuery query1 = ses.createNativeQuery("SELECT * FROM EMPTAB");

// results of entity query not mapped with entity class

// so we get each as the Object class Object[]

List<Object[]> list1 = query1.getResultList();

list1.forEach(emp -> {

for (Object obj : emp) {

System.out.print(obj + " ");

}

System.out.println();

});

System.out.println("------------------");

// how to map entity query res with entity class

// select * from emptab

NativeQuery query2 = ses.createNativeQuery("SELECT * FROM EMPTAB");

query2.addEntity(Employee.class);

List<Employee> list2 = query2.getResultList();

list2.forEach(System.out::println);

// scalar query for two cols

// SELECT ID,SAL FROM EMPTAB WHERE SAL BETWEEN ? AND ?

NativeQuery query3 = ses.createNativeQuery("SELECT ID,SAL FROM EMPTAB WHERE SAL BETWEEN ? AND ?");

//map the scalar query results with hibernate data types

query3.addScalar("ID", StandardBasicTypes.INTEGER);

query3.addScalar("SAL", StandardBasicTypes.DOUBLE);

//set the query praram values

query3.setParameter(1, 5000.0);

query3.setParameter(2, 6000.0);

//execute the query

List<Object[]> list3 = query3.getResultList();

list3.forEach(emp -> {

for (Object obj : emp) {

System.out.print(obj + " ");

}

System.out.println();

});

//scalar query for getting single col values

//"SELECT ID FROM EMPTAB WHERE SAL>=?1"

NativeQuery query4 = ses.createNativeQuery("SELECT ID FROM EMPTAB WHERE SAL>=?1");

//map scalar query res with hb data types

query4.addScalar("ID", StandardBasicTypes.INTEGER);

//set the query param values

query4.setParameter(1, 6000.0);

//execute the query4

List<Integer> list4= query4.getResultList();

//print

list4.forEach(System.out::println);

//native sql entity query with named params

//SELECT * FROM EMPTAB WHERE SAL>=:salRange AND ID>=:idRange

NativeQuery query5 = ses.createNativeQuery("SELECT * FROM EMPTAB WHERE SAL>=:salRange AND ID>=:idRange");

//map entity query result with entity class

query5.addEntity(Employee.class);

//set the query param values

query5.setParameter("salRange", 6000.0);

query5.setParameter("idRange", 5);

//execute the query

List<Employee> list5 = query5.getResultList();

//print

list5.forEach(System.out::println);

ses.beginTransaction();

//UPDATE EMPTAB SET SAL=? WHERE ID = ?

NativeQuery query6 = ses.createNativeQuery("UPDATE EMPTAB SET SAL=? WHERE ID = ?");

query6.setParameter(1, 8000.0);

query6.setParameter(2, 3);

//non-select

int count = query6.executeUpdate();

//print the records updated

System.out.println("No of records updated "+count);

ses.getTransaction().commit();

ses.beginTransaction();

//UPDATE EMPTAB SET SAL=? WHERE ID = ?

NativeQuery query7 = ses.createNativeQuery("DELETE FROM EMPTAB WHERE ID >= ?");

query7.setParameter(1, 8);

//non-select

int count1 = query7.executeUpdate();

//print the records updated

System.out.println("No of records updated "+count1);

ses.getTransaction().commit();

ses.beginTransaction();

NativeQuery query8 = ses.createNativeQuery("INSERT EMPTAB(DEPT,NAME,SAL) VALUES(?,?,?)");

query8.setParameter(1, "QA");

query8.setParameter(2, "Shon");

query8.setParameter(3, 7000.0);

//non-select

int count2 = query8.executeUpdate();

//print the records updated

System.out.println("No of records inserted "+count2);

ses.getTransaction().commit();

} catch (Exception e) {

e.printStackTrace();

}

}

}