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>1.0</version>
</dependency>

Setup database

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

DataSource ds = ... // obtain ds (e.g. via JNDI or other way)
DB db = DB.create(ds::getConnection); // shortcut for DB.builder().build(ds::getConnection)
// or
DB db = DB.builder()
          .withMaxConnections(10) // defaults to Runtime.getRuntime().availableProcessors()
          .build(() -> DriverManager.getConnection("vendor-specific-string"));
// do things...
db.close(); // cleaning used resources: closes underlying connection pool, executor service (if configured to do so) etc...

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());

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.

The N+1 problem resolution

For the cases when it is needed to process (say - enrich) each mapped row with an additional data the Select.ForBatch can be used

Stream<Entity> entities = db.select("SELECT * FROM HUGE_TABLE")
        .forBatch(/* map resultSet here to needed type*/)
        .size(1000)
        .execute(batchOfObjects -> {
		  // list of mapped rows with size not more than 1000
		  batchOfObjects.forEach(obj -> obj.setSomethingElse());
        });

For cases where it is needed to issue any additional queries to database use:

// suppose the USERS table contains thousands of records
Stream<User> users = db.select("SELECT * FROM USERS")
    .forBatch(rs -> new User(rs.getLong("id"), rs.getString("name")))
    .size(1000)
    .execute((batchOfUsers, session) -> {
	  Map<Long, UserAttr> attrs = session.select(
		"SELECT * FROM USER_ATTR WHERE id IN (:ids)",
                entry("ids", batchOfUsers.stream().map(User::getId).collect(Collectors.toList()))
          ).execute(rs -> {
			UserAttr attr = new UserAttr();
			attr.setId(rs.getLong("attr_id"));
			attr.setUserId(rs.getLong("user_id"));
			attr.setName(rs.getString("attr_name"));
			// etc...
			return attr;
		  })
          .groupingBy(UserAttr::userId, Function.identity());
	  batchOfUsers.forEach(user -> user.addAttrs(attrs.getOrDefault(user.getId(), Collections.emptyList())));
	});
// stream of users objects will consist of updated (enriched) objects

Using this to process batches you must keep some things in mind:

• Executor service is used internally to power parallel processing
• All batches are processed regardless any possible short circuits
• Select.fetchSize and Select.ForBatch.size are not the same but connected

Metadata processing

For the special cases when only a metadata of the query is needed Select.forMeta can be used:

// suppose we want to collect information of which column of the provided query is a primary key
Map<String, Boolean> processedMeta = db.select("SELECT * FROM TEST").forMeta(metadata -> {
  Map<String, Boolean> map = new HashMap<>();
  metadata.forEachColumn(columnIndex -> map.put(metadata.getName(columnIndex), metadata.isPrimaryKey(columnIndex)));
  return map;
});

Insert

with question marks:

// res is an affected rows count
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+
long res = db.update("INSERT INTO TEST(name) VALUES(:name)", Map.entry("name","New_Name")).execute();

Getting generated keys

To retrieve possible generated keys provide a mapping function to execute method:

Collection<Long> generatedIds = db.update("INSERT INTO TEST(name) VALUES(?)", "New_Name").execute(rs -> rs.getLong(1));

See docs for more options.

Update

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

or

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+
long res = db.update("UPDATE TEST SET NAME=:name WHERE NAME=:new_name", Map.entry(":name", "new_name_2"), Map.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(2).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:

• 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();

• 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

Long story short - an example:

// suppose we have to insert a bunch of new users by name and get the latest one filled with its attributes....

Logger LOG = getLogger(); //... logger used in application 
User latestUser = db.transaction()
  .isolation(Transaction.Isolation.SERIALIZABLE)
  .execute(session ->
      session.update("INSERT INTO users(name) VALUES(?)", new Object[][]{ {"name1"}, {"name2"}, {"name3"} })
        .skipWarnings(false)
        .timeout(1, TimeUnit.MINUTES)
        .print(LOG::debug)
        .execute(rs -> rs.getLong(1))
        .stream()
        .peek(id -> session.procedure("{call PROCESS_USER_CREATED_EVENT(?)}", id).call())
        .max(Comparator.comparing(i -> i))
        .flatMap(id -> session.select("SELECT * FROM users WHERE id=?", id).print(LOG::debug).single(rs -> {
		  User u = new User();
		  u.setId(rs.getLong("id"));
		  u.setName(rs.getString("name"));
		  // ...fill other user's attributes...
		  return user;
        }))
        .orElse(null)
);

Nested transactions and deadlocks

Providing connection supplier function with plain connection
like this: DB db = DB.create(() -> connection));
or this:   DB db = DB.builder().withMaxConnections(1).build(() -> DriverManager.getConnection("vendor-specific-string"));
e.g - if supplier function always return the same connection
the concept of transactions will be partially broken.

The simplest case:

DB db = DB.create(() -> connection); // or DB.builder().withMaxConnections(1).build(ds::getConnection)
db.transaction().run(session1 -> db.transaction().run(session2 -> {}))
// runs forever since each transaction tries to obtain new connection and the second one cannot be provided with new one

Logging & Debugging

Convenient logging methods provided.

Logger LOG = // ... 
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.

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 to a tuple as String alias to Object value:

List<Map<String, Object>> = db.select("SELECT name FROM TEST").execute().collect(Collectors.toList());

Prerequisites

Java8, Maven, Appropriate JDBC driver.

License

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