jdbc-fn

Functional style programming over plain JDBC.

Execute SQL SELECT query and process results with Java Stream API.
Fire SQL UPDATE(S) batch(es) in a single line of code.
Invoke stored procedures and retrieve its results without dealing with SQLExceptions
Execute parameterized scripts.

And more…

Getting Started

Add maven dependency:

<dependency>
  <groupId>com.github.buckelieg</groupId>
  <artifactId>jdbc-fn</artifactId>
  <version>0.3</version>
</dependency>

Setup database

There are a couple of ways to set up the things:

// 1. Provide DataSource
DataSource ds = // obtain ds (e.g. via JNDI or other way) 
DB db = new DB(ds);
// 2. Provide connection supplier
DB db = new DB(ds::getConnection);
// or
DB db = new DB(() -> {/*sophisticated connection supplier function*/});
// do things...
db.close();
// DB can be used in try-with-resources statements
try (DB db = new DB(/*init*/)) {
    ...
} finally {
    
}

Note that providing connection supplier function with plain connection
like this: DB db = new DB(() -> connection));
or this: DB db = new DB(() -> DriverManager.getConnection("vendor-specific-string"));
e.g - if supplier function always return the same connection
the concept of transactions will be partially broken: see Transactions section.

Select

Use question marks:

Collection<String> names = db.select("SELECT name FROM TEST WHERE ID IN (?, ?)", 1, 2).execute(rs -> rs.getString("name")).collect(Collectors.toList());
// an alias for execute method is stream - for better readability
Collection<String> names = db.select("SELECT name FROM TEST WHERE ID IN (?, ?)", 1, 2).stream(rs -> rs.getString("name")).collect(Collectors.toList());
// or use shorthands for stream reduction
Collection<String> names = db.select("SELECT name FROM TEST WHERE ID IN (?, ?)", 1, 2).list(rs -> rs.getString("name"));

or use named parameters:

// in java9+
import static java.util.Map.of;
Collection<String> names = db.select("SELECT name FROM TEST WHERE 1=1 AND ID IN (:ID) OR NAME=:name", of(":ID", new Object[]{1, 2}, ":name", "name_5")).execute(rs -> rs.getString("name"))
        .reduce(
                new LinkedList<>(),
                (list, name) -> {
                    list.add(name);
                    return list;
                },
                (l1, l2) -> {
                  l1.addAll(l2);
                  return l1;
                }
        );

Parameter names are CASE SENSITIVE! ‘Name’ and ‘name’ are considered different parameter names.
Parameters may be provided with or without leading colon.

Insert

with question marks:

long res = db.update("INSERT INTO TEST(name) VALUES(?)", "New_Name").execute();

Or with named parameters:

long res = db.update("INSERT INTO TEST(name) VALUES(:name)", new SimpleImmutableEntry<>("name","New_Name")).execute();
// in java9+
import static java.util.Map.entry;
long res = db.update("INSERT INTO TEST(name) VALUES(:name)", entry("name","New_Name")).execute();

Update

long res = db.update("UPDATE TEST SET NAME=? WHERE NAME=?", "new_name_2", "name_2").execute();

long res = db.update("UPDATE TEST SET NAME=:name WHERE NAME=:new_name", 
  new SimpleImmutableEntry<>("name", "new_name_2"), 
  new SimpleImmutableEntry<>("new_name", "name_2")
).execute();
// in java9+
import static java.util.Map.entry;
long res = db.update("UPDATE TEST SET NAME=:name WHERE NAME=:new_name", entry(":name", "new_name_2"), entry(":new_name", "name_2")).execute();

Batch mode

For batch operation use:

long res = db.update("INSERT INTO TEST(name) VALUES(?)", new Object[][]{ {"name1"}, {"name2"} }).batch(true).execute();

Delete

long res = db.update("DELETE FROM TEST WHERE name=?", "name_2").execute();

Stored Procedures

Invoking stored procedures is also quite simple:

String name = db.procedure("{call GETNAMEBYID(?,?)}", P.in(12), P.out(JDBCType.VARCHAR)).call(cs -> cs.getString(2)).orElse("Unknown");

Note that in the latter case stored procedure must not return any result sets.
If stored procedure is considered to return result sets it is handled similar to regular selects (see above).

Scripts

There are two options to run an arbitrary SQL scripts:

1) Provide a script itself

db.script("CREATE TABLE TEST (id INTEGER NOT NULL, name VARCHAR(255));INSERT INTO TEST(id, name) VALUES(1, 'whatever');UPDATE TEST SET name = 'whatever_new' WHERE name = 'whatever';DROP TABLE TEST;").execute();

2) Provide a file with an SQL script

  db.script(new File("path/to/script.sql")).timeout(60).execute();

Script:
Can contain single- and multiline comments.
Each statement must be separated by a semicolon (“;”).
Execution results ignored and not handled after all.
Support named parameters
Support escaped syntax, so it is possible to include JDBC-like procedure call statements.

Transactions

There are a couple of methods provides transaction support.
Tell whether to create new transaction or not, provide isolation level and transaction logic function.

