Spring Data JPA @NamedNativeQuery

By
Rakesh
-

In this tutorial, we will see Spring Data JPA @NamedNativeQuery annotation Example using Spring Boot and oracle. Also, we will see how to write multiple @NamedNativeQuery using @NamedNativeQueries annotation.

@NamedNativeQuery and @NamedNativeQueries annotations used with entity class. Using @NamedNativeQuery annotation we define a query. Using @NamedNativeQueries we can define multiple @NamedNativeQuery.

Consider we have an entity called Student.java as below.

package com.javatute.entity;

@Entity
public class Student {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int id;

	@Column(name = "name")
	private String name;

	@Column(name = "roll_number")
	private String rollNumber;

	@Column(name = "university")
	String university;
	

}

and we have some record in the database as below.

Spring Data JPA @NamedNativeQuery

Using @NamedNativeQuery.

package com.javatute.entity;


@Entity
@NamedNativeQuery(name = "Student.findByName", query = "select * from Student where name = ?1", resultClass = Student.class)
public class Student {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int id;

	@Column(name = "name")
	private String name;

	@Column(name = "roll_number")
	private String rollNumber;

	@Column(name = "university")
	String university;
}
package com.javatute.entity;

@Entity
@NamedNativeQueries({
		@NamedNativeQuery(name = "Student.findByName", query = "select * from Student where name = ?1", resultClass = Student.class),
		@NamedNativeQuery(name = "Student.findByNameAndRollNumber", query = "select * from Student where name = ?1 and roll_number = ?2", resultClass = Student.class),
		@NamedNativeQuery(name = "Student.findByNameOrRollNumber", query = "select * from Student where name = ?1 or roll_number = ?2", resultClass = Student.class) })
public class Student {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int id;

	@Column(name = "name")
	private String name;

	@Column(name = "roll_number")
	private String rollNumber;

	@Column(name = "university")
	String university;

}

Note – If we don’t define resultClass = Student.class then we may not get the proper response.

For example – You may get response data in below format.

http://localhost:9091/student/findByName/john 
[
	[
		1,
		"john",
		"120",
		"rgtu"
	]
]

Sample Repository Example.

package com.javatute.repository;

import java.io.Serializable;
import java.util.List;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import com.javatute.entity.Student;

@Repository
public interface StudentRepository extends CrudRepository<Student, Serializable> {

	List<Student> findByName(String name);

	List<Student> findByNameAndRollNumber(String name, String rollNumber);

	List<Student> findByNameOrRollNumber(String name, String rollNumber);

}

Till now We have seen Spring Data JPA @NamedNativeQuery and @NamedNativeQueries annotations. Let’s see a complete example from scratch.

Spring Data JPA @NamedNativeQuery and  @NamedNativeQueries Example using  Spring Boot and Oracle.

Open eclipse and create maven project, Don’t forget to check ‘Create a simple project (skip)’ click on next.  Fill all details(GroupId – springdatanamednativequery, ArtifactId – springdatanamednativequery and name – springdatanamednativequery) and click on finish. Keep packaging as the jar.

Modify 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>springdatanamednativequery</groupId>
	<artifactId>springdatanamednativequery</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>springdatanamednativequery</name>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.2.RELEASE</version>
	</parent>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>

		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>com.oracle</groupId>
			<artifactId>ojdbc6</artifactId>
			<version>11.2.0.3</version>
		</dependency>

	</dependencies>
	<build>
		<finalName>${project.artifactId}</finalName>
		<plugins>

			<plugin>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.1</version>
				<configuration>
					<fork>true</fork>
					<executable>C:\Program Files\Java\jdk1.8.0_131\bin\javac.exe</executable>
				</configuration>
			</plugin>


		</plugins>
	</build>
</project>

Note – In pom.xml we have defined javac.exe path in configuration tag. You need to change accordingly i.e where you have installed JDK.

If you see any error for oracle dependency then follow these steps.

Directory structure –

Spring Data JPA @NamedNativeQuery

Student.java

