DevOps Zone is brought to you in partnership with:

I am the founder and CEO of Data Geekery GmbH, located in Zurich, Switzerland. With our company, we have been selling database products and services around Java and SQL since 2013. Ever since my Master's studies at EPFL in 2006, I have been fascinated by the interaction of Java and SQL. Most of this experience I have obtained in the Swiss E-Banking field through various variants (JDBC, Hibernate, mostly with Oracle). I am happy to share this knowledge at various conferences, JUGs, in-house presentations and on our blog. Lukas is a DZone MVB and is not an employee of DZone and has posted 255 posts at DZone. You can read more from them at their website. View Full User Profile

The Code That Made Me Cry

11.07.2013
| 21266 views |
  • submit to reddit

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. As in Code That Made Me Cry, or #CTMMC. 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 reasonswhy 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.

Published at DZone with permission of Lukas Eder, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Giovanni Cândid... replied on Thu, 2013/11/07 - 9:22pm

Good article. The only this code makes me cry to. kkkk

I'm so lazy, that I'm constant searching for better ways in everything I do, and I always find them, a good programmer is almost never satisfied with the code.

Camilo Rivera replied on Thu, 2013/11/07 - 9:57pm

This is why I hate ORMs most of the time. An average programmer is usually never sure about how many database calls a particular ORM code performs.

Ender Orak replied on Fri, 2013/11/08 - 2:15am

Lukas,

While I understand your efforts to praise and promote your own open source project (jooq?) it really started to become annoying to see you referencing the project in every single article. It makes me think that the whole purpose of the article is just marketing even if it is not the case. So I recommend you to make a clear seperation between related articles and not so related ones like this. 

Also as another friendly suggestion, please find a better and more easily pronounciable name for your project.

Juraj Misur replied on Fri, 2013/11/08 - 5:26am in response to: Ender Orak

I'm also under impression that this example is totally extreme and not a real threat to happen to an "average" developer. 

The only important thing here IMHO is the potential for sql injection by creating query in this style...

Lukas Eder replied on Fri, 2013/11/08 - 8:05am in response to: Ender Orak

Hi Ender,

Thank you for your feedback. Do note that all of my posts are originally written on our product blog, which is called Java, SQL ,and jOOQ. When DZone curators see posts by any DZone MVB that they deem interesting for syndication on DZone, they copy the entire post and attribute authorship at the top and at the bottom of each syndicated post.

I agree that in this particular post, the jOOQ reference appears out of context. But I'm sure you will agree that this is not the case in the original article.

Now, if you do feel that there is too much branded content in my articles on DZone, I suggest you communicate this concern with the DZone curators. In this particular case, the article would not have lost any meaning, if that reference had been edited and removed by DZone curators.

In the mean time, I hope you may be able to enjoy my articles and skip over the occasional brand reference.

Sincerely,
Lukas

Lukas Eder replied on Fri, 2013/11/08 - 8:08am in response to: Juraj Misur

Juraj, as a matter of fact, this concrete example is real, and I have seen many similar concrete examples. Not necessarily the actual database call, but also the fact that formatted content is re-formatted. Both problems are equally severe if they appear systematically over a code base.

And you're totally right. The risk of SQL injection is significant.

Lukas Eder replied on Fri, 2013/11/08 - 8:10am in response to: Camilo Rivera

Camilo, I'm not really sure how this article is related to ORMs ;-)

Martín Proenza replied on Fri, 2013/11/08 - 10:43am

Hey, you might have created a new term (unless it already exists and I'm not aware): CTMMC (Code That Made Me Cry). Now we can refer to every piece of code we find flaws in, as a CTMMC; and even make an article about it.

Also, I think using the term as a result of reviewing one of your folk's code could be something fun as well as constructive: "Hey, this is a CTMMC dude. No offense."

Raging Infernoz replied on Sat, 2013/11/09 - 12:11pm

I wouldn't cry, I'd swear, then ask why this moron was allowed to do any enterprise Java development!

Dina Zjaca replied on Wed, 2013/11/13 - 8:18am in response to: Giovanni Cândido Da Silva

 Well sometimes better performance code means actually more lines..

Lukas Eder replied on Wed, 2013/11/13 - 8:36am in response to: Martín Proenza

That is brilliant! The #CTMMC hashtag on Twitter! :-)

Terry Milan replied on Wed, 2013/11/13 - 10:17am

Wow, I guess this one slipped right through the <strong>code review</strong>.  What is worse than the code above, is the fact there is no one to set this individual straight.  Scan the source repository for this person's name and you will be able to write a book.

Peter Headland replied on Wed, 2013/11/13 - 9:33pm

What's impressive about this is the sheer depth of onion-layers of error in so small a fragment of code. Sadly, this is far from an isolated case: the experience of reviewing code and coming up with more errors than the number of lines is all too familiar.

Lukas Eder replied on Thu, 2013/11/14 - 3:40am in response to: Peter Headland

There's actually a (not 100% accurate) claim that the number of bugs increases with the factorial of the number of lines of code...

Lukas Eder replied on Wed, 2013/11/20 - 12:06pm in response to: Martín Proenza

Martin! Your #CTMMC idea was so awesome, let's see if it works: http://www.ctmmc.net
Tweet this, share it, let's see if we can create a new, geeky acronym!

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.