Embedded SQL in OO languages like Java

One of the things that annoys me working with SQL in OO languages is having to define SQL statements in strings.

When I used to work on IBM mainframes, the languages used an SQL preprocessor to parse SQL statements out of the native code, so the statements could be written in cleartext SQL without the obfuscation of st开发者_StackOverflow中文版rings, for instance in Cobol there is a EXEC SQL .... END-EXEC syntax construct that allows pure SQL statements to be embedded in the Cobol code.

<pure cobol code, including assignment of value
 to local variable HOSTVARIABLE>    

EXEC SQL
       SELECT COL_A, COL_B, COL_C
       INTO :COLA, :COLB, :COLC
       FROM TAB_A
       WHERE COL_D = :HOSTVARIABLE
END_EXEC

<more cobol code, variables COLA, COLB, COLC have been set>

...this makes the SQL statement really easy to read & check for errors. Between the EXEC SQL .... END-EXEC tokens there are no constraints on indentation, linebreaking etc., so you can format the SQL statement according to taste.

Note that this example is for a single-row select, when a multiple-row resultset is expected, the coding is different (but still v. easy to read).

So, taking Java as an example

1. What made the "old COBOL" approach undesirable ? Not only SQL, but system calls could be made much more readable with that approach. Let's call it the embedded foreign language preprocessor approach.

2. Would an embedded foreign language preprocessor for SQL be useful to implement ? Would you see a benefit in being able to write native SQL statements inside java code ?

Edit

I'm really asking if you think SQL in OO languages is a throwback, and if not then what could be done to make it better.

There is already a standard for embedded SQL in Java, it's called SQLJ.

Having said that, I've never seen it used in the wild, and I've no idea if it's really an option any more, with modern tools. Oracle went for it in a big way when the standard appeared, but I think it died on the vine.

There already is something similar to an 'embedded language preprocessor' for Java and .NET in the SQL domain: http://ibatis.apache.org/

Also, what people usually do is to use a full fledged ORM, like Hibernate, to abstract away SQL.

Mind you, these tools do not allow to store the SQL strings in Java code itself, but serve a similar intent. I personally see no benefit in storing the SQL strings in the code itself, as that is usually messier. Having all the SQL neatly written in a specific file aids reusability and maintainability of your SQL. They do allow SQL as strings if the need should arise, but that is usually as a last resort measure (when the ORM tool doesn't have a good abstraction for your use case.)

EDIT: I do think mixing SQL and code (be it OO or not) is fragile and not desirable. It's a lot better to have a centralized place to store your queries. This is the iBATIS approach.

Object-Relational mapping tools, such as Hibernate, theoretically make this sort of stuff less of an issue. "theoretically" ;)

Also, if you can use Grails, I've heard that you can just write fantastic multi-line strings which makes reading SQL statements easier.

You can workaround such an inability with a decent IDE. For example, IntelliJ IDEA supports a features which is called injected languages. It allows you to write code in a language you want inside of a string literal and be able to use code highlighting, completion, navigation and other services. You can read more about it here: http://blogs.jetbrains.com/idea/2009/03/user-defined-language-injection/

For the current state of affairs I can offer the following points

• As mentioned above there is technic in java doing that: SQLJ which never took of.
• Typically one uses an ORM to get a similiar result (iBatis and Hibernate being the ones I here most about
• C# has LINQ which does something similiar

There are many problems with embedding sql as part of the language:

• It tends to tie your language to a database vendor, since the various sql dialects are very different. This is a no go for most modern languages. This wasn't a problem for COBOL since portability was not a requirement.
• It makes either the language way more complex or requires preprocessing, both are bad things in their own right. But with modern IDEs this is even worse, since they would have a hard time to handle sql inside their code (although they start to actually do it, even when sql is embedded in Strings). This wasn't a problem for COBOL, since in a modern sense it is a butt ugly language anyway (although it probably was nice when it was invented)
• It requires resources for compiling that are hard to control (namingly the database) and follow a completely different approach then 'normal' programming. Again a COBOL Programm and it's database is pretty much fused together so no problem there.
• SQL doesn't fit to the OO Paradigm. It returns two dimensional arrays of values, not objects. So you need some kind of ORM anyway.

On the other hand DSLs are all the hype now. And there are languages that have XML literals. So I think it is quite possible that languages emerge (or already exist) that have ORM like capabilities embedded in them and allow you to use a SQL (or HQL?) like DSL inside the code.

Well, the simplest, most brain-dead way to do this is simply to include your SQL as a string in your code.

Something like

   Statement s = new Statement("Select * from wherever");

That may not be very sophisticated, but it works. The downside is that the compiler can't check your SQL syntax. Slightly better solution is Prepaired Statements where you specify a parametic template. So you can do stuff like:

PreparedStatement s = connection.prepareStatement("Select * from wherever where state = ?");

That way, your JDBC connection should throw an exception as soon as you create the prepared statement at run time. So if the code works the first time, it should always work.

Then in your code later when you want to change the parameter you do:

s.setString(1, "CA");

Microsoft has an embedded query language for .net called LINQ. For databases you use LINQ to SQL which lets you embed queries right in your code.