EN
Spring / JPA - native query with params using @Query annotation
7 points
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:
xxxxxxxxxx1
(2
value= "SELECT * FROM `users` WHERE `name`=?1 AND `age`=?2",3
nativeQuery = true4
)5
UserEntity findUser(String userName, Integer userAge);Note: that approach prevents against of SQL Injection attack.
UsersRepository.java file:
xxxxxxxxxx1
package com.example.database.repository;2
3
import com.example.database.entity.UserEntity;4
import org.springframework.data.jpa.repository.JpaRepository;5
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;6
import org.springframework.data.jpa.repository.Query;7
import org.springframework.stereotype.Repository;8
9
import java.util.List;10
11
12
public interface UsersRepository13
extends JpaRepository<UserEntity, Long>, JpaSpecificationExecutor<UserEntity>14
{15
(16
value= "SELECT * FROM `users` WHERE `name`=?1 AND `age`=?2",17
nativeQuery = true18
)19
UserEntity findUser(String userName, Integer userAge);20
}UsersController.java file:
xxxxxxxxxx1
package com.example.controllers;2
3
public class UsersController4
{5
6
private UsersRepository usersRepository;7
8
// somewhere in the code we can call:9
// UserEntity user = this.usersRepository.findUser("john", 24);10
//11
// to find user with name="john" and age=2412
}