DataSource Routing with Spring @Transactional

I was inspired by Carl Papa's use of aspects with the Spring Framework to determine the DataSource to use (either read-write or read-only). So, I'm writing this post.

I must admit that I have long been familiar with Spring's AbstractRoutingDataSource. But I did not have a good idea where it can be used. Thanks to Carl and team, and one of their projects. Now, I know a good use case.

@Transactional

With Spring, read-only transactions are typically marked with annotations.

public class ... {
    @Transactional(readOnly=true)
    public void ...() {...}

    @Transactional // read-write
    public void ...() {...}
}

To take advantage of this, we use Spring's TransactionSynchronizationManager to determine if the current transaction is read-only or not.

AbstractRoutingDataSource

Here, we use Spring's AbstractRoutingDataSource to route to the read-only replica if the current transaction is read-only. Otherwise, it routes to the default which is the master.

public class ... extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        if (TransactionSynchronizationManager
                .isCurrentTransactionReadOnly() ...) {
            // return key to a replica
        }
        return null; // use default
    }
    ...
}

Upon using the above approach, we found out that the TransactionSynchronizationManager is one step behind because Spring will have already called DataSource.getConnection() before a synchronization is established. Thus, a LazyConnectionDataSourceProxy needs to be configured as well.

As we were discussing this, we figured if there was another way to determine if the current transaction is read-only or not (without resorting to LazyConnectionDataSourceProxy). So, we came up with an experimental approach where an aspect captures the TransactionDefinition (from the @Transactional annotation, if any) as a thread-local variable, and an AbstractRoutingDataSource that routes based on the captured information.

The relevant source code can be found on GitHub. Thanks again, Carl! BTW, Carl is also an award-winning movie director. Wow, talent definitely knows no boundaries.

Spring-managed Transactions Explained - Part 2 (JPA)

In the first part of the series, I showed how transactions work in plain-vanilla JDBC. And then I showed how Spring manages JDBC-based transactions. In this second part of the series, I'll show how transactions work in plain-vanilla JPA first. And then show how Spring manages JPA-based transactions.

Funds Transfer

To help illustrate transactions, I'll be using the same case study of transferring funds from one bank account to another. Here, we show code snippets of debit, credit, and transfer methods.

... class BankAccountService {
  public void transfer(MonetaryAmount amount, ...) {
    debit(amount, ...);
    credit(amount, ...);
    ...
  }
  public void credit(MonetaryAmount amount, AccountId accountId) {
    ...
  }
  public void debit(MonetaryAmount amount, AccountId accountId) {
    ...
  }
  ...
}

JPA Transactions

In plain-vanilla JPA, transactions are started by calling getTransaction().begin() on the EntityManager. The code snippet below illustrates this.

import javax.persistence.*;
...
EntityManagerFactory emf = ...;
EntityManager em = emf.createEntityManager();
try {
  em.getTransaction().begin();
  // make changes through entities
  em.getTransaction().commit();
  ...
} catch(Exception e) {
  em.getTransaction().rollback();
  throw e;
} finally {
  em.close();
}

Technically, the EntityManager is in a transaction from the point it is created. So calling begin() is somewhat redundant. Until begin() is called, certain operations such as persist, merge, remove cannot be called. Queries can still be performed (e.g. find()).

Objects that were returned from queries can be changed. Although the JPA specification is somewhat unclear on what will happen to these changes when no transaction has been started.

Now, let's apply JPA to the funds transfer case study.

We defined a BankAccount entity to handle debit() and credit() behavior.

import javax.persistence.*;

@Entity
... class BankAccount {
  @Id ...;
  ...
  public void debit(MonetaryAmount amount) {...}
  public void credit(MonetaryAmount amount) {...}
  ...
}

We add an EntityManagerFactory to BankAccountService to enable the creation of EntityManagers when needed.

import javax.persistence.*;

