Tuesday, June 25, 2013

Evolution of web.xml with the Servlet Spec

A simple compilation of how the web.xml has evolved with changes in the servlet specification

servlet 2.3 web.xml

{{{
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN" "http://java.sun.com/dtd/web-app_2_3.dtd">
<web-app>

</web-app>
}}}

servlet 2.4 web.xml

{{{
<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_9" version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">

</web-app>
}}}


servlet 2.5 web.xml

{{{
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
version="2.5">

</web-app>
}}}

servlet 3.0 web.xml

{{{
<?xml version="1.0" encoding="UTF-8"?>
<web-app
        version="3.0"
        xmlns="http://java.sun.com/xml/ns/javaee"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">

</web-app>
}}}


servlet 3.0 web-fragment.xml

{{{
<web-fragment xmlns="http://java.sun.com/xml/ns/javaee"
              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
              xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-fragment_3_0.xsd" 

version="3.0">
</web-fragment>
}}}

 


Wednesday, May 15, 2013

Back to JDBC basics - PART 3

In this article, we shall finally enhance the solution to allow for multiple operations in a single transaction.

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:
  1. Add new User
  2. Add new Account
  3. Update Account with User Information
With the current DAO, each operation happens in its own transaction. If you would like these three operations to be implemented as a unit, then you've been out of luck until now. This is the challenge which I will tackle in this article.

 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.

Back to JDBC basics - PART 2

In this article, we shall put all the common JDBC code into a template, and just have the changing parts abstracted out into other classes.

Quick recap from PART 1. JDBC is a wonderful beast - it lets you tame the Database!

But oops! It also is a complex beast. Too many options to do one job done!

Reflecting back in PART 1, what if all you had to do to insert a User was simplified to a single interface which has ONLY two methods?
  1. Obtaining a Query string
  2. Optionally setting the Query parameters,

Let me show you an example. The interface would look as shown below

Interface:

public interface Update {

    public abstract String getQuery();

   
    public abstract void setParameters(PreparedStatement pStatement) throws SQLException;
}

A concrete implementation for Inserting a User would be simple and straight forward as shown below. Notice that only the variable parts (the query and query data) are required to implement this interface

Concrete Class:

public class InsertUser implements Update{

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

That's it!!

Ok, let's extend the reasoning above to also insert an Account entity:

Concrete Class:

public class InsertAccount implements Update{

    private final Account account;
   
    public InsertAccount(Account account) {
        super();
        this.account = account;
    }

    public String getQuery() {
        return "INSERT INTO TBL_ACCOUNTS (EMAIL_ADDR, USER_NAME, PASS_WORD) VALUES (?, ?, ?)";
    }

    public void setParameters(PreparedStatement pStatement)  throws SQLException{
        pStatement.setString(1, account.emailAddress);
        pStatement.setString(2, account.username);
        pStatement.setString(3, account.password);
    }
}

Voila!!

Introducing the "Update" interface lets you focus on only the query to execute. Life is getting much better now, don't you think? You can extrapolate the "insert" operation shown above to "update" and "delete" operations, which I will demonstrate in the third article, so hang on tight!

What if you want to "select"? Well, we'll have to use a slightly different interface, but it will look awfully familiar again.

It looks just like the Update, except that it takes a Type parameter <T> and has an additional method which returns a fully assembled object of type <T>

Interface:

public interface Select<T> {

    public abstract String getQuery();
   
    public abstract void setParameters(PreparedStatement pStatement) throws SQLException;
   
    public abstract RowExtractor<T> getRowExtractor() throws SQLException;
}

A concrete example to illustrate the usage of "Select<T>"  is as show below

Concrete class:

public class SelectAccountById implements Select<Account>{

    private final Long id;
   
    public SelectAccountById(Long id) {
        super();
        this.id = id;
    }

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

Oh, so what is that AccountExtractor class all about? Remember AutoIDExtractor in PART 1? It's the same beast. Both of those classes implement a common Interface, revealed here now.

Interface:

public interface RowExtractor<T> {

    public T extract(ResultSet resultSet) throws SQLException;
}

So how does this interface know how to assemble a database object? An illustration is given below which puts together an "Account" object, given a ResultSet

Concrete Class:

public class AccountExtractor implements RowExtractor<Account>{

