Multiple Datasource in single query with JDBCTemplate

Question

I have two datasource connections DS1 and DS2. I am using JDBC template. I have conditional select statement as below, which will use two datasource to select from DS1.table1 and DS2.table2.

Is it possible to connect two schema in single JDBC query ?

searchResult = this.jdbcTemplate.query(search_query, parameterValues.toArray(new Object[parameterValues.size()]), new ManageMapper());


StringBuilder search_query = new StringBuilder();
        search_query.append(...................);
        .
        .
        .
        .
        stringBuilder.append(" left  join schema1.table t1 on t1.emp_id = t111.emp_id ");

        if (searchRequest.getOnboardStatus() != null && (searchRequest.getOnboardStatus().equals("True")) {         
            stringBuilder.append(" inner join schema2.table2 t2 on t2.dept_id = t222.dept_id and t222.to_dt is null");
        }


@Autowired
@Qualifier(value = "jdbcDS1")
protected JdbcTemplate jdbcDS1Template;


@Autowired
@Qualifier(value = "jdbcDS2")
protected JdbcTemplate jdbcDS2Template;

Answer 1

JDBC operates at DBMS level, generating SQL code for execution. A SQL can only be send to 1 DBMS at 1 time. In special situations the DBMS engine can execute a query in distributed style like in Oracle RAC, or parallel query. However the DBMS in in control over this.

In case of multiple DBMS schemas used over more then 1 JDBC data source, the DBMS has no knowledge of where the other data in other schemas is, so with multiple JDBC data sources, this can not be done. However if you know that the schemas reside in 1 DBMS, then you can send a query to that DBMS accessing multiple schemas. At Java side you will have to do this with 1 JDBC data source only.