... class BankAccountService {
  private EntityManagerFactory emf; // injected via constructor
  ...
  public void transfer(MonetaryAmount amount, ...) ... {
    EntityManager em = emf.createEntityManager();
    try {
      em.getTransaction().begin();
      BankAccount fromAccount = em.find(BankAccount.class, ...);
      BankAccount toAccount = em.find(BankAccount.class, ...);
      fromAccount.debit(amount);
      toAccount.credit(amount);
      em.getTransaction().commit();
      ...
    } catch(Exception e) {
      em.getTransaction().rollback();
      // handle exception (possibly rethrowing it)
    } finally {
      em.close();
    }
  }
  public void credit(MonetaryAmount amount, AccountId ...) ... {
    EntityManager em = emf.createEntityManager();
    try {
      em.getTransaction().begin();
      BankAccount theAccount = em.find(BankAccount.class, ...);
      theAccount.credit(amount);
      em.getTransaction().commit();
      ...
    } catch(Exception e) {
      em.getTransaction().rollback();
      // handle exception (possibly rethrowing it)
    } finally {
      em.close();
    }
  }
  public void debit(MonetaryAmount amount, AccountId ...) ... {
    EntityManager em = emf.createEntityManager();
    try {
      em.getTransaction().begin();
      BankAccount theAccount = em.find(BankAccount.class, ...);
      theAccount.debit(amount);
      em.getTransaction().commit();
      ...
    } catch(Exception e) {
      em.getTransaction().rollback();
      // handle exception (possibly rethrowing it)
    } finally {
      em.close();
    }
  }
}

Spring-managed JPA Transactions

The transfer, credit, and debit methods could sure use a template class (something like a JdbcTemplate) to remove all the boilerplate code. Spring previously provided a JpaTemplate class, but was deprecated as of Spring 3.1, in favor of native EntityManager usage (typically obtained through @PersistenceContext).

So, let's do just that — use EntityManager obtained through @PersistenceContext.

import javax.persistence.*;

... class BankAccountService {
  @PersistenceContext
  private EntityManager em;
  ...
  public void transfer(MonetaryAmount amount, ...) ... {
    try {
      em.getTransaction().begin();
      BankAccount fromAccount = em.find(BankAccount.class, ...);
      BankAccount toAccount = em.find(BankAccount.class, ...);
      fromAccount.debit(amount);
      toAccount.credit(amount);
      em.getTransaction().commit();
      ...
    } catch(Exception e) {
      em.getTransaction().rollback();
      // handle exception (possibly rethrowing it)
    }
  }
  public void credit(MonetaryAmount amount, AccountId ...) ... {
    try {
      em.getTransaction().begin();
      BankAccount theAccount = em.find(BankAccount.class, ...);
      theAccount.credit(amount);
      em.getTransaction().commit();
      ...
    } catch(Exception e) {
      em.getTransaction().rollback();
      // handle exception (possibly rethrowing it)
    }
  }
  public void debit(MonetaryAmount amount, AccountId ...) ... {
    try {
      em.getTransaction().begin();
      BankAccount theAccount = em.find(BankAccount.class, ...);
      theAccount.debit(amount);
      em.getTransaction().commit();
      ...
    } catch(Exception e) {
      em.getTransaction().rollback();
      // handle exception (possibly rethrowing it)
    }
  }
}

Our code is a little bit simpler. Since we didn't create an EntityManager, we don't have to close it. But we are still calling getTransaction().begin(). Is there a better way? And how does an EntityManager get injected into the object in the first place?

From my previous post in this series, the astute reader is probably already thinking of having Spring do the work for us. And rightfully so!

EntityManager and @PersistenceContext

We tell Spring to inject an EntityManager from the EntityManagerFactory by adding a PersistenceAnnotationBeanPostProcessor (either through XML <bean>, or simply using a Java-based configuration via @Configuration classes loaded via AnnotationConfigApplicationContext).

• When using XML-based configuration, a PersistenceAnnotationBeanPostProcessor is transparently activated by the <context:annotation-config /> element. And this element also gets transparently activated by <context:component-scan />.
• When using Java-based @Configuration, the AnnotationConfigApplicationContext is used. And with it, annotation config processors are always registered (one of which is the aforementioned PersistenceAnnotationBeanPostProcessor).

By adding a single bean definition, the Spring container will act as a JPA container and inject an EnitityManager from your EntityManagerFactory.

JPA and @Transactional

Now that we have an EntityManager, how can we tell Spring to begin transactions for us?

We tell Spring to start transactions by marking methods as @Transactional (or mark the class as @Transactional which makes all public methods transactional). This is consistent with the way Spring enables transactions with JDBC.

import javax.persistence.*;
import org.springframework.transaction.annotation.Transactional;

