Database Connection Pool

Good day, habra people!

2 weeks ago I started working as a juior java developer, and, accordingly, getting a lot of new experience for myself. Today I decided to combine business with pleasure and begin this experience in writing thoughts - in the form of articles about those technologies, principles and techniques that I encountered on my junior path. The following article is the first among similar, and putting it here, I want, first, to understand whether the habrasoobshchestvu need such things - stories not experienced and hundreds of old-timers projects, and small attempts to share experiences from the junior junior, - and secondly as usual, hear comments, corrections and criticisms.

Thanks for attention.



The vast majority of modern web applications use databases to store information. An application can exchange information with a database using a database connection. If you create each time you access the database, you lose time: the execution of a transaction can take several milliseconds, while creating a connection can take up to several seconds. On the other hand, you can create a single connection (for example, using the " Singleton " template) and access the database only through it. But this solution is fraught with problems in case of high load: if at the same time one hundred users try to access the database using one connection, a queue is formed, which also adversely affects the performance of the application.



Database Connection Pool (dbcp) is a way to solve the above problem. It implies that we have at our disposal a certain set (“pool”) of connections to the database. When a new user requests access to the database, he is given an already open connection from this pool. If all open connections are already taken, a new one is created. As soon as the user releases one of the already existing connections, it becomes available to other users. If the connection is not used for a long time, it closes.

')

An example of implementing the simplest pool of connections can be found on the official java.sun.com website: Connection Pooling



Since this approach is most useful in the case of enterprise and web applications, it is only logical that such a popular servlet container as Apache Tomcat provides its own solution for creating dbcp. This solution is based on the apache-commons-dbcp library . To implement support for a pool of connections with your application, you need to go through several stages.



First, you need to declare a new resource in the context of the application. The resource (in our case, the database) is described by the following code:



<Resource name="jdbc/appname" auth="Container"

type="javax.sql.DataSource" maxActive="100"

maxIdle="30" maxWait="10000"

username="username"

password="password"

driverClassName="jdbc.driver.name"

url="jdbc:protocol://hostname:port/dbname"/>



I think no explanation needed.



The application context is described by an XML file. I consider it correct to store it in% document_root% / META-INF / context.xml, however this is not the only option. More information about the context can be read on the Tomcat site: The Context Container .



Now you need to add a link to this resource in the web.xml:



<resource-ref>

DB Connection

<res-ref-name>jdbc/appname</res-ref-name>

<res-type>javax.sql.DataSource</res-type>

<res-auth>Container</res-auth>

</resource-ref>



Now we can use this resource in our application. In order to get the Connection object to execute the sql code, the following code is used:



InitialContext initContext= new InitialContext();

DataSource ds = (DataSource) initContext.lookup("java:comp/env/jdbc/dbconnect");

Connection conn = ds.getConnection();



To obtain a data source (data source), the JNDI mechanism is used (you can read more about it here )



Everything! Now you can execute conn.createStatement () and implement the logic of working with the database. At the end, as usual, you should close the connection (conn.close ()), but unlike the usual connection through the JDBC driver, this connection does not actually close: it will be marked in the pool as free and can be reused later. Before returning the connection to the pool, all statements and ResultSets obtained using this connection are automatically closed in accordance with the API (thanks to Colwin for the comment).