MC365 JDBC and Server-Side Programming: Updating a database via JDBC & Connection Pooling

1. MC365JDBC and Server-Side Programming:Updating a database via JDBC&Connection Pooling

2. Today We Will Cover: • Prepared Statements • Performing Updates and Inserts in JDBC • Connection Pooling • Stored Procedures

3. Prepared Statements • Prepared Statements • A PreparedStatement is a subinterface of Statement that offers several benefits: • The contained SQL is sent to the database and compiled or prepared beforehand. • From this point on, the prepared SQL is sent and this step is bypassed. • The more dynamic Statement requires this step on every execution. • Depending on the DB engine, the SQL may be cached and reused even for a different PreparedStatement and most of the work is done by the DB engine rather than the driver. • A PreparedStatement can take IN parameters, which act much like arguments to a method, for column values. • PreparedStatements deal with data conversions that can be error prone in straight ahead, built on the fly SQL; handling quotes and dates in a manner transparent to the developer, for example.

4. JDBC Updates and Inserts • Must use executeUpdate method instead of executeQuery for inserts, updates and deletes • executeUpdate() returns an int containing the affected row count for INSERT, UPDATE, or DELETE statements, or zero for SQL statements that do not return anything. • Example of a JDBC Updatehttp://www2.bc.edu/~bernier/MC365/Lecture Notes/JDBCUpdate.java • Example of a JDBC Inserthttp://www2.bc.edu/~bernier/MC365/Lecture Notes/JDBCInsert.java • Checking for nulls from a Select • To do this simply check the ResultSet for null after performing a Select statement.

5. Connection Pooling • What is connection pooling? • Connection pooling is a technique used for managing server resources. The program does this by making a limited set of connections to the database. • Clients or servlets go through this connection pool to get a connection to the database. • The connection pool class manages connections that are in use and ones that are available. • Why do we use it? • Connecting to a database is one of the most resource-intensive processes. It can take a long time and really slow down your server if a large number of connections are made. In fact, it can even stall your server. • The biggest benefit of connection pooling is that it creates the connections in advance. • When a servlet needs a connection, it is already available. This is much more efficient than having each servlet make its own connection. • In addition, it limits the number of connections made to the db. Many servlets can share a small set of connections because once they use the connection, they can release it fairly quickly.

6. Connection Pooling • Some app servers have built-in connection pooling. • You can also write your own or find shareware code online. Here is an example of a class that manages connection pooling that you can use:http://www2.bc.edu/~bernier/MC365/Lecture Notes/ConnectionPool.java • Here is an example of a servlet using this ConnectionPool class:http://www2.bc.edu/~bernier/MC365/Lecture Notes/ConnectionPoolServlet.java

7. Stored Procedures • What are they? • These are basically SQL statements, or a set of SQL statements, that are stored directly on the database. • Why do we use them? • Offers a lot more flexibility. • Leaves the SQL coding the DBA. • This is a true separation of the data layer. • Even more efficient. • Exampleshttp://www2.bc.edu/~bernier/MC365/Lecture Notes/SQLExamples.doc