@Transactional
... class BankAccountService {
  @PersistenceContext
  private EntityManager em;
  ...
  public void transfer(MonetaryAmount amount, ...) ... {
      BankAccount fromAccount = em.find(BankAccount.class, ...);
      BankAccount toAccount = em.find(BankAccount.class, ...);
      fromAccount.debit(amount);
      toAccount.credit(amount);
  }
  public void credit(MonetaryAmount amount, AccountId ...) ... {
      BankAccount theAccount = em.find(BankAccount.class, ...);
      theAccount.credit(amount);
  }
  public void debit(MonetaryAmount amount, AccountId ...) ... {
      BankAccount theAccount = em.find(BankAccount.class, ...);
      theAccount.debit(amount);
  }
}

Wow, that was nice! Our code just got a lot shorter.

And just as explained in the first part of this series, when Spring encounters this annotation, it proxies the object (usually referred to as a Spring-managed bean). The proxy starts a transaction (if there is no on-going transaction) for methods that are marked as @Transactional, and ends the transaction when the method returns successfully.

A call to debit() will use a transaction. A separate call to credit() will use a transaction. But what happens when a call to transfer() is made?

Since the transfer() method is marked as @Transactional, Spring will start a transaction. This same transaction will be used for calls to debit() and credit(). In other words, debit(amount) and credit(amount) will not start a new transaction. It will use the on-going transaction (since there is one).

But wait! How does Spring know when to inject a proper entity manager? Is it only injected when a transactional method is invoked?

Shared EntityManager

In one of my training classes, I tried the following to better understand how Spring injects an EntityManager via @PersistenceContext. And I believe it will help others too. So, here's what I tried:

import javax.persistence.*;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.beans.factory.InitializingBean;

@Transactional
... class BankAccountService implements InitializingBean {
  @PersistenceContext
  private EntityManager em;
  ...
  @Override
  public void afterPropertiesSet() {
    System.out.println(em.toString());
  }
  ...
}

An output of something like this was displayed on the console after the application context started.

Shared EntityManager proxy for target factory [...]

So what is this shared entity manager?

When the application context starts, Spring injects a shared entity manager. The shared EntityManager will behave just like an EntityManager fetched from an application server's JNDI environment, as defined by the JPA specification. It will delegate all calls to the current transactional EntityManager, if any; otherwise, it will fall back to a newly created EntityManager per operation.

Going back to our question. Spring doesn't inject the right entity manager at the right time. It always injects a shared entity manager. But this shared entity manager is transaction-aware. It delegates to the current transactional EntityManager, if there is an on-going transaction.

Conclusion

This concludes the two-part series. I hope that by starting off with the plain-vanilla versions of JDBC and JPA (sans DAOs and repositories), I was able to make it clearer as to how Spring is able to manage transactions behind the scenes. And that by having a clearer idea as to what Spring is doing behind the scenes, you can troubleshoot better, understand why you get an TransactionRequiredException saying "No transactional EntityManager available", and add better fixes to your applications.

Now, it's time for a cold one.

Spring-managed Transactions Explained - Part 1 (JDBC)

I've been meaning to write about this for quite some time now. In the training courses I've been privileged to conduct, I've noticed that course participants have had the most difficulty trying to understand how Spring manages transactions. In the first part of this series, I'll start by showing how transactions work in plain-vanilla JDBC. And then show how Spring manages JDBC-based transactions.

In the second part of this series, I'll show how transactions work in plain-vanilla JPA. And then show how Spring manages JPA-based transactions.

Update: The second part of this series has just been posted.

Funds Transfer

To help illustrate transactions, I'll be using the often used case study of transferring funds from one bank account to another. Here, we show code snippets of debit, credit, and transfer methods.

... class BankAccountService {
  public void transfer(MonetaryAmount amount, ...) {
    debit(amount, ...);
    credit(amount, ...);
    ...
  }
  public void credit(MonetaryAmount amount, AccountId accountId) {
    ...
  }
  public void debit(MonetaryAmount amount, AccountId accountId) {
    ...
  }
  ...
}

By default a Connection object is in auto-commit mode, which means that it automatically commits changes after executing each statement. If auto-commit mode has been disabled, the method commit() must be called explicitly in order to commit changes; otherwise, database changes will not be saved.

JDBC Transactions

In plain-vanilla JDBC, transactions are started by setting the Connection's auto-commit mode to false (or to manual commit mode). Once the Connection's auto-commit is set to false, subsequent database changes are not committed until a call to commit() is made. The changes brought about by the SQL operations will not be seen by other connections until the changes are committed. The code snippet below illustrates this.

