Native SQL queries in Spring Boot - Mini Project: Build & Apply

Native SQL Queries in Spring Boot

📖 Scenario: You are building a simple Spring Boot application to manage a list of books in a library. You want to use native SQL queries to fetch data directly from the database.

🎯 Goal: Create a Spring Boot repository interface that uses native SQL queries to retrieve books from the database.

📋 What You'll Learn

Create an entity class Book with fields id, title, and author.

Create a repository interface BookRepository that extends JpaRepository.

Add a native SQL query method to find books by author name.

Add a native SQL query method to find all books ordered by title.

💡 Why This Matters

🌍 Real World

Native SQL queries let you write exact SQL commands when you want more control or need database-specific features in Spring Boot applications.

💼 Career

Many backend developer roles require knowledge of JPA and native SQL queries to optimize database access and handle complex queries.

1

Create the Book entity

Create a class called Book annotated with @Entity. Add fields id (annotated with @Id and @GeneratedValue), title, and author all of type String except id which is Long. Include getters and setters.

Spring Boot

// Your code here to create the Book entity class with id, title, and author fields

Need a hint?

Use @Entity on the class. Use @Id and @GeneratedValue(strategy = GenerationType.IDENTITY) on the id field.

2

Create the BookRepository interface

Create an interface called BookRepository that extends JpaRepository<Book, Long>. This will allow basic CRUD operations on Book entities.

Spring Boot

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

// Your code here to create BookRepository interface extending JpaRepository

Need a hint?

Use public interface BookRepository extends JpaRepository<Book, Long>.

3

Add native SQL query to find books by author

In BookRepository, add a method List<Book> findByAuthorNative(String author) annotated with @Query using native SQL: SELECT * FROM book WHERE author = :author. Use nativeQuery = true in the annotation.

Spring Boot

import org.springframework.data.jpa.repository.Query;
import java.util.List;

// Your code here to add native query method findByAuthorNative

Need a hint?

Use @Query with nativeQuery = true and write the SQL exactly as SELECT * FROM book WHERE author = :author.

4

Add native SQL query to find all books ordered by title

In BookRepository, add a method List<Book> findAllOrderByTitleNative() annotated with @Query using native SQL: SELECT * FROM book ORDER BY title. Use nativeQuery = true in the annotation.

Spring Boot

// Your code here to add native query method findAllOrderByTitleNative

Need a hint?

Use @Query with nativeQuery = true and write the SQL exactly as SELECT * FROM book ORDER BY title.