Connection fail-over if the db-user is locked out

Recently I was facing an issue, the connection pool of datasource was failing to establish a connection with the DB due to account lockout. There are many application servers like Tomcat, JBoss, WebSphere, etc. where you cannot implement the connection pool using multiple users.

So, how can we overcome such issue?

Yes, the best solution is to configure the db-user to never lockout but this will be against some company security policy where you cannot set the user account to never lockout.

There is a way in Jboss Application Server to overcome such issue:

1) Configure the datasource to allow multiple users to acquire connection using the following command so that multiple users can acquire connection. Execute through cli

 /subsystem=datasources/data-source=ExampleDS:write-attribute(name=allow-multiple-users,value=true)

2) Add the following code in your application to allow two users to acquire connection in the same connection pool.

 public class conn {

public void acquireConnection(){
 Connection con_ = null;
 try {
 InitialContext context = new InitialContext();
 DataSource ds = (DataSource) context.lookup("java:jboss/H2DB");
 try {
 con_= ds.getConnection();
 System.out.println(con_);
 } catch (SQLException ex) {
 Logger.getLogger(conn.class.getName()).log(Level.SEVERE, null, ex);
 }
 if(con_ == null){
 try{
 con_= ds.getConnection("another-user","password-of-another-user");
 System.out.println("Connection using h2admin user-id");
 System.out.println(con_);
 }catch(SQLException ex){
 Logger.getLogger(conn.class.getName()).log(Level.SEVERE, null, ex);
 }
 }
 } catch (NamingException ex) {
 Logger.getLogger(conn.class.getName()).log(Level.SEVERE, null, ex);
 }
 }

}