import java.sql.*;
...
try (Connection conn = dataSource.getConnection()) {
  conn.setAutoCommit(false);
  ... // SQL operations here (inserts, updates, deletes, etc)
  // database changes will not be saved until commit
  conn.commit(); // save changes (all-or-nothing)
} catch (SQLException e) {
  // handle exception
  conn.rollback(); // rollback changes
}

Now, let's apply this to the funds transfer case study.

The transfer() method is now overloaded with an additional connection parameter.

import java.sql.*;
...
... class BankAccountService {
  public void transfer(MonetaryAmount amount, ...)
      throws SQLException {...}
  public void transfer(
        MonetaryAmount amount, ..., Connection conn)
      throws SQLException {...}
  ...
}

The method that does not accept a connection, creates a new connection, and calls the same method that accepts a connection parameter. The method that accepts a connection, uses it to carry out debit and credit operations.

import java.sql.*;

... class BankAccountService {
  public void transfer(MonetaryAmount amount, ...)
      throws SQLException {
    try (Connection conn = dataSource.getConnection()) {
      conn.setAutoCommit(false);
      transfer(amount, ..., conn);
      conn.commit(); // save changes (all-or-nothing)
    } catch (SQLException e) {
      // handle exception
      conn.rollback(); // rollback changes
    }
  }
  public void transfer(
        MonetaryAmount amount, ..., Connection conn)
      throws SQLException {
    debit(amount, ..., conn);
    credit(amount, ..., conn);
    ...
  }
  public void credit(MonetaryAmount amount, AccountId accountId)
      throws SQLException {...}
  public void credit(MonetaryAmount amount, AccountId accountId,
      Connection conn) throws SQLException {...}
  public void debit(MonetaryAmount amount, AccountId accountId)
      throws SQLException {...}
  public void debit(MonetaryAmount amount, AccountId accountId,
      Connection conn) throws SQLException {...}
  ...
}

The debit() and credit() methods are also overloaded. Just like the transfer() methods, the one that does not accept a connection parameter, creates a new connection, and calls the one that accepts a connection object. The method that accepts a connection, uses it to carry out SQL operations.

import java.sql.*;

... class BankAccountService {
  public void transfer(MonetaryAmount amount, ...)
      throws SQLException {
    ... transfer(amount, ..., conn); ...
  }
  public void transfer(
        MonetaryAmount amount, ..., Connection conn)
      throws SQLException {
    debit(amount, ..., conn);
    credit(amount, ..., conn);
    ...
  }
  public void credit(MonetaryAmount amount, AccountId accountId)
      throws SQLException {
    try (Connection conn = dataSource.getConnection()) {
      conn.setAutoCommit(false);
      credit(amount, accountId, conn);
      conn.commit(); // save changes (all-or-nothing)
    } catch (SQLException e) {
      // handle exception
      conn.rollback(); // rollback changes
    }
  }
  public void credit(MonetaryAmount amount, AccountId accountId,
      Connection conn) throws SQLException {
    ... = conn.prepareStatement(...);
    ... // SQL operations for credit
  }
  public void debit(MonetaryAmount amount, AccountId accountId)
      throws SQLException {
    try (Connection conn = dataSource.getConnection()) {
      conn.setAutoCommit(false);
      debit(amount, accountId, conn);
      conn.commit(); // save changes (all-or-nothing)
    } catch (SQLException e) {
      // handle exception
      conn.rollback(); // rollback changes
    }
  }
  public void debit(MonetaryAmount amount, AccountId accountId,
      Connection conn) throws SQLException {
    ... = conn.prepareStatement(...);
    ... // SQL operations for debit
  }
  ...
}

Notice that we'll need to pass connection objects all over the place. Yes, this looks ugly. But stay with me for a few more minutes and read on.

A pattern emerges from these methods. Whenever a database transaction needs to be made, a connection is either provided or established, then used to carry out SQL operations (or database changes), and the changes are committed (or rolled back) by the method that established the connection.

The method accepting a connection object gives up control of the transaction to its caller (as in the case of credit(amount) calling credit(amount, connection)). The scope of transactions can span one method call, or several methods calls (as in the case of transfer(amount) calling debit(amount, connection) and credit(amount, connection)).

As already mentioned, this looks ugly and error-prone. The connection object is passed all over the place. So, how can we improve on this?

