• dkAllen (unregistered) in reply to Been there
    Been there:
    We have lots of code like this in the codebase where I work.

    My condolences. Try not to add to the insanity.

    Been there:
    1. Your IDE doesn't warn you about strings that need localization, since those strings are now constants.

    You need a non-stupid IDE. Coding stupid in order to out-smart a stupid IDE is not smart.

    Been there:
    2. You get auto-completion and typo-avoidance.

    Auto-completion is nice, except when it get in your way (which it does, often). You do not get typo-avoidance, you just get a compiler error instead of a run-time error, which is more helpful I suppose, but one should really learn how to type more consistently. Laziness produces bugs.

    Been there:
    Granted, most of our devs don't write code this way. But it's not wrong.

    It isn't right. It works, but it isn't right. Ergo, it is wrong.

  • coder_mom (unregistered) in reply to Ryan
    Ryan:
    Has the number of submissions to this site become so low that this is one of the better choices? I sense that Alex is trying to juggle too many projects.

    Anyway... In addition to the excessive flexibility of this code, I assume that "mstr" is short for "master". Would typing one extra character really be that much more work so that you don't have the "mental overhead" of having to deal with abbreviations?

    One extra character would have been fine, but apparently two extra characters was just going too far...

  • Bobby Jones (unregistered)

    One word : Hibernate

  • Dave (unregistered) in reply to Homer

    I don't see the point at all unless of course like you said all the basic key words change, functional but I don't see the value at all.

  • (cs) in reply to DES
    DES:
    With a proper abstraction layer (JDBC does not qualify), there is no need to write SQL code at all; the abstraction layer translates method calls on a query object into whatever query language the underlying RDBMS uses, which may or may not be some sort of SQL dialect.
    You've misunderstood the point of JDBC. It's not there to abstract away from SQL, it's there to abstract away from the majority of crap about connecting to different SQL databases. (It doesn't abstract away from the different SQL dialects, but if it did that then it would be difficult to do DB-specific code; there's a use for that sort of thing.)

    The usual problem with high-level abstraction layers is that they too often work by bringing the whole results of the queries into memory. At that point, you've got horrible problems with large result sets and re-sorting things that could have been put in order in the DB in the first place. By the time you've put everything in place to avoid all the nasty gotchas, you might as well be using SQL, since you've sure got that level of complexity already. (Scattering the SQL that you're using all over the place though... that'd be dumb.)

  • (cs)

    After the apocalypse, a lone survivor will begin an epic journey to the disaster recovery site, guided by a mysterious Oracle*. With him, he will carry the only remaining copy of a book that carries the secret to restoring civilization - the pocket SQL guide.

    *Is there any other kind?

  • Craig (unregistered)

    It's a poor man's Linq!

  • Scott Peterson (unregistered)

    Long, long ago in another life I was a COBOL and Pl/1 programmer writing writing code to access IBM's mainframe data base, IMS. Since those old versions of COBOL did not support literals in CALL statements we had to code the IMS queries using exactly that type of structure using storage variables for parenthesis, logic signs, etc.

    I guess some ideas never die.

  • Thomas (unregistered)

    I wonder if maybe the original coder of this was used to writing macros for Excel, where the keywords change according to the locale of the system on which the code is running?

  • Aussie Contractor (unregistered) in reply to Been there
    Been there:
    We have lots of code like this in the codebase where I work. There are several advantages:

    We have many examples of code like this at our work too.

    There are several advantages:

    1. We are all contractors so we make out like bandits on any maintenance work
    2. We are all contractors so we have higher contract security due to the overly complex and bloated code base.

    capcha: valetudo - the place where contractors ski.

  • FieryFred (unregistered)

    So how do you do a table join?

  • Metacoder (unregistered)

    This code is good. Pointless abstraction is beautiful. Criticism only makes me smugger, because it is obvious that you are not enlightened.

  • (cs) in reply to SR
    SR:
    If you need to guard against that level of WTFery you need some new developers.

    Thank you!

  • (cs) in reply to Homer
    Homer:
    Here are my results and now I want them in DESCENDING order. Oh Crap!

    That is an easy fix. Just write the output to a file and use a bubble sort until it is in the correct order.

  • anon (unregistered)

    Actually, I think it's quite good. The variables being declared as static saves them from having it garbage-collected as soon as the resulting string is not used.

  • (cs) in reply to FieryFred
    FieryFred:
    So how do you do a table join?

    Manually from two result sets.

  • SurturZ (unregistered)

    LINQ anyone?

  • Sylver (unregistered)

    Bo-ring! These posts are getting increasingly tame and lame.

    Where have the Paula Beans of the world gone?

    For f* sakes, there isn't just one way to code that is holy and sacred.

    How long has it been since the last genuine WTF?

  • Chris (unregistered) in reply to Been there

    I'm guessing that there's no performance hit either, since the string is easy for the compiler to substitute in place during preprocessing since it's never redefined. Makes sense, if a bit unorthodox.

  • Mike (unregistered)

    I require the full sql grammar added by yesterday,

    http://savage.net.au/SQL/sql-2003-2.bnf.html

    get started!

  • Roel Vlemmings (unregistered)

    It does enhance the readability of the code, never a bad thing.

  • Sveta (unregistered)

    Hello World v2

    private static final String HELLO = "Hello";
