How To Work Effectively With JDBC in Java Scripts

I would like to to introduce you a Java class with less than 170 lines of code to facilitate work with SQL queries called via the JDBC API. What makes this solution interesting? The class can be embedded in a Java version 17 script.

Using a Java Script

The advantage of a Java script is easy portability in text format and the possibility of running without prior compilation, while we have considerable resources available from the language's standard library at runtime. The use of scripts is offered for various prototypes, in which even more complicated data exports or data conversions can be solved (after connecting to the database). Scripts are useful wherever we don't want to (or can't) put the implementation into a standard Java project.

However, the use of the script has some limitations. For example, the code must be written in a single file. We can include all the necessary libraries when we run the script, but these will likely have additional dependencies, and simply listing them on the command line can be frustrating. The complications associated with the distribution of such a script probably do not need to be emphasized. For the above reasons, I believe that external libraries in scripts are best avoided. If we still want to go the script route, the choice falls on pure JDBC. Multi-line text literals can be advantageously used for writing SQL queries, and the automatic closing of objects like PreparedStatement (implementing the interface AutoCloseable). So what's the problem?

Mapping SQL Parameter Values

For security reasons, it is advisable to map SQL parameter values to question marks. I consider the main handicap of JDBC to be the mapping of parameters using the sequence number of the question mark (starting with one). The first version of the parameter mapping to the SQL script often turns out well, but the risk of error increases as the number of parameters and additional SQL modifications increase. I remind you that by inserting a new parameter in the first position, the following row must be renumbered. 

Another complication is the use of the operator IN because for each value of the enumeration, a question mark must be written in the SQL template which must be mapped to a separate parameter. If the parameter list is dynamic, the list of question marks in the SQL template must also be dynamic. Debugging a larger number of more complex SQLs can start to take a significant amount of time.

For inserting SQL parameters using String Templates we will have to wait a little longer. However, inserting SQL parameters could be facilitated by a simple wrapper over the interfacePreparedStatement, which would (before calling the SQL statement) append the parameters using JPA-style named tags (alphanumeric text starting with a colon). A wrapper could also simplify reading data from the database (with a SELECT statement) if it allowed the necessary methods to be chained into a single statement, preferably with a return type Stream<ResultSet>.

SqlParamBuilder Class

Visualization of the SQL command with attached parameters would sometimes be useful for debugging or logging the SQL query. I present to you the class SqlParamBuilder. The priority of the implementation was to cover the stated requirements with a single Java class with minimalistic code. The programming interface was inspired by the library JDBI. The samples use the H2 database in in-memory mode. However, connecting the database driver will be necessary.

void mainStart(Connection dbConnection) throws Exception {
    try (var builder = new SqlParamBuilder(dbConnection)) {
        System.out.println("# CREATE TABLE");
        builder.sql("""
                        CREATE TABLE employee
                        ( id INTEGER PRIMARY KEY
                        , name VARCHAR(256) DEFAULT 'test'
                        , code VARCHAR(1)
                        , created DATE NOT NULL )
                        """)
                .execute();

        System.out.println("# SINGLE INSERT");
        builder.sql("""
                        INSERT INTO employee
                        ( id, code, created ) VALUES
                        ( :id, :code, :created )
                        """)
                .bind("id", 1)
                .bind("code", "T")
                .bind("created", someDate)
                .execute();

        System.out.println("# MULTI INSERT");
        builder.sql("""
                        INSERT INTO employee
                        (id,code,created) VALUES
                        (:id1,:code,:created),
                        (:id2,:code,:created)
                        """)
                .bind("id1", 2)
                .bind("id2", 3)
                .bind("code", "T")
                .bind("created", someDate.plusDays(7))
                .execute();
        builder.bind("id1", 11)
                .bind("id2", 12)
                .bind("code", "V")
                .execute();

        System.out.println("# SELECT");
        List<Employee> employees = builder.sql("""
                        SELECT t.id, t.name, t.created
                        FROM employee t
                        WHERE t.id < :id
                          AND t.code IN (:code)
                        ORDER BY t.id
                        """)
                .bind("id", 10)
                .bind("code", "T", "V")
                .streamMap(rs -> new Employee(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getObject("created", LocalDate.class)))
                .toList();

        System.out.printf("# PRINT RESULT OF: %s%n", builder.toStringLine());
        employees.stream()
                 .forEach((Employee employee) -> System.out.println(employee));
        assertEquals(3, employees.size());
        assertEquals(1, employees.get(0).id);
        assertEquals("test", employees.get(0).name);
        assertEquals(someDate, employees.get(0).created);
    }
}

record Employee (int id, String name, LocalDate created) {}
static class SqlParamBuilder {…}

Usage Notes and Final Thoughts

An instance of the type SqlParamBuilder can be recycled for multiple SQL statements. After calling the command, the parameters can be changed and the command can be run again. The parameters are assigned to the last used object PreparedStatement.

• Method sql() automatically closes the internal object PrepradedStatement (if there was one open before).
• If we change the group of parameters (typically for the IN operator), we need to send the same number for the same PreparedStatement. Otherwise, the method againsql() will need to be used.
• An object is required after the last command execution to explicitly close the SqlParamBuilder. However, since we are implementing an interface AutoCloseable, just enclose the entire block in a try block. Closing does not affect the contained database connection.
• In the Bash shell, the sample can be run with a script SqlExecutor.sh, which can download the necessary JDBC driver (here, for the H2 database).
• If we prefer Kotlin, we can try a Bash script SqlExecutorKt.sh, which migrates the prepared Kotlin code to a script and runs it.
• Let's not get confused by the fact that the class is stored in a Maven-type project. One reason is the ease of running JUnit tests.
• The class is licensed under the Apache License, Version 2.0.

Probably the fastest way to create your own implementation is to download the example script, redesign the method mainRun(), and modify the connection parameters to your own database. Use your own JDBC driver to run.