Usually
a Connection object is used to connect to a databse. By default a
Connection object is in auto-commit mode, which means that it
automatically commits changes after executing each statement. If
auto-commit mode has been disabled, the method commit must be called
explicitly in order to commit changes; otherwise, database changes
will not be saved.
java.sql.Connection
interface provides 'setAutoCommit' method to enable/disable auto
commit mode.
void
setAutoCommit(boolean autoCommit) throws SQLException
if
autoCommit is true, enables the auto commit mode. Else disables auto
commit mode. By default every connection to database is in
auto-commit mode.
Lets
take an example
/* Step 1: Import sql package */ import java.sql.*; public class SampleApp { /* Update username, password and driver details here */ static Connection getConnection() throws ClassNotFoundException, SQLException{ /* Step 2: Load Driver */ System.out.println("Loading/Registering driver"); Class.forName("com.mysql.jdbc.Driver"); /* Step 3: Open connection to database */ System.out.println("Connecting to database"); String url = "jdbc:mysql://localhost/world"; String userName = "root"; String pasword = "tiger"; return DriverManager.getConnection(url, userName, pasword); } public static void main(String args[]) throws SQLException, ClassNotFoundException{ Connection conn = getConnection(); conn.setAutoCommit(false); /* Create table employee */ String query = "CREATE TABLE employee (id int, name varchar(30), PRIMARY KEY(id))"; Statement stmt = conn.createStatement(); stmt.execute(query); /* Insert data to employee table */ query = "INSERT INTO employee values(1, \"Krishna\")"; stmt.execute(query); query = "INSERT INTO employee values(2, \"Arjun\")"; stmt.execute(query); System.out.println("Calling roll back operation"); conn.rollback(); query = "SELECT * FROM employee"; ResultSet rs = stmt.executeQuery(query); while(rs.next()){ int id = rs.getInt("id"); String name = rs.getString("name"); System.out.println(id +" " + name); } System.out.println("Dropping table employee"); query = "DROP TABLE employee"; stmt.execute(query); rs.close(); stmt.close(); conn.close(); } }
Output
Loading/Registering driver
Connecting to database
Calling roll back operation
Dropping table employee
As
you observe in the above program,
conn.setAutoCommit(false);
statement
disables the auto commit mode. So you must explicitly commit the
transactions.
conn.rollback();
Undoes
all changes made in the current transaction and releases any database
locks currently held by this Connection object. So the records are
not commited to the table employee.
Java.sql.Connection
class provides 'commit' method to commit the transaction against the
database.
void
commit() throws SQLException
Makes
all changes made since the previous commit/rollback permanent and
releases any database locks currently held by this Connection object.
Call this method if auto-commit mode has been disabled.
Will
call 'commit' method just before calling rollback operation.
/* Step 1: Import sql package */ import java.sql.*; public class SampleApp { /* Update username, password and driver details here */ static Connection getConnection() throws ClassNotFoundException, SQLException{ /* Step 2: Load Driver */ System.out.println("Loading/Registering driver"); Class.forName("com.mysql.jdbc.Driver"); /* Step 3: Open connection to database */ System.out.println("Connecting to database"); String url = "jdbc:mysql://localhost/world"; String userName = "root"; String pasword = "tiger"; return DriverManager.getConnection(url, userName, pasword); } public static void main(String args[]) throws SQLException, ClassNotFoundException{ Connection conn = getConnection(); conn.setAutoCommit(false); /* Create table employee */ String query = "CREATE TABLE employee (id int, name varchar(30), PRIMARY KEY(id))"; Statement stmt = conn.createStatement(); stmt.execute(query); /* Insert data to employee table */ query = "INSERT INTO employee values(1, \"Krishna\")"; stmt.execute(query); query = "INSERT INTO employee values(2, \"Arjun\")"; stmt.execute(query); System.out.println("Calling roll back operation"); conn.commit(); conn.rollback(); query = "SELECT * FROM employee"; ResultSet rs = stmt.executeQuery(query); while(rs.next()){ int id = rs.getInt("id"); String name = rs.getString("name"); System.out.println(id +" " + name); } System.out.println("Dropping table employee"); query = "DROP TABLE employee"; stmt.execute(query); rs.close(); stmt.close(); conn.close(); } }
Output
Loading/Registering driver Connecting to database Calling roll back operation 1 Krishna 2 Arjun Dropping table employee
No comments:
Post a Comment