Overview

JPQL (Java Persistence Query Language) is an object-oriented query language that works with entities and their fields, not tables and columns. Understanding the differences between JPQL and SQL is critical for efficient JPA usage.

---

Junior Level

What is JPQL

JPQL - a query language that works with entities and fields, not tables and columns.

// JPQL - works with entity classes
List<User> users = entityManager.createQuery(
    "SELECT u FROM User u WHERE u.age > :age", User.class)
    .setParameter("age", 18)
    .getResultList();

// SQL - works with tables
// SELECT * FROM users WHERE age > 18;

Main Differences

JPQL	SQL
User (entity class)	users (table name)
u.age (field name)	age (column name)
u.userProfile.city (navigation)	JOIN user_profiles ON ...
Database-agnostic (abstracts query structure, but not all dialect differences - e.g., string and date functions may differ)	Database-specific
Automatically generates SQL	Direct SQL

JPQL Examples

// Simple SELECT
SELECT u FROM User u WHERE u.email = :email

// JOIN
SELECT o FROM Order o JOIN o.user u WHERE u.name = :name

// Aggregation
SELECT u, COUNT(o) FROM User u LEFT JOIN u.orders o GROUP BY u

// UPDATE
UPDATE User u SET u.status = :status WHERE u.id = :id

// DELETE
DELETE FROM Order o WHERE o.status = 'cancelled'

Parameterization

// Named parameters (recommended)
@Query("SELECT u FROM User u WHERE u.name = :name AND u.age > :age")
List<User> find(@Param("name") String name, @Param("age") int age);

// String concatenation (SQL injection!)
"SELECT u FROM User u WHERE u.name = '" + name + "'"  // SQL injection risk!

---

Middle Level

JPQL vs SQL - Detailed Comparison

JPQL:
- Works with entities and fields
- Database-agnostic (works with any DB)
- Automatically generates SQL
- Supports polymorphic queries
- Works with inheritance

SQL:
- Works with tables and columns
- Database-specific (syntax depends on DB)
- Full query control
- No automatic mapping
- No polymorphism

JPQL Examples

// SELECT with constructor (DTO projection)
@Query("SELECT new com.example.UserDto(u.name, u.email) FROM User u")
List<UserDto> findUserDtos();

// JOIN FETCH
@Query("SELECT DISTINCT o FROM Order o JOIN FETCH o.items WHERE o.id = :id")
Order findByIdWithItems(@Param("id") Long id);

// Subquery
@Query("SELECT u FROM User u WHERE u.id IN (SELECT o.user.id FROM Order o WHERE o.total > :amount)")
List<User> findUsersWithOrdersOver(@Param("amount") BigDecimal amount);

// GROUP BY with HAVING
@Query("SELECT u, COUNT(o) FROM User u JOIN u.orders o GROUP BY u HAVING COUNT(o) > :minOrders")
List<Object[]> findActiveUsers(@Param("minOrders") int minOrders);

// CASE WHEN
@Query("SELECT u, CASE WHEN COUNT(o) > 0 THEN 'active' ELSE 'inactive' END FROM User u LEFT JOIN u.orders o GROUP BY u")
List<Object[]> findUserStatus();

Native SQL - When Needed

// Database-specific functions
List<User> users = entityManager.createNativeQuery(
    "SELECT * FROM users WHERE created_at > NOW() - INTERVAL '30 days'",  // PostgreSQL-specific! In MySQL: NOW() - INTERVAL 30 DAY
    User.class
).getResultList();

// CTE (Common Table Expressions)
List<User> users = entityManager.createNativeQuery("""
    WITH recent_orders AS (
        SELECT user_id, MAX(created_at) as last_order
        FROM orders GROUP BY user_id
    )
    SELECT u.* FROM users u JOIN recent_orders ro ON u.id = ro.user_id
    WHERE ro.last_order > :date
    """, User.class).setParameter("date", date).getResultList();

Common Mistakes