    public Account extract(ResultSet resultSet) throws SQLException{
        Account account = new Account();
        if(resultSet.next()){
            account.id = resultSet.getLong("ID");
            account.username = resultSet.getString("USER_NAME");
            account.password = resultSet.getString("PASS_WORD");
            account.emailAddress = resultSet.getString("EMAIL_ADDR");
        }
        return account;
    }
}

You can extrapolate the implementation above to extract Users also.

So, what this means is that I can now have a DAO which will take on all the boiler code and have it in one location, and I can thereby enjoy a beer on a hummock.

This is how the DAO would look like. However, you will notice that we still have no mechanism for executing several operations under a single transaction. That will be in part 3

You will also notice that the "update" and "delete" functions identical. You can choose to have a private method which both "update" and "delete" can delegate to, thereby eliminating this redundancy

public class ActiveDao {

    protected static final Logger log = LoggerFactory.getLogger(ActiveDao.class);
    private final ConnPool pool = ConnPool.getInstance();
    private static ActiveDao instance;

    private ActiveDao() {
        super();
    }

    public static ActiveDao getInstance(){
        if(instance == null){
            instance = new ActiveDao();
        }
        return instance;
    }
   
    public Connection createConnection() {
        return pool.getConnection();
    }

    public Long insert(Update insert) {
        // get a connection
        Connection conn = createConnection();

        try {
            conn.setAutoCommit(false);

            // get prepared statement
            PreparedStatement pstatement = conn.prepareStatement(insert.getQuery(), Statement.RETURN_GENERATED_KEYS);
            insert.setParameters(pstatement);
            int affectedRows = pstatement.executeUpdate();
            assert affectedRows == 1;
           
            Long lastRowId = new AutoIDExtractor().extract(pstatement.getGeneratedKeys());
            pstatement.close();

            // commit changes
            conn.commit();
            return lastRowId;
        } catch (SQLException e) {
            log.debug(e.getMessage() + " --> rolling back changes");
            // rollback changes
            try {
                conn.rollback();
            } catch (SQLException ex) {
                log.debug(ex.getMessage());
                throw new RuntimeException("Failed to save data");
            }
        } finally {
            try {
                conn.close();
            } catch (SQLException ex) {
                log.debug(ex.getMessage());
            }
        }
        return 0l;
    }
   
    public int delete(Update delete) {
        // get a connection
        Connection conn = createConnection();

        try {
            conn.setAutoCommit(false);

            // get prepared statement
            PreparedStatement pstatement = conn.prepareStatement(delete.getQuery());
            delete.setParameters(pstatement);
            int affectedRows = pstatement.executeUpdate();
            pstatement.close();

            // commit changes
            conn.commit();
            return affectedRows;
        } catch (SQLException e) {
            log.debug(e.getMessage() + " --> rolling back changes");
            // rollback changes
            try {
                conn.rollback();
            } catch (SQLException ex) {
                log.debug(ex.getMessage());
                throw new RuntimeException("Failed to delete data");
            }
        } finally {
            try {
                conn.close();
            } catch (SQLException ex) {
                log.debug(ex.getMessage());
            }
        }
        return 0;
    }
   
    public int update(Update update) {
        // get a connection
        Connection conn = createConnection();

        try {
            conn.setAutoCommit(false);

            // get prepared statement
            PreparedStatement pstatement = conn.prepareStatement(update.getQuery());
            update.setParameters(pstatement);
            int affectedRows = pstatement.executeUpdate();
            pstatement.close();

            // commit changes
            conn.commit();
            return affectedRows;
        } catch (SQLException e) {
            log.debug(e.getMessage() + "--> rolling back changes");
            // rollback changes
            try {
                conn.rollback();
            } catch (SQLException ex) {
                log.debug(ex.getMessage());
                throw new RuntimeException("Failed to update data");
            }
        } finally {
            try {
                conn.close();
            } catch (SQLException ex) {
                log.debug(ex.getMessage());
            }
        }
        return 0;
    }

    public <T> T select(Select<T> select) {
        // get a connection
        Connection conn = createConnection();

        try {
            conn.setAutoCommit(false);

            // get prepared statement
            PreparedStatement pstatement = conn.prepareStatement(select.getQuery());
            select.setParameters(pstatement);
            ResultSet resultSet = pstatement.executeQuery();
            T result = select.getRowExtractor().extract(resultSet);
            pstatement.close();

            // commit changes
            conn.commit();
            return result;
        } catch (SQLException e) {
            log.debug(e.getMessage() + "--> rolling back changes");
            // rollback changes
            try {
                conn.rollback();
            } catch (SQLException ex) {
                log.debug(ex.getMessage());
                throw new RuntimeException("Failed to retrieve data");
            }
        } finally {
            try {
                conn.close();
            } catch (SQLException ex) {
                log.debug(ex.getMessage());
            }
        }
        return null;
    }
   