package com.javatute.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedNativeQueries;
import javax.persistence.NamedNativeQuery;

@Entity
@NamedNativeQueries({
		@NamedNativeQuery(name = "Student.findByName", query = "select * from Student where name = ?1", resultClass = Student.class),
		@NamedNativeQuery(name = "Student.findByNameAndRollNumber", query = "select * from Student where name = ?1 and roll_number = ?2", resultClass = Student.class),
		@NamedNativeQuery(name = "Student.findByNameOrRollNumber", query = "select * from Student where name = ?1 or roll_number = ?2", resultClass = Student.class) })
public class Student {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int id;

	@Column(name = "name")
	private String name;

	@Column(name = "roll_number")
	private String rollNumber;

	@Column(name = "university")
	String university;

	public int getId() {
		return id;
	}

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

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getRollNumber() {
		return rollNumber;
	}

	public void setRollNumber(String rollNumber) {
		this.rollNumber = rollNumber;
	}

	public String getUniversity() {
		return university;
	}

	public void setUniversity(String university) {
		this.university = university;
	}

}

StudentController.java

package com.javatute.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import com.javatute.entity.Student;
import com.javatute.service.StudentService;

@RestController
@RequestMapping(value = "/student")
public class StudentController {

	@Autowired
	private StudentService studentService;

	@RequestMapping(value = "/saveall", method = RequestMethod.POST)
	@ResponseBody
	public List<Student> saveAllStudents(@RequestBody List<Student> studentList) {
		List<Student> studentResponse = (List<Student>) studentService.saveAllStudent(studentList);
		return studentResponse;
	}

	@RequestMapping(value = "/findByName/{name}", method = RequestMethod.GET)
	@ResponseBody
	public List<Student> findByName(@PathVariable String name) {
		List<Student> studentResponse = (List<Student>) studentService.findByName(name);
		return studentResponse;
	}

	@RequestMapping(value = "/findByNameAndRollNumber/{name}/{rollNumber}", method = RequestMethod.GET)
	@ResponseBody
	public List<Student> findByNameAndRollNumber(@PathVariable String name, @PathVariable String rollNumber) {
		List<Student> studentResponse = (List<Student>) studentService.findByNameAndRollNumber(name, rollNumber);
		return studentResponse;
	}

	@RequestMapping(value = "/findByNameOrRollNumber/{name}/{rollNumber}", method = RequestMethod.GET)
	@ResponseBody
	public List<Student> findByNameOrRollNumber(@PathVariable String name, @PathVariable String rollNumber) {
		List<Student> studentResponse = (List<Student>) studentService.findByNameOrRollNumber(name, rollNumber);
		return studentResponse;
	}

}

Note – See more details about @Controller and RestController here.

StudentRepository.java – interface

package com.javatute.repository;

import java.io.Serializable;
import java.util.List;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import com.javatute.entity.Student;

@Repository
public interface StudentRepository extends CrudRepository<Student, Serializable> {

	List<Student> findByName(String name);

	List<Student> findByNameAndRollNumber(String name, String rollNumber);

	List<Student> findByNameOrRollNumber(String name, String rollNumber);

}

StudentService.java – interface

package com.javatute.service;

import java.util.List;

import org.springframework.stereotype.Component;

import com.javatute.entity.Student;

@Component
public interface StudentService {
	public List<Student> saveAllStudent(List<Student> studentList);

	List<Student> findByName(String name);

	List<Student> findByNameAndRollNumber(String name, String rollNumber);

	List<Student> findByNameOrRollNumber(String name, String rollNumber);
}

Note – See here more about @Component, @Controller, @Service and @Repository annotations here.

StudentServiceImpl.java

package com.javatute.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.javatute.entity.Student;
import com.javatute.repository.StudentRepository;
import com.javatute.service.StudentService;

@Service("studentServiceImpl")
public class StudentServiceImpl implements StudentService {

	@Autowired
	private StudentRepository studentRepository;

