Hibernate Native SQL Query provides developers with the flexibility to execute raw SQL queries while still leveraging Hibernate’s powerful object-relational mapping capabilities. This approach becomes essential when you need to perform complex database operations, use database-specific features, or optimize performance-critical queries that HQL or Criteria API can’t handle efficiently. In this guide, you’ll learn how to implement native SQL queries in Hibernate, handle various scenarios from simple selects to complex stored procedure calls, and avoid common pitfalls that can lead to security vulnerabilities or performance issues.

How Native SQL Queries Work in Hibernate

Native SQL queries in Hibernate bypass the HQL translation layer and execute raw SQL directly against the database. This gives you complete control over the SQL statement while maintaining the ability to map results back to entity objects or handle scalar values. Hibernate provides several approaches to execute native queries through the Session interface:

• createNativeQuery() – The primary method for creating native SQL queries
• createSQLQuery() – Legacy method (deprecated in newer versions)
• @NamedNativeQuery – Annotation-based approach for predefined queries
• Stored procedure calls – Direct database procedure execution

The key advantage is that you get the raw power of SQL while Hibernate handles connection management, transaction boundaries, and result set mapping. However, you lose database portability since you’re writing database-specific SQL.

Step-by-Step Implementation Guide

Let’s start with a basic entity class and then explore different native query implementations:

@Entity
@Table(name = "users")
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 = "created_date")
    private LocalDateTime createdDate;
    
    // Constructor, getters, setters
}

Basic Native Query Example

// Simple select query returning entities
Session session = sessionFactory.getCurrentSession();
NativeQuery<User> query = session.createNativeQuery(
    "SELECT * FROM users WHERE created_date > :startDate", 
    User.class
);
query.setParameter("startDate", LocalDateTime.now().minusDays(30));
List<User> recentUsers = query.getResultList();

Scalar Result Queries

// Query returning scalar values
NativeQuery<Object[]> countQuery = session.createNativeQuery(
    "SELECT COUNT(*) as user_count, MAX(created_date) as latest_date FROM users"
);
Object[] result = countQuery.getSingleResult();
Long userCount = ((Number) result[0]).longValue();
Timestamp latestDate = (Timestamp) result[1];

Complex Query with Joins

// Complex query with custom result mapping
String sql = """
    SELECT u.id, u.username, u.email, 
           COUNT(o.id) as order_count,
           SUM(o.total_amount) as total_spent
    FROM users u 
    LEFT JOIN orders o ON u.id = o.user_id 
    WHERE u.created_date > :startDate
    GROUP BY u.id, u.username, u.email
    HAVING COUNT(o.id) > :minOrders
    """;

NativeQuery<Object[]> query = session.createNativeQuery(sql);
query.setParameter("startDate", LocalDateTime.now().minusMonths(6));
query.setParameter("minOrders", 5);

List<Object[]> results = query.getResultList();
for (Object[] row : results) {
    Long userId = ((Number) row[0]).longValue();
    String username = (String) row[1];
    String email = (String) row[2];
    Long orderCount = ((Number) row[3]).longValue();
    BigDecimal totalSpent = (BigDecimal) row[4];
    
    System.out.printf("User %s has %d orders totaling $%.2f%n", 
                     username, orderCount, totalSpent);
}

Advanced Native Query Techniques

Named Native Queries

For frequently used queries, define them as named queries on your entity:

@Entity
@Table(name = "users")
@NamedNativeQuery(
    name = "User.findActiveUsersWithStats",
    query = """
        SELECT u.*, 
               COALESCE(stats.login_count, 0) as login_count,
               COALESCE(stats.last_login, u.created_date) as last_login
        FROM users u 
        LEFT JOIN user_statistics stats ON u.id = stats.user_id 
        WHERE u.active = true 
        ORDER BY stats.last_login DESC
        """,
    resultClass = User.class
)
public class User {
    // Entity definition
}

// Usage
NativeQuery<User> query = session.createNamedQuery("User.findActiveUsersWithStats", User.class);
List<User> activeUsers = query.getResultList();

Stored Procedure Calls

