Java: MySQL Connector/J

Connects, creates a todo table if it does not exist, inserts a row and counts open rows. The code is the same against the original database and against MachSQL; only the address changes.

The only change

JDBC_URL=jdbc:mysql://old-mysql:3306/app
JDBC_URL=jdbc:mysql://machsql-host:3306/app

Nothing else changes: not the code, not the driver, not its version, not the SQL.

Versions

• Java 25 (LTS)
• MySQL Connector/J 26.10.0
• Maven (compiler plugin 3.16.0, exec plugin 3.6.4)

Run

export JDBC_URL=jdbc:mysql://machsql-host:3306/app DB_USER=app DB_PASSWORD=...
mvn -q compile exec:java

Expected output: inserted todo <id>, <n> open.

Source

The program as it is kept and tested in the Mach Database for PostgreSQL repository; highlighted lines read the connection setting.

src/main/java/example/Main.java

package example;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/** JDBC with MySQL Connector/J. */
public final class Main {
    public static void main(String[] args) throws SQLException {
        String url = System.getenv("JDBC_URL");
        try (Connection conn = DriverManager.getConnection(url, System.getenv("DB_USER"), System.getenv("DB_PASSWORD"))) {
            try (Statement st = conn.createStatement()) {
                st.execute("CREATE TABLE IF NOT EXISTS todo (id BIGINT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200) NOT NULL, done BOOLEAN NOT NULL DEFAULT FALSE)");
            }
            long id;
            try (PreparedStatement ps = conn.prepareStatement("INSERT INTO todo (title) VALUES (?)", Statement.RETURN_GENERATED_KEYS)) {
                ps.setString(1, "write the examples");
                ps.executeUpdate();
                try (ResultSet keys = ps.getGeneratedKeys()) {
                    keys.next();
                    id = keys.getLong(1);
                }
            }
            long open;
            try (Statement st = conn.createStatement(); ResultSet rs = st.executeQuery("SELECT count(*) FROM todo WHERE NOT done")) {
                rs.next();
                open = rs.getLong(1);
            }
            System.out.printf("inserted todo %d, %d open%n", id, open);
        }
    }
}

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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>example.machsql</groupId>
  <artifactId>java-mysql</artifactId>
  <version>1.0.0</version>

  <properties>
    <maven.compiler.release>25</maven.compiler.release>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>

  <dependencies>
    <dependency>
      <groupId>com.mysql</groupId>
      <artifactId>mysql-connector-j</artifactId>
      <version>26.10.0</version>
    </dependency>
  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.16.0</version>
      </plugin>
      <plugin>
        <groupId>org.codehaus.mojo</groupId>
        <artifactId>exec-maven-plugin</artifactId>
        <version>3.6.4</version>
        <configuration>
          <mainClass>example.Main</mainClass>
        </configuration>
      </plugin>
    </plugins>
  </build>
</project>