How to use Transaction Management in JDBC

How Transaction management is used in JDBC? How it works in web application level? and if i wanted to send multiple SQL statement does it consider the SQL command as Multiple SQL commands.

If anyone has any further information about it please also let me know the same thing.

Thanks in Advance

Most major RDMBS systems support the concept of transactions. A transaction allows you to group multiple SQL statements together. Using a transaction-aware RDBMS, you can begin a transaction, perform any number of actions, and either commit the results to the database or roll back all of your SQL statements.

A transaction is isolated from the rest of the database until finished. As far as the rest of the database is concerned, everything takes place at once (in other words, transactions are atomic). This means that other users accessing the database will always see a valid view of the data, although not necessarily an up-to-date view. If a user requests a report on widgets sold before your widget sales transaction is completed, the report will not include the most recent sale.

This is done because transactions are linked to connections and, therefore, connections using transactions cannot be shared.

Transactions without having to connect to the database every time a page is requested.

• Synchronize the doPost() method. This means that each instance of the servlet deals with only one request at a time. This works well for very low traffic sites, but it does slow things down for your users because every transaction has to finish before the next can start. If you need to perform database-intensive updates and inserts, the delay will probably be unacceptable.
  
• Leave things as they are, but create a new Connection object for each transaction. If you need to update data only once in every few thousand page requests, this might be the simplest route.
  
• Create a pool of Connection objects in the init() method and hand them out as needed.This is probably the most efficient way to handle the problem, if done right. It can, however, become very complicated very quickly without third-party support classes.
  
• Create a single Connection object in the init() method and have the servlet implement SingleThreadModel, so the web server creates a pool of servlet instances with a Connection for each,This has the same effect as synchronizing doPost().

Database Transactions with JDBC

When a connection is created using JDBC, by default it is in auto-commit mode. This means that each SQL statement is treated as a transaction and will be automatically committed immediately after it is executed.The following code illustrates this:

Code:

---

conn.setAutoCommit(false);
try{
PreparedStatement updateSales = _
conn.prepareStatement(
    "UPDATE PRODUCT_SALES SET NUMBER_OF_SALES=NUMBER _
_OF_SALES+1 WHERE
PRODUCT_ID = ?");
updateSales.setString(1, productId);
updateSales.executeUpdate();
PreparedStatement updateInventory = _
conn.prepareStatement(
    "UPDATE INVENTORY SET STOCK=STOCK-1 _
WHERE PRODUCT_ID = ?");
updateInventory.setString(1, productId);
updateInventory.executeUpdate();
conn.commit();
}
catch(SQLException se){
  conn.rollback();
}
conn.setAutoCommit(true);

---