Connect to many databases in different servers

Question

Maybe somebody else asked this question before, but I've not found it yet.

My problem it's simple, I want to validate connection with lot of databases. I created a process in Java (I'm using Spring) that create datasources connection with some datas that obtained from a query. I just want to know if there's a better approach or a faster way to do it. My process take lot of time to validate the whole connections (near of 5K).

This is how my code looks like

public void validateConnections() throws IllegalBlockSizeException, BadPaddingException {
    BasicDataSource dataSource;

    List<Element> elements = dataToCreateDatasourcesRepository.getAllDatas();

    for(Element element : elements) {
        dataSource = new BasicDataSource();
        dataSource.setDriverClassName(Constants.DRIVER_CLASS_NAME_PROP);
        dataSource.setUrl(getConnectionUrl(element.getElementNbr(), element.getCode()));
        dataSource.setUsername(userName);
        dataSource.setPassword(EncryptionAlgorithm.decryptFromHex(password));
        try {
          Connection conn = dataSource.getConnection();
          Statement stmt = conn.createStatement();
          ResultSet rs = stmt.executeQuery(query);
          if(rs == null || !rs.next()) {
            LOG
                .info(
                    "Connection for element [" + element.getElementNbr() + "]." + element.getCode() + " UNSUCCESSFULY ");
          }
          else {
            LOG.info("Connection for element [" + element.getElementNbr() + "]." + element.getCode() + " SUCCESSFULY ");
          }
        }
        catch(Exception e) {
          LOG.info("Connection for element [" + element.getElementNbr() + "]." + element.getCode() + " UNSUCCESSFULY ");
          LOG.error(e);
        }
      }
  }

  public String getConnectionUrl(String elementNbr, String code) {
    return String
        .format(
            dbUrl,
            PREFIX.concat(elementNbr.toString()),
            code.concat(Constants.HOSTNAME),
            PREFIX.concat(elementNbr.toString()),
            code.toLowerCase());
  }

Answer 1

Establishing a connection is an expensive operation, which takes a lot of time on the server side. You're running a single loop which most likely stays idle waiting for the next connection. You may try using a thread pool executor to send many connections in parallel.

As a side note, you may also want to use try-with-resources statements to automatically close result sets, statements and connections.