通过JDBC组合来自不同数据库的表

Is there a way to combine 2 (or more) tables from 2 (or more) different database/connections?

是否有一种方法将两个(或多个)不同的数据库/连接中的两个(或多个)表合并在一起?

So far, I can only export one table from a database, and this works fine:

到目前为止，我只能从数据库中导出一个表，这很好:

private void populateWorksheet(Database db, Sheet sheet) {
    PreparedStatement preStmt;              // An object that represents a precompiled SQL statement. See http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html
    ResultSet rsltSet;                      // A table of data representing a DB result set
    ResultSetMetaData meta;                 // An object that can be used to get information about the types and properties of the columns in a ResultSet object

    String columnName, data;
    Row headerRow, dataRow;
    Cell currHeaderCell, currDataCell;

    int columnCount, rowCount;              // keep track of indices

    try {
        preStmt = db.getConnection().prepareStatement(columnsQuery);
        rsltSet = preStmt.executeQuery();    
        meta = rsltSet.getMetaData();
        columnCount = meta.getColumnCount();

        //Creating a Row for columns headings - starts with row 0
        headerRow = sheet.createRow(HEADER_ROW_INDEX);

        //Get column headings and print them in Excel - on the first row
        for(int i = 1; i <= columnCount; i ++) {
            columnName = meta.getColumnName(i);
            currHeaderCell = headerRow.createCell(i - 1);
            currHeaderCell.setCellValue(columnName);
        }

        rowCount = FIRST_DATA_ROW_INDEX;    // 1st row after the header row (data/record starts here)

        while(rsltSet.next()) {
            dataRow = sheet.createRow(rowCount);

            for(int i = 1; i <= columnCount; i ++) {
                data = rsltSet.getString(i);
                currDataCell = dataRow.createCell(i - 1);
                currDataCell.setCellValue(data);
            }
            rowCount ++;
        }
    }
    catch (SQLException e) {
        e.printStackTrace();
    }
}

But what if my application is connected to DatabaseA and DatabaseB and I want to inner-join TableA from DatabaseA and TableB from DatabaseB, and transferring the result to an Excel file. How do I go about in doing this?

但是，如果我的应用程序连接到DatabaseA和DatabaseB，并且我希望从DatabaseA和从DatabaseB连接到TableA，并将结果传输到Excel文件，该怎么办呢?我该怎么做呢?

EDIT: And what if the databases are in different servers?

编辑:如果数据库在不同的服务器上怎么办?

1 个解决方案

#1

If the databases reside inside the same DB server and your user has permissions in both, you can do stuff like

如果数据库驻留在同一个数据库服务器中，并且您的用户在这两种服务器上都有权限，那么您可以做类似的事情。

Mysql

Mysql

 SELECT * FROM db1.table1 A INNER JOIN db2.table2 B  ON A.col = B.col

SQL-Server (which default-schema dbo)

sql server(默认模式dbo)

 SELECT * FROM db1.dbo.table1 A INNER JOIN db2.dbo.table2 B  ON A.col = B.col

In cases where the databases reside on different servers, your options are more limited.

如果数据库驻留在不同的服务器上，您的选择就会更加有限。

For SQL-Server you could add a "linked Server" - wich allows access through linkname...table.

对于SQL-Server，您可以添加一个“链接服务器”——允许通过linkname…table进行访问。

#1