What is a Transaction (ACID)? What does setAutoCommit do?

What is a Transaction (ACID)? What does setAutoCommit do?

A 43: A transaction is a set of operations that should be completed as a unit. If one operation fails then all the other operations fail as well. For example if you transfer funds between two accounts there will be two operations in the set
1. Withdraw money from one account.
2. Deposit money into other account.
These two operations should be completed as a single unit. Otherwise your money will get lost if the withdrawal is
successful and the deposit fails. There are four characteristics (ACID properties) for a Transaction.

Atomicity :
All the individual
operations should
either complete or fail.

Consistency:
The design of the
transaction should
update the database
correctly.

Isolation :
Prevents data being corrupted by concurrent
access by two different sources. It keeps
transactions isolated or separated from each
other until they are finished.
Ensures that the database
is definitely updated once
the Transaction is
completed.

Durability:
Transactions maintain data integrity. A transaction has a beginning and an end like everything else in life. The
setAutocommit(….), commit() and rollback() are used for marking the transactions (known as transaction
demarcation). When a connection is created, it is in auto-commit mode. This means that each individual SQL
statement is treated as a transaction and will be automatically committed immediately after it is executed. The way
to allow two or more statements to be grouped into a transaction is to disable auto-commit mode:
try{
Connection myConnection = dataSource.getConnection();
// set autoCommit to false
myConnection .setAutoCommit(false);
withdrawMoneyFromFirstAccount(.............); //operation 1
depositMoneyIntoSecondAccount(.............); //operation 2
myConnection .commit();
}
catch(Exception sqle){
try{
myConnection .rollback();
}catch( Exception e){}
}
finally{
try{if( conn != null) {conn.close();}} catch( Exception e) {}
}

The above code ensures that both operation 1 and operation 2 succeed or fail as an atomic unit and consequently
leaves the database in a consistent state. Also turning auto-commit off will provide better performance.

Reuse of database connection in jsp/servlet

Reuse of database connection in jsp/servlet:

Hope the below example will help you for reuse of database connection 

Servlet class:

package com.jspcr.servlets;

import java.io.*;

import java.sql.*;

import javax.servlet.*;

import javax.servlet.http.*;

import javax.servlet.jsp.*;

/**

* An example of a JSP superclass that can be selected with

* the extends attribute of the page

* directive.  This servlet automatically loads the

* JDBC-ODBC driver class when initialized and establishes

* a connection to the USDA nutrient database.

*/

public abstract class NutrientDatabaseServlet

   extends HttpServlet

   implements HttpJspPage

{

   protected Connection con;

   /**

   * Initialize a servlet with the driver

   * class already loaded and the database

   * connection established.

   */

   public void init(ServletConfig config)

      throws ServletException

   {

      super.init(config);

      try {

         Class.forName("com.mysql.jdbc.Driver");

         con = DriverManager.getConnection("jdbc:mysql://localhost/usda15");

      }

      catch (Exception e) {

         throw new UnavailableException(e.getMessage());

      }

      jspInit();

   }

   /**

   * Closes the database connection when

   * the servlet is unloaded.

   */

   public void destroy()

   {

      try {

         if (con != null) {

            con.close();

            con = null;

         }

      }

      catch (Exception ignore) {}

      jspDestroy();

      super.destroy();

   }

   /**

   * Called when the JSP is loaded.

   * By default does nothing.

   */

   public void jspInit() {}

   /**

   * Called when the JSP is unloaded.

   * By default does nothing.

   */

   public void jspDestroy() {}

   /**

   * Invokes the JSP's _jspService method.

   */

   public final void service(

         HttpServletRequest request,

         HttpServletResponse response)

      throws ServletException, IOException

   {

      _jspService(request, response);

   }

   /**

   * Handles a service request.

   */

   public abstract void _jspService(

         HttpServletRequest request,

         HttpServletResponse response)

      throws ServletException, IOException;

} 

JSP Page:

<%--

  Copyright (c) 2002 by Phil Hanna

  All rights reserved.

  

  You may study, use, modify, and distribute this

  software for any purpose provided that this

  copyright notice appears in all copies.

  

  This software is provided without warranty

  either expressed or implied.

--%>

<%@ page extends="com.jspcr.servlets.NutrientDatabaseServlet" %>

<%--

   This JSP page subclasses the NutrientDatabaseServlet

   parent class, which automatically loads the

   database driver and establishes the connection.

--%>

<%@ page import="java.io.*,java.sql.*" %>

Food Groups

CodeDescription

<%

   // Execute a query

   Statement stmt = con.createStatement();

   String sql = "SELECT * FROM FD_GROUP ORDER BY FDGP_DESC";

   ResultSet rs = stmt.executeQuery(sql);

   while (rs.next()) {

      String code = rs.getString(1);

      String desc = rs.getString(2);

%>

http://astore.amazon.com/httpsjavatop1-20

   <%= code %>

   <%= desc %>

<%

   }

   

   // Close the database objects

   rs.close();

   stmt.close();

%>