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

1
MC365JDBC and Server-Side ProgrammingUpdating
a database via JDBCConnection 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/berni
  er/MC365/Lecture Notes/JDBCUpdate.java
• Example of a JDBC Inserthttp//www2.bc.edu/berni
  er/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
  usehttp//www2.bc.edu/bernier/MC365/Lecture
  Notes/ConnectionPool.java
• Here is an example of a servlet using this
  ConnectionPool classhttp//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