There are plenty of situations where databases need to execute a series of SQL statements together to maintain the integrity of the data. In such situations, either all of the statements must succeed or none of them must succeed. Bank accounts are a good example.
In many cases, a customer may have a savings account and a checking account. If a customer moves money from the savings account into the checking account, then two updates are required on two tables. If the update only succeeds on the savings account but fails on the checking account, then the customer’s money will disappear. That is less than ideal for the customer, so either both the checking and savings account tables must update, or the entire operation must fail.
Grouping SQL statements together is known as a transaction. We can manually manage our transactions in JDBC by setting the autoCommit property to false on the connection object. After we set autoCommit to false, we have to make sure to call commit() on the connection object after each transaction. Below is a simple Kotlin program that demonstrates transactions.
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>OCJP-DB</groupId>
<artifactId>ocjpdb</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<kotlin.version>1.2.10</kotlin.version>
<main.class>stonesoupprogramming.MainKt</main.class>
</properties>
<dependencies>
<dependency>
<groupId>org.apache.derby</groupId>
<artifactId>derby</artifactId>
<version>10.14.1.0</version>
</dependency>
<dependency>
<groupId>org.jetbrains.kotlin</groupId>
<artifactId>kotlin-stdlib-jre8</artifactId>
<version>${kotlin.version}</version>
</dependency>
<dependency>
<groupId>org.jetbrains.kotlin</groupId>
<artifactId>kotlin-test</artifactId>
<version>${kotlin.version}</version>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<sourceDirectory>src/main/kotlin</sourceDirectory>
<plugins>
<plugin>
<groupId>org.jetbrains.kotlin</groupId>
<artifactId>kotlin-maven-plugin</artifactId>
<version>${kotlin.version}</version>
<executions>
<execution>
<id>compile</id>
<phase>compile</phase>
<goals>
<goal>compile</goal>
</goals>
</execution>
<execution>
<id>test-compile</id>
<phase>test-compile</phase>
<goals>
<goal>test-compile</goal>
</goals>
</execution>
</executions>
<configuration>
<jvmTarget>1.8</jvmTarget>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-jar-plugin</artifactId>
<version>2.6</version>
<configuration>
<archive>
<manifest>
<addClasspath>true</addClasspath>
<mainClass>${main.class}</mainClass>
</manifest>
</archive>
</configuration>
</plugin>
<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>exec-maven-plugin</artifactId>
<version>1.2.1</version>
<executions>
<execution>
<phase>test</phase>
<goals>
<goal>java</goal>
</goals>
</execution>
</executions>
<configuration>
<mainClass>${main.class}</mainClass>
</configuration>
</plugin>
</plugins>
</build>
</project>
Employees.kt
package stonesoupprogramming
import java.sql.Connection
import java.sql.DriverManager
import java.sql.ResultSet
import java.util.*
fun main(args: Array<String>) {
val properties = Properties()
//Populate the properties file with user name and password
with(properties) {
put("user", "admin")
put("password", "pw")
}
//Open a connection to the database
DriverManager
.getConnection("jdbc:derby:stonesoup;create=true", properties)
.use { connection ->
//Set autoCommit to false to manually manage transactions
connection.autoCommit = false
createOrTruncateTable(connection)
//Create an updatable result set
val rs = connection
.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)
.executeQuery("SELECT * FROM BURGERS.EMPLOYEES")
//Populate the table with data
with(rs) {
moveToInsertRow()
updateInt("ID", 1);
updateString("NAME", "Bob")
insertRow()
moveToInsertRow()
updateInt("ID", 2)
updateString("NAME", "Linda")
insertRow()
}
//Commit the transation
connection.commit()
//Read only queries are still transactions
val rsq = connection
.createStatement()
.executeQuery("SELECT * FROM BURGERS.EMPLOYEES")
with(rsq) {
while (next()) {
println("${getInt("ID")}\t${getString("NAME")}")
}
}
//So we need to commit this query also even though it doesn't change anything
connection.commit()
}
}
private fun createOrTruncateTable(connection: Connection) {
val metaData = connection.metaData
if (!metaData.getTables(null, "BURGERS", "EMPLOYEES", null).next()) {
connection
.createStatement()
.executeUpdate("CREATE TABLE BURGERS.EMPLOYEES (ID INT PRIMARY KEY, NAME VARCHAR(255))")
} else {
connection
.createStatement()
.executeUpdate("TRUNCATE TABLE BURGERS.EMPLOYEES")
}
connection.commit()
}
Let’s begin with the explanation. We start by connecting to the database (lines 12-20), then we set the connection’s autoCommit property to false (line 22). From this point on, we are responsible for managing our database transactions. Lines 63-75 create a table for us to work on in the database. It will either make a brand new table, or truncate an existing one.
We populate our table with data on lines 27-45 by creating an updatable ResultSet object and using it’s update methods. The ResultSet will prepare SQL insert statements. However, none of the rows get inserted into the database until line 47 when we call commit() on the connection object.
It’s worth noting that we have to call commit() on read only transactions also. So on lines 50-57, we read from the database to verify the inserts were performed. However, notice that on line 59, we still call commit() even though we haven’t actually changed anything. This is because the underlying database still considers a read only statement to be a transaction.
Stone Soup Programming 