Is it possible to use raw SQL within a Spring Repository?

java

spring-data

sql-queries

repository-patterns

byAnton Shumikhin·Feb 24, 2025

Yes, it's indeed possible. The key tool here is the @Query annotation with nativeQuery=true. Here's a straightforward example:

public interface MyRepository extends JpaRepository<MyEntity, Long> {
  
  // "Hey SQL, do your magic 🪄 !"
  @Query(value = "SELECT * FROM my_table WHERE my_column = :value", nativeQuery = true)
  List<MyEntity> findByColumn(@Param("value") String value);
  
}

This demonstrates a simple, yet powerful, way to execute a native SQL query within @Query itself.

The Right Tool for the Job: Scenarios for raw SQL

Spring Data repositories are highly efficient for standard operations. But sometimes, you need more — enter raw SQL.

Achieve Views: Projections

When you need specific views on entity models, projections come into play:

public interface MyEntityProjection {
  // "Hey, I only care about my column! 😏"
  String getMyColumn();
}

Utilize this in your repository:

public interface MyRepository extends JpaRepository<MyEntity, Long> {
  
  // "SQL, fetch me the myColumn! 🤠"
  @Query(value = "SELECT my_column as myColumn FROM my_table", nativeQuery = true)
  List<MyEntityProjection> findProjectedBy();
}

Direct Control with EntityManager

Crave for more control? You can employ EntityManager:

@Autowired
private EntityManager entityManager;

// "Breaking boundaries with EntityManager 🚜!"
public List<MyEntity> customQueryMethod() {
  // "SQL unleashed!!! 💥"
  return entityManager.createNativeQuery("SELECT * FROM my_table", MyEntity.class).getResultList();
}

Custom Results: Array mapping and Response Class

Unleash the maps! Map store results:

// "And here... We... Go...😈"
List<Object[]> results = query.getResultList();
for(Object[] result : results){
  // process each result
}

Or, create a Response class:

public class CustomResponse {
  private String columnValue; // could vary
   
 // getters and setters, here!
}

// "SQL, meet Response Class😎."
List<CustomResponse> customResponses = entityManager
  .createNativeQuery("SELECT my_column FROM my_table", CustomResponse.class)
  .getResultList();

Safety First: Security and Best Practices

Security can't be compromised. Use @Param for safely injecting parameters into your queries:

@Query(value = "SELECT * FROM my_table WHERE my_column = :value", nativeQuery = true)
List<MyEntity> findByColumn(@Param("value") String value);

Validate User Input

Guard your queries against malicious attacks:

// "Try me! Just ensure you catch me 😇"
try {
  // Execute your query
} catch (PersistenceException e) {
  // Handle exceptions
}

Performance Checks

Performance matters. Ensure your queries are efficient:

-- "Hey SQL, fetch me the first 10 records! 🐎"
SELECT * FROM my_table WHERE my_column = :value LIMIT 10

Exposing through API Endpoints

Expose these SQL queries via your API for flexible usage:

@RestController
public class MyController {

  @Autowired
  private MyRepository myRepository;

  // "Hey Spring, let's go public! 🚀"
  @GetMapping("/search")
  public List<MyEntity> search(@RequestParam String value) {
    return myRepository.findByColumn(value);
  }
}