    public int[] batch(List<String> queries) {
        // get a connection
        Connection conn = createConnection();

        try {
            conn.setAutoCommit(false);

            // get prepared statement
            Statement pStatement = conn.createStatement();
            for(String query : queries){
                pStatement.addBatch(query);
            }
            int[] result = pStatement.executeBatch();
            pStatement.close();

            // commit changes
            conn.commit();
            return result;
        } catch (SQLException e) {
            log.debug(e.getMessage() + "--> rolling back changes");
            // rollback changes
            try {
                conn.rollback();
            } catch (SQLException ex) {
                log.debug(ex.getMessage());
                throw new RuntimeException("Failed to execute batch data");
            }
        } finally {
            try {
                conn.close();
            } catch (SQLException ex) {
                log.debug(ex.getMessage());
            }
        }
        return null;
    }
}

Now what just happened is that the ActiveDao just soaked in all the boiler-plate code you had before. All you have to do to insert a user would be:

User user  = new User(); /* some new user */

InsertUser ins = new InsertUser(user);

 ActiveDao dao = ActiveDao.getInstance();
Long id = dao.insert(ins);
System.out.println("new user --> " + id);

In the same manner, to insert a new Account, you would simply do this:

Account account  = new Account(); /* some new account*/

InsertAccount ins = new InsertAccount(account);

 ActiveDao dao = ActiveDao.getInstance();
Long id = dao.insert(ins);
System.out.println("new account --> " + id);

And using yet another example for illustration, to select an Account given an ID, we'd simply do this:

Long id = 1l;
ActiveDao dao = ActiveDao.getInstance();
SelectAccountById sel = new SelectAccountById(id);
Account account = dao.select(sel);
System.out.println("account id --> " + account.id);

Now we can see that life is becoming so much better. But what if we want to do the following in one transaction? If one fails, jump ship please., otherwise, buckle up and commit

(a) Insert new User
(b) Insert new Account
(c) Update Account with User Information

This will be the subject of PART 3!!


Tuesday, May 14, 2013

Back to JDBC basics - PART 1

There has always been this flurry of never-ending movement towards demystifying JDBC, which has generated all kinds of creative solutions. As noble as they all are, they also however introduce another subtle level of complexity. And interestingly, some have come out on top with a lot of success! ORM for instance. It's a great technology that also comes with a fair amount of great pain nonetheless!

But what about those folks that want to screech the rubber on the tarmac (i.e. use raw JDBC)? To be more particular for instance, what about those folks coming from a background of either python or php? Can JDBC be muzzled down to do the bare basics without looking like Sasquatch? Well. I think so!

This is what I'm gonna be discussing here in the next three articles.

1) Revisit the old-faithful JDBC API
2) Abstract some level of CRUD using the template design pattern for later reuse
3) Introduce a new interface for handling transactions (keeping it at single database level)

Ok. Let's visit point (1)

This should be a familiar thing for any Java developer - JDBC API. Much of it is boiler code (JDBC lingo, excuse me). Without writing any code, these are the steps you would need to follow. (I'm gonna ignore stored statements in this entire discussion)

1) Create a data source or connection pool (whichever you prefer)
2) Obtain a connection, and set "AutoCommit" to false
3) Create a statement/prepared statement using the connection
4) For "select" query
  (a)  executeQuery on the statement/prepared statement, and return a ResultSet
  (b)  parse the ResultSet and return a Database Object
5) For "update", "insert" or "delete" queries
   (a) executeUpdate on the statement/prepared statement (and optionally return a result value)
   (b) life goes on
6) Close the statement/prepared statement
7) Commit the connection
8) Close the connection (or rollback if step either step (4) or (5) above fails)

Using the default "test" database in MYSQL, let's assume a case where we have two tables that we have to deal with, namely "tbl_users" and "tbl_accounts".

These are the two domain objects we will have to deal with:

User:

public class User {

    public long id;
    public String firstName;
    public String lastName;
    public String phoneNumber;
}

Account:

public class Account {

    public long id;
    public String emailAddress;
    public String username;
    public String password;
    public User user;
}

