SQLite Database Manager (java, written by Claude Code)

Claude Code wrote this java project from the task below. It installed and ran on a clean Ubuntu 22.04 machine as written.

Task given to the agent:

TASK: SQLite Database Manager

Write a program that provides a command-line interface for managing SQLite databases, supporting table creation, CRUD operations, schema inspection, data import/export, and query execution.

FUNCTIONAL REQUIREMENTS:
- Accept a database file path as a command-line argument (create it if it does not exist)
- Support creating tables with typed columns (TEXT, INTEGER, REAL, BLOB) and constraints (PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT)
- Support INSERT, SELECT, UPDATE, and DELETE operations via command-line subcommands
- Support executing arbitrary SQL queries via a --query flag
- Implement schema inspection: list all tables, show column details for a given table, show row counts
- Import data from a CSV file into a specified table via --import flag, auto-detecting column types
- Export a table or query result to CSV via --export flag
- Support basic transactions: --begin, --commit, --rollback flags for grouping operations
- Print query results as formatted tables to console with column headers and aligned values
- If no database path is given, create a sample database with at least three related tables (e.g., users, orders, products), populate with sample data, and run demonstration queries showing joins, aggregations, and filtering
- Handle database errors gracefully with clear messages distinguishing connection, schema, constraint, and query errors

Create a complete Java project for a clean Ubuntu 22.04 machine with only JDK 17+ installed. Include:
- Source code
- pom.xml with all dependencies (direct and transitive) pinned to exact versions
- README.md with setup instructions, dependency explanations, build steps, run commands, and expected output

[]

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.dbmanager</groupId>
    <artifactId>sqlite-db-manager-t1</artifactId>
    <version>1.0.0</version>
    <packaging>jar</packaging>

    <name>SQLite Database Manager - Trial 1</name>

    <properties>
        <maven.compiler.source>11</maven.compiler.source>
        <maven.compiler.target>11</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.xerial</groupId>
            <artifactId>sqlite-jdbc</artifactId>
            <version>3.44.1.0</version>
        </dependency>
        <dependency>
            <groupId>info.picocli</groupId>
            <artifactId>picocli</artifactId>
            <version>4.7.5</version>
        </dependency>
        <dependency>
            <groupId>com.google.code.gson</groupId>
            <artifactId>gson</artifactId>
            <version>2.10.1</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-jar-plugin</artifactId>
                <version>3.3.0</version>
                <configuration>
                    <archive>
                        <manifest>
                            <mainClass>dbmanager.DbManager</mainClass>
                        </manifest>
                    </archive>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-shade-plugin</artifactId>
                <version>3.5.1</version>
                <executions>
                    <execution>
                        <phase>package</phase>
                        <goals><goal>shade</goal></goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
</project>

# SQLite Database Manager - Java Trial 1

CLI for managing SQLite databases using JDBC, picocli, and Gson.

## Dependencies

- Java 11+
- sqlite-jdbc 3.44.1.0
- picocli 4.7.5
- gson 2.10.1

## Build

```bash
mvn clean package
```

## Usage

```bash
java -jar target/sqlite-db-manager-t1-1.0.0.jar --help

# Create a table
java -jar target/sqlite-db-manager-t1-1.0.0.jar create-table users id:INTEGER name:TEXT email:TEXT

# Insert a row
java -jar target/sqlite-db-manager-t1-1.0.0.jar insert users -c "name,email" -v "Alice,alice@example.com"

# Select rows
java -jar target/sqlite-db-manager-t1-1.0.0.jar select users
java -jar target/sqlite-db-manager-t1-1.0.0.jar select users -w "name='Alice'"

# Update rows
java -jar target/sqlite-db-manager-t1-1.0.0.jar update users --set "email='new@example.com'" -w "name='Alice'"

# Delete rows
java -jar target/sqlite-db-manager-t1-1.0.0.jar delete users -w "name='Alice'"

# Inspect schema
java -jar target/sqlite-db-manager-t1-1.0.0.jar schema
java -jar target/sqlite-db-manager-t1-1.0.0.jar schema users

# Import/Export CSV
java -jar target/sqlite-db-manager-t1-1.0.0.jar import-csv users data.csv
java -jar target/sqlite-db-manager-t1-1.0.0.jar export-csv users output.csv

# Execute raw SQL
java -jar target/sqlite-db-manager-t1-1.0.0.jar query "SELECT * FROM employees WHERE salary > ?" -p "80000"

# Use a specific database
java -jar target/sqlite-db-manager-t1-1.0.0.jar --db mydata.db select employees
```

