Java.Hibernate.Medium.Describe all types of queries, including spring ones, when to use which ?

✅ Main Types of Queries

🔹 1) JPQL (Java Persistence Query Language) Queries

• What: Object-oriented, database-agnostic query language operating on entity classes and their fields.
• How:
  • Dynamic JPQL → createQuery("SELECT e FROM MyEntity e WHERE ...")
  • Named JPQL queries → @NamedQuery
• When to use:
  ✅ Need object-oriented, database-independent queries.
  ✅ When reusing the same query logic → Named Queries.
  ✅ When building flexible queries at runtime → Dynamic JPQL.

🔹 2) Native SQL Queries

• What: Plain SQL queries executed directly against the database.
• How:
  • session.createNativeQuery("SELECT * FROM table WHERE ...")
  • @NamedNativeQuery
• When to use:
  ✅ Need DB-specific features (e.g., stored procedures, vendor-specific functions).
  ✅ Need performance optimizations that JPQL doesn’t support.
  ❗ Avoid for basic CRUD — reduces portability.

🔹 3) Criteria API

• What: Type-safe, programmatic query building using Java objects.
• How:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<MyEntity> cq = cb.createQuery(MyEntity.class);
Root<MyEntity> root = cq.from(MyEntity.class);
cq.select(root).where(cb.equal(root.get("status"), "ACTIVE"));

• When to use:
  ✅ Need dynamic query building with conditional logic.
  ✅ Need type safety (field names checked at compile-time).
  ❗ Verbose for simple static queries.

🔹 4) Spring Data JPA Derived Queries

• What: Spring auto-generates queries based on method names in your repository interface.
• How:

List<MyEntity> findByStatus(String status);

• When to use:
  ✅ Simple queries following naming conventions.
  ✅ Need to move quickly without writing JPQL manually.
  ❗ Not suitable for complex joins or dynamic conditions.

🔹 5) Spring Data JPA @Query Annotation

• What: Inline JPQL or SQL defined directly in the repository interface.
• How:

@Query("SELECT e FROM MyEntity e WHERE e.status = :status")
List<MyEntity> findByStatus(@Param("status") String status);

• When to use:
  ✅ Need custom queries beyond what derived queries can express.
  ✅ Queries don’t require dynamic conditions.
  ✅ Great for readable, concise custom queries.

🔹 6) Spring Data JPA Named Queries

• What: Reuses @NamedQuery defined on entities or XML, called through Spring repositories.
• When to use:
  ✅ Same as JPA named queries → central definition and reuse across app.

📊 Quick Comparison Table

Query Type	Strengths	Weaknesses	Best Use Case
Dynamic JPQL	Flexible, object-oriented	Runtime validation only	Ad-hoc queries, dynamic conditions
Named JPQL	Centralized, validated at startup	Less flexible	Reusable, static queries
Native SQL	Full SQL power	DB-specific, less portable	Complex DB logic, optimizations
Criteria API	Type-safe, dynamic, programmatic	Verbose, harder to read	Complex dynamic queries
Spring Derived	Fast, no JPQL needed	Limited to naming patterns	Simple CRUD, rapid prototyping
Spring @Query	Concise, readable custom queries	Static, no dynamic conditions	Slightly complex queries not covered by names
Named Native Queries	Centralized raw SQL, reusable	Same downsides as native SQL	Reusable raw SQL in multiple places

💡 Which to Use When?

✅ Simple CRUD / Filters → Spring Derived Queries (fastest, most concise).
✅ Slightly Custom Static Queries → Spring @Query (readable, no dynamic logic needed).
✅ Dynamic Query Logic → Criteria API (conditional building).
✅ Reusable Standard Queries → Named Queries (JPQL or native).
✅ Advanced SQL Needs → Native Queries or @NamedNativeQuery (vendor-specific, stored procs).

📌 Key Takeaways

✅ Choose Derived Queries or @Query for 90% of Spring Data JPA use cases — they’re simple, readable, and effective.
✅ Use Criteria API when you need dynamic, type-safe query construction.
✅ Rely on Named Queries for centralized, reusable standard queries.
✅ Turn to Native SQL only for complex scenarios where JPQL or Criteria fall short.