JDBC: Transactions with Commit and Rollback
Problem
A bank keeps accounts in a table. Lines start with acct NAME BALANCE (create an account) or transfer FROM TO AMOUNT (move money). A transfer must be all-or-nothing: take AMOUNT from FROM and add it to TO inside one transaction. If FROM would go below 0, the transfer must be undone completely (rollback) and nothing changes. Process the lines in order and print the final balances as: name balance, sorted by name.
Constraints
- 1 <= lines <= 100
- balances and amounts are whole numbers from 0 to 10000
Examples
Input
acct ann 100 acct bob 50 transfer ann bob 30
Output
ann 70 bob 80
Solutions
A transaction groups statements so that they succeed or fail together. The program does both updates, looks at the result, and calls commit to keep it or rollback to undo it. Without a transaction, a crash between the two updates would lose money. JavaScript and TypeScript use the built-in node:sqlite module (Node.js 22.5 or newer), which prints an experimental warning. Python uses the sqlite3 module that comes with Python.
Java needs a JDBC driver at run time. Download sqlite-jdbc from Maven Central (org.xerial:sqlite-jdbc) and run: java -cp .:sqlite-jdbc.jar Main (on Windows use ; instead of :).
/*
* Problem : Run the commands.
* Input : Input lines.
* Output : Output lines.
* Approach: Check, then commit or rollback
* Time : O(n)
* Space : O(accounts)
* Java : 8 or newer.
*/
import java.util.Scanner;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
public class Main {
// This method solves the task. It receives the input and returns the answer.
static String[] solve(String[] lines) {
List<String> out = new ArrayList<String>();
try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
try (Statement create = conn.createStatement()) {
create.execute("CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER)");
}
// Step 1: By default every statement is saved at once (auto-commit). Turn it off to group statements into one transaction.
conn.setAutoCommit(false);
try (PreparedStatement open = conn.prepareStatement("INSERT INTO accounts (name, balance) VALUES (?, ?)");
PreparedStatement change = conn.prepareStatement("UPDATE accounts SET balance = balance + ? WHERE name = ?");
PreparedStatement read = conn.prepareStatement("SELECT balance FROM accounts WHERE name = ?")) {
for (String line : lines) {
String[] part = line.split(" ");
if (part[0].equals("acct")) {
open.setString(1, part[1]);
open.setInt(2, Integer.parseInt(part[2]));
open.executeUpdate();
conn.commit();
continue;
}
int amount = Integer.parseInt(part[3]);
// Step 2: Both updates belong to one transaction. Nothing is saved until commit.
change.setInt(1, -amount);
change.setString(2, part[1]);
change.executeUpdate();
change.setInt(1, amount);
change.setString(2, part[2]);
change.executeUpdate();
// Step 3: Check the sender balance inside the transaction. If it is negative, rollback undoes both updates.
read.setString(1, part[1]);
int left;
try (ResultSet rs = read.executeQuery()) {
rs.next();
left = rs.getInt(1);
}
if (left < 0) {
conn.rollback();
} else {
conn.commit();
}
}
}
try (Statement query = conn.createStatement(); ResultSet rs = query.executeQuery("SELECT name, balance FROM accounts ORDER BY name")) {
while (rs.next()) {
out.add(rs.getString(1) + " " + rs.getInt(2));
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
return out.toArray(new String[0]);
}
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
java.util.List<String> all = new java.util.ArrayList<>();
while (scanner.hasNextLine()) {
String line = scanner.nextLine().trim();
if (!line.isEmpty()) {
all.add(line);
}
}
String[] lines = all.toArray(new String[0]);
System.out.println(String.join("\n", solve(lines)));
}
}
Hints (2)
- What does auto-commit do?
- What does rollback undo?