JDBX User Guide
- Intro
- Statements
- Running SQL queries
- Running DML or DDL updates
- Execute arbitrary SQL commands
- Run batches
- Exceptons
- Run a single command
- Handling multiple statements
- More Examples
JDBX offers a JDBC alternative to execute SQL commands and read query or update results.
For that it replaces JDBC Statement and ResultSet classes with an own API.
Still the starting point of all its operations is a java.sql.Connection or javax.sql.DataSource object.
Statements are used to execute SQL commands. JDBX provides three alternative statement classes to replace the corresponding JDBC classes. Implementation-wise the JDBX statements wrap their JDBC counterparts:
| JDBC | JDBX | Used to |
|---|---|---|
java.sql.Statement |
org.jdbx.StaticStmt |
execute static, non-parameterized SQL commands |
java.sql.PreparedStatement |
org.jdbx.PrepStmt |
execute precompiled, parameterized SQL commands |
java.sql.CallableStatement |
org.jdbx.CallStmt |
call stored procedures |
JDBX - as JDBC - differentiates between
- Running a SQL SELECT query, returning a result.
- Running a SQL update command (INSERT, UPDATE, DELETE or DDL command or other non SELECT command), returning an update count and generated column values.
- Running a SQL command whose result type is unknown or which can return multiple query and/or update results.
- Running SQL commands in a batch.
- Calling stored procedures, returning values of OUT parameters and/or query results.
StaticStmt and PrepStmt can be used to run commands as described in (1-4), CallStmt can call stored procedures (5).
In order to create a JDBX statement you need a java.sql.Connection or javax.sql.DataSource:
Connection con = ...
DataSource ds = ...
StaticStmt stmt = new StaticStmt(con); // or new StaticStmt(ds)
PrepStmt pstmt = new PrepStmt(con); // or new PrepStmt(ds)
CallStmt cstmt = new CallStmt(con); // or new CallStmt(ds)
Statement objects should be actively closed once they are no longer used. Since all JDBX statement classes implement java.io.AutoCloseable
the typical pattern is to create and use a statement object within a try-with-resources block:
Connection con = ...
try (StaticStmt stmt = new StaticStmt(con)) {
... // use the statement
}
Statements created from a DataSource will use a connection obtained from the DataSource. When the statement is closed that
connection will also be closed automatically.
You need to initialize PrepStmt and CallStmt by calling its init(String) method with a SQL command before you can execute the statement.
The SQL command string uses ? as placeholder for positional parameters:
PrepStmt pstmt = new PrepStmt(con);
pstmt.init("INSERT INTO Users VALUES (DEFAULT, ?, ?)");
CallStmt cstmt = new CallStmt(con);
cstmt.init("{call getUserName(?, ?)}");
PrepStmt allows for more initialization options: Calling init() on a PrepStmt will return an initialization builder.
The builder allows you to define the returned columns or if the sql statement uses named parameters.
The terminal call of the .sql(String) method to specify a SQL command is mandatory in order to complete the initialization:
// instruct the statement to return the value of the 'id' column (see the chapter on running updates)
pstmt.init().returnCols("id").sql("INSERT INTO Users VALUES (DEFAULT, ?, ?)");
// use named parameters instead of numbered parameters
pstmt.init().namedParams().sql("UPDATE Users SET name = :name WHERE id = :id");
A StaticStmt is already initialized when it is created - the SQL command which is executed by the StaticStmt
is not precompiled and passed to the statement when you run a query or update.
Implementation-wise initialization of a JDBX statement is the equivalent of creating a JDBC statement You may reinitialize a JDBX statement at any time which internally will recreate a new JDBC statement object.
To set or retrieve statement options use the builder returned by the options() method of the statement. JDBX also
introduces proper enums for result type, concurreny, fetch direction and holdability.
stmt.options()
.setQueryTimeoutSeconds(20)
.setFetchRows(5000)
.setResultType(ResultType.SCROLL_INSENSITIVE);
int timeoutSecs = stmt.options().getQueryTimeoutSeconds();
The SQL command string of a PrepStmtand CallStmt can (or should) contain positional parameters, specified as ? within the SQL string:
PrepStmt pstmt = ...
pstmt.init("INSERT INTO Rooms (id, name, area) VALUES (DEFAULT, ?, ?)");
Before running the command you need to provide parameter values. Parameters are referred to sequentially by number, with the first parameter being 1:
pstmt.param(1).setString("Living Room");
pstmt.param(2).setInt(51);
The builder returned by param(int) provides setters for various types. In most cases the JDBC driver
is able to recognize the type, so you can skip the explicit setter and simply pass parameters as objects:
pstmt.param(1, "Living Room").param(2, 51);
or even shorter, setting all parameter values in one call:
pstmt.params("Living Room", 51);
JDBX - unlike JDBC - also supports named parameters. On the builder returned by PrepStmt.init() call the method namedParams()
and in the SQL string specify parameters as a colon followed by the parameter name. A named parameter may occur
several times. To set a named parameter value call param(String) using the parameter name and then call the appropriate
setter:
pstmt.init().namedParams()
.sql("INSERT INTO Users (id, lastname, firstname, fullname) VALUES (DEFAULT, :last, :first, :last || ', ' || :first)");
pstmt.param("last").setString("John");
pstmt.param("first").setString("Doe");
Setting parameters on a CallStmt works exactly the same. Additionally you can register OUT and INOUT parameters, and
read the values of OUT and INOUT parameters after the statement has been executed:
CallStmt ctstmt = ...
cstmt.init("{call GetUserName(?,?,?)}"); // the SQL cmd has three parameters
cstmt.param(1).setLong(831L); // set the value of IN parameter 1
cstmt.param(2).out(java.sql.Types.VARCHAR); // register type of OUT parameter 2
cstmt.param(3).out(java.sql.Types.VARCHAR); // register type of OUT parameter 3
cstmt.execute(); // execute the command, explained in next chapters
String lastName = cstmt.param(2).getString(); // read the value of OUT parameter 2
String firstName = cstmt.param(3).getString(); // read the value of OUT parameter 3
To clear current parameters of a PrepStmt or CallStmt call:
pstmt.clearParams();
cstmt.clearParams();
In JDBC executing a query returns a java.sql.ResultSet. Given the ResultSet you can loop over its rows and extract
values from the rows.
JDBX uses fluent APIs and functional programming to avoid most of the boilerplate code needed in JDBC.
Example 1: Read all rows from a query result and convert each row into a data object
Connection con = ...
String sql = "SELECT * FROM Cities ORDER BY name";
// JDBC:
try (Statement stmt = con.createStatement()) {
ResultSet result = stmt.executeQuery(sql);
List<City> cities = new ArrayList<>();
while (result.next()) {
City city = City.read(result);
cities.add(city);
}
return cities;
}
// JDBX:
try (StaticStmt stmt = new StaticStmt(con)) {
return stmt.query(sql).rows().read(City::read);
}
Example 2: Extract a single value from the first row of a result set
Connection con = ...
String sql = "SELECT name FROM Cities WHERE code = ?";
// JDBC:
try (PreparedStatement pstmt = con.prepareStatement(sql)) {
pstmt.setString(1, "MUC");
ResultSet result = pstmt.executeQuery();
String name = null;
if (result.next())
name = result.getString(1);
return name;
}
// JDBX:
try (PrepStmt pstmt = new PrepStmt(con)) {
return pstmt.init(sql).params("MUC").query().row().col().getString();
}
To execute a SQL query you need an initialized JDBX statement.
StaticStmt.query(String), PrepStmt.query(), CallStmt.query() return a org.jdbx.QueryResult object
which provides a fluent API to extract values from the result:
QueryResult qr = stmt.query("SELECT * FROM Cities WHERE id = 51");
QueryResult qr = pstmt.init("SELECT * FROM Cities WHERE id = ?").params(51).query();
In the following variable qr represents a QueryResult object obtained from a call to a statement's query method.
Thanks to its fluent API you rarely will need to store a QueryResult in a local variable but rather chain
method calls until you receive the result value of the query.
Note that the actual JDBC query is usually not run until you invoke the terminal method of the fluent call chain.
Call QueryResult.row() to retrieve a QResultOneRow builder to read values from the first result row:
qr.row()...
qr.row().col()... // returns a builder to retrieve a value of the first column
qr.row().col().getString(); // returns the value of the first column as String
qr.row().col(3)... // returns a builder to retrieve a value of the third column
qr.row().col(3).getInteger(); // returns the value of the third column as Integer
qr.row().col("sort")... // returns a builder to retrieve a value of the "sort" column
qr.row().col("sort").getInt(); // returns the value of "sort" column as int
qr.row().cols(); // returns the value of all columns, as Object[]
qr.row().cols(1,3,7); // returns the value of columns 1,3,7, as Object[]
qr.row().map(); // returns a Map<String,Object> mapping column name to value
If the result is empty, all the examples above will return a null value (or a default value for primitive terminals like getInt()).
If you want to rule out this case use QueryResult.row().required():
// will throw a JdbxException if the result contains no rows
qr.row().required().col().getString()
You may also want to detect the case when the result contains more than one row, using QueryResult.row().unique():
// will throw a JdbxException if the result contains more than one row
qr.row().unique().col().getString()
Call QueryResult.rows() to retrieve a QResultRows builder to read values from all rows and return as java.util.List:
qr.rows()...
qr.rows().col()... // return values of first column
qr.rows().col().getString(); // return values of first column as List<String>
qr.rows().col(3)... // return values of column by number
qr.rows().col(3).getDouble(); // return values of third column, as List<Double>
qr.rows().col("sort")...; // return values of column by name
qr.rows().col("sort").getInteger(); // return values of "sort" column, as List<Integer>
qr.rows().cols(); // return values of all columns, as List<Object[]>
qr.rows().cols(1,3,7); // return values of columns 1,3,7, as List<Object[]>
qr.rows().map(); // return a List<Map<String,Object>>
You may also limit the number of processed rows if this is not done within the SQL query itself:
qr.rows().max(5) ...
Call QueryResult.skip(int) if you want to skip a number of rows before you extract values
by calling QueryResult.row(), .rows() or .rows(int):
qr.skip(3).rows()... // all rows after the first three rows
As shown above the QueryResult class makes it easy to extract a column value or an array of column values from a result row
using the various col() and cols() builder methods.
For more complicated cases JDBX provides the org.jdbx.QueryCursor class which replaces/wraps java.sql.ResultSet and allows
you to navigate thought the result rows and read values from each row.
When positioned on a result row, QueryCursor offers similar methods like the builder returned by QueryResult.row() to extract values
from the row:
QueryCursor qc = ... // a QueryCursor, positioned on a result row
qc.col()... // first column
qc.col().getString(); // first column as String
qc.col(3)... // column by number
qc.col(3).getDouble(); // third column as double
qc.col("sort")...; // column by name
qc.col("sort").getInteger(); // "sort" column, as Integer
qc.cols(1,3,7); // columns 1,3,7, as Object[]
qc.map(); // returns a Map<String,Object>
Given this API it is easy to create a function which obtains a QueryCursor and returns a complex
value constructed from the values of the current row:
public class City {
public static City read(QueryCursor qc) {
City city = new City();
city.setCode(qc.col(1).getString());
city.setName(qc.col(2).getString());
...
return city;
}
public void setName(String name) { ...
}
Now the builders returned by QueryResult.row() and .rows() accept such a reader function and invoke it for the first row / all rows
to return a single object / a list of objects:
City city = qr.row().read(City::read); // read a single data object from first result row
List<City> city = qr.rows().read(City::read); // read a list of data objects from all rows
If you want to navigate through a QueryCursor yourself you can obtain the cursor by calling
QueryResult.cursor(). You should actively close the QueryCursor once it is no longer used
therefore it is best wrapped in a try-with-resources block:
QueryResult qr = ...
try (QueryCursor qc = qr.cursor()) {
// loop through result and read its rowss
}
Given a QueryCursor it is easy to run through its rows in a forward only manner:
while (qc.next()) {
// read the result row
}
If your cursor is scrollable you can ask for the position and freely move the current row,
by using the service objects returned by QueryCursor.position() and .move():
// configure a scroll sensitive cursor
StaticStmt stmt = ....
stmt.options().setResultType(ResultType.SCROLL_SENSITIVE);
// and run the query
try (QueryCursor qc = stmt.query(sql).cursor()) {
// read position
boolean beforeFirst = qc.position().isBeforeFirst();
// also: .isAfterLast(), .isLast()
// move current row
qc.move().first()
qc.move().absolute(5)
qc.move().relative(2)
// also: .relative(), .afterLast(), .beforeFirst(), .first(), .etc.
}
If your cursor is updatable, you can or update or delete the current row, or insert a new row:
// configure the result to be updatable
StaticStmt stmt = ....
stmt.options().setResultConcurrency(Concurrency.CONCUR_UPDATABLE);
QueryCursor qc = stmt.query(sql).cursor();
// position row
...
qc.col("status").setString("ok");
qc.row().update();
qc.row().refresh();
// also: .insert(), .isUpdated(), .delete(), .isDeleted(), etc.
You can still obtain the underlying java.sql.ResultSet of a query cursor if you need to:
ResultSet resultSet = qc.resultSet();
while (resultSet.next())
...
If you have obtained a java.sql.ResultSet from somewhere else you can also turn it into a query cursor or query result using
the factory methods QueryCursor.of(ResultSet) and QueryResult.of(ResultSet):
java.sql.ResultSet resultSet = ...
List<String> names = QueryResult.of(resultSet).rows().col("name").getString();
JDBX - as JDBC - uses the term update for running DML commands (i.e. UPDATE, INSERT, DELETE) or DDL commands.
Running an update command returns the number of affected records and optionally the values of changed columns, e.g. most important the auto-generated values of primary key columns.
Updates are executed by either using a StaticStmt or a PrepStmt.
If you just want to run an update command and are not interested in returned column values then simply call
StaticStmt.update(String) or PrepStmt.update():
String sql = ...
StaticStmt stmt = ...
stmt.update(sql);
// or:
PrepStmt ptmt = ... // pstmt is initialized
pstmt.update();
Both calls return the update result as an org.jdbx.UpdateResult object. The method UpdateResult.count()
gives the update count, i.e. the number of affected records:
String sql = "INSERT INTO ...";
long inserted = stmt.update(sql).count();
if (inserted != 1)
throw new IllegalStateException("insert failed");
Testing the update count can be shortened by calling UpdateResult.requireCount:
stmt.update(sql).requireCount(1); // throws a JdbxException if the update count is not 1
If you want to retrieve returned columns values, e.g. auto-generated primary key values, or want to enable large update counts
then call StaticStmt.createUpdate(String) and PrepStmt.createUpdate() which returns a org.jdbx.Update object.
The Update class provides a fluent API to first configure and then run the update to return a UpdateResult:
Update u = stmt.createUpdate(sql);
Update u = pstmt.init(sql).params("1", "2").createUpdate();
Because of its fluent API you will rarely need to store an Update object in a local variable but rather chain
method calls to retrieve the result.
If you want to obtain returned column values, e.g. auto-generated primary key values then
- specify the columns which should be returned
- invoke an appropriate method on the
Updateobject to read the returned column values and store them in theUpdateResult
For StaticStmt steps 1) and 2) are done by configuring the Update object:
StaticStmt stmt = ...
UpdateResult<Integer> result = stmt.createUpdate("INSERT INTO Users VALUES (DEFAULT, 'John', 'Doe')")
.returnAutoKeyCols() // step 1: tell the Update to return the auto-generated key columns
.runGetCol(Integer.class); // step 2: run the update, extract the new inserted primary key column as Integer
long inserted = result.count();
Integer newId = result.value();
The convenience method UpdateResult.requireValue tests if the column value stored in the result is not null and returns the value.
This allows to write:
Integer newId = stmt.createUpdate("INSERT INTO ...")
.returnAutoKeyCols()
.runGetCol(Integer.class)
.requireCount(1) // throws an Exception if update count != 1
.requireValue(); // throws an Exception if returned col value is null
For step 1 there are alternative ways to specify the returned columns, for instance by using column numbers or names:
stmt.createUpdate(sql).returnCols(1, 5, 7)...
stmt.createUpdate(sql).returnCols("id", "timestamp")...
For step 2 there are alternative ways to retrieve the returned column values. If multiple records are affected, and you want
to retrieve one column value from each row you can call Update.runGetCols(Class<T>) to retrieve the values as java.util.List<T>:
List<Integer> newIds = stmt.createUpdate("INSERT INTO Names (name) VALUES ('Peter'), ('Paul')")
.returnAutoKeyCols()
.runGetCols(Integer.class)
.value();
For the general case Update has a runGetCols method which accepts a reader function: The function then receives the result set
containing the returned values and must read and construct a final value which is then stored in the UpdateResult.
For PrepStmt step 1) must be done during the initialization phase:
PrepStmt pstmt = ...
pstmt.init().returnAutoKeyCols().cmd("INSERT INTO Users VALUES (DEFAULT, ?, ?)")
Integer newId = pstmt.params("John", "Doe").createUpdate()
.runGetCol(Integer.class)
.requireValue();
JDBC reports the update count as int value. Since version 4.2 JDBC can also return large update counts as a long value.
JDBX always reports the update count as long but since not all JDBC drivers support large update counts you will
need to explicitly ask for large update counts - else the returned count will always be in the int range.
long updated = stmt.createUpdate("UPDATE MegaTable SET timestamp = NOW()")
.enableLargeCount() // configures the Update to retrieve large counts
.run() // runs the Update and returns the UpdateResult
.count(); // returns the update count
JDBX - as JDBC - uses the term execute for running SQL commands with yet unknown result type - update or query - or which can return multiple update and query results.
Given an initialized statement you can execute a command by calling StaticStmt.execute(String sql), PrepStmt.execute() or CallStmt.execute().
These methods return a org.jdbx.ExecuteResult which allows you to loop through the individual results, detect the result type
and evaluate the UpdateResult or QueryResult.
String sql = ...
StaticStmt stmt = ...
ExecuteResult result = stmt.execute(sql);
while (result.next()) {
if (result.isQuery())
... // process result.getQueryResult()
else
... // process result.getUpdateResult()
}
All JDBX statement classes - like their counterparts in JDBC - allow to bundle SQL commands in batches to improve
roundtrip performance. Instead of directly incorporating batch related methods into the statement interface,
all JDBX statements know a batch() method which returns a org.jdbc.Batch object to add or clear commands and run the batch.
When the Batch is run it returns a org.jdbx.BatchResult which similar to UpdateResult allows you to access the update counts:
StaticStmt stmt = ...
stmt.batch()
.add("INSERT INTO BatchDemo (name) VALUES ('A')")
.add("INSERT INTO BatchDemo (name) VALUES ('B'), ('C'), ('D')")
.run() // returns a BatchResult
.requireSize(2) // check that the batch result has 2 entries
.requireCount(0, 1) // check the update count of result entry 0
.requireCount(1, 3); // check the update count of result entry 1
PStmt pstmt = ...
pstmt.init("INSERT INTO BatchDemo (name) VALUES (?)")
pstmt.params("A").batch().add();
pstmt.params("B").batch().add()
.run() // returns a BatchResult
.requireSize(2) // check that the batch result has 2 entries
.requireCount(0, 1; // check the update count of result entry 0
.requireCount(1, 1); // check the update count of result entry 1
JDBC reports database errors as checked exceptions using java.sql.SQLException and derived classes.
JDBX instead favors unchecked exceptions and introduces an own unchecked exception class
org.jdbx.JdbxException which can be thrown by its operations. Especially any SQLException thrown by
the underlying JDBC operations is wrapped into a JdbxException and reported as its exception cause.
For easier exception handling a JdbxExeption contains an enum JdbxExeption.Reason to classify the exception
context. If based on a SQLException it also contains an enum JdbxExeption.SqlExType to classify the
the SQLException.
If you only want to run a single SQL query or DML update you can use the convenience methods in class org.jdbx.Jdbx
which handle creation and closing of a statement object.
Connection con = ...
// run a static SELECT: creates a StaticStmt internally and returns the QueryResult
int cityCount = Jdbx.query(con, "SELECT COUNT(*) FROM Cities").row().col().getInt();
// run a parameterized INSERT: creates a PrepStmt internally and returns the UpdateResult
Jdbx.update(con, "INSERT INTO Status (flag) VALUES (?)", "F").requireCount(1);
But if you need to run a couple of SQL commands it is more efficient to create a statement and reuse it.
org.jdbx.MultiStmt is a utility class which creates and keeps track of multiple statements.
When the MultiStmt object is closed it automatically closes all created statements:
Connection con = ...
try (MultiStmt mstmt = new MultiStmt(con)) {
StaticStmt s1 = mstmt.newStaticStmt(); // no need to close explicitly
PrepStmt s2 = mstmt.newPrepStmt(); // no need to close explicitly
... // use s1 and s2
}
The package org.jdbx.demo contains more code snippets to demonstrate the JDBX API.