package dbmanager;

import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import picocli.CommandLine;
import picocli.CommandLine.Command;
import picocli.CommandLine.Option;
import picocli.CommandLine.Parameters;

import java.io.*;
import java.sql.*;
import java.util.*;

/**
 * SQLite Database Manager CLI - Trial 1 (JDBC + picocli + Gson).
 */
@Command(name = "db-manager", mixinStandardHelpOptions = true, version = "1.0.0",
        description = "SQLite Database Manager - manage tables, data, and queries.",
        subcommands = {
                DbManager.CreateTableCmd.class,
                DbManager.InsertCmd.class,
                DbManager.SelectCmd.class,
                DbManager.UpdateCmd.class,
                DbManager.DeleteCmd.class,
                DbManager.SchemaCmd.class,
                DbManager.ImportCsvCmd.class,
                DbManager.ExportCsvCmd.class,
                DbManager.QueryCmd.class
        })
public class DbManager implements Runnable {

    @Option(names = {"--db"}, description = "Path to SQLite database file.", defaultValue = "sample.db", scope = CommandLine.ScopeType.INHERIT)
    String dbPath;

    public static void main(String[] args) {
        int exitCode = new CommandLine(new DbManager()).execute(args);
        System.exit(exitCode);
    }

    @Override
    public void run() {
        CommandLine.usage(this, System.out);
    }

    static Connection getConnection(String dbPath) throws SQLException {
        return DriverManager.getConnection("jdbc:sqlite:" + dbPath);
    }

    static void ensureDemoDb(String dbPath) {
        File file = new File(dbPath);
        if (file.exists()) return;
        try (Connection conn = getConnection(dbPath); Statement stmt = conn.createStatement()) {
            stmt.execute("CREATE TABLE IF NOT EXISTS employees (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    "name TEXT NOT NULL, " +
                    "department TEXT NOT NULL, " +
                    "salary REAL NOT NULL, " +
                    "hire_date TEXT NOT NULL)");
            String insertSql = "INSERT INTO employees (name, department, salary, hire_date) VALUES (?, ?, ?, ?)";
            PreparedStatement ps = conn.prepareStatement(insertSql);
            String[][] data = {
                    {"Alice Johnson", "Engineering", "95000.0", "2021-03-15"},
                    {"Bob Smith", "Marketing", "72000.0", "2020-07-01"},
                    {"Carol White", "Engineering", "102000.0", "2019-11-20"},
                    {"David Brown", "Sales", "68000.0", "2022-01-10"},
                    {"Eve Davis", "Marketing", "75000.0", "2021-09-05"}
            };
            for (String[] row : data) {
                ps.setString(1, row[0]);
                ps.setString(2, row[1]);
                ps.setDouble(3, Double.parseDouble(row[2]));
                ps.setString(4, row[3]);
                ps.executeUpdate();
            }
            System.out.println("Created demo database at " + dbPath);
        } catch (SQLException e) {
            System.err.println("Error creating demo database: " + e.getMessage());
        }
    }

    static void printTable(ResultSet rs) throws SQLException {
        ResultSetMetaData meta = rs.getMetaData();
        int colCount = meta.getColumnCount();
        List<String> headers = new ArrayList<>();
        for (int i = 1; i <= colCount; i++) {
            headers.add(meta.getColumnName(i));
        }
        List<List<String>> rows = new ArrayList<>();
        while (rs.next()) {
            List<String> row = new ArrayList<>();
            for (int i = 1; i <= colCount; i++) {
                String val = rs.getString(i);
                row.add(val == null ? "NULL" : val);
            }
            rows.add(row);
        }
        if (rows.isEmpty()) {
            System.out.println("No rows returned.");
            return;
        }

        // Calculate column widths
        int[] widths = new int[colCount];
        for (int i = 0; i < colCount; i++) {
            widths[i] = headers.get(i).length();
        }
        for (List<String> row : rows) {
            for (int i = 0; i < colCount; i++) {
                widths[i] = Math.max(widths[i], row.get(i).length());
            }
        }

        // Print header separator
        StringBuilder sep = new StringBuilder("+");
        for (int w : widths) {
            sep.append("-".repeat(w + 2)).append("+");
        }
        System.out.println(sep);

        // Print headers
        StringBuilder headerLine = new StringBuilder("|");
        for (int i = 0; i < colCount; i++) {
            headerLine.append(String.format(" %-" + widths[i] + "s |", headers.get(i)));
        }
        System.out.println(headerLine);
        System.out.println(sep);

        // Print rows
        for (List<String> row : rows) {
            StringBuilder rowLine = new StringBuilder("|");
            for (int i = 0; i < colCount; i++) {
                rowLine.append(String.format(" %-" + widths[i] + "s |", row.get(i)));
            }
            System.out.println(rowLine);
        }
        System.out.println(sep);
        System.out.println("(" + rows.size() + " row(s))");
    }

