Hi, I'm trying to figure out if it is possible to implement a connection pool using JTOpen driver on Open Liberty and set the libraries list programatically everytime I get a connection from the pool?

I've configured a connection pool using the instructions provided here:

 Configuring relational database connectivity in Liberty

And I'm successfully getting a DataSource object using JNDI in my code. Now what I'm trying to do is to set/change the list of libraries everytime I get a connection from the pool, is that even possible?

Thank you

Yes, it is possible to implement a connection pool using the JTOpen driver on Open Liberty and set the libraries list programmatically each time you get a connection from the pool.

When you configure a connection pool in Open Liberty, you typically define the necessary configuration properties, such as the database URL, username, and password, in the server.xml configuration file. However, if you want to dynamically change the list of libraries each time you get a connection, you'll need to perform additional steps.

Thanks for your response, could you please tell what those steps are?

I've tried casting the DataSource object to com.ibm.as400.access.AS400JDBCConnectionPoolDataSource and com.ibm.as400.access.AS400JDBCConnectionPool without success and also tried using DataSource.unwrap to try to get the same object type mentioned but this doesn't work.

I also tried a similar approach with the java.sql.Connection to try to get an object such as com.ibm.as400.access.AS400JDBCConnection but didn't work as well.

Thanks

Hi Josue, 

To set the libraries list programmatically for each connection obtained from the connection pool using the JTOpen driver in Open Liberty, you can utilize a custom connection wrapper.

First, configure the connection pool in your Open Liberty configuration file (server.xml) as described in the documentation you mentioned. Ensure that the JTOpen driver (com.ibm.as400.access.AS400JDBCDriver) is included in the classpath.

Next, you need to create a custom connection wrapper that extends com.ibm.as400.access.AS400JDBCConnection to set the libraries for each connection. Here's an example:

import com.ibm.as400.access.AS400JDBCConnection;

import java.sql.SQLException;

public class CustomAS400JDBCConnection extends AS400JDBCConnection {

  public CustomAS400JDBCConnection(AS400JDBCConnection connection) throws SQLException {
    super(connection);
  }

  @Override
  public void setLibrary(String library) throws SQLException {
    // Set the desired libraries for the connection
    super.setLibrary(library);
  }
}

In your code, when obtaining a connection from the pool, you can wrap the connection object with your custom connection wrapper. Here's an example:

import com.ibm.as400.access.AS400JDBCConnectionPoolDataSource;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class ConnectionPoolExample {

  public static void main(String[] args) {
    try {
      InitialContext ctx = new InitialContext();
      DataSource dataSource = (DataSource) ctx.lookup("jdbc/myDataSource");

      try (Connection connection = dataSource.getConnection()) {
        // Wrap the connection with the custom wrapper
        CustomAS400JDBCConnection customConnection = new CustomAS400JDBCConnection(
            (AS400JDBCConnection) connection);

        // Set the libraries for the connection
        customConnection.setLibrary("lib1 lib2");

        // Use the connection
        // ...
      }
    } catch (NamingException | SQLException e) {
      e.printStackTrace();
    }
  }
}

By creating a custom connection wrapper, you can override the setLibrary method to set the desired libraries for each connection obtained from the pool. Wrapping the connection in your custom wrapper allows you to access and modify the libraries for that specific connection.

I hope this solves or clarify your issue better, as I try to give you enough context based on the input provided.

Try having a look on it and let me know if it makes things better. 

Have a good day !

Thanks Youssef, I'll give it a try and let you know.