// Calling a stored procedure
StoredProcedureQuery procedureQuery = session.createStoredProcedureQuery("calculate_user_metrics");
procedureQuery.registerStoredProcedureParameter("user_id", Long.class, ParameterMode.IN);
procedureQuery.registerStoredProcedureParameter("metric_result", String.class, ParameterMode.OUT);

procedureQuery.setParameter("user_id", 123L);
procedureQuery.execute();

String metricResult = (String) procedureQuery.getOutputParameterValue("metric_result");

Result Set Mapping Strategies

When dealing with complex queries that don’t map directly to entities, you have several options:

Custom Result Set Mapping

@SqlResultSetMapping(
    name = "UserSummaryMapping",
    classes = @ConstructorResult(
        targetClass = UserSummaryDTO.class,
        columns = {
            @ColumnResult(name = "id", type = Long.class),
            @ColumnResult(name = "username", type = String.class),
            @ColumnResult(name = "order_count", type = Long.class),
            @ColumnResult(name = "total_spent", type = BigDecimal.class)
        }
    )
)
@Entity
public class User {
    // Entity definition
}

// DTO class
public class UserSummaryDTO {
    private Long id;
    private String username;
    private Long orderCount;
    private BigDecimal totalSpent;
    
    public UserSummaryDTO(Long id, String username, Long orderCount, BigDecimal totalSpent) {
        this.id = id;
        this.username = username;
        this.orderCount = orderCount;
        this.totalSpent = totalSpent;
    }
    // Getters
}

// Usage
NativeQuery<UserSummaryDTO> query = session.createNativeQuery(sql, "UserSummaryMapping");
List<UserSummaryDTO> summaries = query.getResultList();

Performance Considerations and Best Practices

Aspect	Native SQL	HQL/JPQL	Criteria API
Performance	Highest (optimized SQL)	Good (automatic optimization)	Good (type-safe)
Database Portability	Low	High	High
Complexity Handling	Excellent	Limited	Moderate
Security Risk	High (if not parameterized)	Low	Very Low
Learning Curve	Low (SQL knowledge)	Moderate	High

Performance Optimization Tips

• Use pagination for large result sets – Always implement LIMIT/OFFSET or database-specific pagination
• Leverage database indexes – Ensure your WHERE clauses use indexed columns
• Batch operations – Use batch processing for bulk operations
• Connection pooling – Configure appropriate connection pool settings
• Query plan analysis – Use EXPLAIN PLAN to analyze query performance

// Pagination example
NativeQuery<User> query = session.createNativeQuery(
    "SELECT * FROM users WHERE active = true ORDER BY created_date DESC", 
    User.class
);
query.setFirstResult(pageNumber * pageSize);
query.setMaxResults(pageSize);
List<User> pagedUsers = query.getResultList();

Common Pitfalls and Troubleshooting

SQL Injection Prevention

The biggest security risk with native queries is SQL injection. Always use parameterized queries:

// WRONG - Vulnerable to SQL injection
String dangerousQuery = "SELECT * FROM users WHERE username = '" + userInput + "'";
NativeQuery query = session.createNativeQuery(dangerousQuery);

// CORRECT - Safe parameterized query
String safeQuery = "SELECT * FROM users WHERE username = :username";
NativeQuery<User> query = session.createNativeQuery(safeQuery, User.class);
query.setParameter("username", userInput);

Column Mapping Issues

When column names don’t match entity properties, use aliases:

// Map database columns to entity properties using aliases
String query = """
    SELECT user_id as id, 
           user_name as username, 
           email_address as email,
           creation_timestamp as createdDate
    FROM legacy_users 
    WHERE active_flag = 1
    """;
    
NativeQuery<User> nativeQuery = session.createNativeQuery(query, User.class);

Transaction Management

// Proper transaction handling for native queries
Transaction transaction = null;
try {
    transaction = session.beginTransaction();
    
    // Bulk update using native SQL
    int updatedRows = session.createNativeQuery(
        "UPDATE users SET last_login = :loginTime WHERE id IN (:userIds)"
    )
    .setParameter("loginTime", LocalDateTime.now())
    .setParameter("userIds", Arrays.asList(1L, 2L, 3L))
    .executeUpdate();
    
    transaction.commit();
    System.out.println("Updated " + updatedRows + " users");
    
} catch (Exception e) {
    if (transaction != null) {
        transaction.rollback();
    }
    throw e;
}