    @Command(name = "create-table", description = "Create a new table.")
    static class CreateTableCmd implements Runnable {
        @CommandLine.ParentCommand DbManager parent;
        @Parameters(index = "0", description = "Table name.") String tableName;
        @Parameters(index = "1..*", description = "Column definitions: name:TYPE") String[] columns;

        @Override
        public void run() {
            ensureDemoDb(parent.dbPath);
            List<String> colDefs = new ArrayList<>();
            for (String col : columns) {
                String[] parts = col.split(":");
                if (parts.length != 2) {
                    System.err.println("Error: Invalid column definition '" + col + "'. Use name:TYPE format.");
                    return;
                }
                colDefs.add(parts[0] + " " + parts[1]);
            }
            String sql = "CREATE TABLE IF NOT EXISTS " + tableName + " (" + String.join(", ", colDefs) + ")";
            try (Connection conn = getConnection(parent.dbPath); Statement stmt = conn.createStatement()) {
                stmt.execute(sql);
                System.out.println("Table '" + tableName + "' created successfully.");
            } catch (SQLException e) {
                System.err.println("SQL Error: " + e.getMessage());
            }
        }
    }

    @Command(name = "insert", description = "Insert a row into a table.")
    static class InsertCmd implements Runnable {
        @CommandLine.ParentCommand DbManager parent;
        @Parameters(index = "0", description = "Table name.") String tableName;
        @Option(names = {"-v", "--values"}, required = true, description = "Comma-separated values.") String values;
        @Option(names = {"-c", "--columns"}, description = "Comma-separated column names.") String columns;

        @Override
        public void run() {
            ensureDemoDb(parent.dbPath);
            String[] valArr = values.split(",");
            String placeholders = String.join(", ", Collections.nCopies(valArr.length, "?"));
            String sql;
            if (columns != null && !columns.isEmpty()) {
                sql = "INSERT INTO " + tableName + " (" + columns + ") VALUES (" + placeholders + ")";
            } else {
                sql = "INSERT INTO " + tableName + " VALUES (" + placeholders + ")";
            }
            try (Connection conn = getConnection(parent.dbPath); PreparedStatement ps = conn.prepareStatement(sql)) {
                for (int i = 0; i < valArr.length; i++) {
                    ps.setString(i + 1, valArr[i].trim());
                }
                ps.executeUpdate();
                System.out.println("Row inserted into '" + tableName + "'.");
            } catch (SQLException e) {
                System.err.println("SQL Error: " + e.getMessage());
            }
        }
    }

    @Command(name = "select", description = "Select rows from a table.")
    static class SelectCmd implements Runnable {
        @CommandLine.ParentCommand DbManager parent;
        @Parameters(index = "0", description = "Table name.") String tableName;
        @Option(names = {"-c", "--columns"}, defaultValue = "*", description = "Columns to select.") String columns;
        @Option(names = {"-w", "--where"}, description = "WHERE clause.") String where;
        @Option(names = {"-o", "--order-by"}, description = "ORDER BY clause.") String orderBy;
        @Option(names = {"-l", "--limit"}, description = "Row limit.") Integer limit;

        @Override
        public void run() {
            ensureDemoDb(parent.dbPath);
            StringBuilder sql = new StringBuilder("SELECT " + columns + " FROM " + tableName);
            if (where != null) sql.append(" WHERE ").append(where);
            if (orderBy != null) sql.append(" ORDER BY ").append(orderBy);
            if (limit != null) sql.append(" LIMIT ").append(limit);
            try (Connection conn = getConnection(parent.dbPath);
                 Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(sql.toString())) {
                printTable(rs);
            } catch (SQLException e) {
                System.err.println("SQL Error: " + e.getMessage());
            }
        }
    }

