Languages
[Edit]
EN

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

7 points
Created by:
AnnLen
12070

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
}
Native Advertising
­čÜÇ
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

ÔŁĄ´ŞĆ­čĺ╗ ­čÖé

Join