Executing SQL DDL and DML Statements

The most common plain old SQL tasks are those that involve executing DDL statements (such as CREATE TABLE) or DML statements (such as INSERT INTO). Using Fluent JDBC, you can easily invoke single SQL statements, as well as SQL scripts.

• Executing a Single SQL Statement
• Executing an SQL Script
  • Support for PostgreSQL Dollar-Quoted String Constants
  • Using Auto-Commit During Script Execution
  • Ignoring Errors During Script Execution

Executing a Single SQL statement

You can easily execute a statement in a string literal.

jdbc.execute("CREATE TABLE person ( name VARCHAR(50) )");

You could also put a single statement into a file and execute it. The execute method accepts any SQLSource as input. For example, using ResourceSQLSource, we could execute a statement in a classpath resource:

jdbc.execute(ResourceSQLSource.with("classpath:createTable.sql");

Or in any file:

jdbc.execute(ResourceSQLSource.with("file:/path/to/some/file.sql");

When using the execute method with a SQLSource, only the first statement that appears in the file is executed. If you want to execute a sequence of statements (i.e. a script), read on.

Executing an SQL Script

Of course, when doing tasks like database initialization or database migration, you often want to execute all of the statements in a file. You can do that using the executeScript method. This method takes a single SQLSource argument, that provides the SQL script to execute. Using ResourceSQLSource we could execute a classpath resource as a script:

jdbc.executeScript(ResourceSQLSource.with("classpath:db/createSchema.sql"));

Or any other file:

jdbc.executeScript(ResourceSQLSource.with("file:/path/to/createSchema.sql"));

Fluent JDBC can read SQL scripts for most database dialects. Simply terminate each statement with a semicolon (;). A script can contain single-line or block comments, too. For example:

-- create the PERSON table
CREATE TABLE person (id BIGINT, name VARCHAR(50), age INTEGER);

INSERT INTO PERSON(id, name, age)     -- first person
VALUES(1, 'Jennifer Wilson', 29);

INSERT INTO PERSON(id, name, age)     -- another person
VALUES(2, 'Nadine Bennett', 31);

/* this one is commented out for now
INSERT INTO PERSON(id, name, age)     -- excluded person
VALUES(3, 'Megan Marshall', 27);
*/

As of version 2, Fluent JDBC's executeScript supports statements with block constructs such as those used in defining stored procedures.

When executing a script, Fluent JDBC uses a single connection to execute every statement in the file. Moreover, you can configure the connection to utilize auto-commit mode, so that each statement in the script is committed on successful execution. This enables Fluent JDBC to support an execution mode in which all errors encountered while executing the script are ignored, which is useful in schema drop-create scenarios.

Support for PostgreSQL Dollar-Quoted String Constants

One extension to the SQL syntax supported by PostgreSQL is an alternate means of delimiting string constants known as "dollar quoting" as described in the PostgreSQL Lexical Structure documentation.

In order to successfully use PostgreSQL syntax in your scripts, you'll need to specify the PostgresScanner instance when creating an SQLSource. For example,

jdbc.executeScript(
    ResourceSQLSource.with("file:/path/to/postgresFunctionDefn.sql"), 
        PostgresScanner.INSTANCE));

Using Auto-Commit During Script Execution

Prior to version 1.2.0, Fluent JDBC would execute scripts using auto-commit mode. In an managed transaction environment (such as in a Java EE application) this may cause errors. Fluent JDBC version 1.2.0 and later provides an auto-commit flag as a property of a FluentJdbc instance, allowing the developer to control auto-commit mode usage.

If you wish to execute scripts in auto-commit mode, set the flag as follows:

FluentJdbc jdbc = new FluentJdbc(dataSource);
jdbc.setAutoCommit(true);

NOTE: Fluent JDBC uses auto-commit mode only for script execution.

Ignoring Errors During Script Execution

Prior to version 1.2.0, Fluent JDBC provided an overload for the executeScript method that allowed an ignoreErrors flag to be set. Fluent JDBC version 1.2.0 and later now provides this as a property of a FluentJdbc instance. To ignore errors during script execution, set the flag as follows:

FluentJdbc jdbc = new FluentJdbc(dataSource);
jdbc.setIgnoreErrors(true);

Generally, ignoring errors is only effective when executing the script in auto-commit mode.