Java Reference
In-Depth Information
The primary commands used in managing transactions are
COMMIT and R OLLBACK
. As their names
suggest, the
COMMIT
command commits changes made from the beginning of the transaction to the
point at which the command is issued, and the
ROLLBACK
command undoes them. In addition, most
databases support the
AUTOCOMMIT
option, which tells the RDBMS to commit all commands
individually, as they are executed. This option can be used with the
SET
command. For example:
SET AUTOCOMMIT [ON | OFF] ;
By default, the
SET AUTOCOMMIT ON
command is executed at startup, telling the RDBMS to commit all
statements automatically as they are executed. If you do not want these commands to be
automatically executed, set the
AUTOCOMMIT
option to off as follows:
SET AUTOCOMMIT OFF;
When you start to work with a transaction, turn
Autocommit
off; then issue the commands required by
the transaction, and, assuming that everything executes correctly, commit the transaction using this
command:
COMMIT;
If any problems should arise during the transaction, you can cancel the entire transaction by using the
following command:
ROLLBACK;
Note
Transaction-management syntax varies considerably from one database management
system to the next, but the basic syntax shown previously is supported by all common
database management systems.
The use of
COMMIT
and
ROLLBACK
in a JDBC example is very straightforward. Here's a modification to
the example of
Listing 6-3
, which specifically turns
Autocommit
on. Simply insert the
con.
setAutoCommit(true)
line into the
stmt.execute(SQLCommand)
method, as shown:
public void execute(String SQLCommand){
String url = urlRoot+dbName;
try {
Connection con = DriverManager.getConnection(url);
con.setAutoCommit(true);
Statement stmt = con.createStatement();
stmt.execute(SQLCommand);
con.close();
}
catch(SQLException e){
System.err.print(e.getMessage());
}
}
