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'}
...
