CODEWITHSUNDEEP
javasql-serverdatabasejdbcconnectivity
Rikhi Sahu
Rikhi Sahu

Reputation: 655

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

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>");
  }
}

Upvotes: 5

Views: 18014

Answers (5)

Animesh Goyal
Animesh Goyal

Reputation: 9

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.

Upvotes: -1

JotaBe
JotaBe

Reputation: 39025

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).

Upvotes: 3

Mishra Shreyanshu
Mishra Shreyanshu

Reputation: 644

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

Upvotes: 0

Pat
Pat

Reputation: 2257

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! :)

Upvotes: 0

user3703582
user3703582

Reputation:

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.

Upvotes: 0

Related Questions