10 Common Mistakes Java Developers Make when Writing SQL

[Lukas Eder]

10 Common Mistakes Java Developers Make when Writing SQL

Java developers mix object-oriented thinking with imperative thinking, depending on their levels of:

Skill (anyone can code imperatively)

Dogma (some use the "Pattern-Pattern", i.e. the pattern of applying patterns everywhere and giving them names)

Mood (true OO is more clumsy to write than imperative code. At first)

But when Java developers write SQL, everything changes. SQL is a declarative language that has nothing to do with either object-oriented or imperative thinking. It is very easy to express a query in SQL. It is not so easy to express it optimally or correctly. Not only do developers need to re-think their programming paradigm, they also need to think in terms of set theory.

In this post, I have listed some common mistakes that a Java developer makes when writing SQL

[Jeanne Boyarsky]

Why not include returning too many columns if you are including not returning too many rows?

[Lukas Eder]

Jeanne Boyarsky wrote:Why not include returning too many columns if you are including not returning too many rows?

I'll include it in the "10 More Common Mistakes" article :-)

[Martin Vashko]

I have always thought that the most common mistake is not using PreparedStatement, either out of ignorance, or - less often - because of a belief, usually based on a flawed performance test, that a simple Statement is faster.

But in any case, these are important points and a nice article. Lucas, wouldn't you want to incorporate some of these points into our SQL Best practices wiki page? It doesn't cover anything from your article yet.

[Lukas Eder]

Martin Vajsar wrote:I have always thought that the most common mistake is not using PreparedStatement, either out of ignorance, or - less often - because of a belief, usually based on a flawed performance test, that a simple Statement is faster.

Hmm, true, another one for the "10 More Common Mistakes" sequel post. Of course, Statement can be faster than PreparedStatement when you explicitly want to avoid problems derived from bad statistics and histograms in your database, if your database supports cursor caching (talking about Oracle). But even then, the problem should probably be fixed in the database, not in Java.

Martin Vajsar wrote:But in any case, these are important points and a nice article. Lucas, wouldn't you want to incorporate some of these points into our SQL Best practices wiki page? It doesn't cover anything from your article yet.

How is authorship and reference attributed in that Wiki?

[Ulf Dittmer]

How is authorship and reference attributed in that Wiki?

The wiki is a community effort, so there are no author names attached to it. Just putting a link to your writeup with a short abstract might also be a good addition to the wiki.

[Lukas Eder]

Ulf Dittmer wrote:

How is authorship and reference attributed in that Wiki?

The wiki is a community effort, so there are no author names attached to it. Just putting a link to your writeup with a short abstract might also be a good addition to the wiki.

Thanks for the hints. I'm still trying to get used to this forum and what can be done here. So I have now copied one sample paragraph to the Wiki as a grounds for discussion:

https://coderanch.com/how-to/java/SqlForgettingAboutNull

Would that be an acceptable format? I don't want to spend too much time rewriting things in a new format or new wording. But I'd be fine with copy pasting from my own blog as long as the source is cited. Otherwise, feel free to summarise what I've posted in your own words and put it up on the Wiki :-)

[Jayesh A Lalwani]

That is a very well written article.

[Lukas Eder]

Ulf Dittmer wrote:

How is authorship and reference attributed in that Wiki?

The wiki is a community effort, so there are no author names attached to it. Just putting a link to your writeup with a short abstract might also be a good addition to the wiki.

So I've added all 10 common mistakes to the Wiki here:

https://coderanch.com/how-to/java/SqlBestPractices

Feel free to moderate the entries.

[Jeanne Boyarsky]

Thanks Luke! I gave you a cow for your efforts.

[aleena jackson]

very nice post, well written , thanks for sharing