About Lukas Eder

Lukas is a Java and SQL enthusiast developer. He created the Data Geekery GmbH. He is the creator of jOOQ, a comprehensive SQL library for Java, and he is blogging mostly about these three topics: Java, SQL and jOOQ.

The Code That Made me Cry

A friend of mine recently told me about the kind of problems he’s currently struggling with in the legacy application he’s maintaining. Here’s a sample piece of code to illustrate what I’m talking about:
 
 
 
 
 
 
 
 

String q = "select replace('" +
            accountNo +
           "%','- ','-') from dual";
rs = stmt.executeQuery(q);
if (rs.next()) {
    accountNoFormatted = rs.getString(1);
}

It instantly made me cry. If this is just a sample, I can imagine what the rest of the application looks like. As a matter of fact, these problems were the very reason why he thought he needed to sort out a couple of things first, before he could even think about introducing jOOQ or any other new technology in that application. Yes, there’s some serious teaching to be done (or slapping?)

If you’ve read through this article thus far without knowing what I’m talking about, then let me give you some advice. Please follow this advice to keep my friend from jumping out the window:

NEVER send such trivial logic to the database for execution!

I’ve recently blogged about various reasons why you should calculate / execute some stuff in the database. A simple string replacement is not one of those things! Heck, why risk the database round trip / network latency, connection and/or data transfer timeouts, and all sorts of other stuff for something that could be written as such in Java?

accountNo.replace("- ", "-");

The method even has the same name as the SQL function. Heck, why even go through the hassle of using the horrible JDBC API for this? Please, dear developer. Take 1h and study the entire list of methods available to java.lang.String. It’s such an awesome and completely underestimated class!

NEVER reformat previously formatted data

This is the rule of thumb: Once data is formatted, it is eternally lost and unavailable to computing / data processing. There is only one simple reason why anyone would ever format any data. It is for displaying data to human beings. Humans are not good at deciphering or memorizing things like

a56225e0-45ef-11e3-8f96-0800200c9a66

Humans are good at reading and memorizing things like:

My wife's bank account

So repeat after me. Once data is formatted, it is eternally lost and unavailable to computing / data processing. If the formatting was wrong in any way, then fix the formatting where it is wrong. NEVER re-format the previously formatted data. NEVER.

NEVER format data in the data access layer

Just as humans are incredibly bad at operating on long technical IDs, machines are incredibly bad at operating on formatted data. In fact, there are so few reasons to ever format data in the data access layer that it should probably not even occur to you. One acceptable reason is when you have a very very sophisticated, highly tuned report which runs in the DB. But you don’t have that, because you considered using the SQL replace() function to remove a whitespace from a Java string. That isn’t exactly sophisticated reporting.

So read after me. NEVER format data in the data access layer, unless you have a compelling technical reason for it. Your accountNo should remain as untouched and technical and ID-style as long as possible throughout your application. There is absolutely no need to format it for human consumption before the accountNo hits the UI.

OK, to be fair, there’s another exception to this rule. When you choose to sort data in the UI, then you might want to sort the data by the formatted version of the accountNo, as the sorting result will be consumed by a human:

SELECT ..
FROM accounts a
ORDER BY a.account_no_formatted

Be lazy

There is one very simple way to become a better programmer: Be lazy. Be too lazy to write 10 lines of code for a simple replacement of "- " by "-". By being so incredibly lazy, you will always think:

There HAS to be a better way to write this

There’s nothing wrong with not knowing. But there’s everything wrong with using the path of least resistance and writing 10 lines of code for something as trivial as this. Believe me. Your life will be so much better, once trivial stuff can be written in one-liners. You can focus again on your business logic.
 

Reference: The Code That Made me Cry from our JCG partner Lukas Eder at the JAVA, SQL, AND JOOQ blog.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

JPA Mini Book

Learn how to leverage the power of JPA in order to create robust and flexible Java applications. With this Mini Book, you will get introduced to JPA and smoothly transition to more advanced concepts.

JVM Troubleshooting Guide

The Java virtual machine is really the foundation of any Java EE platform. Learn how to master it with this advanced guide!

Given email address is already subscribed, thank you!
Oops. Something went wrong. Please try again later.
Please provide a valid email address.
Thank you, your sign-up request was successful! Please check your e-mail inbox.
Please complete the CAPTCHA.
Please fill in the required fields.

5 Responses to "The Code That Made me Cry"

  1. Dainius says:

    This is why “geeks” part in “javacodegeeks.com” makes my cry. When developers that’s implied to be good, starts complaining that hist prediction of what and why code does doesn’t match what he thinks it should do..
    Saying that you need entire framework to make a simple sql query is stupid. Of course when see that he’s creator of one of these libs than it make some sence (advertising his own framework).
    Theres’ nothing wrong about that code until you know something more than 7 lines that are presented. If you will introduce more dependencies on simple program that just print account list.. it’s how thinks gets nasty.
    It’s so childish to think that formatting output is bad (when you have no idea how it will be used, and why it’s used like this), but that entire string variable is put in sql query without any escaping – that’s not bad.

    • Lukas Eder says:

      Thanks for your interest in my article. Yes there is always a bit of jOOQ context in my blog posts which are syndicated to “javacodegeeks.com”.

      The essence of my post here, however, is not to introduce a new framework to solve an issue. The essence is to use Java’s String.replace() instead of sending a database query across the wire for the same.

  2. Philipp says:

    Even though SQL replace() function should obviously not be used to remove a whitespace from a Java string in the first place, I think it would be worth mentioning that this piece of code also suffers from a potential SQL injection! In order to build your SQL statements, use a PreparedStatement instead of String concatenation.

  3. Lukas Eder says:

    You’re absolutely right. Although, I was thinking that if you actually find the author of this beautiful code, SQL injection is not the sort of problem complexity that you want to get into explaining in the first place…

  4. Lukas Eder says:

    More examples of such code that makes me cry can be seen on this website here:

    http://www.ctmmc.net

Leave a Reply


nine + 8 =



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy | Contact
All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.
Do you want to know how to develop your skillset and become a ...
Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

Get ready to Rock!
You can download the complementary eBooks using the links below:
Close