Spring-managed JDBC Transactions

First, we'll use Spring's JdbcTemplate to help us deal with JDBC boilerplate code and SQLExceptions. Second, we'll use Spring's declarative transaction management.

When we start using a JdbcTemplate inside BankAccountService, we no longer need to handle connections explicitly, and we can remove those overloaded methods. The connection object actually gets hidden in some thread local object.

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.annotation.Transactional;

@Transactional
... class BankAccountService {
  private JdbcTemplate jdbcTemplate; // injected via constructor
  ...
  public void transfer(MonetaryAmount amount, ...) {
    ...
  }
  public void credit(MonetaryAmount amount, AccountId accountId) {
    jdbcTemplate.update(...); // SQL operations for credit
    ...
  }
  public void debit(MonetaryAmount amount, AccountId accountId) {
    jdbcTemplate.update(...); // SQL operations for debit
    ...
  }
  ...
}

How does JdbcTemplate get a Connection?

For each JdbcTemplate method, it retrieves a connection from the given DataSource that is provided (either as constructor argument, or as property setDataSource(DataSource)). After the JdbcTemplate method returns, the connection is closed. Without transactions, the connection's auto-commit mode remains true. The good thing is, JdbcTemplate is transaction-aware. That means, it will participate in the on-going transaction, if there is one (i.e. use the on-going transaction's connection).

So, how do we set the connection's auto-commit mode to false, so that a transaction can be started and span more than one method call to JdbcTemplate?

To use transactions, we do not set the connection's auto-commit mode to false. Instead, we tell Spring to start transactions. In turn, Spring will retrieve a connection, set its auto-commit mode to false, and keep using this same connection until the transaction is completed (either with all changes saved/committed, or all changes rolled back).

So, how do we tell Spring to start transactions, and what does @Transactional do?

