JDBC, SQLite and local transactions

JDBC is Java’s interface to relational databases. SQLite is a concrete local database. PreparedStatement separates SQL and values. A transaction connects several changes. PDF Batch Studio Desktop uses SQLite and Flyway for projects and history. This learning example first demonstrates JDBC without JavaFX so the database rule remains independent of an interface.

Example files

01Add the SQLite driver

Save pom.xml and Main.java in a new learning folder. The SQLite JDBC version comes from the checked desktop project. jdbc:sqlite:demo.sqlite uses a local file. No existing product database is opened.

pom.xml · xmlOpen file

<project xmlns="http://maven.apache.org/POM/4.0.0">
  <modelVersion>4.0.0</modelVersion>
  <groupId>example</groupId><artifactId>learning</artifactId><version>1.0</version>
  <properties><maven.compiler.release>21</maven.compiler.release><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding></properties>
  <dependencies><dependency><groupId>org.xerial</groupId><artifactId>sqlite-jdbc</artifactId><version>3.53.4.0</version></dependency></dependencies>
  <build><plugins>
    <plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-compiler-plugin</artifactId><version>3.14.1</version></plugin>
    <plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-surefire-plugin</artifactId><version>3.5.3</version></plugin>
  </plugins></build>
</project>

Run / check

mvn package dependency:copy-dependencies
java -cp "target/classes:target/dependency/*" tutorial.Main

02Write with parameters

CREATE TABLE describes a small projects table. INSERT has placeholders. setString binds values. ON CONFLICT updates the same learning key so repeated execution creates no duplicate record.

src/main/java/tutorial/Main.java · javaOpen file

package tutorial;
import java.sql.DriverManager;
public class Main {
  public static void main(String[] args) throws Exception {
    try (var connection = DriverManager.getConnection("jdbc:sqlite:demo.sqlite")) {
      try (var sql = connection.createStatement()) {
        sql.execute("CREATE TABLE IF NOT EXISTS projects(id TEXT PRIMARY KEY, title TEXT NOT NULL)");
      }
      connection.setAutoCommit(false);
      try (var insert = connection.prepareStatement("INSERT INTO projects VALUES (?, ?) ON CONFLICT(id) DO UPDATE SET title=excluded.title")) {
        insert.setString(1, "p1"); insert.setString(2, "Letters");
        insert.executeUpdate(); connection.commit();
      } catch (Exception error) {
        connection.rollback(); throw error;
      }
      try (var query = connection.prepareStatement("SELECT title FROM projects WHERE id=?")) {
        query.setString(1, "p1");
        try (var rows = query.executeQuery()) {
          if (rows.next()) System.out.println(rows.getString("title"));
        }
      }
    }
  }
}

03Understand commit and rollback

setAutoCommit(false) begins the controlled transaction phase. commit confirms changes made so far. rollback discards them on failure. try-with-resources closes the connection and statements.

04Move from creation to migration

CREATE TABLE IF NOT EXISTS does not change an existing table. Flyway runs versioned SQL files and records their state. The real StudioRepository initialises migrations before access. A new column needs a separate migration step that checks existing data.

Expected result

demo.sqlite persists. Record p1 has the title Letters. Repeating execution creates no duplicate.

Your exercise

Insert two records in one transaction and make the second fail with an invalid value.

Show the solution and explanation

Use different IDs and null for the second title. NOT NULL rejects it. After rollback, the first new record must also be absent. Check using a newly opened connection.

Project context and sources

The example is simplified for learning. The linked source version shows its use in the project and the checks applied there.

PDF Batch Studio