Hibernate/JPA Named Native Query XML and Annotation Example

Previously, we saw how to use named queries. This tutorial, we show how to use Hibernate/JPA Named Native Queries. With Named Native Queries, we can use native SQL statements.

Maven Dependencies

We use Apache Maven to manage the projects dependencies.

<?xml version="1.0" encoding="UTF-8"?>
<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.memorynotfound.db.hibernate</groupId>
    <artifactId>jpa-named-query</artifactId>
    <version>1.0.0-SNAPSHOT</version>
    <packaging>war</packaging>
    <name>HIBERNATE - ${project.artifactId}</name>
    <url>https://memorynotfound.com</url>

    <properties>
        <mysql.driver.version>6.0.4</mysql.driver.version>
        <hibernate.version>5.2.3.Final</hibernate.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.driver.version}</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>${hibernate.version}</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.5.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

Create Model + Mappings

You can annotate the class with the @NamedNativeQueries annotation to register multiple @NamedNativeQuery annotations. This annotation specifies a static named native query. With Named Native Queries, we can use native SQL statements. These queries can be re-used without having the overhead to re-compile the same query again. Query names are scoped to the persistence unit. The Named Native Query can be applied to an entity or mapped superclass.

package com.memorynotfound.hibernate;

import javax.persistence.*;

@Entity
@NamedNativeQueries(value = {
        @NamedNativeQuery(
                name = "Book.findAll",
                query = "SELECT * FROM Book",
                resultClass = Book.class),
        @NamedNativeQuery(
                name = "Book.findById",
                query = "SELECT * from Book WHERE id = :id",
                resultClass = Book.class)
})
public class Book {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String title;

    public Book() {
    }

    public Book(String title) {
        this.title = title;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    @Override
    public String toString() {
        return "Book{" +
                "id=" + id +
                ", title='" + title + '\'' +
                '}';
    }
}

If you prefer XML over Annotations, you can use the equivalent JPA XML mapping. This file is located in the src/main/resources/META-INF folder and is named orm.xml.

<?xml version="1.0" encoding="UTF-8" ?>
<entity-mappings xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm"
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence/orm
                                     http://xmlns.jcp.org/xml/ns/persistence/orm_2_0.xsd" version="2.1">

    <!-- JPA Named Native Queries -->
    <named-native-query name="Book.findAll" result-class="com.memorynotfound.hibernate.Book">
        <query>SELECT * FROM Book</query>
    </named-native-query>
    <named-native-query name="Book.findById" result-class="com.memorynotfound.hibernate.Book">
        <query>SELECT * FROM Book WHERE id = :id</query>
    </named-native-query>

    <!-- entity mapping -->
    <entity class="com.memorynotfound.hibernate.Book">
        <attributes>
            <basic name="title"/>
        </attributes>
    </entity>

</entity-mappings>

Hibernate/JPA Configuration

We configure the JPA Persistence Unit using the persistence.xml file, which is located in the src/main/resources/META-INF directory.

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
                                 http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
    <persistence-unit name="mnf-pu" transaction-type="RESOURCE_LOCAL">
        <!-- enable if you want xml mappings
        <mapping-file>META-INF/orm.xml</mapping-file>
        -->
        <properties>
            <!-- Configuring JDBC properties -->
            <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/memorynotfound?serverTimezone=Europe/Brussels"/>
            <property name="javax.persistence.jdbc.user" value="root"/>
            <property name="javax.persistence.jdbc.password" value=""/>
            <property name="javax.persistence.jdbc.driver" value="com.mysql.cj.jdbc.Driver"/>

            <!-- Hibernate properties -->
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
            <property name="hibernate.hbm2ddl.auto" value="create-drop"/>
            <property name="hibernate.format_sql" value="false"/>
            <property name="hibernate.show_sql" value="true"/>
        </properties>
    </persistence-unit>
</persistence>

Create, Run and Test

package com.memorynotfound.hibernate;

import javax.persistence.*;
import java.util.List;

public class App {

    public static void main (String...args) throws InterruptedException {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("mnf-pu");
        EntityManager em = emf.createEntityManager();

        em.getTransaction().begin();
        Book book = new Book("Hibernate/JPA Named Native Query Example");
        em.persist(book);
        em.getTransaction().commit();

        List<Book> r1 = em.createNamedQuery("Book.findAll", Book.class).getResultList();
        System.out.println(r1);

        Book r2 = em.createNamedQuery("Book.findById", Book.class)
                .setParameter("id", 1)
                .getSingleResult();
        System.out.println(r2);

        emf.close();
    }
}

...
Hibernate: insert into Book (title) values (?)
Hibernate: SELECT * FROM Book
[Book{id=1, title='Hibernate/JPA Named Native Query Example'}]
Hibernate: SELECT * FROM Book WHERE id = ?
Book{id=1, title='Hibernate/JPA Named Native Query Example'}
...

Download