As you might have guessed, the @Transactional annotation tells Spring when to start a transaction (by setting the connection's auto-commit mode to false).

We tell Spring to start transactions by marking methods as @Transactional (or mark the class as @Transactional which makes all public methods transactional). And for Spring to start noticing @Transactional annotations, we'll need to add <tx:annotation-driven /> in our XML-based configuration, or add @EnableTransactionManagement in our Java-based @Configuration.

When Spring encounters this annotation, it proxies the object (usually referred to as a Spring-managed bean). The proxy starts a transaction (if there is no on-going transaction) for methods that are marked as @Transactional, and ends the transaction when the method returns successfully.

Since JdbcTemplate is transaction-aware, it knows how to get the on-going transaction's connection (if one exists), and not create a new one.

...
import org.springframework.transaction.annotation.Transactional;

@Transactional
... class BankAccountService {
  ...
  public void transfer(MonetaryAmount amount, ...) {
    ...
    debit(amount, ...);
    credit(amount, ...);
    ...
  }
  public void credit(MonetaryAmount amount, AccountId accountId) {...}
  public void debit(MonetaryAmount amount, AccountId accountId) {...}
  ...
}

So, a call to debit() will use a transaction. A separate call to credit() will use a transaction. But what happens when a call to transfer() is made?

Since the transfer() method is marked as @Transactional, Spring will start a transaction. This same transaction will be used for calls to debit() and credit(). In other words, debit(amount) and credit(amount) will not start a new transaction. It will use the on-going transaction (since there is one).

Here's another good blog post to better understand Transactions, Caching and AOP in Spring.

Blob Handling in Java and JDBC

Whenever I search the web for storing and retrieving BLOBs to and from a database, I usually get a complicated sample that uses byte arrays (byte[]) and JDBC code. Since I usually deal with web-based enterprise applications, I need to store BLOBs (e.g. images, spreadsheets, documents, PDFs) to a database and retrieve them later (e.g. as an <img> tag in an HTML page, or downloaded from a URL).

JDBC and BLOBs

The typical way of storing BLOBs with JDBC is shown below. Note that the created objects need to be closed properly to prevent resource leaks.

Connection connection = ...getConnection();
try {
  PreparedStatement stmt =
        connection.prepareStatement("INSERT...");
  try {
    // Sometimes, a byte[] is used here :(
    InputStream bytes = new FileInputStream("..."); // e.g. image file
    try {
      ... // other parameters set
      // Unsure on how to create a Blob and use setBlob(...)
      stmt.setBinaryStream(..., bytes);
      stmt.execute();
    } finally {
      bytes.close();
    }
  } finally {
    stmt.close();
  }
} finally {
  connection.close();
}

Note that the code above is possibly an over-simplification. Most often, the BLOB is embedded with another persistent object (e.g. a Person with a photo that is stored as a BLOB).

To retrieve the BLOB, the following code is typically seen. Note that the BLOB is only available while the JDBC connection stays open. Thus, some developers resort to using byte arrays (byte[]) to temporarily store and transfer them, before the connection gets closed.

Connection connection = ...getConnection();
try {
  PreparedStatement stmt =
        connection.prepareStatement("SELECT...");
  try {
    ... // other parameters set
    ResultSet rs = stmt.executeQuery();
    try {
      InputStream bytes = rs.getBinaryStream(...);
      // Sometimes, a byte[] is used with rs.getBytes() :(
      try {
        // Must use bytes before JDBC connection gets closed
      } finally {
        bytes.close();
      }
    } finally {
      rs.close();
    }
  } finally {
    stmt.close();
  }
} finally {
  connection.close();
}

The astute reader can see (lines also highlighted) how error-prone BLOB handling is with JDBC. The lesser known Connection.createBlob() (introduced in Java 6 / JDBC 4.0) and ResultSet.getBlob(...) is not often used.

File Systems and BLOBs

Another often seen way of handling BLOBs in Java is to store them as files in a file system. But this has some drawbacks, like the tricky to use unique file names, and possible un-tracked tampering of file contents. In some cases, storing them as files may not be permitted when running in a server with a security manager (e.g. Servlet-container like Tomcat with a SecurityManager). The security manager protects the server from trojan servlets, JSPs, JSP beans, tag libraries, or even inadvertent mistakes (e.g. <% System.exit(1); %> in a JSP).

Introducing jBlubble

I find the above handling of BLOBs to be over-complicated. Because of this, I wanted to make BLOB handling easier, and less error-prone. I am also inspired by how Google's AppEngine for Java was handling BLOBs. Thus, I started looking at the BLOB-related code I have worked with in the past years, and came up with a simple API, I called jBlubble. The main interface is BlobstoreService.

public interface BlobstoreService {
  String createBlob(
      InputStream in,
      String name, String contentType) ...;
  String createBlob(
      BlobstoreWriteCallback callback,
      String name, String contentType) ...;
  BlobInfo getBlobInfo(String blobKey) ...;
  void serveBlob(String blobKey, OutputStream out) ...;
  int[] delete(String... blobKeys);
}

The code is available at my GitHub account. As of this writing, a JDBC-implementation of the API is available. Other implementations are also welcome.

The createBlob methods abstract the JDBC-related code of using java.sql.Blobs, and making sure allocated resources are closed properly. To store BLOBs uploaded to a webapp, the code looks something like:

// Servlet configured to support multipart/form-data
// HttpServletRequest
request.getPart("...").write(fileName);
// Open an input stream with the file (created from uploaded part)
InputStream in = new FileInputStream(fileName);
try {
 ... blobKey = blobstoreService.createBlob(in, ...);
} finally {
 in.close();
}

To serve the previously stored BLOB, the code looks something like:

// HttpServletResponse
BlobInfo blobInfo = blobstoreService.getBlobInfo(blobKey);
response.setContentType(blobInfo.getContentType());
OutputStream out = response.getOutputStream();
blobstoreService.serveBlob(blobKey, out);

In some cases, like when generating reports, the output stream can be stored as a BLOB too, like so:

... JasperPrint print = ...;
... JRExporter exporter = ...; //  new JRPdfExporter();
... blobKey = blobstoreService.createBlob(
    new BlobstoreWriteCallback() {
      @Override
      public long writeToOutputStream(OutputStream out) ... {
        ...
        exporter.setParameter(JRExporterParameter.JASPER_PRINT, print);
        exporter.setParameter(JRExporterParameter.OUTPUT_STREAM, out);
        exporter.exportReport();
        ...
      }
    }, ...);
// generated blobKey can then be
// used to identify the generated report

Since report generation can take its time (especially when the report has several pages), it is a good idea to store the report in a database, rather than directly writing to a servlet output stream. When the report is complete, it can be downloaded (and re-downloaded) anytime.

The key design consideration was to abstract the persistence-related specifics (like JDBC) and use a simpler API. A callback interface was used to support writing to OutputStreams to ensure that it gets closed properly. A higher level method was used to serve the BLOBs directly to an output stream. The BlobInfo contains a timestamp field that can be used to support Last-Modified HTTP header for caching purposes. As of the moment, BLOBs are immutable and cannot be updated. To change, a new BLOB should be created, delete the old one, and reference the new one.

Instead of embedding the BLOB with their related persistent objects (or entities), I find it better to simply reference the BLOB. So, instead of this...

@Entity
public class Person {
 @Id private Long id;
 @Lob byte[] photo;
 ...
}

... I find it better to just reference the BLOB like this.

@Entity
public class Person {
 @Id private Long id;
 String photoId; // retrieved via BlobstoreService
 ...
}

I've started replacing existing BLOB handling code with the API we've developed. So far, it has simplified things. I hope this will help others too. Now, it's time for a cold one.

Domain Logic Patterns (Martin Fowler's Revenue Recognition Problem)

In the training courses I've given, providing concrete examples (not just UML diagrams) help the course participants understand better. Here in this post, I'm sharing a Java-implementation of the revenue recognition problem (as described in Martin Fowler's PoEAA book). The problem is used to explain three domain logic patterns: transaction script, table module, and domain model.

Revenue Recognition Problem

Just in case you haven't read the book, here's an excerpt from the book that explains the revenue recognition problem, taken from Martin Fowler's PoEAA book (page 112).

Revenue recognition is a common problem in business systems. It's all about when you can actually count the money you receive on your books. If I sell you a cup of coffee, it's a simple matter: I give you the coffee, I take your money, and I count the money to the books that nanosecond. For many things it gets complicated, however. Say you pay me a retainer to be available that year. Even if you pay me some ridiculous fee today, I may not be able to put it on my books right away because the service is to be performed over the course of a year. One approach might be to count only one-twelfth (1/12) of that fee for each month in the year, since you might pull out of the contract after a month when you realize that writing has atrophied my programming skills.

The rules for revenue recognition are many, various, and volatile. Some are set by regulation, some by professional standards, and some by company policy. Revenue tracking ends up being quite a complex problem.

I don't fancy delving into the complexity right now, so instead we'll imagine a company that sells three (3) kinds of products: word processors, databases, and spreadsheets. According to the rules, when you sign a contract for a word processor, you can book all the revenue right away. If it's a spreadsheet, you can book one-third (1/3) today, one-third in sixty (60) days, and one-third in ninety (90) days. If it's a database, you can book one-third today, one-third in thirty (30) days, and one-third in sixty (60) days. There's no basis for these rules other than my own fevered imagination. I'm told that the real rules are equally rational.

Emphasis, formatting, and numbers added for clarity

Domain Logic Patterns Example (Java-implementation)

The working code is available at my GitHub account.

I used the same integration test (AbstractRevenueRecognitionServiceFacadeTests) and applied it against three different implementations: transaction script, table module, and domain model.

Here are the three (3) patterns:

• transaction script — organizes business logic by procedures where each procedure handles a single request from the presentation.
• domain model — an object model of the domain that incorporates both behavior and data.
• table module — a single instance that handles the business logic for all rows in a database table or view.

For transaction script and table module implementations, I used table data gateways.

For domain model, I used data mapper (via JPA/Hibernate). I tried to stick as close as possible to the database schema provided. In fact, all three patterns work with the same database schema.

The transaction script implementation can be found under the revenue.recognition.transaction.script package. The table module implementation is under the revenue.recognition.table.module package. The domain model is found under the following packages: revenue.recognition.interfaces.facade.internal, revenue.recognition.application.impl, and revenue.recognition.domain.model.

Please feel free to review the implementation, and let me know of any items I've missed.

Note that having three (3) different patterns in one application is unnecessary (and quite confusing). I've only done this to better compare the different approaches to organizing domain logic.

Kettle (Pentaho Data Integration): Connecting to Google Cloud SQL

We were playing around Google Cloud SQL and wanted to see if we can migrate some of our MySQL database data onto the cloud. We opted to use Pentaho Data Integration (aka Kettle) to do some ETL. We were surprised to see that Kettle could not connect properly to Google Cloud SQL (even if Google says it's running MySQL 5.5). So, we had to dig deeper. Here's what we found out.

We tried using the MySQL connection type. But we could not get it to use the correct URL. We also found out the following::

1. Using a MySQL connection type does not allow a custom URL. It was prefixing the URL with "jdbc:mysql://". We needed to use a URL that starts with "jdbc:google:rdbms://".
2. Using a "Generic database" connection type allows Kettle to connect successfully. But it still fails in retrieving meta information about tables/columns/datatypes.
3. Kettle optimizes by using meta data from a JDBC prepared statement (not from the result set). Unfortunately, calling PreparedStatement.getMetaData on Google Cloud SQL JDBC driver always returns null (even after the statement is executed). This prevents Kettle from proceeding to display the table in its UI.

To solve the above problems, we decided to create our own database plugin.

Creating a Database Plugin for Kettle

For more information on developing your own Kettle database plugin go to creating database plugins at Pentaho InfoCenter.

To create a database plugin for Kettle, we need to implement the DatabaseInterface. Here's what we did.

We extended from the original MySQLDatabaseMeta class to get the default behavior of a MySQL connection type. Then we override a couple of methods to achieve behavior that is specific to Google Cloud SQL. We had to specify a URL that starts with "jdbc:google:rdbms", and com.google.cloud.sql.Driver as the driver class.

// the annotation allows PDI to recognize this class as a database plug-in 
@DatabaseMetaPlugin(
    type="GoogleCloudSqlJdbc", // A globally unique ID for database plugin
    typeDescription="Google Cloud SQL" // The label to use in the database dialog
)
public class GoogleCloudSqlDatabaseMeta extends MySQLDatabaseMeta implements
        DatabaseInterface {

    @Override
    public String getDriverClass() {
        return "com.google.cloud.sql.Driver";
    }

    /**
     * As of Google App Engine SDK 1.7.4, the driver does not
     * support metadata retrieval with prepared statements.
     */
    @Override
    public boolean supportsPreparedStatementMetadataRetrieval() {
        return false;
    }

    /**
     * As of Google App Engine SDK 1.7.4, the driver only supports
     * result set metadata (which requires the execution of the
     * statement).
     */
    @Override
    public boolean supportsResultSetMetadataRetrievalOnly() {
        return true;
    }

    /**
     * Due to code that is specific to {@link MySQLDatabaseMeta},
     * a fetch size of {@link Integer#MIN_VALUE} causes an error
     * with the Google Cloud SQL driver. Thus, this is turned off.
     */
    @Override
    public boolean isFetchSizeSupported() {
        return false;
    }

    /**
     * As of Google App Engine SDK 1.7.4, the driver is not supporting
     * {@link Statement#setMaxRows(int)}.
     */
    @Override
    public boolean supportsSetMaxRows() {
        return false;
    }

    @Override
    public String getURL(String hostname, String port, String databaseName) {
        // port number is actually ignored
        return "jdbc:google:rdbms://" + hostname + "/" + databaseName;
    }

    @Override
    public String[] getUsedLibraries() {
        return new String[] { "google_sql.jar" };
    }

    @Override
    public int[] getAccessTypeList() {
        return new int[] { DatabaseMeta.TYPE_ACCESS_NATIVE };
    }

}

While tinkering with Kettle code, we found out that it has MySQLDatabaseMeta-specific code that sets a fetch size of Integer.MIN_VALUE. Something along the lines of:

    if (databaseMeta.getDatabaseInterface() instanceof MySQLDatabaseMeta
            && databaseMeta.isStreamingResults()) {
        sel_stmt.setFetchSize(Integer.MIN_VALUE);
    }

Because of the above, the Google Cloud SQL driver throws an exception. We had to disable the setting of fetch size.

Deploying the Database Plugin to Kettle

This was tested on version 4.4 of Kettle. The resulting JAR file is copied to the plugins/databases folder of your Kettle installation (e.g. /Applications/pentaho/design-tools/data-integration). The JDBC driver from Google Cloud SQL can be downloaded from http://dl.google.com/cloudsql/tools/google_sql_tool.zip, or if you already have the App Engine SDK for Java (90+ MB) (look for the google_sql.jar file under the lib/impl folder). Copy the driver to the libext/JDBC folder of your Kettle installation.

After deploying, restart Spoon (the GUI) and you should be able to see the "Google Cloud SQL" connection type.

Acknowledgements

Thanks to Karl Benavidez, Jose Araneta, and the awesome developers at Orange and Bronze Software Labs for making this possible.

Let us know if this works for you.