I want to write lambdas, not SQL

When lambda was introduced in Java, I was over the moon. I thought it was the best feature ever… soo useful! Though lambdas is no longer a new feature in Java (it has been in production for ~1.5 years), I have not used it nearly as much as I wanted. Why? Because the application I write most of my code for use plain old JDBC. Why is that a problem, why can I not use lambdas anyway?

Look at the figure below, these two examples do the same thing, one in SQL and one with Lambdas:

SQL

private static final String GET_CALCULATED_RESULT =
    "SELECT SUM(vertrans_amount) AS verifSum " +
    "FROM verif_trans vt, verifs v, periods p, accounts a, financial_years fy " +
    "WHERE vt.acc_id = a.acc_id AND a.acc_num >= 3000 AND a.acc_num < 9000 " +
    "AND vt.ver_id = v.ver_id AND v.period_id = p.period_id " +
    "AND p.finyear_id = fy.finyear_id AND fy.finyear_id = ? ";

Lambdas / Object model

SLMoney totSum = allVerifications.stream()
    .flatMap(verification -> verification.getVerifTransactions())
    .filter(verifTrans -> verifTrans.getAccount().isProfitAndLossAccount())
    .map(verifTrans -> verifTrans.getAmount())
    .collect(SLMoney.sum());

The two examples above do the same thing, why is the lambda version better?

Here are some reasons, the really good reasons require some more explaining (more about that later):

  • The lambda version can be parsed by the IDE and compiler (that will tell me if I am making some syntactic error or misspelled something)
  • The lambda version can be easily tested in a unit test. The JDBC version require testing in a real database that first must be populated with test data.
  • The lambda version require no extra code around. The JDBC code is best written in a separate class with a DAO interface in front to keep the java code readable.
  • The lambda version is written in Java, the other is not (only wrapped in java).

Object model – the key to using lambdas

To efficiently use lambdas on our domain, we need a real object model. An object model where our entities have real relations to each other. Not relations through database ids but real java references. Like this:

organization.getFirstFinancialYear().getAccouns().get(0);

And not like this.

FinancialYear financialYear = financialYearDao.getFinancialYear(organization.getFirstFinancialYearId());
List<Accounts> accounts = accountsDao.getAccounts(financialYear.getId());
Account account = accounts.get(0);

One way of getting what I want

It would be tempting to throw away the application and start all over again with the object model approach. But for several reasons, a big rewrite of our application is not the best option . Instead, we’ll have to “dig where we stand”. When we introduce an object model, the “old” code must still work in parallel. The new object model can be used when implementing new features.

The obvious way to introduce the object model is an ORM tool! This way we can keep the database while still exposing it as an object model.

An ORM tool would have to have the following characteristics:

  • It must be uncached, because it will have to work in parallel with old code written for JDBC.
  • I would like the entities to be immutable (why? more about this in my next blog post : Event Sourcing)
  • I would like the object model NOT to mirror the data model entirely. A naive ORM tool would map database tables to objects that exactly mimic the table with all the attributes. I do not want this because I would like to hide some attributes that are not needed in an object model (like database Ids and unused columns). And in some cases, I want to remodel the entities and relations between them in a way that does not directly map to the database.

My choice fell on Slick, a Scala ORM framework.. And as an extra bonus, no SQL whatsoever is needed since Slick lets me write all database queries/updates/inserts in pure Scala (with lambdas!).

20% Project

I told my boss Pål about my ideas, how we could simplify things, reduce the number of lines-of-code (LOC) and make the work more fun. Being a visionary guy (and an ex developer himself), Pål immediately saw the potential. We discussed the idea and decided that I could dedicate some time to work on this. I would dedicate one day a week to this project, a so called “20% project”.

Results

I have now spent 60 hours implementing the object model in our flagship product (the accounting application). And it really looks promising! We can now write new functionality using lambdas (in Java or Scala) like figure 1. The amount of code needed to write a new service in the application is reduced to a fraction, while the readability is improved.

Exporting the object model in JSON

A really cool side effect of this is that we can now export customer data from the database as JSON! Since the object model is entirely made of Java/Scala objects, I can use reflection to export all the data in the model as JSON (and then importing it again). We have attempted this before (extracting customer data for one customer in JSON or XML) but doing this directly from the database has has been too complex (there is 120 database tables involved). Now it is trivial The class that serializes the entire object model as JSON is 58 LOC long and the deserializer is 73 LOC.

Number of code lines left (a thought experiment)

Code is a liability. This is the view of myself and my colleagues. If some LOC can be removed, they should! (if readability is maintained). Less code means less number of bugs and less code to maintain. Therefore it is interesting to see if and how much code we can save if we would rewrite the entire application using the new object model and lambdas.

Of course, all the old JDBC code could be removed. All other Java code could be rewritten using lambdas (I assume this code would be ~1/3 of the original size, I think this is a conservative estimate). Some code will be added to the new Scala slick database classes and entities.

Total accounting code 161857
JDBC classes (SQL + Java) -17606
DAO interfaces -5181
JDBC test classes -16869
Old Model objects -27419
All other code -2/3 * 94782
Scala slick DB classes +4572
New entities +6508
Total LOC left 42474

LOC left: 26% of the original

That’s a real big win by itself! But, wait till you see whats comes next…

Next blog post

In my next blog post, I will explain the really big advantages of the object model. Namely: a way to introduce Event Sourcing and removing the entire database.

Leave a Reply