JavaPostgres如何从多个数据库获取数据

Question

我在不同的端口上有两个PostgreSQL数据库：端口5432上的DB1和端口5431上的DB2

我有这样的代码从DB1获取数据：

try {
    Class.forName("org.postgresql.Driver");
    String conString = "jdbc:postgresql://127.0.0.1:5432/DB1?user=MyUser&pass=MyPass" ; 
    c = DriverManager.getConnection(conString);
      st = c.createStatement();
      ResultSet rs = st.executeQuery(query);
      while (rs.next()){
          vaArrL.add(rs.getDouble("va"));
          vbArrL.add(rs.getDouble("vb"));
          vcArrL.add(rs.getDouble("vc"));
      }

当我只向DB1发送一个查询时，效果很好。 但现在，我需要同时查询两个数据库，如：

select va, vb from  DB1.public.t1 where datatime >=  1417384860 and datatime <=  1417381199 
union  
select va, vb from dblink('hostaddr=127.0.0.1 port=5431 dbname=DB2 user=MyUser password =MyPass '::text,
           'select va, vb 
            from Db2.public.t2 order by datatime ')
            datos(va integer,vb integer);

当我从pgAdmin运行查询时，我得到了结果

但当我向gunction发送查询时，我得到：连接不可用

现在。如何将查询发送到函数并获取值

Answer 1

您可以尝试使用JDBC的^{}方法吗

setCatalog'sjavadoc声明：

Calling setCatalog has no effect on previously created or prepared Statement objects. It is implementation defined whether a DBMS prepare operation takes place immediately when the Connection method prepareStatement or prepareCall is invoked. For maximum portability, setCatalog should be called before a Statement is created or prepared.

try {
    Class.forName("org.postgresql.Driver");
    // Connect to DB1 (specified in connection string/URL).
    String conString = "jdbc:postgresql://127.0.0.1:5432/DB1?user=MyUser&pass=MyPass" ; 
    c = DriverManager.getConnection(conString);
    st = c.createStatement();

    // Execute query on DB1.
    ResultSet rs = st.executeQuery(query);
    while (rs.next()){
        vaArrL.add(rs.getDouble("va"));
        vbArrL.add(rs.getDouble("vb"));
        vcArrL.add(rs.getDouble("vc"));
    }

    // Switch to DB2 and execute query.
    c.setCatalog("DB2");    
    Statement st2 = c.createStatement();
    ResultSet rs2 = st2.executeQuery(...);
}

如果JDBC驱动程序不支持setCatalog，那么可以显式执行SQL查询USE DB2，但这可能会影响已经打开的语句（我不确定这一点）

---

编辑：OP希望来自同一结果集中两个数据库的所有结果

假设DB1和DB2在同一台服务器上，我建议在数据库DB1中创建一个视图，该视图可以访问数据库DB2中的表并返回组合结果。然后您可以通过JDBC从视图中SELECT *获得结果

您可以对视图使用这样的查询（假设视图是在DB1中创建的）：

SELECT all.va, all.vb FROM
    (SELECT va, vb, datatime FROM t2
     UNION
    SELECT va, vb, datatime FROM DB2.public.t2) all
ORDER BY all.datatime

注意：要访问另一个数据库中的表，需要指定[db name]。[模式]。[表格名称]

如果查询需要动态参数，则可以创建存储过程而不是视图

Answer 2

我正在寻找一个解决方案 我正在使用2连接并将查询从客户端发送到xmlrpc服务器，如下所示：

String conString = "jdbc:postgresql://" + host + ":" + port + "/" + DBName + 
            "?user=" + user + "&pass=" + pass;

String conString1 = "jdbc:postgresql://" + host + ":" + port2 + "/" + DBName2 + 
            "?user=" + user + "&pass=" + pass;

       c = DriverManager.getConnection(conString);
      c2 = DriverManager.getConnection(conString1);

      st = c.createStatement();
      st2 = c2.createStatement();       
          List<ResultSet> resultSets = new ArrayList<>();
          resultSets.add(st.executeQuery(query));
          resultSets.add(st2.executeQuery(query2));
           ResultSets rs = new ResultSets(resultSets);
      while (rs.next()){
          unbArrL.add(rs.getUnbalance("unbalance"));
      }

从数据库中获取值的resultSets类是：

class ResultSets {

 private java.util.List<java.sql.ResultSet> resultSets;

    private java.sql.ResultSet current;


    public ResultSets(java.util.List<java.sql.ResultSet> resultSets) {
        this.resultSets = new java.util.ArrayList<>(resultSets);
        current = resultSets.remove(0);
    }

    public boolean next() throws SQLException {
        if (current.next()) {
            return true;
        }else if (!resultSets.isEmpty()) {
            current = resultSets.remove(0);
            return next();
        }
        return false;
    }
    public Double getUnbalance(String unbalance) throws SQLException{
        return current.getDouble("unbalance");
    }

}