JPA Interview Question 2

How can we implement a REST endpoint using a GET request to filter Employee entities based on any of their fields, without adding all 100+ fields as request parameters, and further support field-level data sources dynamically without changing application code or application.yml?

Answer:

To achieve zero-code and zero-config dynamic filtering, we design a system where each field of an Employee can pull its data from different data sources or queries based on metadata stored in a database table instead of application.yml.

---

✅ Step 1: Create Metadata Table

CREATE TABLE employee_field_sources (
  field_name VARCHAR(100) PRIMARY KEY,
  query TEXT NOT NULL,
  datasource_bean VARCHAR(100) NOT NULL
);

Sample Data:

INSERT INTO employee_field_sources VALUES ('empId', 'SELECT emp_id FROM employee_main WHERE id = ?', 'empDsJdbc');
INSERT INTO employee_field_sources VALUES ('fname', 'SELECT fname FROM employee_names WHERE id = ?', 'nameDsJdbc');
INSERT INTO employee_field_sources VALUES ('designation', 'SELECT designation FROM hr_data WHERE id = ?', 'hrDsJdbc');

---

✅ Step 2: Register Field Sources Dynamically

@Component
public class FieldSourceRegistry {

    @Autowired
    private JdbcTemplate defaultJdbcTemplate; // From the metadata DB

    private final Map<String, FieldSource> fieldSources = new HashMap<>();

    @PostConstruct
    public void loadFieldSources() {
        List<Map<String, Object>> rows = defaultJdbcTemplate.queryForList("SELECT * FROM employee_field_sources");
        for (Map<String, Object> row : rows) {
            FieldSource fs = new FieldSource();
            fs.setQuery((String) row.get("query"));
            fs.setDatasourceBean((String) row.get("datasource_bean"));
            fieldSources.put((String) row.get("field_name"), fs);
        }
    }

    public FieldSource get(String fieldName) {
        return fieldSources.get(fieldName);
    }

    public static class FieldSource {
        private String query;
        private String datasourceBean;
        // Getters and setters
    }
}

---

✅ Step 3: Dynamic Fetcher

@Component
public class DynamicFieldFetcher {

    @Autowired
    private ApplicationContext applicationContext;

    @Autowired
    private FieldSourceRegistry registry;

    public Object fetch(String field, Long empId) {
        FieldSourceRegistry.FieldSource meta = registry.get(field);
        if (meta == null) return null;

        JdbcTemplate jdbcTemplate = (JdbcTemplate) applicationContext.getBean(meta.getDatasourceBean());

        return jdbcTemplate.queryForObject(meta.getQuery(), new Object[]{empId}, Object.class);
    }
}

---

✅ Step 4: Define Multiple JdbcTemplates (for different DBs)

@Configuration
public class DataSourceConfig {

    @Bean(name = "empDsJdbc")
    public JdbcTemplate empJdbcTemplate(@Qualifier("empDataSource") DataSource ds) {
        return new JdbcTemplate(ds);
    }

    @Bean(name = "nameDsJdbc")
    public JdbcTemplate nameJdbcTemplate(@Qualifier("nameDataSource") DataSource ds) {
        return new JdbcTemplate(ds);
    }

    @Bean(name = "hrDsJdbc")
    public JdbcTemplate hrJdbcTemplate(@Qualifier("hrDataSource") DataSource ds) {
        return new JdbcTemplate(ds);
    }
}

---

✅ Step 5: REST Controller

@RestController
@RequestMapping("/employees")
public class EmployeeController {

    @Autowired
    private DynamicFieldFetcher fieldFetcher;

    @GetMapping("/field")
    public ResponseEntity<Object> getField(@RequestParam String field, @RequestParam Long id) {
        Object value = fieldFetcher.fetch(field, id);
        return ResponseEntity.ok(value);
    }
}

---

✅ Sample GET Request

GET /employees/field?field=designation&id=101

If the employee_field_sources table has an entry for designation, it will:

• Pick the right query
• Pick the right datasource bean
• Execute with id=101

---

✅ Benefits:

• 💡 Fully dynamic
• 📦 No code or YAML changes
• ⚙️ New fields supported via DB insert only
• 🧩 Easily extendable to non-SQL sources via strategy pattern

This is a highly scalable design for enterprise systems where field sources evolve and grow independently.