aspose file tools*
The moose likes Blog around the Campfire and the fly likes 10 Common Mistakes Java Developers Make when Writing SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Other » Blog around the Campfire
Bookmark "10 Common Mistakes Java Developers Make when Writing SQL" Watch "10 Common Mistakes Java Developers Make when Writing SQL" New topic
Author

10 Common Mistakes Java Developers Make when Writing SQL

Lukas Eder
Ranch Hand

Joined: Jul 22, 2013
Posts: 41
    
    4

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


    When Java and SQL work together, great software can evolve. That's why I have created jOOQ. Follow me on blog.jooq.org
    Jeanne Boyarsky
    author & internet detective
    Marshal

    Joined: May 26, 2003
    Posts: 30929
        
    158

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


    [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
    Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
    Lukas Eder
    Ranch Hand

    Joined: Jul 22, 2013
    Posts: 41
        
        4

    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

    Joined: Aug 22, 2010
    Posts: 3611
        
      60

    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

    Joined: Jul 22, 2013
    Posts: 41
        
        4

    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
    Marshal

    Joined: Mar 22, 2005
    Posts: 42612
        
      65
    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.


    Ping & DNS - my free Android networking tools app
    Lukas Eder
    Ranch Hand

    Joined: Jul 22, 2013
    Posts: 41
        
        4

    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
    Bartender

    Joined: Jan 17, 2008
    Posts: 2434
        
      28

    That is a very well written article.
    Lukas Eder
    Ranch Hand

    Joined: Jul 22, 2013
    Posts: 41
        
        4

    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

    Joined: May 26, 2003
    Posts: 30929
        
    158

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

    Joined: Sep 06, 2013
    Posts: 1
    very nice post, well written , thanks for sharing


    alex
     
     
    subject: 10 Common Mistakes Java Developers Make when Writing SQL