How to reuse the pool of connections in each method of the google cloud endpoints frameworks v2?

Question

I am developing a cloud endpoints frameworks API with java 8 and appengine standard environment. I trying to retrieve some information from Cloud SQL and right now is ok, but each method create a new pool of connections to the db, being somewhat inefficient. How can i create only one pool of connections and reuse it in each method?

right now I use the createConnectionPool() method for the connections, but this is the problem, since this is called to every method call, and what I need is to reuse it.

I have tried to implement another class that implements ServletContextListener, being responsible for creating the pool of connections and storing it to be able to use it from the class that implements the API. However, I do not know how to reuse it from this last class

This is the code for createConnectionPool()

private DataSource createConnectionPool() {

        HikariConfig config = new HikariConfig();       
        config.setJdbcUrl(String.format("jdbc:mysql:///%s", DB_NAME));
        config.setUsername(DB_USER); 
        config.setPassword(DB_PASS);        
        config.addDataSourceProperty("socketFactory", "com.google.cloud.sql.mysql.SocketFactory");
        config.addDataSourceProperty("cloudSqlInstance", CLOUD_SQL_CONNECTION_NAME);
        config.addDataSourceProperty("useSSL", "false");        
        config.setMaximumPoolSize(5);       
        config.setMinimumIdle(5);       
        config.setConnectionTimeout(10000); // 10 seconds       
        config.setIdleTimeout(600000); // 10 minutes        
        config.setMaxLifetime(1800000); // 30 minutes

        return new HikariDataSource(config);
    }

And this are the code for the API methods

@ApiMethod(name = "pacientes", httpMethod = ApiMethod.HttpMethod.GET)
    public Message pacientes(User user, @Named("id") Integer id) throws UnauthorizedException{
        if (user == null) {
            throw new UnauthorizedException(INVALID_CREDENTIALS); 
        }   
        Message resultadoConsulta = new Message();      
        final String queryPacientes="SELECT nombre FROM paciente WHERE idpaciente=? LIMIT ?" ;
        final int LIMIT=1;
        DataSource pool = createConnectionPool();
        try (Connection conn = pool.getConnection();
                PreparedStatement consultaPacientes = conn.prepareStatement(queryPacientes);){
            consultaPacientes.setInt(1, id);
            consultaPacientes.setInt(2, LIMIT);
            try(ResultSet resultadoPacientes = consultaPacientes.executeQuery()){
                while (resultadoPacientes.next()) {
                    String nombre = resultadoPacientes.getString("nombre");
                    resultadoConsulta.setMessage(nombre);

                }
            }   
        }catch (SQLException e) {
            throw new RuntimeException(SQL_EXCEPTION, e);
        }
        return resultadoConsulta;
    }

    @ApiMethod(name="consulta", httpMethod = ApiMethod.HttpMethod.GET)
    public Persona consulta(User user) throws UnauthorizedException{
        if (user == null) {
            throw new UnauthorizedException(INVALID_CREDENTIALS);
        }
        Persona alguien= new Persona();
        final String consultaSQL="SELECT idpaciente,nombre,telefono FROM paciente LIMIT 1";
        DataSource pool = createConnectionPool();
        try (Connection conn = pool.getConnection();
                PreparedStatement consultaPacientes = conn.prepareStatement(consultaSQL);){

            try(ResultSet resultadoPacientes = consultaPacientes.executeQuery()) {
                while (resultadoPacientes.next()) {
                    String idpaciente = resultadoPacientes.getString(1);
                    String nombre = resultadoPacientes.getString(2);                    
                    alguien.setNombre(nombre);
                    alguien.setApellido(nombre);
                    alguien.setIdentificacion(idpaciente);
                    alguien.setTelefono(318);

                }
            }   

        }catch (SQLException e) {
            throw new RuntimeException(SQL_EXCEPTION, e);
        }
        return alguien;

    }

Answer 1

To create a connection pool just once, you can make it a static variable within your class. To achieve that, you just have to replace the code for the method ‘createConnectionPool‘ with the following block:

    private static DataSource dataSource;

    static {

        HikariConfig config = new HikariConfig();

        config.setJdbcUrl(String.format("jdbc:mysql:///%s", DB_NAME));
        config.setUsername(DB_USER); 
        config.setPassword(DB_PASS);        
        config.addDataSourceProperty("socketFactory", "com.google.cloud.sql.mysql.SocketFactory");
        config.addDataSourceProperty("cloudSqlInstance", CLOUD_SQL_CONNECTION_NAME);
        config.addDataSourceProperty("useSSL", "false");        
        config.setMaximumPoolSize(5);       
        config.setMinimumIdle(5);       
        config.setConnectionTimeout(10000); // 10 seconds       
        config.setIdleTimeout(600000); // 10 minutes        
        config.setMaxLifetime(1800000); // 30 minutes

        dataSource = new HikariDataSource(config);
    }

    private DataSource createConnectionPool() {
        return dataSource;
    }

Keep in mind that the method ‘createConnectionPool‘ now has the purpose of not breaking the code of the rest of the class where it might be called.

Now you can use the variable 'dataSource' rather than calling the method ‘createConnectionPool‘.