<dependency>
<groupId>com.github.mjdbc</groupId>
<artifactId>mjdbc</artifactId>
<version>1.3.1</version>
</dependency>
// Step 1: Create mJDBC adapter for java.sql.DataSource
java.sql.DataSource ds = ...;
Db db = DbFactory.wrap(ds);
// Step 2: Design SQL interface
public interface UserSql {
@Sql("INSERT INTO users(login) VALUES (:login)")
int insertUser(@BindBean User user);
@Sql("SELECT * FROM users WHERE login = :login")
User getUserByLogin(@Bind("login") String login);
}
// Step 3: Define rules to map SQL results to Java objects
db.registerMapper(User.class, r -> {
User user = new User();
user.id = r.getInt("id");
user.login = r.getString("login");
...
return user;
};)
// Step 4: Get implementation for SQL interface
UserSql userSql = db.attachSql(UserSql.class);
// Step 5: Use SQL interface to perform queries
User user = userSql.getUserByLogin("login");
Example 2: Transactions
// Step 1: Create an interface with transactional methods
public interface UserDbi {
void registerUser(User user);
}
// Step 2: Implement the interface
public class UserDbiImpl implements UserDbi {
public void registerUser(User user) {
User copy = userSql.getUserByLogin(user.login);
assertNull(copy, "User already exists: " + user.login);
user.id = userSql.insertUser(user);
}
}
// Step 3: Get proxy-adapter for the interface with transactions support
UserDbi userDbi = db.attachDbi(UserDbi.class, new UserDbiImpl());
// Step 4: Use the adapter
userDbi.registerUser(new User("root"));
To start using mJDBC create an instance of com.github.mjdbc.Db providing javax.sql.DataSource object to the factory method:
static Db DbFactory.wrap(DataSource ds)
Transactional interfaces allows to execute multiple SQL requests within a single transaction: either all of SQL request inside transaction will succeed or no effect on database state will done at all.
Any Java interface can be made transactional. To register the interface as transactional use the following method:
<T> T attachDbi(T impl, Class<T> dbiInterface)
SQL interfaces - is a way to create maintainable set of SQL queries that checked for correctness during the interface registration. SQL queries are written as @Sql annotations to methods and parameters are bound with @Bind and @BindBean classes. When SQL interface is registered in mJDBC it returns a Proxy for the interface with SQL queries checked and implemented.
<T> T attachSql(Class<T> sqlInterface)
Set of supported SQL interface parameter types can be extended by custom Java types by creating and registering new DbBinder<T> implementations, which are responsible for Java objects mapping to parameters of java.sql.PreparedStatement
<T> void registerBinder(Class<? extends T> binderClass, DbBinder<T> binder)
Return values for SQL interfaces are instantiated using corresponding DbMapper<T> instances. mJDBC supports by default primitive Java types, JDBC types and Java Lists. Support for custom return types can be added by using implementing and registering new DbMapper<T> instance using the following method:
<T> void registerMapper(Class<T> mapperClass, DbMapper<T> mapper)
mJDBC provides low level API to create and run queries without SQL or transactional interfaces still utilising most of the benefits of mJDBC while having full access and control over JDBC objects.
@Nullable and @NotNull annotations are used throughout all API for strict control over null values. Some API calls that may or may not return null are intentionally duplicated and users of the API are encouraged to follow null safety.
@Nullable <T> T execute(@NotNull DbOp<T> op) @NotNull <T> T executeNN(@NotNull DbOpNN<T> op) void executeV(@NotNull DbOpV op)
mJDBC automatically collects and provides simple profiling information about number of calls and total time spent in methods of SQL and transactional interfaces. This information is available via getTimers method:
Map<Method, DbTimer> getTimers()
Every method of the interface is a separate transaction: commit for is called after successful method call, rollback in case if exception was thrown.
No new transaction created if one transactional interface method is called from within another. The method called becomes a part of the upper-level transaction.
Implementation of transactions in mJDBC works the way that the real transaction as well as SQL connection request from java.sql.DataSource occurs not during a call of the proxy method, but only during execution of the first real SQL call. This way the methods of transactional interface that do not run any operations over database: use cache or trigger exceptions on arguments check are executed without any database related overhead.
Transactional interfaces are simple Java interfaces: mJDBC does not require extending marker interfaces or use special annotations. To get proxy implementation for a transactional interface the interface and its implementation must be registered using the following mJDBC call:
@NotNull <T> T attachDbi(@NotNull T impl, @NotNull Class<T> dbiInterface)
The dbi prefix here stands for "database interface".
Examples of transactional interfaces: SampleDbi, SampleDbiImpl, SamplesTest.
SQL interface is a Java interface with every method associated with a single SQL request. mJDBC provides implementation for SQL interface methods and handles method parameters and results mappings.
To create new SQL interface:
SQL interface example:
public interface UserSql {
@NotNull
@GetGeneratedKeys
@Sql("INSERT INTO users(login, first_name, last_name)
VALUES (:login, :firstName, :lastName)")
UserId insertUser(@BindBean User user);
@Nullable
@Sql("SELECT * FROM users WHERE id = :id")
User getUserById(@Bind("id") UserId id);
@Nullable
@Sql("SELECT * FROM users WHERE login = :login")
User getUserByLogin(@Bind("login") String login);
@Sql("SELECT COUNT(*) FROM users")
int countUsers();
}
To obtain SQL interface implementation use the following method:
@NotNull <T> T attachSql(@NotNull Class <T> sqlInterface);
During this method call mJDBC will parse and check that all SQL parameters used in the request are present and types of parameters and return value are supported.
All SQL parameters are named and are bound to Java parameters by their names. All names are prefixed with ':' symbol in SQL.
@Bind annotation is used to bind SQL parameters to Java method parameters. The annotation holds a name for corresponding parameter in SQL query. @BindBean annotation is an alternative for @Bind: if used all parameters are derived from annotated Java object fields. These fields must be public or have a public get methods.
mJDBC creates a separate java.sql.PreparedStatement object for each SQL interface method. To bind Java or standard JDBC parameter types mJDBC calls corresponding methods of PreparedStatement object. In order to use custom data types as SQL parameters there is a special API to register parameter binder class instances: DbBinder, or implement interface for special set of additional data types supported by mJDBC: DbInt, DbString etc.
To enable support for custom non-Java or non-JDBC return type for SQL interface methods implement and register DbMapper class. This class will be used by mJDBC to map a single java.sql.ResultSet row into an instance of user defined type. Lists (java.util.List) and basic Java and JDBC types are supported by mJDBC by default.
To return auto-generated SQL value use @GetGeneratedKeys annotation for SQL method. Use of @GetGeneratedKeys is optional for SQL statements that starts from "INSERT " prefix: mJDBC will apply @GetGeneratedKeys to them automatically.
In case if Java parameter is iterator (java.util.Iterator), collection (java.util.Iterable) or Java array instance mJDBC will use batch mode to execute the query.
To handle SQL interface parameters mJDBC uses DbBinder interface and @Bind / @BindBean annotations. DbBinder interface defines the way Java type is converted to the real java.sql.PreparedStatements parameter:
public interface DbBinder<T> {
void bind(PreparedStatement statement, int idx, T value)
}
When executing SQL query mJDBC automatically selects correct DbBinder from the set of registered binders. The priority of DbBinder selection is the following:
If more than one interface is found mJDBC will throw java.lang.IllegalArgumentException. DbBinder are resolved for all parameters during SQL interface registration call.
To register new DbBinder implementation use the following method
<T> void registerBinder(Class<? extends T> binderClass, DbBinder<T> binder)
@Bind annotation used to specify a name of the annotated parameter as used in SQL request.
@BindBean annotation enforces mJDBC to bind all SQL parameter to public object field names or getter methods. Today @BindBean parameter must be the only parameter of the method.
In case if SQL statement contains more named parameters than can be resolved from method parameters annotated with @Bind or @BindBean an exception is thrown. Unused @Bind and @BindBean parameters are ignored. The names of parameters are validated to be valid Java identifiers.
mJDBC verifies that all SQL statement parameters are correctly mapped is performed during SQL interface registration.
mJDBC must know how to correctly instantiate and initialize results of Sql interface methods. Out of the box mJDBC supports all primitive Java type and their Object counterparts, java.sql.Date, java.sql.Timestamp, java.util.Date, and java.math.BigDecimal types and java.util.List interface for the case when multiple results are returned.
To extend the set of supported method result type a new instance of DbMapper interface must be registered. This instance must provide a method that is used to map any given ResultSet position into valid result object instance.
public interface DbMapper<T> {
T map(@NotNull java.sql.ResultSet r) throws SQLException;
}
Example:
DbMapper<User> mapper = (r) -> {
User user = new User();
user.id = new UserId(r.getInt("id"));
user.login = r.getString("login");
return user;
}
db.registerMapper(User.class, mapper);
To make mJDBC automatically find DbMapper implementation for Sql interface results the following condition must be met for any given type <T>:
The resolve algorithm described above is performed by mJDBC only if for the given type <T> there is no manually registered DbMapper<T> instance.
DbMapper instance resolution is performed during Sql interface registration. If there are 2 or more candidates for the given type <T> mJDBC throws Exception and does not register Sql interface.
Multiple registerMapper calls for the same type <T> are allowed and result to active DbMapper instance update.
Batch operations are used to optimize a series of identical parametrized database updates. Example:
@Sql("UPDATE users SET score = :score WHERE id = :id")
void updateScore(@Bind("id") int[] ids, @Bind("score") int score);
@Sql("UPDATE users SET score = :score WHERE id = :id")
void updateScore(@BindBean List<User> users);
@Sql("INSERT INTO users(login, score) VALUES (:login, :score)")
void insertUsers(@BindBean List<User> users);
With batch operations mJDBC uses a single instance of java.sql.PreparedStatement object to execute the whole series of updates and utilizes addBatch/executeBatch JDBC API.
Count of addBatch methods invocations before executeBatch call can be set up by batchChunkSize parameter of @Sql annotation. Default value for this parameter is Integer.MAX_VALUE. It means that mJDBC will try to put all data into a single batch update by default.
@Sql(value = "UPDATE users SET score = :score WHERE id = :id",
batchChunkSize = 100)
void updateScore(@BindBean List<User> users);
mJDBC detects batch parameters by their types: the following Java types are considered as batch parameters: Java arrays, java.util.Iterable and java.util.Iterator.
Batch parameters are mapped to native named SQL parameters like regular parameters with @Bind and @BindBean annotations. The only restriction for batch parameters: only 1 batch parameter per @SQL query is allowed.
@NotNull Map<Method, DbTimer> getTimers();where DbTimer is the following structure:
public final class DbTimer {
@NotNull
private final Method method;
protected volatile long invocationCount;
protected volatile long totalTimeInNanos;
mJDBC allows to declare transactional boundaries manually without use of transactional or SQL interfaces. This option may be useful for small applications in order to avoid coding of interfaces or in case if transactional interfaces do not provide features needed.
In order to run some code inside of SQL transaction and avoid transactional interfaces use one the the following mJDBC methods:
@Nullable <T> T execute(@NotNull DbOp<T> op); @NotNull <T> T executeNN(@NotNull DbOpNN<T> op); void executeV(@NotNull DbOpV op);
When any of the methods above is called mJDBC will wrap the DbOp implementation into a real SQL transaction first and run the implementation from within the transaction. The logic here is the same as with transactional interfaces: once the top-level method finishes successfully the transaction commits. Transaction is rolled back if the method throws any exception. All nested method calls reuse existing transactional context.
/**
* Database operation with @Nullable result.
*/
public interface DbOp<T> {
@Nullable
T run(@NotNull DbConnection c) throws Exception;
}
/**
* Database operation with no result.
*/
public interface DbOpV {
void run(@NotNull DbConnection c) throws Exception;
}
/**
* Database operation with @NotNull result.
*/
public interface DbOpNN<T> {
@NotNull
T run(@NotNull DbConnection c) throws Exception;
}
mJDBC provides 3 similar DbOp interfaces that handles return values differently. It allows to use more expressive syntax and utilize power of static code analyzers.
DbOp's DbConnection parameter provides to user direct access to java.sql.Connection and is used by mJDBC together with DbPreparedStatement class described below.
If the top level DbOp.run method is finished normally without throwing an exception, active transaction is committed. In case if there was an exception the transaction is rolled back. Nested execute(DbOp) calls do not create new transactions and reuse transaction context from the caller method.
To create SQL queries directly without use of SQL interfaces mJDBC provides DbPreparedStatement class. This class is a wrapper and provides direct access to java.sql.PreparedStatement and the following additional functionality:
Examples of DbPreparedStatement used with DbOp:
List<User> users = db.execute(c ->
new DbPreparedStatement<>(c, "SELECT * FROM users", User.class)
.queryList());
User user = db.execute(c ->
new DbPreparedStatement<>(c, "SELECT * FROM users WHERE login = :login", User.class).set("login", "admin").query());
All queries with DbPreparedStatement run inside another DbOp wrapper use the same transaction. All SQL resources inside the transaction are tracked by mJDBC and automatically released when transaction is finised.