Simple Connection Pool without DataSource in Java

It's no secret that in Java EE Connection Pool is implemented using Data Source. An example implementation in Apache Tomcat can be found at this link: habrahabr.ru/post/101342 . But what to do if we use only Java SE and we need to organize multi-threaded access to the database using the Connection Pool scheme. After all, we don’t have an application server in this case, therefore, we cannot use Data Source, and we will probably have to use java.sql.DriverManager to create connections to the database. You can also use something from this article to replace this approach: docs.oracle.com/javase/jndi/tutorial/ldap/connect/pool.html . Most likely, this approach has already been implemented by someone in Java, at least I have not found such an implementation on the network. And I decided to invent my bike. It would be nice if in the search engine when searching for the Connection pool in Java, a link to this article was added, supplemented and edited from the comments and additions from habr.ru, and this article would be useful to someone. If it became interesting, then I ask for cat.

We implement everything in the form of a class, the constructor of which takes as input the initial number of connections in the pool and connection parameters to the database (the name of the class being loaded from the driver and the connection string along with the user name and password). Let's call the class ConnectionPool, its constructor will look like:

public ConnectionPool(String url, String driver, int initConnCnt) { try { Class.forName(driver); } catch (Exception e) { e.printStackTrace(); } this.url = url; for (int i = 0; i < initConnCnt; i++) { availableConns.addElement(getConnection()); } } 

url is a connection string, for MS SQL Server it will look like: jdbc: sqlserver: //192.168.0.1; databaseName = dbname; username = username; password = pwd. You can separate the username and password, transfer them separately to the DriverManager. Here everything is in one place, at least it seemed so more convenient to me. One of the valid drivers for working with MS SQL Server is as follows: com.microsoft.sqlserver.jdbc.SQLServerDriver.
In this class two vectors should be declared:

 private Vector<Connection> availableConns = new Vector<Connection>(); private Vector<Connection> usedConns = new Vector<Connection>(); 

The first will contain a list of available compounds, the second list used at any time.
In addition, the url variable is defined, which will store the connection string to the database:
')

 private String url; 

The constructor uses the getConnection function, which simply creates a new connection. Its implementation is as follows:

 private Connection getConnection() { Connection conn = null; try { conn = DriverManager.getConnection(url); } catch (Exception e) { e.printStackTrace(); } return conn; } 

So, we have a vector availableConns, filled with Connections in the number of initConnCnt pieces, none of which are currently used directly for their intended purpose, i.e. to access the database. Now we’ll write the retrieve function, this function takes the next Connection from the availableConns and adds it to the usedConns, then returns this connection, thus it becomes used:

 public synchronized Connection retrieve() throws SQLException { Connection newConn = null; if (availableConns.size() == 0) { newConn = getConnection(); } else { newConn = (Connection) availableConns.lastElement(); availableConns.removeElement(newConn); } usedConns.addElement(newConn); return newConn; } 

The logic is clear: first we check whether there are free connections, if not, we create a new connection, if there is, then we extract the last element from the availableConns and remove it from the vector of free connections. Then we just add a connection or retrieve it from the list of free ones to the list used by the line.

 usedConns.addElement(newConn); 

and return this connection. Of course, synchronized is indispensable. How else? Access is multi-threaded, all of a sudden the same connection will be allocated to two threads! When the connection is no longer needed, we perform the reverse operation, in other words putback:

 public synchronized void putback(Connection c) throws NullPointerException { if (c != null) { if (usedConns.removeElement(c)) { availableConns.addElement(c); } else { throw new NullPointerException("Connection not in the usedConns"); } } } 

The logic is also understandable and does not require declarations.
Then you can, if necessary, write a bunch of additional functions that provide access to additional information, for example, the function for getting the number of free connections will look like this:

 public int getAvailableConnsCnt() { return availableConns.size(); } 

Fully all class:

 import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Vector; class ConnectionPool { private Vector<Connection> availableConns = new Vector<Connection>(); private Vector<Connection> usedConns = new Vector<Connection>(); private String url; public ConnectionPool(String url, String driver, int initConnCnt) { try { Class.forName(driver); } catch (Exception e) { e.printStackTrace(); } this.url = url; for (int i = 0; i < initConnCnt; i++) { availableConns.addElement(getConnection()); } } private Connection getConnection() { Connection conn = null; try { conn = DriverManager.getConnection(url); } catch (Exception e) { e.printStackTrace(); } return conn; } public synchronized Connection retrieve() throws SQLException { Connection newConn = null; if (availableConns.size() == 0) { newConn = getConnection(); } else { newConn = (Connection) availableConns.lastElement(); availableConns.removeElement(newConn); } usedConns.addElement(newConn); return newConn; } public synchronized void putback(Connection c) throws NullPointerException { if (c != null) { if (usedConns.removeElement(c)) { availableConns.addElement(c); } else { throw new NullPointerException("Connection not in the usedConns array"); } } } public int getAvailableConnsCnt() { return availableConns.size(); } } 

upd. changed the putback according to the comment.