Lab 3.06: Intermediate JPA (202302)

1. Task Management Software

We modeled a simple task management system using Spring JPA:

• Task: base entity with title, dueDate, and status (boolean).
• BillableTask: extends Task, adds hourlyRate.
• InternalTask: extends Task, no additional fields.

All tasks use JOINED inheritance strategy in JPA.

---

2. PR Company

We modeled a PR company database:

• Contact: entity with title and company.
• Name: embedded object containing firstName, middleName, lastName, and salutation.
• The Name object is embedded in Contact and not stored in a separate table.

This setup allows storing structured names while keeping the database schema simple.

---

3. Native SQL vs JPQL

3 Tasks Only Possible with Native SQL (Not JPQL)

1️⃣ Using Database-Specific Functions

Some databases provide special functions that JPQL doesn’t support.

• Example: PostgreSQL’s ILIKE for case-insensitive search or MySQL’s MATCH ... AGAINST for full-text search.

Why Native SQL?
JPQL only supports standard functions. When you need database-specific capabilities, native SQL is required.

---

2️⃣ Applying Optimizer or Index Hints

You can give the database direct hints for query optimization.

• Example:

SELECT /*+ INDEX(task idx_task_title) */ * FROM task WHERE title='Design';

Why Native SQL? JPQL abstracts away database details, so you cannot instruct the database to use a specific index or optimizer strategy.

3️⃣ Complex Queries with UNION, INTERSECT, or Recursive CTEs

Advanced queries like hierarchical or combined datasets are not supported in JPQL.

Example: Recursive query to retrieve all subtasks:

WITH RECURSIVE sub_tasks AS (
SELECT * FROM task WHERE id = 1
UNION ALL
SELECT t.* FROM task t
INNER JOIN sub_tasks st ON t.parent_id = st.id
)
SELECT * FROM sub_tasks;

Why Native SQL? JPQL cannot express unions, intersections, or recursive queries, so native SQL is necessary for complex data structures.

Conclusion

Native SQL is the best choice when you need:

Database-specific functions not supported by JPQL

Fine-grained performance optimizations with indexes or hints

Advanced query structures like recursion or unions

JPQL is sufficient for standard, database-independent queries, but Native SQL is required for anything beyond its capabilities.