Skip to main content

Transaction

Transaction

  • Transaction is a group of SQL query which are executed as a unit so that
    either all of them executed or none of them executed
  •  Transaction Help us to achieve Data consistency.
Steps To obtain Transaction
Step 1:Begin transaction by disabling auto commit mode.

con.setautocommit(false);

step 2: Issue set of SQL query

step 3:  If no error then Commit Transaction

con.commit();

step 4: If error then Rollback the Transaction

Scenario:
Suppose we are issuing three query which are inserting value into three different
table now suppose after issuing two query some Exception occur so we have to
Rollback of Previously Executed query using Transaction Concept.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
package com.spy.javaforlearn;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
 
public class TransactionExample 
{
public static void main(String[] args)
{
	Connection con=null;
	PreparedStatement pstmt1=null;
	PreparedStatement pstmt2=null;
	PreparedStatement pstmt3=null;
 
	try 
	{
 
	    Class.forName("com.mysql.jdbc.Driver").newInstance();
 
 
	    String db="jdbc:mysql://localhost:3306/test?user=root&password=root";
	    con=DriverManager.getConnection(db);
 
 
	    /*
	     * 1.begin transaction by setting con.autocmmit(false) method
	     */
 
	   con.setAutoCommit(false);
 
 
	    /*
	     * 2.issue set of sql query
	     */
 
	    String query1="insert into student_detail values(5,'sahil','desai')";
	    pstmt1=con.prepareStatement(query1);
 
	    int count1= pstmt1.executeUpdate();
 
	    System.out.println("rows affected in student_detail "+count1);
 
 
 
 
	    String query2="insert into User_credential values(5,'sahil','abc5')";
	    pstmt2=con.prepareStatement(query2);
 
	    int count2= pstmt2.executeUpdate();
 
	    System.out.println("rows affected in user_credential "+count2);
 
 
 
	    int a=5/0;  // Exception</strong>
 
 
	    String query3="insert into contact_detail values(5,'352246')";
	    pstmt3=con.prepareStatement(query3);   
 
	    int count3= pstmt3.executeUpdate();
 
	    System.out.println("rows affected in Contact detail "+count3);
 
 
            con.commit();
            System.out.println("profile successfully created ");
 
 
	}
	catch(Exception e)
	{
		System.out.println("Something Wrong, Please try again...");
 
		try 
		{
			con.rollback();
		} 
		catch (SQLException e1) 
                {
 
			e1.printStackTrace();
		}
 
		System.out.println("unable to create profile:");
	}
 
 
	finally
	{
 
		try {
			if(con!=null)
			{
				con.close();
			}
			if(pstmt1!=null)
			{
				pstmt1.close();
			}
			if(pstmt2!=null)
			{
				pstmt2.close();
			}
			if(pstmt3!=null)
			{
				pstmt3.close();
			}
		}
		catch (Exception e) 
		{
 
			e.printStackTrace();
		}
 
 
	  }
 
	}
 
}
 
 
output:
 
rows affected in student_detail1
rows affected in user_credential1
<strong>Something Wrong, Please try again...</strong>
unable to create profile:

Now If There is NO error Then Profile should Successfully create.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
package com.spy.javaforlearn;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
 
public class TransactionExample 
{
public static void main(String[] args)
{
	Connection con=null;
	PreparedStatement pstmt1=null;
	PreparedStatement pstmt2=null;
	PreparedStatement pstmt3=null;
 
	try 
	{
 
		Class.forName("com.mysql.jdbc.Driver").newInstance();
 
 
	    String db="jdbc:mysql://localhost:3306/test?user=root&password=root";
	    con=DriverManager.getConnection(db);
 
 
	    /*
	     * 1.begin transaction by setting con.autocmmit(false) method
	     */
	    con.setAutoCommit(false);
 
 
	    /*
	     * 2.issue set of sql query
	     */
 
	    String query1="insert into student_detail values(5,'sahil','desai')";
	    pstmt1=con.prepareStatement(query1);
 
	    int count1= pstmt1.executeUpdate();
 
	    System.out.println("rows affected in student_detail "+count1);
 
 
 
 
	    String query2="insert into User_credential values(5,'sahil','abc5')";
	    pstmt2=con.prepareStatement(query2);
 
	    int count2= pstmt2.executeUpdate();
 
	    System.out.println("rows affected in user_credential "+count2);
 
 
 
	 //   int a=5/0;
 
 
 
 
	    String query3="insert into contact_detail values(5,'352246')";
	    pstmt3=con.prepareStatement(query3);   
 
	    int count3= pstmt3.executeUpdate();
 
	    System.out.println("rows affected in Contact detail "+count3);
 
 
         con.commit();
         System.out.println("profile successfully created ");
 
 
 
 
	}
	catch(Exception e)
	{
		System.out.println("Something Wrong, Please try again...");
 
 
 
		try 
		{
			con.rollback();
		} 
		catch (SQLException e1) 
                {
 
			e1.printStackTrace();
		}
 
 
		System.out.println("unable to create profile:");
	    }
 
 
	     finally
	     {
 
		try {
			if(con!=null)
			{
				con.close();
			}
			if(pstmt1!=null)
			{
				pstmt1.close();
			}
			if(pstmt2!=null)
			{
				pstmt2.close();
			}
			if(pstmt3!=null)
			{
				pstmt3.close();
			}
	      }
 
               catch (Exception e) 
		{
 
			e.printStackTrace();
		}
 
 
	    }
 
	}
 
  }
 
 
 
output:
 
rows affected in student_detail 1
rows affected in user_credential 1
rows affected in Contact detail 1
profile successfully created

Now you can see Table data

student_detail
st-detail
Contact_detail
contact

user_credential
user_credential