google-site-verification: google61fe8ba583a51912.html
MACRO NEPAL
Native SQL Queries in Spring Data in Java
awiskar acharya
November 5, 2025
2:19 pm

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 @SqlResultSetMapping for 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.

Category : JAVA

Leave a Reply

Your email address will not be published. Required fields are marked *


Macro Nepal Helper