Introduction
Native SQL queries in Spring Data JPA allow developers to execute database-specific SQL statements while still leveraging Spring Data's convenience features. This approach is useful for complex queries, database-specific functions, performance optimization, and scenarios where JPQL/HQL is insufficient.
Basic Native Query Setup
Entity Configuration
@Entity
@Table(name = "users")
@NamedNativeQuery(
name = "User.findActiveUsers",
query = "SELECT * FROM users WHERE active = true",
resultClass = User.class
)
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "username")
private String username;
@Column(name = "email")
private String email;
@Column(name = "active")
private Boolean active;
@Column(name = "created_date")
private LocalDateTime createdDate;
// Constructors, getters, setters
public User() {}
public User(String username, String email, Boolean active) {
this.username = username;
this.email = email;
this.active = active;
this.createdDate = LocalDateTime.now();
}
// Getters and setters...
}
@Entity
@Table(name = "orders")
@SqlResultSetMapping(
name = "OrderSummaryMapping",
classes = @ConstructorResult(
targetClass = OrderSummary.class,
columns = {
@ColumnResult(name = "order_id", type = Long.class),
@ColumnResult(name = "customer_name", type = String.class),
@ColumnResult(name = "total_amount", type = Double.class),
@ColumnResult(name = "order_count", type = Integer.class)
}
)
)
public class Order {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "customer_name")
private String customerName;
@Column(name = "amount")
private Double amount;
@Column(name = "order_date")
private LocalDate orderDate;
@ManyToOne
@JoinColumn(name = "user_id")
private User user;
// Constructors, getters, setters...
}
// DTO for custom result mapping
public class OrderSummary {
private Long orderId;
private String customerName;
private Double totalAmount;
private Integer orderCount;
public OrderSummary(Long orderId, String customerName, Double totalAmount, Integer orderCount) {
this.orderId = orderId;
this.customerName = customerName;
this.totalAmount = totalAmount;
this.orderCount = orderCount;
}
// Getters and setters...
}
Repository Methods with Native Queries
Basic Native Query Repository
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
// Basic native query with result class
@Query(value = "SELECT * FROM users WHERE active = true", nativeQuery = true)
List<User> findActiveUsers();
// Native query with parameters
@Query(value = "SELECT * FROM users WHERE email = :email", nativeQuery = true)
Optional<User> findByEmail(@Param("email") String email);
// Native query with multiple parameters
@Query(value = "SELECT * FROM users WHERE username LIKE %:username% AND active = :active",
nativeQuery = true)
List<User> findByUsernameContainingAndActive(@Param("username") String username,
@Param("active") boolean active);
// Native query with LIMIT
@Query(value = "SELECT * FROM users ORDER BY created_date DESC LIMIT :limit",
nativeQuery = true)
List<User> findRecentUsers(@Param("limit") int limit);
// Native query for count
@Query(value = "SELECT COUNT(*) FROM users WHERE active = :active", nativeQuery = true)
long countByActiveStatus(@Param("active") boolean active);
// Native query with LIKE and wildcards
@Query(value = "SELECT * FROM users WHERE email LIKE CONCAT('%', :domain)", nativeQuery = true)
List<User> findByEmailDomain(@Param("domain") String domain);
// Using named native query
@Query(nativeQuery = true)
List<User> findActiveUsers();
}
Advanced Native Query Repository
@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
// Complex join with native query
@Query(value = """
SELECT o.id as order_id, o.customer_name, o.amount as total_amount,
COUNT(oi.id) as order_count
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.order_date BETWEEN :startDate AND :endDate
GROUP BY o.id, o.customer_name, o.amount
HAVING COUNT(oi.id) > :minItems
""", nativeQuery = true)
List<Object[]> findOrderSummaries(@Param("startDate") LocalDate startDate,
@Param("endDate") LocalDate endDate,
@Param("minItems") int minItems);
// Native query with pagination
@Query(value = "SELECT * FROM orders WHERE customer_name LIKE %:customerName% ORDER BY order_date DESC",
countQuery = "SELECT COUNT(*) FROM orders WHERE customer_name LIKE %:customerName%",
nativeQuery = true)
Page<Order> findByCustomerNameContaining(@Param("customerName") String customerName,
Pageable pageable);
// Native query for update/delete
@Modifying
@Query(value = "UPDATE orders SET amount = amount * 1.1 WHERE order_date < :cutoffDate",
nativeQuery = true)
@Transactional
int applyDiscountToOldOrders(@Param("cutoffDate") LocalDate cutoffDate);
// Native query with database-specific functions (PostgreSQL example)
@Query(value = "SELECT * FROM orders WHERE DATE_PART('month', order_date) = :month",
nativeQuery = true)
List<Order> findByOrderMonth(@Param("month") int month);
// MySQL specific function example
@Query(value = "SELECT * FROM orders WHERE MONTH(order_date) = :month", nativeQuery = true)
List<Order> findByOrderMonthMySQL(@Param("month") int month);
// Using stored procedure
@Query(value = "CALL get_orders_by_amount_range(:minAmount, :maxAmount)", nativeQuery = true)
List<Order> findOrdersByAmountRange(@Param("minAmount") Double minAmount,
@Param("maxAmount") Double maxAmount);
}
Custom Repository Implementation
Custom Repository Interface
public interface CustomOrderRepository {
List<OrderSummary> findOrderSummariesNative(LocalDate startDate, LocalDate endDate);
List<Map<String, Object>> findSalesStatistics();
int bulkUpdateOrderStatus(String oldStatus, String newStatus);
List<UserOrderStats> getUserOrderStatistics();
}
// Custom DTO for statistics
public class UserOrderStats {
private String username;
private Long totalOrders;
private Double totalAmount;
private LocalDate lastOrderDate;
public UserOrderStats(String username, Long totalOrders, Double totalAmount, LocalDate lastOrderDate) {
this.username = username;
this.totalOrders = totalOrders;
this.totalAmount = totalAmount;
this.lastOrderDate = lastOrderDate;
}
// Getters and setters...
}
Custom Repository Implementation
@Repository
public class CustomOrderRepositoryImpl implements CustomOrderRepository {
@PersistenceContext
private EntityManager entityManager;
@Override
public List<OrderSummary> findOrderSummariesNative(LocalDate startDate, LocalDate endDate) {
String sql = """
SELECT o.id as order_id, u.username as customer_name,
SUM(o.amount) as total_amount, COUNT(o.id) as order_count
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.order_date BETWEEN :startDate AND :endDate
GROUP BY o.id, u.username
HAVING SUM(o.amount) > 100
ORDER BY total_amount DESC
""";
Query query = entityManager.createNativeQuery(sql, "OrderSummaryMapping")
.setParameter("startDate", startDate)
.setParameter("endDate", endDate);
return query.getResultList();
}
@Override
public List<Map<String, Object>> findSalesStatistics() {
String sql = """
SELECT
u.username,
COUNT(o.id) as order_count,
SUM(o.amount) as total_revenue,
AVG(o.amount) as avg_order_value,
MAX(o.order_date) as last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.active = true
GROUP BY u.id, u.username
HAVING COUNT(o.id) > 0
ORDER BY total_revenue DESC
""";
Query query = entityManager.createNativeQuery(sql);
List<Object[]> results = query.getResultList();
return results.stream()
.map(this::mapToSalesStats)
.collect(Collectors.toList());
}
private Map<String, Object> mapToSalesStats(Object[] result) {
Map<String, Object> stats = new HashMap<>();
stats.put("username", result[0]);
stats.put("orderCount", ((Number) result[1]).longValue());
stats.put("totalRevenue", ((Number) result[2]).doubleValue());
stats.put("avgOrderValue", ((Number) result[3]).doubleValue());
stats.put("lastOrderDate", result[4]);
return stats;
}
@Override
@Transactional
public int bulkUpdateOrderStatus(String oldStatus, String newStatus) {
String sql = "UPDATE orders SET status = :newStatus WHERE status = :oldStatus";
return entityManager.createNativeQuery(sql)
.setParameter("newStatus", newStatus)
.setParameter("oldStatus", oldStatus)
.executeUpdate();
}
@Override
public List<UserOrderStats> getUserOrderStatistics() {
String sql = """
SELECT
u.username,
COUNT(o.id) as total_orders,
COALESCE(SUM(o.amount), 0) as total_amount,
MAX(o.order_date) as last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.active = true
GROUP BY u.id, u.username
ORDER BY total_amount DESC
""";
Query query = entityManager.createNativeQuery(sql);
List<Object[]> results = query.getResultList();
return results.stream()
.map(this::mapToUserOrderStats)
.collect(Collectors.toList());
}
private UserOrderStats mapToUserOrderStats(Object[] result) {
return new UserOrderStats(
(String) result[0],
((Number) result[1]).longValue(),
((Number) result[2]).doubleValue(),
result[3] != null ? ((Date) result[3]).toLocalDate() : null
);
}
}
Result Set Mapping
Advanced Result Set Mapping
@Entity
@SqlResultSetMappings({
@SqlResultSetMapping(
name = "UserOrderStatsMapping",
classes = @ConstructorResult(
targetClass = UserOrderStats.class,
columns = {
@ColumnResult(name = "username", type = String.class),
@ColumnResult(name = "total_orders", type = Long.class),
@ColumnResult(name = "total_amount", type = Double.class),
@ColumnResult(name = "last_order_date", type = Date.class)
}
)
),
@SqlResultSetMapping(
name = "SalesReportMapping",
classes = @ConstructorResult(
targetClass = SalesReport.class,
columns = {
@ColumnResult(name = "period", type = String.class),
@ColumnResult(name = "total_sales", type = Double.class),
@ColumnResult(name = "order_count", type = Integer.class),
@ColumnResult(name = "average_order_value", type = Double.class)
}
)
)
})
public class User {
// entity definition
}
// Custom DTO for sales report
public class SalesReport {
private String period;
private Double totalSales;
private Integer orderCount;
private Double averageOrderValue;
public SalesReport(String period, Double totalSales, Integer orderCount, Double averageOrderValue) {
this.period = period;
this.totalSales = totalSales;
this.orderCount = orderCount;
this.averageOrderValue = averageOrderValue;
}
// Getters and setters...
}
Dynamic Result Set Mapping
@Component
public class NativeQueryExecutor {
@PersistenceContext
private EntityManager entityManager;
public <T> List<T> executeNativeQuery(String sql,
Map<String, Object> parameters,
Class<T> resultClass) {
Query query = entityManager.createNativeQuery(sql, resultClass);
parameters.forEach(query::setParameter);
return query.getResultList();
}
public List<Map<String, Object>> executeNativeQuery(String sql,
Map<String, Object> parameters) {
Query query = entityManager.createNativeQuery(sql);
parameters.forEach(query::setParameter);
@SuppressWarnings("unchecked")
List<Object[]> results = query.getResultList();
// Get column names from metadata
List<String> columnNames = getColumnNames(query);
return results.stream()
.map(row -> mapToRowMap(row, columnNames))
.collect(Collectors.toList());
}
@SuppressWarnings("unchecked")
private List<String> getColumnNames(Query query) {
try {
NativeQuery<?> nativeQuery = query.unwrap(NativeQuery.class);
return nativeQuery.getQueryReturns().stream()
.map(queryReturn -> queryReturn.getResultAlias())
.collect(Collectors.toList());
} catch (Exception e) {
// Fallback for when metadata is not available
return Collections.emptyList();
}
}
private Map<String, Object> mapToRowMap(Object[] row, List<String> columnNames) {
Map<String, Object> rowMap = new LinkedHashMap<>();
for (int i = 0; i < row.length; i++) {
String columnName = i < columnNames.size() ? columnNames.get(i) : "column_" + i;
rowMap.put(columnName, row[i]);
}
return rowMap;
}
@Transactional
public int executeUpdate(String sql, Map<String, Object> parameters) {
Query query = entityManager.createNativeQuery(sql);
parameters.forEach(query::setParameter);
return query.executeUpdate();
}
}
Spring Data JPA with Native Queries
Complete Repository Example
@Repository
public interface AdvancedUserRepository extends JpaRepository<User, Long>, CustomOrderRepository {
// Projection with native query
public interface UserProjection {
String getUsername();
String getEmail();
Long getOrderCount();
}
@Query(value = """
SELECT u.username, u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email
HAVING COUNT(o.id) > :minOrders
""", nativeQuery = true)
List<UserProjection> findUsersWithOrderCount(@Param("minOrders") Long minOrders);
// Native query with CASE statements
@Query(value = """
SELECT
u.username,
u.email,
CASE
WHEN COUNT(o.id) = 0 THEN 'NO_ORDERS'
WHEN COUNT(o.id) BETWEEN 1 AND 5 THEN 'FEW_ORDERS'
ELSE 'MANY_ORDERS'
END as order_category
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email
""", nativeQuery = true)
List<Object[]> findUsersWithOrderCategory();
// Window functions (PostgreSQL example)
@Query(value = """
SELECT
username,
email,
amount,
RANK() OVER (PARTITION BY u.id ORDER BY o.amount DESC) as rank
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
""", nativeQuery = true)
List<Object[]> findRecentOrdersWithRank();
}
Service Layer Implementation
Service with Native Queries
@Service
@Transactional(readOnly = true)
public class UserService {
private final UserRepository userRepository;
private final OrderRepository orderRepository;
private final CustomOrderRepository customOrderRepository;
private final NativeQueryExecutor nativeQueryExecutor;
public UserService(UserRepository userRepository,
OrderRepository orderRepository,
CustomOrderRepository customOrderRepository,
NativeQueryExecutor nativeQueryExecutor) {
this.userRepository = userRepository;
this.orderRepository = orderRepository;
this.customOrderRepository = customOrderRepository;
this.nativeQueryExecutor = nativeQueryExecutor;
}
public List<User> findActiveUsersByEmailDomain(String domain) {
return userRepository.findByEmailDomain(domain);
}
public Page<Order> searchOrdersByCustomer(String customerName, Pageable pageable) {
return orderRepository.findByCustomerNameContaining(customerName, pageable);
}
public List<OrderSummary> getOrderSummaries(LocalDate startDate, LocalDate endDate) {
return customOrderRepository.findOrderSummariesNative(startDate, endDate);
}
public List<Map<String, Object>> getSalesStatistics() {
return customOrderRepository.findSalesStatistics();
}
@Transactional
public int applyBulkDiscount(LocalDate cutoffDate) {
return orderRepository.applyDiscountToOldOrders(cutoffDate);
}
public List<Map<String, Object>> executeCustomReport(String sql, Map<String, Object> params) {
return nativeQueryExecutor.executeNativeQuery(sql, params);
}
public List<User> findTopSpenders(int limit) {
String sql = """
SELECT u.*
FROM users u
JOIN (
SELECT user_id, SUM(amount) as total_spent
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY user_id
ORDER BY total_spent DESC
LIMIT :limit
) top_users ON u.id = top_users.user_id
WHERE u.active = true
""";
Map<String, Object> params = Map.of("limit", limit);
return nativeQueryExecutor.executeNativeQuery(sql, params, User.class);
}
public Map<String, Object> getUserDashboardStats(Long userId) {
String sql = """
SELECT
u.username,
COUNT(o.id) as total_orders,
COALESCE(SUM(o.amount), 0) as total_spent,
AVG(o.amount) as avg_order_value,
MAX(o.order_date) as last_order_date,
(SELECT COUNT(*) FROM orders o2
WHERE o2.user_id = u.id
AND o2.order_date >= CURRENT_DATE - INTERVAL '30 days') as recent_orders
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id = :userId
GROUP BY u.id, u.username
""";
Map<String, Object> params = Map.of("userId", userId);
List<Map<String, Object>> results = nativeQueryExecutor.executeNativeQuery(sql, params);
return results.isEmpty() ? Collections.emptyMap() : results.get(0);
}
}
Configuration and Best Practices
Configuration Class
@Configuration
@EnableJpaRepositories(
basePackages = "com.example.repository",
repositoryBaseClass = CustomJpaRepositoryImpl.class
)
public class JpaConfig {
@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource) {
LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
em.setDataSource(dataSource);
em.setPackagesToScan("com.example.entity");
JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
em.setJpaVendorAdapter(vendorAdapter);
Properties properties = new Properties();
properties.setProperty("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
properties.setProperty("hibernate.show_sql", "true");
properties.setProperty("hibernate.format_sql", "true");
em.setJpaProperties(properties);
return em;
}
@Bean
public PlatformTransactionManager transactionManager(EntityManagerFactory emf) {
JpaTransactionManager transactionManager = new JpaTransactionManager();
transactionManager.setEntityManagerFactory(emf);
return transactionManager;
}
}
Custom Repository Base Class
public class CustomJpaRepositoryImpl<T, ID> extends SimpleJpaRepository<T, ID> {
private final EntityManager entityManager;
public CustomJpaRepositoryImpl(JpaEntityInformation<T, ?> entityInformation,
EntityManager entityManager) {
super(entityInformation, entityManager);
this.entityManager = entityManager;
}
@SuppressWarnings("unchecked")
public List<T> findByNativeQuery(String sql, Map<String, Object> params) {
Query query = entityManager.createNativeQuery(sql, getDomainClass());
params.forEach(query::setParameter);
return query.getResultList();
}
public List<Object[]> executeNativeQuery(String sql, Map<String, Object> params) {
Query query = entityManager.createNativeQuery(sql);
params.forEach(query::setParameter);
return query.getResultList();
}
@Transactional
public int executeNativeUpdate(String sql, Map<String, Object> params) {
Query query = entityManager.createNativeQuery(sql);
params.forEach(query::setParameter);
return query.executeUpdate();
}
}
Testing Native Queries
Test Configuration
@DataJpaTest
@TestPropertySource(properties = {
"spring.datasource.url=jdbc:h2:mem:testdb",
"spring.jpa.hibernate.ddl-auto=create-drop"
})
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
class NativeQueryRepositoryTest {
@Autowired
private TestEntityManager entityManager;
@Autowired
private UserRepository userRepository;
@Autowired
private OrderRepository orderRepository;
@BeforeEach
void setUp() {
// Create test data
User user1 = new User("john_doe", "[email protected]", true);
User user2 = new User("jane_doe", "[email protected]", true);
User user3 = new User("inactive_user", "[email protected]", false);
entityManager.persist(user1);
entityManager.persist(user2);
entityManager.persist(user3);
Order order1 = new Order();
order1.setCustomerName("John Customer");
order1.setAmount(100.0);
order1.setOrderDate(LocalDate.now().minusDays(1));
order1.setUser(user1);
Order order2 = new Order();
order2.setCustomerName("Jane Customer");
order2.setAmount(200.0);
order2.setOrderDate(LocalDate.now());
order2.setUser(user2);
entityManager.persist(order1);
entityManager.persist(order2);
entityManager.flush();
}
@Test
void testFindActiveUsers() {
List<User> activeUsers = userRepository.findActiveUsers();
assertThat(activeUsers).hasSize(2);
assertThat(activeUsers).extracting(User::getUsername)
.containsExactlyInAnyOrder("john_doe", "jane_doe");
}
@Test
void testFindByEmail() {
Optional<User> user = userRepository.findByEmail("[email protected]");
assertThat(user).isPresent();
assertThat(user.get().getUsername()).isEqualTo("john_doe");
}
@Test
void testNativeQueryWithPagination() {
Pageable pageable = PageRequest.of(0, 10, Sort.by("orderDate").descending());
Page<Order> orders = orderRepository.findByCustomerNameContaining("Customer", pageable);
assertThat(orders.getContent()).hasSize(2);
assertThat(orders.getTotalElements()).isEqualTo(2);
}
@Test
@Transactional
void testBulkUpdate() {
int updatedCount = orderRepository.applyDiscountToOldOrders(LocalDate.now());
assertThat(updatedCount).isEqualTo(1); // Only one order is older than today
}
}
Performance Considerations
Optimization Tips
@Component
public class QueryOptimizationService {
@PersistenceContext
private EntityManager entityManager;
// Use indexes effectively
public List<User> findUsersWithIndexHint() {
String sql = "SELECT /*+ INDEX(users idx_user_active) */ * FROM users WHERE active = true";
return entityManager.createNativeQuery(sql, User.class).getResultList();
}
// Batch processing for large datasets
public void processUsersInBatches(int batchSize) {
String countSql = "SELECT COUNT(*) FROM users WHERE active = true";
Long totalCount = ((Number) entityManager.createNativeQuery(countSql)
.getSingleResult()).longValue();
int offset = 0;
while (offset < totalCount) {
String batchSql = "SELECT * FROM users WHERE active = true LIMIT :limit OFFSET :offset";
List<User> batch = entityManager.createNativeQuery(batchSql, User.class)
.setParameter("limit", batchSize)
.setParameter("offset", offset)
.getResultList();
processBatch(batch);
offset += batchSize;
}
}
// Use EXPLAIN to analyze query performance
public String explainQuery(String sql) {
String explainSql = "EXPLAIN ANALYZE " + sql;
Query query = entityManager.createNativeQuery(explainSql);
@SuppressWarnings("unchecked")
List<Object[]> results = query.getResultList();
return results.stream()
.map(row -> Arrays.toString(row))
.collect(Collectors.joining("\n"));
}
private void processBatch(List<User> batch) {
// Process batch of users
batch.forEach(user -> {
// Business logic here
});
}
}
Best Practices
Security and Maintenance
@Component
public class NativeQuerySecurity {
@PersistenceContext
private EntityManager entityManager;
// Safe parameter binding (prevents SQL injection)
public List<User> safeFindUsersByUsername(String username) {
String sql = "SELECT * FROM users WHERE username = :username";
return entityManager.createNativeQuery(sql, User.class)
.setParameter("username", username)
.getResultList();
}
// UNSAFE - vulnerable to SQL injection
public List<User> unsafeFindUsersByUsername(String username) {
String sql = "SELECT * FROM users WHERE username = '" + username + "'";
return entityManager.createNativeQuery(sql, User.class)
.getResultList();
}
// Validate and sanitize SQL
public boolean isValidSelectQuery(String sql) {
// Basic validation - only allow SELECT queries in read-only contexts
return sql.trim().toUpperCase().startsWith("SELECT");
}
// Use query timeout
public List<User> findUsersWithTimeout() {
String sql = "SELECT * FROM users WHERE active = true";
Query query = entityManager.createNativeQuery(sql, User.class);
// Set timeout to 30 seconds
query.setHint("javax.persistence.query.timeout", 30000);
return query.getResultList();
}
}
Conclusion
Native SQL queries in Spring Data JPA provide powerful capabilities for:
- Complex queries that are difficult or impossible with JPQL
- Database-specific features and functions
- Performance optimization through direct SQL control
- Stored procedure execution
- Advanced reporting and analytics
Key considerations when using native queries:
- Always use parameter binding to prevent SQL injection
- Consider using
@SqlResultSetMappingfor complex result mappings - Use pagination for large result sets
- Be mindful of database portability when using database-specific features
- Test thoroughly as native queries bypass some JPA abstractions
- Monitor performance and use query optimization techniques
By following these patterns and best practices, you can effectively leverage native SQL queries while maintaining the benefits of Spring Data JPA's programming model.