how can i retrieve data from multiple databases in a single query?

Question

if i have multiple database with same Tables and Columns how can i retrieve Data from those Databases using single Query in Java. Done this for single Database, i am newbie in java, please suggest.

public class MultipleDBTest{
   public void dbConnect(String db_connect_string, String db_userid, String db_password){
     try{
       Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
       Connection conn = DriverManager.getConnection(db_connect_string, db_userid, db_password);
       System.out.println("connected");
       Statement statement = conn.createStatement();
       String queryString = "select <Col1>, <Col2> from <Table>";
       ResultSet rs = statement.executeQuery(queryString);
        while(rs.next()){
          System.out.println(rs.getString(1) + " | " + rs.getString(2));
        }
      }
      catch(Exception e){
        e.printStackTrace();
      }
  }

  public static void main(String[] args){
    ConnectMSSQLServer connServer = new ConnectMSSQLServer();
    connServer.dbConnect("jdbc:sqlserver://localhost;databaseName=<Database1>","<Username>","<Password>");
  }
}

What you need is not a jdbc solution. You need support from database vendor.http://technet.microsoft.com/en-us/library/hh393568%28v=sql.110%29.aspx — Jayan, Jun 11 '14 at 11:44
You can't. Create a Java data access class that contains the columns you want to retrieve. Retrieve the rows from each of the databases separately, creating a List of data access objects. Combine this Lists in Java to create one master List of data access objects. — Gilbert Le Blanc, Jun 11 '14 at 12:10

score 3 · Accepted Answer · edited Jan 28 '18 at 22:59

3

The easiest way to get data from multiple servers is linking them, querying the data from each table using the fully qualified table name, i.e. Server.Database.Schema.Table, and make the union of all.

Yo can only specify the desired server in the fully qualified name, Server, if you link the other servers to the server where you're making the query.

You'd end up with something like this

select * from Server1.Database1.dbo.Table
  union
select * from Server2.Database2.dbo.Table
  union
select * from Server3.Database2.dbo.Table

Please, see this article to understand what are linked servers and how you set them up: Linked Servers (Database Engine).

edited Jan 28 '18 at 22:59

halfer

19,824
17
99
186

answered Jun 11 '14 at 11:36

JotaBe

38,030
8
98
117

hi JotaBe, how can i find server name? – Rikhi Sahu Jun 11 '14 at 11:49
Mmmm??? How are you accesing the servers if you don't know their names? How are you going to link them? – JotaBe Jun 11 '14 at 11:59
i got the server name "RIK-PC\SQLEXPRESS" but it shows error for '\' – Rikhi Sahu Jun 11 '14 at 12:06
You need to link ther other servers to your server before doing the query. WHen you link them you can define names for the linked servers. However, if you're using special characters, use square brackets, i.e `[RIC-PC\SQLEXPRESS]` – JotaBe Jun 11 '14 at 12:07

score 0 · Answer 2 · answered Jun 11 '14 at 11:36

Let Sql Server do the work for you. Create a view in one of the databases that references the data from the tables in the other databases. That way your code need only access one object in one database, the view.

This is easiest if the databases are on the same server. If the databases are on separate servers you will need to link them.

score 0 · Answer 3 · edited May 23 '17 at 11:49

If by "multiple databases" - you mean multiple schemas in the same database, then you can use the schema name and make the JOIN. Also, ensure that you have sufficient privileges to read both the schemas. SQL query would be of the form:

select S1T1.Col1, S1T1.Col2, S2T1.Col1, S2T1.Col2 
from Schema1.T1 S1T1, Schema2.T1 S2T1
where S1T1.Col1=S2T1.Col1

And if you mean multiple databases on different database instances, then you may have to create links between the database instances. Refer to this SO post for more information: Querying data by joining two tables in two database on different servers

If the information helps, don't forget to vote. Thanks! :)

it is for multiple Databases – Rikhi Sahu Jun 11 '14 at 11:50 — Rikhi Sahu, Jun 11 '14 at 11:50

score 0 · Answer 4 · answered Jun 11 '14 at 11:52

Database.Schema.Table when referencing tables

and

Database.Schema.Table.Column when referencing columns

You can write joins between databases this way and deffinately pull data from more than one database.

USE [DatabaseA]



SELECT * FROM DatabaseA.dbo.DSNA_tblMaiin

INNER JOIN DatabaseB.dbo.DSNB_tblMaiin ON DatabaseA.dbo.DSNA_tblMaiin.Serialnumber = DatabaseB.dbo.DSNB_tblMaiin.Serialnumber

INNER JOIN DatabaseB.dbo.DSNC_tblMaiin ON DatabaseA.dbo.DSNA_tblMaiin.Serialnumber = DatabaseC.dbo.DSNC_tblMaiin.Serialnumber

score -1 · Answer 5 · answered Dec 31 '18 at 04:47

What you are looking for is the federation layer. the layer will parse the SQL, Queries per DB will be created. Those independent queries will get fired on DB and result will be joined based on where clause. There are some Antlr based SQL Grammars available on the net, so you use them for parsing the SQL and generating DB specific SQLs.

how can i retrieve data from multiple databases in a single query?

5 Answers5