×
☰ See All Chapters

JPA Native Query Support

JPA allow us to execute native SQL queries, instead of using JPQL. This is advantageous in the following scenarios.

  1. When you're migrating a JDBC application to JPA on a strict deadline, and you don't have time to translate your existing SQL selects to JPQL. 

  2. Database developers can write fine-tuned queries, sometimes this cannot be done through JPQL, and in such cases we can use SQL queries in JPA. 

  3. We can make use of certain functions and support given in the SQL of the database. 

How to execute native SQL queries form JPA

The EntityManager has the below two factory methods suitable for creating SQL queries, by passing a string SQL to these methods we can get the Query object.

public Query createNativeQuery(String sqlString, Class resultClass);

public Query createNativeQuery(String sqlString, String resultSetMapping);

These methods return persistent instances of entity class, at a minimum, SQL must select the class' primary key columns, discriminator column (if mapped), and version column (also if mapped).

EntityManagerFactory emf = Persistence.createEntityManagerFactory("StudentPU");

EntityManager em = emf.createEntityManager();

em.getTransaction().begin();

Query query = em.createNativeQuery("SELECT * FROM STUDENT", Student.class);

List<Student> results = (List<Student>)query.getResultList();

We can use both Named parameters and Positional parameters in SQL as below:

Query query = em.createNativeQuery("SELECT * FROM STUDENT WHERE sname = :studentName", Student.class);

query.setParameter("studentName", "Manu Manjunatha");

Query query = em.createNativeQuery("SELECT * FROM STUDENT WHERE sname = ?1", Student.class);

query.setParameter(1, "Manu Manjunatha");

               

JPA Native Query Example

Database script (MySQL)

CREATE        TABLE ADDRESS(

AID INT(5) PRIMARY KEY AUTO_INCREMENT,

CITY VARCHAR(30),

ZIPCODE VARCHAR(30)

);                

 

CREATE TABLE STUDENT(

SID INT(5) PRIMARY KEY AUTO_INCREMENT,

SNAME VARCHAR(30),

AID INT(5),

CONSTRAINT FOREIGN KEY (AID) REFERENCES ADDRESS (AID)

);

 

INSERT  INTO ADDRESS(AID,CITY,ZIPCODE) VALUES (1,'Bangalore', '560010');

 

INSERT  INTO STUDENT(SID,SNAME,AID) VALUES (1,'Manu Manjunatha', 1);

INSERT  INTO STUDENT(SID,SNAME,AID) VALUES (2,'Advith Tyagraj', 1);

INSERT  INTO STUDENT(SID,SNAME,AID) VALUES (3,'Likitha', 1);

INSERT  INTO STUDENT(SID,SNAME,AID) VALUES (4,'Tyagraj', 1);

 

pom.xml

<project xmlns="https://maven.apache.org/POM/4.0.0" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"

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

        <modelVersion>4.0.0</modelVersion>

        <groupId>com.java4coding</groupId>

        <artifactId>JPA_NativeQuerySupport</artifactId>

        <packaging>jar</packaging>

        <version>1.0-SNAPSHOT</version>

        <name>JPA_NativeQuerySupport</name>

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

        <dependencies>

                <dependency>

                        <groupId>junit</groupId>

                        <artifactId>junit</artifactId>

                        <version>3.8.1</version>

                        <scope>test</scope>

                </dependency>

                <dependency>

                        <groupId>org.eclipse.persistence</groupId>

                        <artifactId>javax.persistence</artifactId>

                        <version>2.0.0</version>

                </dependency>

 

                <dependency>

                        <groupId>org.hibernate</groupId>

                        <artifactId>hibernate-entitymanager</artifactId>

                        <version>4.2.8.Final</version>

                </dependency>

 

                <dependency>

                        <groupId>mysql</groupId>

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

                        <version>8.0.11</version>

                </dependency>

        </dependencies>

</project>

 

persistence.xml

<?xml version="1.0" encoding="UTF-8"?>

