Reusing Database Objects (in Jsp&Servlet)

1. Reusing DB Connections:. generate connection in init() of Servlet and close connection.  Then in doPost()/doGet(), we can do any query.

2. Reusing Prepared Statements: Prepared Statement must be thread safe.  Usually it's used as:

synchronized(preparedStatement){

preparedStatement.clearParameters();

preparedStatement.setInt(.., ..);

....

preparedStatement.executeUpdate();

}

3. Transactions: points are:

try{

connection.setAutoCommit(false);

...  //statement.executeUpdate(), usually more than 1 sql sentence.

connection.commit();

}catch(...){

connection.rollback();

}

use transactions without having to connecte the database every time a page is requested:

(1) Synchronize the doPost(). This is for the low traffic sites.

(2) Create a new connection object for each transaction. This is good when we update data only once in every few thousand page requests.

(3) Connection pool

(4) Use HttpSession object to hold onto a Connection for each user.