Recap from PART 2: You can now perform CRUD operations using a single DAO. Your implementations for "InsertUser", "InsertAccount", "SelectgAccountById" now exist as simple, consise and testable classes.
The DAO contains insert", "update", "delete", "select" and "batch" methods which conveniently wrap the JDBC code around each CRUD operation.
However, you are still limited to one operation per transaction/method invocation. With this I mean the following. Let's assume you have three operations you would want to execute:
- Add new User
- Add new Account
- Update Account with User Information
Welcome to Part 3!
To achieve this, I'm gonna introduce two new interfaces.
(1) Single Operation
public interface Transact<T> {
T execute(Connection conn) throws SQLException;
}
(2) Multiple Operations
public interface TransactChain {
public void start(Connection conn) throws SQLException;
}
If you pay close attention to the "try/catch" block in the "update", "insert" and "delete" methods of ActiveDAO, you will notice another pattern beginning to emerge. Here's what I'm taking about:
PreparedStatement pstatement = conn.prepareStatement(update.getQuery().... update.setParameters(pstatement);
int affectedRows = pstatement.executeUpdate();
pstatement.close();
This is interesting now! In this code snippet, the variable 'update', which is passed as the method argument, is invoked twice, and in this way controls the outcome of the method invocation. The rest of the plumbing in all the three methods remains the same. Great!
That's where the 'Transact<T>' interface come in. The execute method will do what the above snippet does. However, what it implies is that we now have access to the result of a CRUD operation before the connection commits.
Let's update the 'UpdateAccount' class to illustrate. You will notice that the entire class remains the same, and the implementation of 'Transact<Integer>' resembles the code snippet provided earlier.
public class UpdateAccount implements Update, Transact<Integer>{
private final Account account;
public UpdateAccount(Account account) {
super();
this.account = account;
}
public Integer execute(Connection conn) throws SQLException{
PreparedStatement pStatement = conn.prepareStatement(getQuery());
setParameters(pStatement);
int result = pStatement.executeUpdate();
pStatement.close();
return result;
}
public String getQuery() {
return "UPDATE TBL_ACCOUNTS SET PASS_WORD = ?, FK_USER_ID = ? WHERE ID = ?";
}
public void setParameters(PreparedStatement pStatement) throws SQLException{
pStatement.setString(1, account.password);
pStatement.setLong(2, account.user.id);
pStatement.setLong(3, account.id);
}
The 'execute()' method makes use of 'getQuery()' and 'setParameters()', just like it was doing in the ActiveDAO, but now it does it from outside
In the same manner, you can extend this thinking to the "Insert' operations. First create an AbstractInsert as shown below
public abstract class AbstractInsert implements Update, Transact<Long>{
public Long execute(Connection conn) throws SQLException{
PreparedStatement pStatement = conn.prepareStatement(getQuery(), Statement.RETURN_GENERATED_KEYS);
setParameters(pStatement);
int result = pStatement.executeUpdate();
assert result == 1;
Long genId = new AutoIDExtractor().extract(pStatement.getGeneratedKeys());
pStatement.close();
return genId;
}
}
Then now you can create a concrete "Insert" class
public class InsertUser extends AbstractInsert{
private final User user;
public InsertUser(User user) {
super();
this.user = user;
}
public String getQuery() {
return "INSERT INTO TBL_USERS (FIRST_NAME, LAST_NAME, PHONE_NUMBER) VALUES (?, ?, ?)";
}
public void setParameters(PreparedStatement pStatement) throws SQLException{
pStatement.setString(1, user.firstName);
pStatement.setString(2, user.lastName);
pStatement.setString(3, user.phoneNumber);
}
}
You can even apply the same thinking towards 'Select' operations as shown below
public class SelectAccountById implements Select<Account>, Transact<Account>{
private final Long id;
public SelectAccountById(Long id) {
super();
this.id = id;
}
public Account execute(Connection conn) throws SQLException {
PreparedStatement pStatement = conn.prepareStatement(getQuery());
setParameters(pStatement);
ResultSet resultSet = pStatement.executeQuery();
Account result = getRowExtractor().extract(resultSet);
pStatement.close();
return result;
}
public String getQuery() {
return "SELECT * FROM TBL_ACCOUNTS WHERE id = ?";
}
public void setParameters(PreparedStatement pStatement) throws SQLException {
pStatement.setLong(1, id);
}
public RowExtractor<Account> getRowExtractor() throws SQLException {
return new AccountExtractor();
}
}
What this approach does is that it gives you access to the actual query executions but outside of the ActiveDAO. This opportunity leads to the next interface I will now discuss, the TransactChain
As the name suggests, it means now that you can chain together some 'Transact' classes and execute them in the order you prefer, and have ActiveDAO wrap all of them under one transaction: exactly what we were hoping for
This is how a TransactChain which would execute the 3 operations highlighted in the beginning of this article would look like.
public class InsertUserWithAccount implements TransactChain{
private final User user;
private final Account account;
public InsertUserWithAccount(User user, Account account) {
super();
this.user = user;
this.account = account;
}
public void start(Connection conn) throws SQLException {
//insert user
InsertUser insertUser = new InsertUser(user);
Long userId = insertUser.execute(conn);
user.id = userId;
//insert account
InsertAccount insertAccount = new InsertAccount(account);
Long accountId = insertAccount.execute(conn);
account.id = accountId;
//update account
account.user = user;
UpdateAccount updateAccount = new UpdateAccount(account);
int result = updateAccount.execute(conn);
assert result == 1;
}
}
The same connection object is used in executing all the operations. If all the executions are successful, ActiveDAO will commit and life becomes blissful. But that means that in the ActiveDAO, you have to introduce another method which will expect an argument of type TransactChain. This is how the method would look like.
public void transact(TransactChain chain) {
// get a connection
Connection conn = createConnection();
try {
conn.setAutoCommit(false);
// kick off chain
chain.start(conn);
// commit changes
conn.commit();
} catch (SQLException e) {
// rollback changes
try {
conn.rollback();
} catch (SQLException ex) {
throw new RuntimeException("Transaction failed");
}
} finally {
try {
conn.close();
} catch (SQLException ex) {
// ignore
}
}
}
An example of an application using this new feature we've added would be like this:
public class App
{
public static void main( String[] args )
{
ResetTables.execute();
System.out.println( "Starting app!" );
User user = new User();
user.firstName = "fname";
user.lastName = "lname";
user.phoneNumber = "123-456-7890";
Account account = new Account();
account.username = "admin";
account.password = "secret";
account.emailAddress = "admin@localhost.com";
InsertUserWithAccount chain = new InsertUserWithAccount(user, account);
ActiveDao dao = ActiveDao.getInstance();
dao.transact(chain);
dao.shutdown();
System.out.println( "Done!" );
}
}
And for completeness, I will put all the code necessary here -- 'ResetTables' class and 'ConnPool' class
The 'ResetTable' looks like this:
public class ResetTables{
public static final String dropUsersTable = "REFER TO Part 1!";
public static final String createUsersTable = "REFER TO Part 1";
public static final String dropAccountsTable = "REFER TO Part 1";
public static final String createAccountsTable = "REFER TO Part 1";
public static void execute(){
ActiveDao dao = ActiveDao.getInstance();
int[] result = dao.batch(Arrays.asList(new String[]{dropAccountsTable, dropUsersTable, createUsersTable, createAccountsTable}));
assert result != null;
System.out.println("Tables reset done!");
}
}
The 'ConnPool' looks like this:
public class ConnPool {
private final String dbUrl = "jdbc:mysql://localhost:3306/";
private BoneCP connectionPool;
private static ConnPool instance;
//make these configurable
private String dbUser = "root";
private String dbPass = "admin";
private String dbName = "test";
private ConnPool() {
super();
//load DB driver
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new RuntimeException("Could not located database drivers");
}
//create configuration for BoneCP
BoneCPConfig config = new BoneCPConfig(); // create a new configuration object
config.setJdbcUrl(dbUrl + dbName); // set the JDBC url
config.setUsername(dbUser); // set the username
config.setPassword(dbPass);
//create connection pool
try {
connectionPool = new BoneCP(config);
} catch (SQLException e) {
throw new RuntimeException("Could not create connection pool");
}
}
public static ConnPool getInstance(){
if(instance == null){
instance = new ConnPool();
}
return instance;
}
public Connection getConnection() {
try {
return connectionPool.getConnection();
} catch (SQLException e) {
throw new RuntimeException("Could not create connection");
}
}
public void shutDown(){
connectionPool.shutdown();
}
}
Make sure to add a shutdown method in the DAO, which will call the shutDown() in the pool object.
And that is it!! We have managed to harness the power of JDBC by rethinking the process. We have also not used any intrusive reflection and/or jdk proxies. It's simply pojos, very easy to understand, they communicate their intention clearly, the business value is now actually testable without all the JDBC noise, and now everyone is happy! I hope this makes someone's day.
