JavaServer Pages, 3rd Edition

Using Connections and Connection Pools

In a JDBC-based application, a lot revolves around the java.sql.Connection interface. Before any database operations can take place, the application must create a Connection to the database. It then acts as the communication channel between the application and the database, carrying the SQL statements sent by the application and the results returned by the database. A Connection is associated with a database user account to allow the database to enforce access control rules for the SQL statements submitted through the Connection. Finally, the Connection is also the boundary for database transactions. Only SQL statements executed through the same Connection can make up a transaction. A transaction consists of a number of SQL statements that must either all succeed or all fail as one atomic operation. A transaction can be committed (the changes resulting from the statements are permanently saved) or rolled back (all changes are ignored) by calling Connection methods.

In a standalone application, a Connection is typically created once and kept open until the application is shut down. This isn’t surprising, since a standalone application serves only one user at a time, and all database operations initiated by a single user are typically related to each other. In a server application that deals with unrelated requests from many different users, it’s not so obvious how to deal with connections. There are three things to consider: a Connection is ...