<persistence xmlns="https://java.sun.com/xml/ns/persistence"

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

        xsi:schemaLocation="https://java.sun.com/xml/ns/persistence

             https://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"

        version="2.0">

 

        <persistence-unit name="StudentPU">

                <provider>org.hibernate.ejb.HibernatePersistence</provider>

                <properties>

                        <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/study" />

                        <property name="hibernate.connection.driver_class" value="com.mysql.cj.jdbc.Driver" />

                        <property name="hibernate.connection.username" value="root" />

                        <property name="hibernate.connection.password" value="root" />

                        <property name="hibernate.archive.autodetection" value="class" />

                        <property name="hibernate.show_sql" value="true" />

                        <property name="hibernate.format_sql" value="true" />

                        <property name="hbm2ddl.auto" value="update" />

                </properties>

        </persistence-unit>

</persistence>

Address.java

package com.java4coding;

 

import javax.persistence.Entity;

import javax.persistence.Id;

import javax.persistence.Table;

 

@Entity

@Table(name = "ADDRESS")

public class Address {

       

        @Id

        private int aid;

       

        private String city;

 

        private String zipcode;

 

        public int getAid() {

                return aid;

        }

        public void setAid(int aid) {

                this.aid = aid;

        }

        public String getCity() {

                return city;

        }

        public void setCity(String city) {

                this.city = city;

        }

        public String getZipcode() {

                return zipcode;

        }

        public void setZipcode(String zipcode) {

                this.zipcode = zipcode;

        }

}

Student.java

package com.java4coding;

 

import javax.persistence.CascadeType;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import javax.persistence.JoinColumn;

import javax.persistence.ManyToOne;

import javax.persistence.Table;

 

@Entity

@Table(name = "STUDENT")

public class Student {

       

        @Id

        @GeneratedValue(strategy = GenerationType.AUTO)

        private int sid;

       

        private String sname;

       

        @ManyToOne(cascade = CascadeType.ALL)

        @JoinColumn(name= "AID")

        private Address address;

 

        public int getSid() {

                return sid;

        }

        public void setSid(int sid) {

                this.sid = sid;

        }

        public String getSname() {

                return sname;

        }

        public void setSname(String sname) {

                this.sname = sname;

        }

        public Address getAddress() {

                return address;

        }

        public void setAddress(Address address) {

                this.address = address;

        }

}

 

Test.java

package com.java4coding;

 

import java.util.List;

 

import javax.persistence.EntityManager;

import javax.persistence.EntityManagerFactory;

import javax.persistence.Persistence;

import javax.persistence.Query;

 

public class Test {

        public static void main(String[] args) {

                EntityManagerFactory emf = Persistence.createEntityManagerFactory("StudentPU");

                EntityManager em = emf.createEntityManager();

                em.getTransaction().begin();

                Query query = em.createNativeQuery("SELECT * FROM STUDENT", Student.class);

 

                List<Student> results = (List<Student>)query.getResultList();

               

                for(Student s: results) {

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

                        if (s.getSname() != null) {

                                System.out.println("Student name: " + s.getSname());

                        }

                        if (s.getSid() != 0) {

                                System.out.println("Student ID: " + s.getSid());

                        }

                        if (s.getAddress().getCity() != null) {

                                System.out.println("Student City: " + s.getAddress().getCity());

                        }

                        if (s.getAddress().getCity() != null) {

                                System.out.println("Student City: " + s.getAddress().getCity());

                        }

                        if (s.getAddress().getZipcode() != null) {

                                System.out.println("Student Zipcode: " + s.getAddress().getZipcode());

                        }

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

                }

               

                em.getTransaction().commit();

        }

}

Project directory structure

jpa-native-query-0
 

Output:

Hibernate:

    SELECT

        *

    FROM

        STUDENT

Hibernate:

    select

        address0_.aid as aid1_0_0_,

        address0_.city as city2_0_0_,

        address0_.zipcode as zipcode3_0_0_

    from

        ADDRESS address0_

    where

        address0_.aid=?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Student name: Manu Manjunatha

Student ID: 1

Student City: Bangalore

Student City: Bangalore

Student Zipcode: 560010

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Student name: Advith Tyagraj

Student ID: 2

Student City: Bangalore

Student City: Bangalore

Student Zipcode: 560010

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Student name: Likitha

Student ID: 3

Student City: Bangalore

Student City: Bangalore

Student Zipcode: 560010

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Student name: Tyagraj

Student ID: 4

Student City: Bangalore

Student City: Bangalore

Student Zipcode: 560010

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

 


All Chapters
Author