Real-World Use Cases

Reporting and Analytics

// Complex analytics query that would be difficult in HQL
String analyticsQuery = """
    WITH monthly_stats AS (
        SELECT 
            DATE_TRUNC('month', created_date) as month,
            COUNT(*) as new_users,
            COUNT(*) FILTER (WHERE email LIKE '%@gmail.com') as gmail_users
        FROM users 
        WHERE created_date >= :startDate
        GROUP BY DATE_TRUNC('month', created_date)
    ),
    retention_stats AS (
        SELECT 
            DATE_TRUNC('month', u.created_date) as month,
            COUNT(DISTINCT CASE WHEN l.login_date > u.created_date + INTERVAL '30 days' 
                              THEN u.id END) as retained_users
        FROM users u
        LEFT JOIN user_logins l ON u.id = l.user_id
        WHERE u.created_date >= :startDate
        GROUP BY DATE_TRUNC('month', u.created_date)
    )
    SELECT 
        ms.month,
        ms.new_users,
        ms.gmail_users,
        COALESCE(rs.retained_users, 0) as retained_users,
        ROUND(COALESCE(rs.retained_users, 0) * 100.0 / ms.new_users, 2) as retention_rate
    FROM monthly_stats ms
    LEFT JOIN retention_stats rs ON ms.month = rs.month
    ORDER BY ms.month
    """;

NativeQuery<Object[]> query = session.createNativeQuery(analyticsQuery);
query.setParameter("startDate", LocalDateTime.now().minusYears(1));

Database-Specific Features

// PostgreSQL-specific features like JSONB queries
String jsonQuery = """
    SELECT u.*, 
           preferences->>'timezone' as user_timezone,
           preferences->'notifications'->>'email' as email_notifications
    FROM users u 
    WHERE preferences @> '{"premium": true}'
    AND preferences->'settings'->>'theme' = '"dark"'
    """;

// MySQL full-text search
String fullTextQuery = """
    SELECT *, MATCH(title, content) AGAINST (:searchTerm IN NATURAL LANGUAGE MODE) as relevance
    FROM articles 
    WHERE MATCH(title, content) AGAINST (:searchTerm IN NATURAL LANGUAGE MODE)
    ORDER BY relevance DESC
    """;

Integration with Spring Data JPA

If you’re using Spring Data JPA, you can combine native queries with repository patterns:

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    
    @Query(value = """
        SELECT u.* FROM users u 
        JOIN user_roles ur ON u.id = ur.user_id 
        JOIN roles r ON ur.role_id = r.id 
        WHERE r.name = :roleName 
        AND u.active = true
        ORDER BY u.created_date DESC
        """, nativeQuery = true)
    List<User> findActiveUsersByRole(@Param("roleName") String roleName);
    
    @Modifying
    @Query(value = "UPDATE users SET last_login = CURRENT_TIMESTAMP WHERE id = :userId", 
           nativeQuery = true)
    int updateLastLogin(@Param("userId") Long userId);
}

Native SQL queries in Hibernate provide the perfect balance between raw SQL power and ORM convenience. While they sacrifice some portability, they’re essential for complex operations, performance optimization, and leveraging database-specific features. Remember to always parameterize your queries, handle transactions properly, and consider the trade-offs between flexibility and maintainability when choosing between native SQL and HQL approaches.

For more detailed information, check out the official Hibernate User Guide on Native SQL and the Jakarta Persistence API documentation.

---

---

This article incorporates information and material from various online sources. We acknowledge and appreciate the work of all original authors, publishers, and websites. While every effort has been made to appropriately credit the source material, any unintentional oversight or omission does not constitute a copyright infringement. All trademarks, logos, and images mentioned are the property of their respective owners. If you believe that any content used in this article infringes upon your copyright, please contact us immediately for review and prompt action.

This article is intended for informational and educational purposes only and does not infringe on the rights of the copyright owners. If any copyrighted material has been used without proper credit or in violation of copyright laws, it is unintentional and we will rectify it promptly upon notification. Please note that the republishing, redistribution, or reproduction of part or all of the contents in any form is prohibited without express written permission from the author and website owner. For permissions or further inquiries, please contact us.