Dynamic Query Engine

A Spring Boot application that optimizes database query performance by fetching only the required fields instead of entire entities, reducing data transfer overhead for large datasets.

🎯 Problem Statement

Traditional JPA queries fetch all entity columns even when only a few fields are needed, resulting in unnecessary data transfer, increased memory consumption, and slower query execution for large datasets.

💡 Solution

This project enables dynamic field selection at runtime - fetch only the columns you need:

POST /api/dynamic-query
{
  "entityClass": "io.dynamicquery.entity.UserEntity",
  "requiredFields": ["username", "email"],
  "filters": {"isActive": true}
}

Result: Only requested columns are fetched from the database.

🚀 Quick Start

Installation

git clone <repository-url>
cd dynamic-query-engine
mvn clean install
mvn spring-boot:run

Basic Usage

curl -X POST http://localhost:8080/api/dynamic-query \
  -H "Content-Type: application/json" \
  -d '{
    "entityClass": "io.dynamicquery.entity.UserEntity",
    "requiredFields": ["username", "email"],
    "filters": {"isActive": true}
  }'

Response:

[
  {"username": "john_doe", "email": "john@example.com"},
  {"username": "jane_smith", "email": "jane@example.com"}
]

✨ Key Features

• ✅ Selective Field Querying - Fetch only required columns
• ✅ Performance Optimization - Reduce data transfer for large datasets
• ✅ Two Metadata Strategies - Reflection (baseline) or compile-time generation (optimized)
• ✅ Dynamic Filtering - Support for = and IN clauses
• ✅ Type-Safe - Field validation against entity metadata
• ✅ SQL Injection Safe - Parameterized queries
• ✅ Generic - Works with any JPA entity

🏗️ Architecture

Two Metadata Resolution Strategies

1. Reflection-Based (Baseline)

• Extracts table/column names from JPA annotations at runtime
• Works with any JPA entity without special configuration
• Suitable for development and prototyping

2. Compile-Time Generation (Optimized)

• Generates metadata classes at build time using annotation processor
• Entities marked with @DynamicQueryable trigger code generation
• Zero runtime reflection - recommended for production

How It Works

1. Client specifies entity, required fields, and filters
2. System resolves field → column mappings
3. Builds SELECT field1, field2 FROM table WHERE ...
4. Executes parameterized query via JPA
5. Returns results as flexible JSON maps

📊 Performance Benefits

Illustrative Example

Entity: 20 columns, 10,000 rows
Needed: 3 columns

Approach	Data Transfer	Memory	Query Time
Traditional JPA	~2 MB	High	Slower
Dynamic Query	~300 KB	Low	Faster

Indicative savings: ~85% reduction in data transfer

Note: Actual performance gains depend on entity size, dataset volume, and network conditions.

🔧 Configuration

Enable Compile-Time Generation (Optional)

Add @DynamicQueryable to entities:

@DynamicQueryable  // Triggers metadata generation at build time
@Entity
@Table(name = "users")
public class UserEntity {
    @Id
    @Column(name = "user_id")
    private Long userId;
    
    @Column(name = "username")
    private String username;
}

Build project:

mvn clean compile

Generated files:

• UserEntity_Metadata.java - Static metadata constants
• DynamicEntityRegistry.java - Centralized registry

Application Properties

# Optional: Configure base package for reflection strategy
dynamic:
  entity:
    base-package: io.dynamicquery.entity

📝 Query Examples

Simple Query

{
  "entityClass": "io.dynamicquery.entity.ItemEntity",
  "requiredFields": ["itemCode", "itemName"],
  "filters": {}
}

With Single Filter

{
  "entityClass": "io.dynamicquery.entity.ItemEntity",
  "requiredFields": ["itemCode", "itemName", "unitPrice"],
  "filters": {"category": "Electronics"}
}

With IN Clause

{
  "entityClass": "io.dynamicquery.entity.ItemEntity",
  "requiredFields": ["itemCode", "itemName"],
  "filters": {"category": ["Electronics", "Furniture"]}
}

Multiple Filters

{
  "entityClass": "io.dynamicquery.entity.ItemEntity",
  "requiredFields": ["itemCode", "itemName", "category"],
  "filters": {
    "category": "Electronics",
    "isActive": true
  }
}

🧪 Testing

# Run all tests
mvn test

# Run specific test
mvn test -Dtest=DynamicEntityRegistryTest

📚 Documentation

• PROJECT_GUIDE.md - Complete technical guide
• DYNAMIC_QUERY_TEST_PAYLOADS.md - 50+ test examples

🎯 Use Cases

• Reporting Systems - Fetch only display columns
• API Responses - Return minimal data for mobile clients
• Data Export - Select specific fields for CSV/Excel
• Search Results - Show summary fields only
• Large Datasets - Optimize queries for millions of rows

🔒 Security

• SQL Injection Protection - All queries use parameterized statements
• Field Validation - Only valid entity fields can be queried
• Type Safety - Compile-time validation through entity structure

🤝 Contributing

1. Fork the repository
2. Create a feature branch
3. Commit your changes
4. Push to the branch
5. Open a Pull Request

📄 License

This project is licensed under the MIT License.

---

Built with ❤️ using Spring Boot and Java