Java + JDBC

Generate Java code using standard JDBC APIs. Works with any JDBC-compatible database including SQLite, DuckDB, PostgreSQL, MySQL, and more.

Overview

Property	Value
Generator	java/sqlite, java/duckdb, or java/postgres
Runtime	JVM (Java 17+)
API Style	Synchronous
Driver	Any JDBC driver

Installation

Add SQG to your build process and include the appropriate JDBC driver:

# Install SQG globally
pnpm add -g @sqg/sqg

Gradle Dependencies

build.gradle

// SQLite
implementation 'org.xerial:sqlite-jdbc:3.51.1.0'

// DuckDB
implementation 'org.duckdb:duckdb_jdbc:1.4.2.0'

// PostgreSQL
implementation 'org.postgresql:postgresql:42.7.0'

Maven Dependencies

pom.xml

<!-- SQLite -->
<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.51.1.0</version>
</dependency>

<!-- DuckDB -->
<dependency>
    <groupId>org.duckdb</groupId>
    <artifactId>duckdb_jdbc</artifactId>
    <version>1.4.2.0</version>
</dependency>

<!-- PostgreSQL -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.0</version>
</dependency>

Example Configuration

sqg.yaml

version: 1
name: my-app

sql:
  - files:
      - queries.sql
    gen:
      - generator: java/sqlite  # or: java/duckdb, java/postgres
        output: ./src/main/java/com/myapp/db/
        config:
          package: com.myapp.db
          migrations: true  # generates applyMigrations() method

Config options:

• package - Java package name for generated classes
• migrations - When true, generates an applyMigrations() method with built-in tracking

Quick Start

1. Write your SQL

   Java JDBC Complete Example

   Full example for Java JDBC generator

   Try in Playground

   -- MIGRATE 1
   CREATE TABLE users (
     id INTEGER PRIMARY KEY,
     name TEXT NOT NULL,
     email TEXT UNIQUE,
     active INTEGER DEFAULT 1
   );
   
   -- QUERY all_users
   SELECT id, name, email, active FROM users ORDER BY name;
   
   -- QUERY get_user :one
   @set id = 1
   SELECT id, name, email, active FROM users WHERE id = ${id};
   
   -- QUERY find_active_users
   @set active = 1
   SELECT id, name, email FROM users WHERE active = ${active};
   
   -- EXEC create_user
   @set name = 'John Doe'
   @set email = '[email protected]'
   INSERT INTO users (name, email) VALUES (${name}, ${email});
   
   -- EXEC update_user
   @set id = 1
   @set name = 'Jane Doe'
   @set email = '[email protected]'
   UPDATE users SET name = ${name}, email = ${email} WHERE id = ${id};
   
   -- EXEC delete_user
   @set id = 1
   DELETE FROM users WHERE id = ${id};

2. Generate code

   sqg sqg.yaml

3. Use the generated code

   import com.myapp.db.MyApp;
   import java.sql.Connection;
   import java.sql.DriverManager;
   
   public class Main {
       public static void main(String[] args) throws Exception {
           // Connect to database
           Connection conn = DriverManager.getConnection("jdbc:sqlite:app.db");
   
           // Apply migrations (with built-in tracking)
           MyApp.applyMigrations(conn);
   
           // Create query instance
           MyApp queries = new MyApp(conn);
   
           // Insert data
           queries.createUser("Alice", "[email protected]");
           queries.createUser("Bob", "[email protected]");
   
           // Query data
           for (MyApp.AllUsersRow user : queries.allUsers()) {
               System.out.println(user.name() + ": " + user.email());
           }
   
           // Get single row
           MyApp.GetUserRow user = queries.getUser(1);
           if (user != null) {
               System.out.println("Found: " + user.name());
           }
   
           // Update data
           queries.updateUser(1, "Alice Smith", "[email protected]");
   
           // Delete data
           queries.deleteUser(2);
   
           conn.close();
       }
   }

Generated Code Structure

The generator creates a single Java file with:

• Record types for each query result
• A class with methods for each query/exec
• Static getMigrations() method returning migration SQL strings
• Static applyMigrations() method (when config.migrations: true) that tracks and applies migrations automatically

Generated Java

From: Java JDBC Complete Example

See full code in Playground

// ...

    public List<AllUsersResult> allUsers() throws SQLException {
        try (
            var stmt = connection.prepareStatement(
                "SELECT id, name, email, active FROM users ORDER BY name;"
            )
        ) {
            try (var rs = stmt.executeQuery()) {
                var results = new ArrayList<AllUsersResult>();
                while (rs.next()) {
                    results.add(
                        new AllUsersResult(
                            (Integer) rs.getObject(1),
                            (String) rs.getObject(2),
                            (String) rs.getObject(3),
                            (Integer) rs.getObject(4)
                        )
                    );
                }
                return results;
            }
        }
    }


// ...

Type Mapping

SQL Type	Java Type
INTEGER, INT	Integer
BIGINT	Long
REAL, DOUBLE	Double
FLOAT	Float
TEXT, VARCHAR	String
BLOB	byte[]
BOOLEAN	Boolean
DATE	LocalDate
TIMESTAMP	LocalDateTime
TIMESTAMPTZ	OffsetDateTime
TIME	LocalTime
DECIMAL, NUMERIC	BigDecimal
UUID	UUID
TEXT[], INTEGER[]	List<String>, List<Integer>
ENUM	String

All types are nullable (using wrapper classes).

PostgreSQL

SQG supports PostgreSQL via the java/postgres generator. PostgreSQL-specific features include:

Setup

PostgreSQL requires a running server for type introspection. Provide the connection string via environment variable:

export SQG_POSTGRES_URL="postgresql://user:password@localhost:5432/mydb"

If SQG_POSTGRES_URL is not set, SQG will automatically start a PostgreSQL container using Testcontainers (requires Docker).

User-Defined Types (ENUMs)

SQG introspects PostgreSQL’s pg_type catalog to resolve user-defined types like ENUMs:

-- MIGRATE 1
CREATE TYPE task_status AS ENUM ('pending', 'active', 'completed', 'cancelled');

CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    status task_status DEFAULT 'pending'
);

-- QUERY get_tasks_by_status
@set status = 'active'
SELECT id, title, status FROM tasks WHERE status = ${status}::task_status;

ENUM values are mapped to String in Java.

Array Types

PostgreSQL arrays are mapped to Java List<T>:

CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    tags TEXT[],
    priority_scores INTEGER[]
);

// Generated record
record GetAllTasksResult(Integer id, String title, List<String> tags, List<Integer> priorityScores) {}

// Usage
for (var task : queries.getAllTasks()) {
    System.out.println(task.tags());            // [urgent, backend]
    System.out.println(task.priorityScores());  // [10, 20, 30]
}

TIMESTAMPTZ Support

PostgreSQL TIMESTAMPTZ columns are mapped to OffsetDateTime (with UTC offset):

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

// Generated type uses OffsetDateTime
record EventResult(Integer id, OffsetDateTime createdAt) {}