Spring Data JPA @NamedNativeQuery and @NamedNativeQueries Example

In the previous article, we have learned how to create database queries using Spring Data JPA @NamedQuery and @NamedQueries annotations. In this article, we will learn how to create database queries using @NamedNativeQuery and @NamedNativeQueries annotations. These annotations let you define the query in native SQL by losing the database platform independence.
We are creating a SQL query so we must follow these steps:
  1. Annotate the entity with the @NamedNativeQuery annotation.
  2. Set the name of the named query (User.findByEmailAddress) as the value of the @NamedNativeQuery annotation’s name attribute.
  3. Set the SQL query (SELECT * FROM todos t WHERE t.title = ‘title’) as the value of the @NamedNativeQuery annotation’s name attribute.
  4. Set the returned entity class (Todo.class) as the value of the of the @NamedNativeQuery annotation’s resultClass attribute.
The relevant part of our entity looks as follows:
@Entity
@Table(name = "users")
@NamedNativeQuery(name = "User.findByEmailAddress", query = "select * from users where email_address = ?1", resultClass = User.class)
@NamedNativeQueries(value = {
  @NamedNativeQuery(name = "User.findByLastname", query = "select * from users where lastname = ?1", resultClass = User.class) })
public class User {
}
Let's develop a complete example to demonstrates usage of @NamedNativeQuery and @NamedNativeQueries using Spring Boot application which quickly bootstraps with autoconfiguration.

Tools and Technologies Used

  • Spring Boot - 2.0.6.RELEASE
  • JDK - 1.8 or later
  • Spring Framework - 5.0.9 RELEASE
  • Spring Data JPA - 2.0.10 RELEASE
  • Maven - 3.2+
  • IDE - Eclipse or Spring Tool Suite (STS)
There are many ways to create a Spring Boot application. The simplest way is to use Spring Initializr at http://start.spring.io/, which is an online Spring Boot application generator.

The pom.xml File

<?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>net.guides.springboot2</groupId>
    <artifactId>springboot-testing-examples</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>springboot-testing-examples</name>
    <description>Demo project for Spring Boot</description>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.5.RELEASE</version>
        <relativePath/>
        <!-- lookup parent from repository -->
    </parent>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>
The Spring Boot Maven plugin provides many convenient features:
  1. It collects all the jars on the classpath and builds a single, runnable "├╝ber-jar", which makes it more convenient to execute and transport your service.
  2. It searches for the public static void main() method to flag as a runnable class.
  3. It provides a built-in dependency resolver that sets the version number to match Spring Boot dependencies. You can override any version you wish, but it will default to Boot’s chosen set of versions.

JPA Entity - User.java

In this example, we store User objects, annotated as a JPA entity.
package net.guides.springboot2.springboottestingexamples.model;

import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

@Entity
@Table(name = "users")
@NamedNativeQuery(name = "User.findByEmailAddress", query = "select * from users where email_address = ?1", resultClass = User.class)
@NamedNativeQueries(value = {
  @NamedNativeQuery(name = "User.findByLastname", query = "select * from users where lastname = ?1", resultClass = User.class) })
public class User {
    private long id;
    private String firstname;
    private String lastname;
    private Date startDate;
    private String emailAddress;
    private int age;
    private int active;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public long getId() {
        return id;
    }

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

    public String getFirstname() {
        return firstname;
    }

    public void setFirstname(String firstname) {
        this.firstname = firstname;
    }

    public String getLastname() {
        return lastname;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname;
    }

    public Date getStartDate() {
        return startDate;
    }

    public void setStartDate(Date startDate) {
        this.startDate = startDate;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public int getActive() {
        return active;
    }

    public void setActive(int active) {
        this.active = active;
    }

    public String getEmailAddress() {
        return emailAddress;
    }

    public void setEmailAddress(String emailAddress) {
        this.emailAddress = emailAddress;
    }

    @Override
    public String toString() {
        return "User [id=" + id + ", firstname=" + firstname + ", lastname=" + lastname + ", startDate=" + startDate +
            ", emailAddress=" + emailAddress + ", age=" + age + ", active=" + active + "]";
    }
}
  • The User class is annotated with @Entity, indicating that it is a JPA entity.
  • The User’s id property is annotated with @Id so that JPA will recognize it as the object’s ID. The id property is also annotated with @GeneratedValue to indicate that the ID should be generated automatically.

@NamedNativeQuery

Specifies a named native SQL query. Query names are scoped to the persistence unit. The NamedNativeQuery annotation can be applied to an entity or mapped superclass.
@Entity
@Table(name = "users")
@NamedNativeQuery(name = "User.findByEmailAddress", query = "select * from users where email_address = ?1", resultClass = User.class)
public class User {
}

@NamedNativeQueries

Used to specify multiple native SQL named queries. Query names are scoped to the persistence unit. The NamedNativeQueries annotation can be applied to an entity or mapped superclass.
@Entity
@Table(name = "users")
@NamedNativeQueries(value = {
  @NamedNativeQuery(name = "User.findByLastname", query = "select * from users where lastname = ?1", resultClass = User.class) })
public class User {
}

Spring Data JPA Repository - UserRepository.java

package net.guides.springboot2.springboottestingexamples.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import net.guides.springboot2.springboottestingexamples.model.User;

/**
 * UserRepository demonstrates the method name query generation.
 * 
 * @author Ramesh Fadatare
 *
 */
@Repository
public interface UserRepository extends JpaRepository < User, Long > {
    User findByEmailAddress(String emailAddress);

    List <User> findByLastname(String lastname);
}

Create an Application class

Here you create an Application class with all the components.
package net.guides.springboot2.springboottestingexamples;

import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import net.guides.springboot2.springboottestingexamples.model.User;
import net.guides.springboot2.springboottestingexamples.repository.UserRepository;

@SpringBootApplication
public class Application implements CommandLineRunner {

    @Autowired
    private UserRepository userRepository;

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

    @Override
    public void run(String...args) throws Exception {
        User user = new User();
        user.setActive(1);
        user.setAge(28);
        user.setEmailAddress("ramesh24@gmail.com");
        user.setFirstname("Ramesh");
        user.setLastname("Fadatare");
        user.setStartDate(new Date());
        user = userRepository.save(user);

        System.out.println("-------------------------------------:: " + user.getId());

        System.out.println(" ---------------@NamedNativeQuery ---------------------");
        System.out.println("--------------findByEmailAddress -----------------");

        User user2 = userRepository.findByEmailAddress("ramesh24@gmail.com");
        System.out.println(user2.toString());

        System.out.println(" ---------------@NamedNativeQueries ---------------------");
        System.out.println("--------------findByLastname -----------------");

        List < User > user3 = userRepository.findByLastname("Fadatare");
        System.out.println(user3.get(0).toString());
    }
}
Note that we have used UserRepository methods to demonstrates @NamedNativeQuery and @NamedNativeQueries annotations.

Running Application

We are using Maven so we can run the application using ./mvnw spring-boot:run. Or you can build the JAR file with ./mvnw clean package. Then you can run the JAR file:
java -jar target/spring-data-jpa-example-0.1.0.jar

Output


The source code of this article available on my GitHub repository - https://github.com/RameshMF/spring-data-jpa-tutorial

Related Posts



Comments