java
jcabi
This is how you fetch text from a SQL table with jcabi-jdbc:
String name = new JdbcSession(source)
.sql("SELECT name FROM employee WHERE id = ?")
.set(1234)
.select(new SingleOutcome<String>(String.class));
Simple and straight forward, isn’t it? The library simplifies interaction with relational databases via JDBC, avoiding the need to use ORM.
jcabi-jdbc is a lightweight wrapper of JDBC. It is very convenient to use when you don’t need a full-scale ORM (like Hibernate), but want just to select, insert, or update a few rows in a relational database.
Every instance of JdbcSession is a “transaction” in a database. You start it by instantiating the class with a single parameter—data source.
You can obtain the data source from your connection pool. There are many implementations of connection pools. I would recommend that you use BoneCP. Below is an example of how you would connect to PostgreSQL:
@Cacheable(forever = true)
private static DataSource source() {
BoneCPDataSource src = new BoneCPDataSource();
src.setDriverClass("org.postgresql.Driver");
src.setJdbcUrl("jdbc:postgresql://localhost/db_name");
src.setUser("jeff");
src.setPassword("secret");
return src;
}
Be sure to pay attention to the @Cacheable annotation. This post explains how it can help you to cache Java method results for some time. Setting the forever attribute to true means that we don’t want this method to be called more than once. Instead, we want the connection pool to be created just once, and every second call should return its existing instance (kind of like a Singleton pattern).
jcabi-jdbc website explains how you can insert, update, or delete a row. You can also execute any SQL statement.
By default, JdbcSession closes the JDBC connection right after the first select/update/insert operation. Simply put, it is designed to be used mainly for single atomic transactions. However, it is possible to leave the connection open and continue, for example:
new JdbcSession(source)
.autocommit(false)
.sql("START TRANSACTION")
.update()
.sql("DELETE FROM employee WHERE name = ?")
.set("Jeff Lebowski")
.update()
.sql("INSERT INTO employee VALUES (?)")
.set("Walter Sobchak")
.insert(Outcome.VOID)
.commit();
In this example we’re executing three SQL statements one by one, leaving connection (and transaction) open until commit() is called.