	@Transactional
	public List<Student> saveAllStudent(List<Student> studentList) {
		List<Student> response = (List<Student>) studentRepository.saveAll(studentList);
		return response;
	}

	@Transactional(readOnly = true)
	public List<Student> findByName(String name) {
		List<Student> studentResponse = studentRepository.findByName(name);
		return studentResponse;
	}

	@Transactional(readOnly = true)
	public List<Student> findByNameAndRollNumber(String name, String rollNumber) {
		List<Student> studentResponse = studentRepository.findByNameAndRollNumber(name, rollNumber);
		return studentResponse;
	}
	
	@Transactional(readOnly = true)
	public List<Student> findByNameOrRollNumber(String name, String rollNumber) {
		List<Student> studentResponse = studentRepository.findByNameOrRollNumber(name, rollNumber);
		return studentResponse;
	}

}

SpringMain.java

package com.javatute.main;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.context.annotation.ComponentScan;

@SpringBootApplication
@ComponentScan(basePackages = "com.*")
@EntityScan("com.javatute.entity")
public class SpringMain {
	public static void main(String[] args) {

		SpringApplication.run(SpringMain.class, args);
	}

}

Note – See more details about @ComponentScan here.

JpaConfig.java

package com.javatute.config;

import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

@Configuration
@EnableJpaRepositories(basePackages = "com.javatute.repository")
public class JpaConfig {

}

Note – See more details about @Configuration annotations here.

application.properties

# Connection url for the database
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:XE
spring.datasource.username=SYSTEM
spring.datasource.password=oracle2
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
# Show or not log for each sql query
spring.jpa.show-sql = true
spring.jpa.properties.hibernate.format_sql=true 
 
spring.jpa.hibernate.ddl-auto =create
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.Oracle10gDialect
 
server.port = 9091

#show sql values
logging.level.org.hibernate.type.descriptor.sql=trace

#hibernate.show_sql = true
#spring.jpa.hibernate.logging.level.sql =FINE
#show sql statement
#logging.level.org.hibernate.SQL=debug

Let’s run the SpringMain class(run as java application).

Perform saveall operation first using below REST API.

http://localhost:9091/student/saveall 
[
    {
        "name": "john",
        "rollNumber": "120",
        "university":"rgtu"
    },
    {
        "name": "mark",
        "rollNumber": "121",
        "university":"rgtu"
    },
    {
        "name": "aric",
        "rollNumber": "123",
        "university":"rgtu"
    },
    {
        "name": "hira",
        "rollNumber": "124",
        "university":"rgtu"
    }
]

Response Data.

[
    {
        "id": 1,
        "name": "john",
        "rollNumber": "120",
        "university": "rgtu"
    },
    {
        "id": 2,
        "name": "mark",
        "rollNumber": "121",
        "university": "rgtu"
    },
    {
        "id": 3,
        "name": "aric",
        "rollNumber": "123",
        "university": "rgtu"
    },
    {
        "id": 4,
        "name": "hira",
        "rollNumber": "124",
        "university": "rgtu"
    }
]
http://localhost:9091/student/findByName/john
http://localhost:9091/student/findByNameAndRollNumber/john/120
Spring Data JPA @NamedNativeQuery
http://localhost:9091/student/findByNameOrRollNumber/john/121

That’s all about Spring Data JPA @NamedNativeQuery and @NamedNativeQueries Example Using Spring Boot and Oracle.

Let’s see what are the fields defined inside @NamedNativeQuery.

public @interface NamedNativeQuery {
    String name();
    String query();
    QueryHint[] hints() default {};
    Class resultClass() default void.class;
    String resultSetMapping() default "";
}

Fields defined inside @NamedNativeQueries.

public @interface NamedNativeQueries {
    NamedNativeQuery[] value ();
}

You may like.

Other Spring Data JPA and Hibernate tutorials.

Spring Data JPA Docs.

Summary –  We have seen about Spring Data JPA @NamedNativeQuery and @NamedNativeQueries.

RELATED ARTICLESMORE FROM AUTHOR