private static final String COMMA = ",";
private static final String SPACE = " ";
private static final String WORLD = "World";
private static final String EXCLAMATION = "!";

public static void main(String[] args) {
	System.out.println(HELLO + COMMA + SPACE + WORLD + EXCLAMATION);
}
  • Patrick Tingen (unregistered)

    Actually, I don't find it so bad. I cannot imagine that the original developer really thinks the keywords for SQL will change in the foreseeable future. A construction like this certainly has readability advantages. The code is clearer, your IDE can offer keyword completion, plus it avoids stupid typos like this

    strQuery = "select" + this.mstrSelect + "form" + this.mstrTable; (btw find the 4 errors)

    That you normally only see when running the code.

  • Zappes (unregistered)

    I Find this quite readable - piecing together SQL from string literals tends to clutter your code with lots of quotes that are, in my opinion, distracting. I guess I wouldn't do anything like that myself, but I can see why one would like to do it.

  • (cs) in reply to SurturZ
    SurturZ:
    LINQ anyone?
    Hey, you're totally the first person to point that out! For reals!
  • leppie (unregistered)

    State management FTW!

  • csrster (unregistered) in reply to Been there
    Been there:
    We have lots of code like this in the codebase where I work. There are several advantages:
    1. Your IDE doesn't warn you about strings that need localization, since those strings are now constants.
    2. You get auto-completion and typo-avoidance.

    Granted, most of our devs don't write code this way. But it's not wrong.

    Some IDEs can do that even without jumping through string-building hoops. Intellij IDEA recognises and highlights SQL query-strings without any extra magic. For example in

    String sql1 = "SELECT * FROM tbl1 WHERE  x=3";