// suppose we have to insert a bunch of new users by name and get the latest one filled with its attributes....
User latestUser = db.transaction(TransactionIsolation.SERIALIZABLE, db1 ->
  // here db.equals(db1) will return true
  // but if we claim to create new transaction it will not, because a new connection is obtained and new DB instance is created
  // so everything inside a transaction (in this case) MUST be done through db1 reference since it will operate on newly created connection   
  db1.update("INSERT INTO users(name) VALUES(?)", new Object[][]{ {"name1"}, {"name2"}, {"name3"} })
    .skipWarnings(false)
    .timeout(1, TimeUnit.MINUTES)
    .print()
    .execute(
        rs -> rs.getLong(1),
        ids -> db1.select("SELECT * FROM users WHERE id=?", ids.peek(id -> db1.procedure("{call PROCESS_USER_CREATED_EVENT(?)}", id).call()).max(Comparator.comparing(i -> i)).orElse(-1L))
                 .print()
                 .single(rs -> {
                     User u = new User();
                     u.setId(rs.getLong("id"));
                     u.setName(rs.getString("name"));
                     //... fill other user's attributes...
                     return u;
                 })
    )
    .orElse(null)
);

As the rule of thumb: always use lambda parameter to do the things inside the transaction

Nested transactions

This must be used with care.
When calling transaction() method createNew flag (if set to true) implies obtaining new connection via DataSource or connection supplier function provided at the DB class initialization stage.
If provided connection supplier function will not return a new connection - then UnsupportedOperationException is thrown:

DB db = new DB(() -> connection);
db.transaction(TransactionIsolation.SERIALIZABLE, db1 -> db1.transaction(true, db2 -> ...))
// throws UnsupportedOperationException

Using nested transactions with various isolation levels may result in deadlocks:

DB db = new DB(datasourceInstance);
db.transaction(TransactionIsolation.READ_UNCOMMITED, db1 -> {
    // do inserts, updates etc...
    long someGeneratedId = ....
    return db1.transaction(true, TransactionIsolation.SERIALIZABLE, db2 -> db2.select("SELECT * FROM TEST WHERE id=?", someGeneratedId).list(rs -> rs.getString("name")));
});
// nested transaction will be done over newly obtained connection but will not able to complete or see the generated values before enclosing transaction is committed and will eventually fail

Whenever desired transaction isolation level is not supported by RDBMS the IllegalArgumentException is thrown.

Logging & Debugging

Convenient logging methods provided.

Logger LOG = ... // configure logger
db.select("SELECT * FROM TEST WHERE id=?", 7).print(LOG::debug).single(rs -> {/*map rs here*/});

The above will print a current query to provided logger with debug method.
All provided parameters will be substituted with corresponding values so this case will output:
SELECT * FROM TEST WHERE id=7
Calling print() without arguments will do the same with standard output.

Scripts logging

For Script query verbose() method can be used to track current script step execution.

db.script("SELECT * FROM TEST WHERE id=:id;DROP TABLE TEST", new SimpleImmutableEntry<>("id", 5)).verbose().execute();

This will print out to standard output two lines:
SELECT * FROM TEST WHERE id=5
DROP TABLE TEST
Each line will be appended to output at the moment of execution.
Calling print() on Script will print out the whole sql script with parameters substituted.
Custom logging handler may also be provided for both cases.

Helper: Queries

For cases when it is all about query execution on existing connection with no tuning, logging and other stuff the Queries helper class can be used:

Connection conn = ... // somewhere previously created connection
List<String> names = Queries.list(conn, rs -> rs.getString("name"), "SELECT name FROM TEST WHERE id IN (:ids)", new SimpleImmutableEntry("ids", new long[]{1, 2, 3}));

There are plenty of pre-defined cases implemented:
list - for list selection
single - for single object selection,
callForList - calling StoredProcedure which returns a ResultSet,
call - call a StoredProcedure either with results or without,
update - to execute various updates,
execute - to execute atomic queries and/or scripts

There is an option to set up the connection with helper class to reduce a number of method arguments:

Connection conn = ... // somewhere previously created connection
Queries.setConnection(conn);
// all subsequent calls will be done on connection set.
List<String> names = Queries.list(rs -> rs.getString("name"), "SELECT name FROM TEST WHERE id IN (:ids)", new SimpleImmutableEntry("ids", new long[]{1, 2, 3}));
List<String> names = Queries.callForList(rs -> rs.getString(1), "{call GETALLNAMES()}");

Note that connection must be closed explicitly after using Queries helper.

Built-in mappers

All Select query methods which takes a mapper function has a companion one without.
Calling that mapper-less methods will imply mapping a tuple as String alias to Object value:

// DB
DB db = new DB(datasourceInstance);
List<Map<String, Object>> = db.select("SELECT name FROM TEST").list();
// Queries
Connection conn = ... // somewhere previously created connection
Queries.setConnection(conn);
List<Map<String, Object>> names = Queries.list("SELECT name FROM TEST WHERE id IN (:ids)", new SimpleImmutableEntry("ids", new long[]{1, 2, 3}));

Prerequisites

Java8, Maven, Appropriate JDBC driver.

License

This project licensed under Apache License, Version 2.0 - see the LICENSE.md file for details

jdbc-fn

JDBC made easy

jdbc-fn

Getting Started

Setup database

Select

Insert

Update

Batch mode

Delete

Stored Procedures

Scripts

Transactions

Nested transactions

Logging & Debugging

Scripts logging

Helper: Queries

Built-in mappers

Prerequisites

License