c3p0 connection pool cannot change database

i am running a web app where 3 databases are involved the first database is the admin database and the two other databases are for two separate institutions, meaning both institutions are using the same app but can access their separate database per a unique_code entered. the databases are starter(admin database),company1 and company2.

when the web app is started, the admin database is initially connected to automatically. (starter database). (first connection pool) code below: which works perfectly.

comboPooledDataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
comboPooledDataSource.setJdbcUrl("jdbc:mysql://host.com/starter");
comboPooledDataSource.setUser("username");
comboPooledDataSource.setPassword("password");                       
comboPooledDataSource.setMinPoolSize(2);
comboPooledDataSource.setMaxPoolSize(3000);
comboPooledDataSource.setAcquireIncrement(1);
comboPooledDataSource.setMaxIdleTime(1800);
comboPooledDataSource.setMaxStatements(0);
comboPooledDataSource.setIdleConnectionTestPeriod(3);
comboPooledDataSource.setBreakAfterAcquireFailure(false);
comboPooledDataSource.setUnreturnedConnectionTimeout(5);

and the user must enter a code in a textfield on the homepage (like a login). if the code exist in the starter database, the database related to the code is connected to and the user can view their contents from that database.

//code to fetch database name is written below: which also works successfully

String entry_code=request.getParameter("Ecode");
 //where 'Ecode' is the name of the html textfield where the user types the code
 try{
 con=Main_C3Po_Connection.getInstance().getConnection();
 String sql="select db from checker where code='"+entry_code+"'";
 pst=con.prepareStatement(sql);
 rs=pst.executeQuery();
 if(rs.next()){
get_db=rs.getString("db");
 }
 
 }catch(SQLException e){
 out.println(e);
 }

eg: starter(admin database) table name : checker
id | code |        db      |
11 |   44   | company1 |
12 |   35   | company2 |

so the second connection pool doesnt have a fixed database url but a variable database name.

eg:("jdbc:mysql://host.com/"+get_db+"?autoReconnect=true&useUnicode=yes");

where get_db is the variable name.
so when the user enters code 44, the value in the db column relating to the code entered is (company1), is then placed into the get_db variable and the database is connected to and can be accessed.

when the first code(44) is entered, the 'company1' value is placed into the 'get_db' variable and the connection is made successfully.

but the problem is after logging out and the second code (35) is entered, the 'company2' value is also placed into the 'get_db' variable BUT the connection pool for some reason still keeps the previous database connection and cannot switch to the other database chosen.
below is the second connection pool which cannot switch to a different database, though the database variable is changed:

comboPooledDataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
comboPooledDataSource.setJdbcUrl("jdbc:mysql://host.com/"+get_db+"?autoReconnect=true&useUnicode=yes");
comboPooledDataSource.setUser("username");
comboPooledDataSource.setPassword("password");
comboPooledDataSource.setMinPoolSize(2);
comboPooledDataSource.setMaxPoolSize(3000);
comboPooledDataSource.setAcquireIncrement(1);
comboPooledDataSource.setMaxIdleTime(1800);
comboPooledDataSource.setMaxStatements(0);
comboPooledDataSource.setIdleConnectionTestPeriod(5);
comboPooledDataSource.setBreakAfterAcquireFailure(false);
comboPooledDataSource.setUnreturnedConnectionTimeout(5);

please how do i configure the second connection pool to kill all connections after logging out so that it can **switch** and access any other database chosen. thank you.

Solution

This is an awkward configuration; I don't recommend it. But it should work. The act of calling

comboPooledDataSource.setJdbcUrl("jdbc:mysql://host.com/"+get_db+"?autoReconnect=true&useUnicode=yes");

should cause a "soft reset", so any new Connections you get from the pool will be to the new DB. Are you sure that you are not still using the old Connection? That is, have you been sure to close() all Connection objects from before the change?

A less awkward approach would just be to make multiple Connection pools, one for each database you need to access. When you are done with a Connection pool, free the threads and Connections associated with it by calling close() on the pool itself.