How to execute a transactional query in SQL Server on Ubuntu using Java is shown in this blog.
Contents
▼1. How to execute a transactional query in SQL Server on Linux
Commit or Rollback by transaction can keep consistency of data when the query fails in the middle of the process.
setAutoCommit method of SQLServerConnection class in JDBC Driver can use commit and rollback to implement a transactional query.
▼2. Implement insert query using transaction in SQL Server on Linux
2-1. Creating Java code
Transactional insert query will add (6,7) and (7,8) into the table “t1”. after the transaction get started, an insert query is executed and then commit is executed.
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.*;
import java.util.Date;
public class TransactionalQueryv2 {
private static final Logger log = LoggerFactory.getLogger(TransactionalQueryv2.class);
public static void main(String[] args) throws InterruptedException {
Connection con = null;
boolean success = false;
int retryc = 0;
int max_retries = 3;
ResultSet resultSet = null;
while (!success && retryc < max_retries) {
try {Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://localhost:1433;" + "databaseName=TestDB;user=sa;password=passwordxx;loginTimeout=30;socketTimeout=60000;queryTimeout=60;cancelQueryTimeout=60;";
con = DriverManager.getConnection(connectionUrl);
Statement statement = con.createStatement();
System.out.println("Current Timestamps: " + new Timestamp(new Date().getTime()));
log.info("Connect to SQL Server successfully");
success = true;
// execute insert in a transaction
con.setAutoCommit(false);
Statement stmt = con.createStatement();
stmt.executeUpdate("insert into t1 values (6,7),(7,8);");
con.commit(); // This commits the transaction.
stmt.close(); // This turns off the transaction.
// execute select
String selectSql = "select c1, c2 from t1;";
resultSet = statement.executeQuery(selectSql);
System.out.println("I retrieve c1" + "\t" + "c2 for t1 table");
while (resultSet.next()){
System.out.println(resultSet.getString("c1") + "\t" + resultSet.getString("c2"));
}
} catch (ClassNotFoundException e) {
retryc++;
System.out.println("Current Timestamps: " + new Timestamp(new Date().getTime()));
log.error("Error Message: {}, retry count is {}", e, retryc);
// 30 seconds (30000 milliseconds) interval
Thread.sleep(30000);
} catch (SQLException se) {
retryc++;
System.out.println("Current Timestamps: " + new Timestamp(new Date().getTime()));
log.error("Error Messages: {}, retry count is {}", se, retryc);
// 30 seconds (30000 milliseconds) interval
Thread.sleep(30000);
}
}
try {
if (con != null) {
con.close();
System.out.println("Current Timestamps: " + new Timestamp(new Date().getTime()));
log.info("The connection is closed");
}else{
log.info("The connection was closed");
}
} catch (SQLException sec) {
System.out.println("Current Timestamps: " + new Timestamp(new Date().getTime()));
log.error("Error MEssages of fin ", sec);
}
}
}
2-2. 実行結果
[main] INFO TransactionalQueryv2 - Connect to SQL Server successfully I retrieve c1 c2 for t1 table 1 2 2 3 3 4 4 5 5 6 6 7 7 8 Current Timestamps: 2020-06-04 15:38:43.886 [main] INFO TransactionalQueryv2 - The connection is closed
▼3. Reference
- Performing transactions with the JDBC driver
- Understanding transactions
- Java -AutoRetry connection by JDBC Driver for SQLServer No53
That’s all. Have a nice day a head !!!