• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

10 Common Mistakes Java Developers Make when Writing SQL

 
Ranch Hand
Posts: 50
5
Oracle Postgres Database Java
  • Likes 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
     
    author & internet detective
    Posts: 41860
    908
    Eclipse IDE VI Editor Java
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • 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: 50
    5
    Oracle Postgres Database Java
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • 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 :-)
     
    Sheriff
    Posts: 3837
    66
    Netbeans IDE Oracle Firefox Browser
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • 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: 50
    5
    Oracle Postgres Database Java
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • 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?
     
    Rancher
    Posts: 43081
    77
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • 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: 50
    5
    Oracle Postgres Database Java
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • 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://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 :-)
     
    Rancher
    Posts: 2759
    32
    Eclipse IDE Spring Tomcat Server
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    That is a very well written article.
     
    Lukas Eder
    Ranch Hand
    Posts: 50
    5
    Oracle Postgres Database Java
    • Likes 1
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • 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://coderanch.com/how-to/java/SqlBestPractices

    Feel free to moderate the entries.
     
    Jeanne Boyarsky
    author & internet detective
    Posts: 41860
    908
    Eclipse IDE VI Editor Java
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Thanks Luke! I gave you a cow for your efforts.
     
    Greenhorn
    Posts: 1
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    very nice post, well written , thanks for sharing
     
    Consider Paul's rocket mass heater.
    reply
      Bookmark Topic Watch Topic
    • New Topic