PreparedStatement stmt = conn.prepareStatement(sql1);

    it bolds the SQL keywords SELECT,FROM,WHERE. Iirc, it also recognises Hibernate QL and spellchecks names of entities/fields. A puritan, of course, would say that this is just encouraging bad coding practice. However my experience is that trying to build SQL queries out of elementary constants causes more errors (due to unreadability) than it solves.

  • Bernhard Hofmann (unregistered)

    But it doesn't cater for changes in the grammar. For example, we could not alter the constants to create Linq queries that don't start with the select word.

  • TS (unregistered) in reply to C.K.

    That's exactly what I thought.

    LINQ might have appeared absurd in the pre-implementation phase, too.

    Not a WTF to me.

  • Anonymous (unregistered) in reply to FieryFred
    FieryFred:
    So how do you do a table join?
    Just pull up a seat and sit down, friend.
  • Tiran Kenja (unregistered)

    I guess it is just one of those things all Java developers need to do: Build their own abstraction of SQL queries.

    Must have been early in this guys carrier tho. He didn't even know about the += operator or StringBuffer (or StringBuilder) ;)

  • Suede (unregistered)

    Stop posting WTF:s that are not WTF:s!!!

    As earlier mentioned this is a good way to avoid typos. This class might be extended with a bunch of new queries written, then its nice to know there is not a typo somewhere... Rules out a bunch of pontential bugs.

    /Suede

  • SCB (unregistered) in reply to Suede
    Suede:
    Stop posting WTF:s that are not WTF:s!!!

    As earlier mentioned this is a good way to avoid typos. This class might be extended with a bunch of new queries written, then its nice to know there is not a typo somewhere... Rules out a bunch of pontential bugs.

    /Suede

    So do you never test anything you write? I guess if your compiler says there are no errors then there can't possibly be any bugs in your software...

  • Anonymous (unregistered) in reply to Suede
    Suede:
    Stop posting WTF:s that are not WTF:s!!!

    As earlier mentioned this is a good way to avoid typos. This class might be extended with a bunch of new queries written, then its nice to know there is not a typo somewhere... Rules out a bunch of pontential bugs.

    /Suede

    You can spot the schoolkids a mile off; they see the other schoolkid's code and think to themselves "that's exactly how I would have done it so this is surely not a WTF!". Guess what Sparky, you've still got some learning to do.

  • mango (unregistered) in reply to Been there
    Been there:
    We have lots of code like this in the codebase where I work. There are several advantages:
    1. Your IDE doesn't warn you about strings that need localization, since those strings are now constants.
    2. You get auto-completion and typo-avoidance.

    Granted, most of our devs don't write code this way. But it's not wrong.

    So, when I set this.mstrTable to "* FROM foo; DELETE FROM foo;#", the resulting database behaviour is "not wrong"?

    I wish I could credit you with being ironic, but your tone is too straight-faced; you are, apparently, just simply missing the point. However cleverly you do it, building up SQL statements using string concatenation is no substitute for prepared statements or stored procedures.

  • Jason Y (unregistered) in reply to Been there

    Exactly.

    This is pretty good as far as custom sql query-building goes (unless I missed something).

  • Gnubeutel (unregistered)

    Nice how the SQL syntax is kept while keywords may be changed.

    I remember some books on programming from the 90s that were translated into german including the code samples.

    And this code is easily adaptable if your database is set to use internationalized SQL commands like

    WÄHLE * VON benutzer WO name IST NICHT NULL

  • Jason Y (unregistered) in reply to Jason Y

    and I'm less likely to miss something if I read the comments, which I have not done. hehe

  • Homer (unregistered) in reply to Sylver

    What was your last submission?

  • Jonathan (unregistered)

    I think the real WTF is that the programmer used String, instead of StringBuffer or StringBuilder.

  • Harley Quinn (unregistered) in reply to Been there

    It's not terribly correct either. Perhaps efficient would be a better modifier.

    1. String x = ""; <--- waste of memory, String is immutable.
    2. All the String concatenations ... more wasting memory because...String is immutable and new instances are created for each concatenation.

    If you really must create your SQL strings this way, at least have the decency to use StringBuffer.

  • C (unregistered) in reply to doofus
    doofus:
    #define FALSE (0 != 0) #define FALSE ((! FALSE))
    I'm guessing that second line should be:

    #define TRUE (!(FALSE))

  • coyo (unregistered) in reply to Been there
    Been there:
    We have lots of code like this in the codebase where I work. There are several advantages:
    1. Your IDE doesn't warn you about strings that need localization, since those strings are now constants.
    2. You get auto-completion and typo-avoidance.

    Granted, most of our devs don't write code this way. But it's not wrong.

    Yikes. Programming to accommodate an IDE? What if the IDE changes? But seriously, things like this are the reason why programs are always many instructions too long and contain more bugs. (assuming bugcount is proportional to LOC)

  • (cs) in reply to coyo

    [quote user="coyo] ...assuming bugcount is proportional to LOC... [/quote]

    That's a big assumption. I like to think that bugcount is O(SLC)

  • (cs) in reply to Anonymous Coward
    Anonymous Coward:
    Besides using string manipulation to build SQL queries, which is a bit last decade, I see no problem with the code.

    It is a bit last decade. That's a first sign of a problem. If this code was written before the advent of OR/SQL mappers or the general adoption of strategies for externalizing SQL statements, then it is understandable. But if that piece of code was written 2004 or later, well...

    Anonymous Coward:
    Where's the WTF? Building query strings (specially managing the spaces between keywords) can be a bit tricky and error prone,

    Why is why you don't do it. You rely on externalizing SQL statements as much as possible, not on building them at run-time. Building SQL statements in Java, whether with raw string literals or constants, that's very sophomorish.

    It is also a problem when you think of it as a SQL-statement building problem, when in reality, it is deeper: it is a problem of coding RDBMS access logic in a general purpose, non-RDBMS language.

    Anonymous Coward:
    so if someone decided to use shortcuts to do it (and again specially if they can be used in multiple places) whats the problem?

    The problem is that it is not a solution. It's like patching a dental cavity with bubble gum. There are better, more solid alternatives to do just that - SQL mappers, SQL statements dynamically extracted from property files, OR mappers, etc.

    Aren't there enough real WTF out there?

    Reinventing the wheel, and poorly, that's a WTF. In this case, de-coupling SQL literals out of code.

    It is certainly not an atrocious piece of code, but neither it is an example of good practice. It was an attempt at what looked like a good idea, based on the limited understanding of a much deeper coding problem.

    At least they tried, which proved they were trying to do the correct thing.

  • Frank Bakker (unregistered) in reply to Quastiophor

    And what about SQL injection?? Looks like a real WTF to me

  • iveqy (unregistered) in reply to Been there

    [cite]

    1. Your IDE doesn't warn you about strings that need localization, since those strings are now constants.
    2. You get auto-completion and typo-avoidance. [/cite]

    So you write bad code because you're using a bad IDE? Makes sense...

  • John Carter (unregistered)

    Well, this is a valid WTF.

    If the project doesn't warrant the use of an ORM, then you should just write raw sql (don't tell me, that your IDE can't highlight it).

    If it does (which is more likely), then just use it (the ORM) and you won't see those selects at all.

    WTF?

    Obviously, if this was written for use on an embedded system, then this is a valid approach, - the SQL keywords must be written out by hand on a piece of paper, put on a wooden table, photographed and converted into C constants, since you have no file system and all.

  • (cs) in reply to luis.espinal
    luis.espinal:
    Anonymous Coward:
    Besides using string manipulation to build SQL queries, which is a bit last decade, I see no problem with the code.

    It is a bit last decade. That's a first sign of a problem. If this code was written before the advent of OR/SQL mappers or the general adoption of strategies for externalizing SQL statements, then it is understandable. But if that piece of code was written 2004 or later, well...

    Anonymous Coward:
    Where's the WTF? Building query strings (specially managing the spaces between keywords) can be a bit tricky and error prone,

    Why is why you don't do it. You rely on externalizing SQL statements as much as possible, not on building them at run-time. Building SQL statements in Java, whether with raw string literals or constants, that's very sophomorish.

    It is also a problem when you think of it as a SQL-statement building problem, when in reality, it is deeper: it is a problem of coding RDBMS access logic in a general purpose, non-RDBMS language.

    Anonymous Coward:
    so if someone decided to use shortcuts to do it (and again specially if they can be used in multiple places) whats the problem?

    The problem is that it is not a solution. It's like patching a dental cavity with bubble gum. There are better, more solid alternatives to do just that - SQL mappers, SQL statements dynamically extracted from property files, OR mappers, etc.

    Aren't there enough real WTF out there?

    Reinventing the wheel, and poorly, that's a WTF. In this case, de-coupling SQL literals out of code.

    It is certainly not an atrocious piece of code, but neither it is an example of good practice. It was an attempt at what looked like a good idea, based on the limited understanding of a much deeper coding problem.

    At least they tried, which proved they were trying to do the correct thing.

    So...What is your one size fits all solution to using SQL directly from Java or any other general purpose language? What if the code is intended to be used with several different DBMS implementations?

  • grammer nasty (unregistered) in reply to Been there
    Been there:
    We have lots of code like this in the codebase where I work. There are several advantages:
    1. Your IDE doesn't warn you about strings that need localization, since those strings are now constants.
    2. You get auto-completion and typo-avoidance.

    Granted, most of our devs don't write code this way. But it's not wrong.

    Quite. Just because you say it's a WTF, doesn't mean it is a WTF. And this ain't.

Leave a comment on “Piecemeal SQL”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article