Java Connection Pool

Each time you get a connection with dataSource.getConnection() the pool creates a new connection only if necessary , if you have connections that are not currently in use (idle), it returns one of these. That's why the pool keeps a number of connections ready to be used (minIdle-maxIdle).

Important is always to call connection.close() , to connections when you end up using it. That in the case of a pool does not close the connection, but returns it to the pool in the idle state to be reused.

Using BasicDataSource you already have a pool of connections. The BasicDataSource implements PoolingDataSource and when it is instantiated it automatically creates a pool of connections by default. So it would not even be necessary to encapsulate it even more, you should only make sure that you use the same instance with all your accesses.

In applications in appserver as tomcat for example, that is ensured in the way you define the default datasource with xml. Without a framework, a service that makes sure to always return the same data source would be a good pattern.

public class DataSourceService{

    private static BasicDataSource basicDataSource=null;
    private String user = "username";
    private String pass = "password":

    public DataSourceService(){
         if (null==basicDataSource){
             basicDataSource = new BasicDataSource();
             basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
             basicDataSource.setUsername(user);
             basicDataSource.setPassword(pass);
             basicDataSource.setUrl(url);
             basicDataSource.setMaxActive(200);
             basicDataSource.setMinIdle(50);
             basicDataSource.setMaxIdle(100);
         }
     }

     public BasicDataSource getDataSource(){
         return basicDataSource;
     }

Then you can simply from your code use the service to get a connection and use it without further considerations:

DataSourceService dss = new DataSourceService();
Connection con = dss.getDataSource().getConnection();
// trabajar con la conexión
//
con.close(); // devuelve la conexión a su pool

A "correct" form does not exist, that's a way that works, what matters is that the implementation is clear and you ensure that the connections are requested in the same data source.

It is still recommended to review the methods setValidationQuery(String query) , setTestOnBorrow(boolean testOnBorrow) (if the source checks the connection before passing it to the world), setTestOnReturn(boolean testOnReturn) (if the source checks the connection when it is returned to the pool) and setTestWhileIdle(boolan testWhileIdle) (check inactive connections when managing the connection pool idle ).

Based on the experience, and observing the code you propose, probably a good option is to create the class ConexionPool as a Singleton, and thus be able to acquire a connection through a method that encapsulates the dataSource.getConnection() , the dataSource should be a class member, should be initialized only once and then administered until the end of the application should deregister all resources, dataSource.close() , also remember to close every connection obtained by calling the close of this connection method

Brother, I think there is a wrong approach

The pool is a pattern, in general, consisting of this "bag" or Pool, of objects of type "connection to bd", waiting to be used, the pool is a list of objects.

Why does the pool exist? the pool exists, as a design pattern when a certain object that is going to be frequently instantiated, has a creation cost, so high that the creation of this object class ends up being slow, or it could be substantially optimized.

The object of connection to the bd is expensive as a structure in memory linked to other objects that internally provide certain routines, and also establishes a network connection, in short it is a relatively expensive object (in high concurrency applications yes it shows).

Then the connection pools were invented, applying the pool pattern, to REUSE, instances of this object, as they are required. When you use objects of type connection without pool , this is destroyed and created, in the new, etc, but if you create it by the pool , the first time it is created, and at the end of the transaction it is not deleted from memory, but it is reset or cleaned over enough to re-attend the next request.

If existing, already 1 object in the pool, and it is being used, and other threads are called asking for these objects, if they are being occupied, those already created, then the pool creates ie instance an additional one in the stack, and if more requests always arrive in the pool, first look for one that has already finished working, clean it and deliver it, but when by concurrency, more objects are created, eventually the maximum limit could be reached. The minimum is the default quantity when initializing, the relevant is the pattern that is applied in similar objects, for example, in web servers, which deal with http requests, for example in tomcat-catalina as a servlet object pool.

Therefore you should not redefine the pool, in each connection, you should define it only once, generally in infrastructure configuration, but you could also do it programmatically as you are doing (I have not done it that way and it seems interesting to me), but only 1 time. Then the pool provides connections to the application when she requests it.

By the way option 2 is the best between both options It is the one that I have also seen in implementation, it is very acceptable

The 1 could be fine, if you make it clear where you are going to trigger it, since it would have to be executed only once, I still have that doubt where you would be placing it, if it is an application bean it could be, or at the start but also depends on your application

La 2, sets the pool, only once, on the first request, and that implies that it will be only once, in a certain way it is like a singleton, but there is the cost of what you are asking if it is equal to null , always, that you have to value depending on your app

================================================================================================= =====================================

2, Create a method within the class connection that the dataSource gives me

public DataSource SourceConnect(){
    if(this.dataSource==null){
        inicializaDataSource();
    }
    return this.dataSource;
}

A datasource on its own does not consume connections to BD, but they are achieved by doing dataSource.getConnection(); . Thus, in response to your question, when creating a Connection Instance, connections are not consumed. The instance of a Connection class is used, and the Datasource is in charge of its management, so that they can be reused. You simply call dataSource.getConnection(); and leave the control in the hands of Datasource.