Transcript Lecture Notes

MC365
JDBC and Server-Side
Programming:
Updating a database via JDBC
&
Connection Pooling
Today We Will Cover:
•
•
•
•
Prepared Statements
Performing Updates and Inserts in JDBC
Connection Pooling
Stored Procedures
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.
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 Update
http://www2.bc.edu/~bernier/MC365/Lecture Notes/JDBCUpdate.java
• Example of a JDBC Insert
http://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.
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.
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
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.
• Examples
http://www2.bc.edu/~bernier/MC365/Lecture
Notes/SQLExamples.doc