    @Command(name = "update", description = "Update rows in a table.")
    static class UpdateCmd implements Runnable {
        @CommandLine.ParentCommand DbManager parent;
        @Parameters(index = "0", description = "Table name.") String tableName;
        @Option(names = {"--set"}, required = true, description = "SET clause.") String setClause;
        @Option(names = {"-w", "--where"}, required = true, description = "WHERE clause.") String where;

        @Override
        public void run() {
            ensureDemoDb(parent.dbPath);
            String sql = "UPDATE " + tableName + " SET " + setClause + " WHERE " + where;
            try (Connection conn = getConnection(parent.dbPath); Statement stmt = conn.createStatement()) {
                int affected = stmt.executeUpdate(sql);
                System.out.println(affected + " row(s) updated in '" + tableName + "'.");
            } catch (SQLException e) {
                System.err.println("SQL Error: " + e.getMessage());
            }
        }
    }

    @Command(name = "delete", description = "Delete rows from a table.")
    static class DeleteCmd implements Runnable {
        @CommandLine.ParentCommand DbManager parent;
        @Parameters(index = "0", description = "Table name.") String tableName;
        @Option(names = {"-w", "--where"}, required = true, description = "WHERE clause.") String where;

        @Override
        public void run() {
            ensureDemoDb(parent.dbPath);
            String sql = "DELETE FROM " + tableName + " WHERE " + where;
            try (Connection conn = getConnection(parent.dbPath); Statement stmt = conn.createStatement()) {
                int affected = stmt.executeUpdate(sql);
                System.out.println(affected + " row(s) deleted from '" + tableName + "'.");
            } catch (SQLException e) {
                System.err.println("SQL Error: " + e.getMessage());
            }
        }
    }

    @Command(name = "schema", description = "Inspect database schema.")
    static class SchemaCmd implements Runnable {
        @CommandLine.ParentCommand DbManager parent;
        @Parameters(index = "0", arity = "0..1", description = "Table name (optional).") String tableName;

        @Override
        public void run() {
            ensureDemoDb(parent.dbPath);
            try (Connection conn = getConnection(parent.dbPath); Statement stmt = conn.createStatement()) {
                if (tableName != null) {
                    ResultSet rs = stmt.executeQuery("PRAGMA table_info(" + tableName + ")");
                    System.out.println("\nSchema for table '" + tableName + "':");
                    printTable(rs);
                } else {
                    ResultSet rs = stmt.executeQuery("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name");
                    System.out.println("\nTables in database:");
                    List<String> tables = new ArrayList<>();
                    while (rs.next()) {
                        tables.add(rs.getString("name"));
                    }
                    if (tables.isEmpty()) {
                        System.out.println("No tables found.");
                        return;
                    }
                    for (String t : tables) {
                        System.out.println("  - " + t);
                        ResultSet info = stmt.executeQuery("PRAGMA table_info(" + t + ")");
                        while (info.next()) {
                            String name = info.getString("name");
                            String type = info.getString("type");
                            boolean notNull = info.getInt("notnull") == 1;
                            boolean pk = info.getInt("pk") == 1;
                            System.out.printf("      %s %s %s%s%n", name, type,
                                    notNull ? "NOT NULL" : "NULLABLE",
                                    pk ? " PRIMARY KEY" : "");
                        }
                    }
                }
            } catch (SQLException e) {
                System.err.println("SQL Error: " + e.getMessage());
            }
        }
    }

    @Command(name = "import-csv", description = "Import data from a CSV file.")
    static class ImportCsvCmd implements Runnable {
        @CommandLine.ParentCommand DbManager parent;
        @Parameters(index = "0", description = "Table name.") String tableName;
        @Parameters(index = "1", description = "CSV file path.") String csvFile;
        @Option(names = {"--no-create"}, description = "Don't auto-create table.") boolean noCreate;

