August 10, 2010 at 00:29Database connection pool
Good afternoon, habralyudi!
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 start writing this experience into written thoughts - in the form of articles about the technologies, principles and techniques that I encountered on my junior path. The following article is the first among similar ones, and laying it out here, I want, firstly, to understand whether the habrasociety needs such things - stories not wise by experience and hundreds of projects of old-timers, but small attempts to share experience from the junior to the junior - and secondly As usual, hear comments, corrections and criticism.
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 get a time loss: the transaction may 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 the case of a 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 application performance.
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, they are 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 existing connections, it becomes available to other users. If the connection is not used for a long time, it closes.
An example implementation of the simplest connection pool can be found on the official java.sun.com website: Connection Pooling
Since this approach is most useful for enterprise and web applications, it is only logical that a popular servlet container such as Apache Tomcat provides its own solution for creating dbcp. The solution is based on the apache-commons-dbcp library . To implement support for the connection pool with your application, you need to go through several steps.
First, you need to declare a new resource in the context of the application. A resource (in our case, a database) is described by the following code: I think no explanation is 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. You can read more about context on Tomcat's website: The Context Container .
Now we need to add a link to this resource in web.xml: Now we can use this resource in our application. In order to get a Connection object for executing sql-code, the following code is used: To get a data source (data source) the JNDI mechanism is used (more about it can be read here )
All! Now you can execute conn.createStatement () and implement the logic of working with the database. In the end, as usual, you should close the connection (conn.close ()), however, unlike a regular connection through the JDBC driver, this connection will not actually close: it will be marked as free in the pool and can be reused later. Before returning a connection to the pool, all Statement and ResultSets received using this connection are automatically closed according to the API (thanks Colwin for the comment).
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 start writing this experience into written thoughts - in the form of articles about the technologies, principles and techniques that I encountered on my junior path. The following article is the first among similar ones, and laying it out here, I want, firstly, to understand whether the habrasociety needs such things - stories not wise by experience and hundreds of projects of old-timers, but small attempts to share experience from the junior to the junior - and secondly As usual, hear comments, corrections and criticism.
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 get a time loss: the transaction may 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 the case of a 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 application performance.
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, they are 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 existing connections, it becomes available to other users. If the connection is not used for a long time, it closes.
An example implementation of the simplest connection pool can be found on the official java.sun.com website: Connection Pooling
Since this approach is most useful for enterprise and web applications, it is only logical that a popular servlet container such as Apache Tomcat provides its own solution for creating dbcp. The solution is based on the apache-commons-dbcp library . To implement support for the connection pool with your application, you need to go through several steps.
First, you need to declare a new resource in the context of the application. A resource (in our case, a database) is described by the following code: I think no explanation is 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. You can read more about context on Tomcat's website: The Context 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"/> Now we need to add a link to this resource in web.xml: Now we can use this resource in our application. In order to get a Connection object for executing sql-code, the following code is used: To get a data source (data source) the JNDI mechanism is used (more about it can be read here )
DB Connection
jdbc/appname
javax.sql.DataSource
Container
InitialContext initContext= new InitialContext();
DataSource ds = (DataSource) initContext.lookup("java:comp/env/jdbc/dbconnect");
Connection conn = ds.getConnection();All! Now you can execute conn.createStatement () and implement the logic of working with the database. In the end, as usual, you should close the connection (conn.close ()), however, unlike a regular connection through the JDBC driver, this connection will not actually close: it will be marked as free in the pool and can be reused later. Before returning a connection to the pool, all Statement and ResultSets received using this connection are automatically closed according to the API (thanks Colwin for the comment).