// Table name instead of entity
entityManager.createQuery("SELECT u FROM users u", User.class);  // wrong
// Entity name
entityManager.createQuery("SELECT u FROM User u", User.class);  // correct

// Column name instead of field
"SELECT u FROM User u WHERE u.email_address = :email"  // wrong
// Field name
"SELECT u FROM User u WHERE u.email = :email"  // correct

// Concat for parameters (SQL injection!)
"SELECT u FROM User u WHERE u.name = '" + name + "'"  // wrong
// Parameters
"SELECT u FROM User u WHERE u.name = :name"  // correct

---

Senior Level

JPQL and Inheritance

// Polymorphic query - includes all subclasses
SELECT p FROM Payment p  // CardPayment, CashPayment, CryptoPayment
// Polymorphic query includes all subclasses. On SINGLE_TABLE - fast (one table).
// On JOINED - generates many JOINs (slow). On TABLE_PER_CLASS - UNION ALL (even slower).

// With TYPE - filter by subclass
SELECT p FROM Payment p WHERE TYPE(p) = CardPayment

// With TREAT - cast to subclass
SELECT p FROM Payment p JOIN TREAT(p AS CardPayment) cp WHERE cp.cardNumber = :number

JPQL Functions

String: CONCAT, SUBSTRING, TRIM, LOWER, UPPER, LENGTH, LOCATE
Math: ABS, SQRT, MOD, SIZE
Date: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP
Collection: SIZE, INDEX, MEMBER OF
Null: NULLIF, COALESCE
Conditional: CASE WHEN, NULLIF

Performance Considerations

JPQL overhead:
- Parsing JPQL -> SQL generation
- Parameter binding
- Entity mapping

Native SQL overhead:
- No parsing (direct SQL)
- Parameter binding
- Entity mapping (if entity class specified)

Difference minimal for simple queries.
For complex ones - native SQL may be faster.

When to Use JPQL vs SQL

JPQL:
Database-agnostic queries
Simple and medium queries
When polymorphism needed
When working with entities

Native SQL:
Database-specific functions (CTE, window functions)
Complex analytical queries
Bulk operations (UPDATE/DELETE thousands of records)
When JPQL doesn't support syntax

Best Practices

JPQL for database-agnostic queries
Native SQL for database-specific queries
Parameterized queries (not concat!)
Indexes for fields in WHERE
DTO projection for read-only
JOIN FETCH for related data

Concat for parameters (SQL injection!)
JPQL for complex database-specific queries
Without indexes on WHERE fields
Loading full entities for read-only reports

---

Interview Cheat Sheet

Must know:

• JPQL works with entities and fields, SQL - with tables and columns
• JPQL is database-agnostic (works with any DB), SQL - database-specific
• JPQL automatically generates SQL, supports polymorphic queries and inheritance
• Parameterization mandatory: :name (named parameters), NOT string concat (SQL injection!)
• Native SQL needed for: CTE, window functions, bulk operations, database-specific functions
• DTO projection in JPQL via constructor: SELECT new com.example.Dto(…)

Frequent follow-up questions:

• When native SQL instead of JPQL? CTE, window functions, complex analytical queries, bulk UPDATE/DELETE
• JPQL and inheritance - how does it work? Polymorphic query SELECT FROM Parent includes all subclasses; TYPE for filtering, TREAT for casting
• Why is concat dangerous? SQL injection: “WHERE name = ‘” + userInput + “’ - vulnerability
• Why JOIN FETCH in JPQL? Loading related entities in one query (N+1 solution)

Red flags (DO NOT say):

• “I use table name instead of entity name in JPQL” - PersistenceException
• “String concat for parameters” - SQL injection vulnerability
• “JPQL for CTE and window functions” - JPQL doesn’t support, need native SQL
• “I load full entities for read-only reports” - DTO projection more efficient

Related topics:

• [[27. What is Criteria API and When to Use It]]
• [[28. How to Use JOIN FETCH to Solve the N+1 Problem]]
• [[29. What is Projection in JPA]]
• [[30. What Inheritance Types Does JPA Support]]