Spring / JPA - native query with params using @Query annotation

In this short article, we would like to show how to use native SQL queries with parameters using Spring Data JPA.

To use native queries it is necessary to add @Query annotation with nativeQuery=true inside repository class.

Each parameter can be passed from associated function arguments by calling ?index:
?1, ?2, ?3, etc.

Quick solution:

@Query(
    value= "SELECT * FROM `users` WHERE `name`=?1 AND `age`=?2",
    nativeQuery = true
)
UserEntity findUser(String userName, Integer userAge);

Note: that approach prevents against of SQL Injection attack.

Practical example

UsersRepository.java file:

package com.example.database.repository;

import com.example.database.entity.UserEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface UsersRepository
        extends JpaRepository<UserEntity, Long>, JpaSpecificationExecutor<UserEntity>
{
    @Query(
        value= "SELECT * FROM `users` WHERE `name`=?1 AND `age`=?2",
        nativeQuery = true
    )
    UserEntity findUser(String userName, Integer userAge);
}

UsersController.java file:

package com.example.controllers;

public class UsersController
{
    @Autowired
    private UsersRepository usersRepository;

    // somewhere in the code we can call:
    //   UserEntity user = this.usersRepository.findUser("john", 24);
    //
    // to find user with name="john" and age=24
}