These are the queries we need to create/drop the tables. Keep them handy for testing purposes

String dropUsersTable =  "DROP TABLE IF EXISTS `test`.`tbl_users";

String createUsersTable = "CREATE TABLE IF NOT EXISTS `test`.`tbl_users` (" +
            "`id` INT NOT NULL AUTO_INCREMENT ," +
            "`first_name` VARCHAR(45) NULL ," +
            "`last_name` VARCHAR(45) NULL ," +
            "`phone_number` VARCHAR(45) NULL ," +
            "PRIMARY KEY (`id`) )";

String dropAccountsTable = "DROP TABLE IF EXISTS `test`.`tbl_accounts`";

String createAccountsTable = "CREATE TABLE IF NOT EXISTS `test`.`tbl_accounts` (" +
            "`id` INT NOT NULL AUTO_INCREMENT ," +
            "`user_name` VARCHAR(45) NULL ," +
            "`pass_word` VARCHAR(45) NULL ," +
            "`fk_user_id` INT NULL ," +
            "`email_addr` VARCHAR(45) NULL ," +
            "PRIMARY KEY (`id`) ," +
            "INDEX `FK_ACCOUNT_USER` (`fk_user_id` ASC) ," +
            "CONSTRAINT `FK_ACCOUNT_USER` " +
            "FOREIGN KEY (`fk_user_id` ) " +
            "REFERENCES `test`.`tbl_users` (`id` ) " +
            "ON DELETE CASCADE " +
            "ON UPDATE CASCADE)";

To execute these four statement in a transactional batch, let's do it this way:


List<String> queries = (Arrays.asList(new String[]{dropAccountsTable, dropUsersTable, createUsersTable, createAccountsTable}));

//execute queries
public int[] batch(List<String> queries) {
        // get a connection
        Connection conn = createConnection();

        try {
            conn.setAutoCommit(false);

            // get prepared statement
            Statement statement = conn.createStatement();
            for(String query : queries){
                statement.addBatch(query);
            }
            int[] result = statement.executeBatch();
            statement.close();

            // commit changes
            conn.commit();
            return result;
        } catch (SQLException e) {
            log.debug(e.getMessage() + "--> rolling back changes");
            // rollback changes
            try {
                conn.rollback();
            } catch (SQLException ex) {
                log.debug(ex.getMessage());
                throw new RuntimeException("Failed to execute batch data");
            }
        } finally {
            try {
                conn.close();
            } catch (SQLException ex) {
                log.debug(ex.getMessage());
            }
        }
        return null;
    }

