Loading...

One SQL query to access multiple data sources in Java (from oracle, excel, sql server)

Answer #1 100 %

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.

To read more about polyglot persistence

https://dzone.com/articles/polyglot-persistence-future

https://www.mapr.com/products/polyglot-persistence

Answer #2 100 %

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_TABLEMY_TABLE2000
    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.UnityDriver to 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.

You’ll also like:


© 2022 CodeForDev.com -