• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

10 Common Mistakes Java Developers Make when Writing SQL

 
Lukas Eder
Ranch Hand
Posts: 48
5
Java Oracle Postgres Database
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
    author & internet detective
    Marshal
    Posts: 34229
    341
    Eclipse IDE Java VI Editor
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Why not include returning too many columns if you are including not returning too many rows?
     
    Lukas Eder
    Ranch Hand
    Posts: 48
    5
    Java Oracle Postgres Database
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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 Vajsar
    Sheriff
    Posts: 3752
    62
    Chrome Netbeans IDE Oracle
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Ranch Hand
    Posts: 48
    5
    Java Oracle Postgres Database
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Rancher
    Posts: 42967
    73
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Ranch Hand
    Posts: 48
    5
    Java Oracle Postgres Database
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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://www.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
    Rancher
    Posts: 2756
    32
    Eclipse IDE Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    That is a very well written article.
     
    Lukas Eder
    Ranch Hand
    Posts: 48
    5
    Java Oracle Postgres Database
    • Likes 1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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://www.coderanch.com/how-to/java/SqlBestPractices

    Feel free to moderate the entries.
     
    Jeanne Boyarsky
    author & internet detective
    Marshal
    Posts: 34229
    341
    Eclipse IDE Java VI Editor
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Thanks Luke! I gave you a cow for your efforts.
     
    aleena jackson
    Greenhorn
    Posts: 1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    very nice post, well written , thanks for sharing
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic