One of the appropriate solution is DataNucleus platform which has JDO, JPA and REST APIs. It has support for almost every RDBMS (PostgreSQL, MySQL, SQLServer, Oracle, DB2 etc) and NoSQL datastore like Map based, Graph based, Doc based etc, database web services, LDAP, Documents like XLS, ODF, XML etc.
Alternatively you can use EclipseLink, which also has support for RDBMS, NoSQL, database web services and XML.
By using JDOQL which is part of JDO API, the requirement of having one query to access multiple datastore will be met. Both the solutions are open-source, relatively lightweight and performant.
Why did I suggest this solution ?
- From your requirement its understood that the datastore will be your customer choice and you are not looking for a Big Data solution.
- You are preferring open-source solutions, which are light weight and performant.
- Considering your use case you might require a data management platform with polyglot persistence behaviour, which has the ability to leverage multiple datastore, based on your/customer's use cases.
Answer #2 100 %
To read more about polyglot persistence
UnityJDBC is a commercial JDBC Driver that wraps multiple datasoruces and allows you to treat them as if they were all part of the same database. It works as follows:
You define a "schema file" to describe each of your databases. The schema file resembles something like:
Database1.MY_TABLE MY_TABLE 2000 MY_TABLE.MY_ID MY_ID 3 DECIMAL...
You also have a central "sources file" that references all of your schema files and gives connection information, and it looks like this:
jdbc:oracle:thin:@localhost:1521:xe scott tiger oracle.jdbc.driver.OracleDriver MyOracleSchema.xml jdbc:sqlserver://localhost:1433 sa Password123 com.microsoft.sqlserver.jdbc.SQLServerDriver MySQLServerSchema.xml
You can then use
unity.jdbc.UnityDriverto allow your Java code to run SQL that joins across databases, like so:
String sql = "SELECT *\n" + "FROM MyOracleDB.Whatever, MySQLServerDB.Something\n" + "WHERE MyOracleDB.Whatever.whatever_id = MySQLServerDB.Something.whatever_id"; stmt.execute(sql);
So it looks like UnityJDBC provides the functionality that you need, however, I have to say that any solution that allows users to execute arbitrary SQL that joins tables across different databases sounds like a recipe to bring your databases to their knees. The solution that I would actually recommend to your type of requirements is to do ETL processes from all of your data sources into a single data warehouse and allow your users to query that; how to define those processes and your data warehouse is definitely too broad for a stackoverflow question.