MySQL Transaction Transaction using JDBC Driver
Example
Transaction using JDBC driver is used to control how and when a transaction should commit and rollback. Connection to MySQL server is created using JDBC driver
JDBC driver for MySQL can be downloaded here
Lets start with getting a connection to database using JDBC driver
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(DB_CONNECTION_URL,DB_USER,USER_PASSWORD);
--->Example for connection url "jdbc:mysql://localhost:3306/testDB");
Character Sets : This indicates what character set the client will use to send SQL statements to the server. It also specifies the character set that the server should use for sending results back to the client.
This should be mentioned while creating connection to server. So the connection string should be like,
jdbc:mysql://localhost:3306/testDB?useUnicode=true&characterEncoding=utf8
See this for more details about Character Sets and Collations
When you open connection, the AUTOCOMMIT mode is set to true by default, that should be changed false to start transaction.
con.setAutoCommit(false);
You should always call setAutoCommit() method right after you open a connection.
Otherwise use START TRANSACTION or BEGIN WORK to start a new transaction.
By using START TRANSACTION or BEGIN WORK, no need to change AUTOCOMMIT false. That will be automatically disabled.
Now you can start transaction. See a complete JDBC transaction example below.
package jdbcTest;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class accTrans {
public static void doTransfer(double transAmount,int customerIdFrom,int customerIdTo) {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
String DB_CONNECTION_URL = "jdbc:mysql://localhost:3306/testDB?useUnicode=true&characterEncoding=utf8";
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(DB_CONNECTION_URL,DB_USER,USER_PASSWORD);
--->set auto commit to false
con.setAutoCommit(false);
---> or use con.START TRANSACTION / con.BEGIN WORK
--->Start SQL Statements for transaction
--->Checking availability of amount
double availableAmt = 0;
pstmt = con.prepareStatement("SELECT ledgerAmt FROM accTable WHERE customerId=? FOR UPDATE");
pstmt.setInt(1, customerIdFrom);
rs = pstmt.executeQuery();
if(rs.next())
availableAmt = rs.getDouble(1);
if(availableAmt >= transAmount)
{
---> Do Transfer
---> taking amount from cutomerIdFrom
pstmt = con.prepareStatement("UPDATE accTable SET ledgerAmt=ledgerAmt-? WHERE customerId=?");
pstmt.setDouble(1, transAmount);
pstmt.setInt(2, customerIdFrom);
pstmt.executeUpdate();
---> depositing amount in cutomerIdTo
pstmt = con.prepareStatement("UPDATE accTable SET ledgerAmt=ledgerAmt+? WHERE customerId=?");
pstmt.setDouble(1, transAmount);
pstmt.setInt(2, customerIdTo);
pstmt.executeUpdate();
con.commit();
}
--->If you performed any insert,update or delete operations before
----> this availability check, then include this else part
/*else { --->Rollback the transaction if availability is less than required
con.rollback();
}*/
} catch (SQLException ex) {
---> Rollback the transaction in case of any error
con.rollback();
} finally {
try {
if(rs != null) rs.close();
if(pstmt != null) pstmt.close();
if(con != null) con.close();
}
}
}
public static void main(String[] args) {
doTransfer(500, 1020, 1021);
-->doTransfer(transAmount, customerIdFrom, customerIdTo);
}
}
JDBC transaction make sure of all SQL statements within a transaction block are executed successful, if either one of the SQL statement within transaction block is failed, abort and rollback everything within the transaction block.
