Working with the thread pool

7.1 Connection pool

Today we will learn how to work with the database even more professionally. And now we'll talk about the thread pool, or connection pool in English.

Connecting to the database takes some time. Especially if the database is remote. If we make a connection to the database for each request, then the response of our application will be incredibly slow in speed. Not to mention the resources it will consume.

As a solution to such problems, it was proposed to store connections to the base in some set, which is called the thread pool.

When we request a new connection, the connection pool creates it, and when closed, it does not close it, but saves it in the connection pool. And if we request a connection from the connection pool again, it will give us one of the old ones instead of creating a new one.

In fact, the application works through a special driver, which is a wrapper for a regular JDBC driver and which has additional functionality for working with the pool. This situation can be represented as follows:

The programmer can set the settings for the connection pool manually: the number of active connections, the timeout, etc.

For especially extreme situations, you can write your own connection pool: a class that will have a list of connections. It will override the close function, which will return the connection back to the list, and there will be many other goodies like the open connection timer. When there is no connection for a long time, the connection is closed.

7.2* Interface DataSource and ConnectionPoolDataSource

The thread pool usually works in tandem with the DataSource object. This object can be thought of as an abstraction of a remote database. The very name Data Source can be literally translated as Data Source. Which is kind of hinting.

DataSource objects are used to obtain a physical connection to a database and are an alternative to DriverManager. There is no need to register the JDBC driver. You only need to set the appropriate parameters to establish a connection andexecute the getConnection() method.

When creating an object of the DataSource type locally (directly in the application), the connection parameters are set by the appropriate methods provided by the JDBC driver provider. These methods are not defined by the DataSource interface, since the parameters for connecting to DBMS from different vendors may differ both in type and in number (for example, not all DBMS require the type of driver or network protocol to be specified).

Thus, when working with the Oracle DBMS, to use the corresponding set and get methods, it is necessary to obtain an object of the OracleDataSource type, which is an instance of the class of the same name that implements the DataSource interface. Therefore, this way of creating objects of the DataSource type makes your code less portable and less dependent on the specific implementation of the driver.

The following is a code example illustrating the local use of objects of type DataSource.

import java.sql.*;
import javax.sql.*;
import oracle.jdbc.driver.*;
import oracle.jdbc.pool.*;

public class DataSource {
    public static void main(String[] args) {
    	try {
        	OracleDataSource ods = new OracleDataSource();

        	ods.setUser("root");
        	ods.setPassword("secret");
        	ods.setDriverType("thin");
        	ods.setDatabaseName("test");
        	ods.setServerName("localhost");
        	ods.setPortNumber(1521);

        	Connection connection = ods.getConnection();
        	System.out.println("Connection successful!!!");

    	} catch (SQLException se) {
        	se.printStackTrace();
    	}
    	System.out.println("Goodbye!");
	}
}

7.3* JNDI Interface

The full capabilities of objects of type DataSource are manifested in combination with the use of the JNDI interface. JNDI is a special service (something like a directory) for large server applications that allows one service to establish a connection with another.

Using the name and directory service allows you to store objects of the DataSource type previously created by the system administrator with predefined connection parameters. The following are some of the standard property (parameter) names developed by Sun:

The combination of DataSource and JNDI interfaces plays a key role in the development of multi-tier enterprise applications based on J2EE component technology.

If you use a combination of the DataSource and JNDI interfaces in your application code, you only need to request an object of type DataSource from the naming and directory service. In this case, the details of the connection and the program code dependent on a particular implementation of the driver are hidden from the application in the object stored in the name and directory service.

Thus, the sharing of objects of type DataSource and JNDI involves two steps that are performed independently of each other:

1. You must store the named object of type DataSource in the naming and directory service using Context.bind()the javax.naming.
2. Request an object of type DataSource from the naming and directory service in the application using the Context.lookup(). After that, you can use its method DataSource.getConnection()to get a connection to the database.

The following is an example of using the JNDI interface and an object of type OracleDataSource together.

// Create a key JNDI object
Hashtable env = new Hashtable();
env.put (Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.fscontext.RefFSContextFactory");
env.put (Context.PROVIDER_URL, "file:JNDI");
Context ctx = new InitialContext(env);

// Get the DataSource object by its name
DataSource ods = (DataSource) ctx.lookup("myDatabase");

// Get the Connection from the DataSource object
Connection connection = ods.getConnection();
System.out.println("Connection successful!!!");

We will not analyze the work of JNDI. This is outside the scope of this course. I just want you to know that this approach is very common in large applications. Don't get lost if you see code like this in the future.

It doesn't matter how it works. You just need to know that with a JNDI service you can get the proxy object of any service registered in the service directory. This is how you get the DataSource object and use it to work with the database.

7.4 Connection Pool Examples

Let's get back to where we started - connection pools.

Java programmers in their programs very often use libraries that contain various implementations of connection pools. Among them there are three most popular:

• Apache Commons DBCP
• C3PO
• HikariCP

The Apache project was the first to create a good connection pool, it is also used inside the Tomcat web server. An example of working with it:

public class DBCPDataSource {

    private static BasicDataSource ds = new BasicDataSource();
    static {
    	ds.setUrl("jdbc:h2:mem:test");
    	ds.setUsername("user");
    	ds.setPassword("password");
    	ds.setMinIdle(5);
    	ds.setMaxIdle(10);
    	ds.setMaxOpenPreparedStatements(100);
    }

    public static Connection getConnection() throws SQLException {
    	return ds.getConnection();
    }

    private DBCPDataSource(){ }
}

The second very popular pool is C3PO . Strange names, I agree. The name C3PO is a reference to the c3po robot from Star Wars. And also CP is short for Connection Pool.

public class C3p0DataSource {

    private static ComboPooledDataSource cpds = new ComboPooledDataSource();
    static {
    	try {
        	cpds.setDriverClass("org.h2.Driver");
        	cpds.setJdbcUrl("jdbc:h2:mem:test");
        	cpds.setUser("user");
        	cpds.setPassword("password");
    	} catch (PropertyVetoException e) {
            // handle the exception
    	}
    }

    public static Connection getConnection() throws SQLException {
    	return cpds.getConnection();
    }

    private C3p0DataSource(){}
}

Documentation for it can be found on the official page .

And finally, the most popular Connection Pool in our time is HikariCP :

public class HikariCPDataSource {

    private static HikariConfig config = new HikariConfig();
    private static HikariDataSource ds;

    static {
    	config.setJdbcUrl("jdbc:h2:mem:test");
    	config.setUsername("user");
    	config.setPassword("password");
    	config.addDataSourceProperty("cachePrepStmts", "true");
    	config.addDataSourceProperty("prepStmtCacheSize", "250");
    	config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
    	ds = new HikariDataSource(config);
    }

    public static Connection getConnection() throws SQLException {
    	return ds.getConnection();
    }

    private HikariCPDataSource(){}
}

Here is his official GitHub page .

And a good article on configuration.

1

Task

Module 4. Working with databases, level 8, lesson 6

Locked

task0807

task0807

Open