Skip to main content

Handling Transaction JDBC


In various application we may group the series of statements in such a way that either all of them execute successfully or all to be failed. For example if we want to execute five SQL queries at a time, which are related to each other, like
ü   Search the particular data
ü    Again search for next from second row
ü     add the data retrieved
ü     Insert into third row
ü     Update the row
To do this in the single steps, we need to do the transaction in the given ordered. When multiple statements are executed in a single transaction, all operations can be committed (made permanent in database) or rollback (that is changes to the database are undone).
When a new connection object is created, it is set to commit every time transaction automatically .so we can not rollback, to solve this problem we use the transaction scheme.
There are the following methods of connection interface which are used for transactions.
 boolean  getAutoCommit() Retrieves the current auto-commit mode for this Connection object.
 void commit() Makes all changes made since the previous commit/rollback permanent and releases any database locks currently held by this Connection object.
 void  rollback(Savepoint savepoint) Undoes all changes made after the given Savepoint object was set.
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; /**  @author Hemraj   *  * Program to demonstrate Transaction in JDBC  */ public class JDBCTransaction {     public static void main(String[] args)         throws  ClassNotFoundException{         Connection dbcon=null;             Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");           //Define the Datasource for the Driver             String sourceURL="jdbc:odbc:hemraj_db";           //Create the connection through drivermanager             try{             dbcon=DriverManager.getConnection(sourceURL);                 System.out.println("Connection is : \n"+dbcon);           //Set the connection not autocommite             dbcon.setAutoCommit(false);             Statement stmt=dbcon.createStatement();           //String insert="INSERT INTO INFO VALUES('HEM1','RAJ1',8973499)";             System.out.println("Inserted : "+stmt.executeUpdate("INSERT INTO INFO " +                 "VALUES('HEM1','RAJ1',8973499)"));             System.out.println("Inserted : "+stmt.executeUpdate("INSERT INTO INFO " +         "VALUES('HEM2','RAJ2',000499)"));             stmt.executeUpdate("UPDATE TABLE INFO " +                 "SET name='HEMRAJ1' WHERE name=HEM1");           //set The chenges permanent             dbcon.commit();             System.out.println("Transaction Completed");             dbcon.close();             }catch(SQLException e){                 System.out.println("Error,Doing Rollback");                try{                 dbcon.rollback();                 dbcon.close();              }catch( SQLException er){                  System.out.println("Error In rollback");                  }           }     } }


Popular posts from this blog

Using HyperSQL (HSQLDB)

HSQLDB is a portable RDBMS implemented in pure java. It can be embedded with your application as well as can be used separately. It is very a small database that supports almost all features of the standard database system. It comes with small jar file that can be found in lib folder. The HSQLDB jar package is located in the /lib directory of the ZIP package and contains several components and programs. Core components of jar file are : HyperSQL RDBMS Engine (HSQLDB), HyperSQL JDBC Driver, Database Manager, and Sql Tool. Installing and Using Download: download latest release of HyperSQL database from website and extract it. You will see following contents. Here "bin" directory contains some batch files those can be used to run a swing based GUI tool. You can use runManagerSwing.bat to connect to database, but database must be on before running it. Directory lib contains File hsqldb.jar . It is the database to be used by you. Running database First

In Process Mode of HSQLDB in web application.

If you want to use the database into your web application, you can use the HSQLDB in In_Process mode. In this mode, you can embed the HSQLDB into your web application and it runs as a part of your web application programm in the same JVM. In this mode, the database does not open any port to connect to the application on the hosing machine and you don't need to configure anything to access it. Database is not expposed to other application and can not be accessed from any dabase tools like dbVisualizer etc. In this mode ,database will be unknown from any other person except you. But in the 1.8.0 version, you can use Server intance for external as well as in process access.  To close the databse, you can issue SHUTDOWN command as an SQL query.   In the in-process mode, database starts from JDBC with the associated databse file provided through  connection URL. for example   DriverManager.getConnection("jdbc:hsqldb:mydatabase","SA","");   Here myd

Write a code for servlet which will display all the fields of Employee table in tabular form.MCS-051,Dec09-3a

import javax.servlet.*; import*; import java.sql.*;   import javax.servlet.http.*;   public class Employee extends HttpServlet   {   public void doGet(HttpServletRequest req, HttpServletResponse res) throws IOException,      ServletException   {   res.setContentType("text/Html");   PrintWriter pw = res.getWriter();   try   {   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   Connection con=DriverManager.getConnection("jdbc:odbc:dsn");   Statement st=con.createStatement();   ResultSet rs=st.executeQuery("select emp_id,emp_name,emp_dob,mob_no,email from employee");   String tr="";   while({   tr=tr+"<tr>";   tr=tr+"<td>"+rs.getString(1)+"</td>";   tr=tr+"<td>"+rs.getString(2)+"</td>";   tr=tr+"<td>"+rs.getString(3)+"</td>";   tr=tr+"<td>"+rs.getString(4)+"</td>&