        @Override
        public void run() {
            ensureDemoDb(parent.dbPath);
            try (BufferedReader br = new BufferedReader(new FileReader(csvFile))) {
                String headerLine = br.readLine();
                if (headerLine == null) {
                    System.err.println("Error: CSV file is empty.");
                    return;
                }
                String[] headers = headerLine.split(",");
                for (int i = 0; i < headers.length; i++) headers[i] = headers[i].trim();

                Connection conn = getConnection(parent.dbPath);
                if (!noCreate) {
                    StringBuilder colDefs = new StringBuilder();
                    for (int i = 0; i < headers.length; i++) {
                        if (i > 0) colDefs.append(", ");
                        colDefs.append(headers[i]).append(" TEXT");
                    }
                    conn.createStatement().execute("CREATE TABLE IF NOT EXISTS " + tableName + " (" + colDefs + ")");
                }

                String placeholders = String.join(", ", Collections.nCopies(headers.length, "?"));
                String colNames = String.join(", ", headers);
                String sql = "INSERT INTO " + tableName + " (" + colNames + ") VALUES (" + placeholders + ")";
                PreparedStatement ps = conn.prepareStatement(sql);

                int count = 0;
                String line;
                while ((line = br.readLine()) != null) {
                    String[] vals = line.split(",", -1);
                    for (int i = 0; i < vals.length; i++) {
                        ps.setString(i + 1, vals[i].trim());
                    }
                    ps.executeUpdate();
                    count++;
                }
                conn.close();
                System.out.println("Imported " + count + " row(s) into '" + tableName + "' from '" + csvFile + "'.");
            } catch (Exception e) {
                System.err.println("Error: " + e.getMessage());
            }
        }
    }

    @Command(name = "export-csv", description = "Export a table to a CSV file.")
    static class ExportCsvCmd implements Runnable {
        @CommandLine.ParentCommand DbManager parent;
        @Parameters(index = "0", description = "Table name.") String tableName;
        @Parameters(index = "1", description = "Output CSV file path.") String csvFile;

        @Override
        public void run() {
            ensureDemoDb(parent.dbPath);
            try (Connection conn = getConnection(parent.dbPath);
                 Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName)) {

                ResultSetMetaData meta = rs.getMetaData();
                int colCount = meta.getColumnCount();
                List<String> headers = new ArrayList<>();
                for (int i = 1; i <= colCount; i++) {
                    headers.add(meta.getColumnName(i));
                }

                try (PrintWriter pw = new PrintWriter(new FileWriter(csvFile))) {
                    pw.println(String.join(",", headers));
                    int count = 0;
                    while (rs.next()) {
                        List<String> vals = new ArrayList<>();
                        for (int i = 1; i <= colCount; i++) {
                            String val = rs.getString(i);
                            vals.add(val == null ? "" : val);
                        }
                        pw.println(String.join(",", vals));
                        count++;
                    }
                    System.out.println("Exported " + count + " row(s) from '" + tableName + "' to '" + csvFile + "'.");
                }
            } catch (Exception e) {
                System.err.println("Error: " + e.getMessage());
            }
        }
    }

    @Command(name = "query", description = "Execute a raw SQL query.")
    static class QueryCmd implements Runnable {
        @CommandLine.ParentCommand DbManager parent;
        @Parameters(index = "0", description = "SQL query string.") String sql;
        @Option(names = {"-p", "--params"}, description = "Comma-separated parameters.") String params;

        @Override
        public void run() {
            ensureDemoDb(parent.dbPath);
            try (Connection conn = getConnection(parent.dbPath)) {
                if (params != null && !params.isEmpty()) {
                    String[] paramArr = params.split(",");
                    PreparedStatement ps = conn.prepareStatement(sql);
                    for (int i = 0; i < paramArr.length; i++) {
                        ps.setString(i + 1, paramArr[i].trim());
                    }
                    if (sql.trim().toUpperCase().startsWith("SELECT") || sql.trim().toUpperCase().startsWith("PRAGMA")) {
                        ResultSet rs = ps.executeQuery();
                        printTable(rs);
                    } else {
                        int affected = ps.executeUpdate();
                        System.out.println("Query executed. " + affected + " row(s) affected.");
                    }
                } else {
                    Statement stmt = conn.createStatement();
                    if (sql.trim().toUpperCase().startsWith("SELECT") || sql.trim().toUpperCase().startsWith("PRAGMA")) {
                        ResultSet rs = stmt.executeQuery(sql);
                        printTable(rs);
                    } else {
                        int affected = stmt.executeUpdate(sql);
                        System.out.println("Query executed. " + affected + " row(s) affected.");
                    }
                }
            } catch (SQLException e) {
                System.err.println("SQL Error: " + e.getMessage());
            }
        }
    }
}