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

The config.package option specifies the Java package name for generated classes.

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");
   
           // Run migrations
           for (String migration : MyApp.getMigrations()) {
               conn.createStatement().execute(migration);
           }
   
           // 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

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
TIME	LocalTime
DECIMAL, NUMERIC	BigDecimal
UUID	UUID

All types are nullable (using wrapper classes).