Here is an example for inserting a new User using the good old-faithful JDBC:

 /*This insert is configured to return the 'lastInsertedRow' ID"
public Long insert() {
        // get a connection
        Connection conn = createConnection();

        try {
            conn.setAutoCommit(false);
          
            //get Query
            String query = "INSERT INTO TBL_USERS (FIRST_NAME, LAST_NAME, PHONE_NUMBER) VALUES (?, ?, ?)";

            // get prepared statement
            PreparedStatement pStatement = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
          
            //set Query parameters
            pStatement.setString(1, user.firstName);
          pStatement.setString(2, user.lastName);
          pStatement.setString(3, user.phoneNumber);
        
          //execure query
            int affectedRows = pStatement.executeUpdate();
            assert affectedRows == 1;
          
            //extract row id
            Long lastRowId = new AutoIDExtractor().extract(pStatement.getGeneratedKeys());
            pStatement.close();

            // commit changes
            conn.commit();
            return lastRowId;
        } catch (SQLException e) {
            log.debug(e.getMessage() + " --> rolling back changes");
            // rollback changes
            try {
                conn.rollback();
            } catch (SQLException ex) {
                log.debug(ex.getMessage());
                throw new RuntimeException("Failed to save data");
            }
        } finally {
            try {
                conn.close();
            } catch (SQLException ex) {
                log.debug(ex.getMessage());
            }
        }
        return 0l;
    }

To insert an account record, again using the old-faithful JDBC, we'd need this query:

/*This insert is configured to return the 'lastInsertedRow' ID"
public Long insert() {
        // get a connection
        Connection conn = createConnection();

        try {
            conn.setAutoCommit(false);
           
            //get Query
            String query = "INSERT INTO TBL_ACCOUNTS (EMAIL_ADDR, USER_NAME, PASS_WORD) VALUES (?, ?, ?)";

            // get prepared statement
            PreparedStatement pStatement = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
           
            //set Query parameters
            pStatement.setString(1, account.emailAddress);
      pStatement.setString(2, account.username);
      pStatement.setString(3, account.password);
         
          //execure query
            int affectedRows = pStatement.executeUpdate();
            assert affectedRows == 1;
           
            //extract row id
            Long lastRowId = new AutoIDExtractor().extract(pStatement.getGeneratedKeys());
            pStatement.close();

            // commit changes
            conn.commit();
            return lastRowId;
        } catch (SQLException e) {
            log.debug(e.getMessage() + " --> rolling back changes");
            // rollback changes
            try {
                conn.rollback();
            } catch (SQLException ex) {
                log.debug(ex.getMessage());
                throw new RuntimeException("Failed to save data");
            }
        } finally {
            try {
                conn.close();
            } catch (SQLException ex) {
                log.debug(ex.getMessage());
            }
        }
        return 0l;
    }


You might be wondering at this point what "AutoIDExtractor" is all about. Let's just assume it get's you back the ID of the last inserted row. This will come up in the next article. But for a sneak-view, this is what it looks like:

public class AutoIDExtractor implements RowExtractor<Long> {

    @Override
    public Long extract(ResultSet resultSet) throws SQLException {
        Long genId = null;
        if (resultSet.next()) {
            genId = resultSet.getLong(1);
        }
        return genId;
    }
}

But we also need to update the account record with a valid user ID. The execution shown below would be a sufficient one:

/*This update is configured to only update the password and fk_user_id"
public int update() {
        // get a connection
        Connection conn = createConnection();

        try {
            conn.setAutoCommit(false);

      //get Query
      String query = "UPDATE TBL_ACCOUNTS SET PASS_WORD = ?, FK_USER_ID = ? WHERE ID = ?";
     
            // get prepared statement
            PreparedStatement pStatement = conn.prepareStatement(query);
           
            //set query parameters
            pStatement.setString(1, account.password);
      pStatement.setLong(2, account.user.id);
      pStatement.setLong(3, account.id);
       
      //execute query
            int affectedRows = pStatement.executeUpdate();
            pStatement.close();

            // commit changes
            conn.commit();
            return affectedRows;
        } catch (SQLException e) {
            log.debug(e.getMessage() + "--> rolling back changes");
            // rollback changes
            try {
                conn.rollback();
            } catch (SQLException ex) {
                log.debug(ex.getMessage());
                throw new RuntimeException("Failed to update data");
            }
        } finally {
            try {
                conn.close();
            } catch (SQLException ex) {
                log.debug(ex.getMessage());
            }
        }
        return 0;
    }

If we would be required to extract an Account record for instance, here would be some suitable code to do that. And as exercise, try to extract a User record. The code would be awfully similar.


/*This code is optimized to extract an entity type Account, give the account_id*/
public Account select(Long id) {
        // get a connection
        Connection conn = createConnection();

        try {
            conn.setAutoCommit(false);
           
            //create query
            String query = "SELECT * FROM TBL_ACCOUNTS WHERE ID = ?";

            // get prepared statement
            PreparedStatement pStatement = conn.prepareStatement(select.getQuery());
           
            //set query parameters
            pStatement.setLong(1, id);
           
            //execute query
            ResultSet resultSet = pStatement.executeQuery();
            Account account = new Account();
            if(resultSet.next()){
                account.id = resultSet.getLong("ID");
                account.username = resultSet.getString("USER_NAME");
                account.password = resultSet.getString("PASS_WORD");
                account.emailAddress = resultSet.getString("EMAIL_ADDR");
            }

            pStatement.close();

            // commit changes
            conn.commit();
            return result;
        } catch (SQLException e) {
            log.debug(e.getMessage() + "--> rolling back changes");
            // rollback changes
            try {
                conn.rollback();
            } catch (SQLException ex) {
                log.debug(ex.getMessage());
                throw new RuntimeException("Failed to retrieve data");
            }
        } finally {
            try {
                conn.close();
            } catch (SQLException ex) {
                log.debug(ex.getMessage());
            }
        }
        return null;
    }

Examining these different pieces of code reveals a lot of repeated instructions (typical of the old-faithful JDBC).

Each of the operations, "insert", "update", "select" and "batch" exists in its own space and largely do the same thing. This can very quickly become tiring and error-prone.

In the second article, I will examine how we can extract all or much of the common instructions into a template, and make life a little bit better for everybody.