Embedded SQL in OO languages ​​such as Java

Question

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

When I was working on IBM mainframes, the languages ​​used the SQL preprocessor to parse SQL statements from native code, so the statements could be written to the cleartext text file without obfuscating the lines, for example, EXEC SQL exists in Cobol .... Syntax Design END- EXEC, which allows pure SQL statements to be embedded in 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 very easy to read and check for errors. Between EXEC SQL tokens .... END-EXEC there are no restrictions on indentation, lines, etc., Therefore, you can format the SQL statement to taste.

Note that this example is designed to select a single row, when a result set with multiple rows is expected, the encoding is different (but still v. Is easy to read).

So, taking Java as an example

• What made the old COBOL approach undesirable? With this approach, not only SQL, but also system calls can be more readable. Let me call it the built-in preprocessor method in a foreign language.

• Will an embedded preprocessor for foreign languages ​​for SQL be useful? Would you see the advantage of writing your own SQL statements inside java code?

Edit

I really ask if you think SQL in OO is a return, and if not, what can be done to make it better.

Answer 1

There is already a standard for embedded SQL in Java called SQLJ .

Having said that, I have never seen it used in the wild, and I have no idea if this is really an option with modern tools. Oracle did it when the standard came about, but I think he died on the vine.

Answer 2

The SQL domain already has something similar to the "embedded language preprocessor" for Java and .NET: http://ibatis.apache.org/ p>

In addition, what people usually do is use a full-fledged ORM like Hibernate to abstract SQL.

Keep in mind that these tools do not allow you to store SQL strings in the Java code itself, but serve similar intentions. I personally see no benefit in storing SQL strings in the code itself, as this is usually messy. Having all the SQL neatly written in a specific file allows you to reuse and maintain your SQL. They allow SQL as strings if the need arises, but this is usually the last resort (when the ORM tool does not have a good abstraction for your use case.)

EDIT: I think mixing SQL and code (whether OO or not) is fragile and undesirable. It is much better to have a centralized place to store your requests. This is the iBATIS approach.

Answer 3

Object-relational mapping tools such as Hibernate theoretically make such things less problematic. "in theory";)

Also, if you can use Grails, I heard that you can just write fantastic multi-line strings to make it easy to read SQL statements.

Answer 4

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

• As mentioned above, there is a technique in java that does this: SQLJ , which has never been accepted.
• ORM is usually used to get a similar result ( iBatis and Hibernate , which I’m most about
• C # has LINQ that does something similar

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

• It tends to associate your language with a database provider, since the various sql dialogs are very different. This is not an option for most modern languages. This was not a problem for COBOL, as portability was not required.
• This makes the language more complex or requires pre-processing, both are bad things in themselves. But with modern IDEs, this is even worse, since it would be difficult for them to process sql inside their code (although they actually start to do it even when sql is built into Strings). This was not a problem for COBOL, since in the modern sense it is an ugly language in any case (although it was probably good when it was invented).
• Compilation requires resources that are difficult to control (name the database) and follow a completely different approach, and then "normal" programming. Again, the COBOL program and its database are largely fused together, so there is no problem.
• SQL is not suitable for the OO paradigm. It returns two-dimensional arrays of values, not objects. So you still need ORM.

DSL, on the other hand, is all hype. And there are languages ​​that have XML literals. Therefore, I think it is quite possible that languages ​​appear (or already exist) that have capabilities built into ORM and allow you to use SQL (or HQL?) Code, such as DSL, in your code.

Answer 5

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

Answer 6

One of the main drawbacks of an approach such as SQLJ is the fact that preprocessors will prevent the use of modern IDE tools to access logic data. With great IDEs like Eclipse , NetBeans , or IntelliJ IDEA , this is a strong argument against preprocessors ( we also wrote about this in our blog ).

At the same time, DSL tools like Eclipse Xtext or JetBrains MPS are still trying to improve the Java language as such in order to have something like what you used in COBOL.

One option is to use an internal DSL such as jOOQ for the job, although this is actually not SQL

Answer 7

Well, the easiest, most sensible way to do this is to simply include your SQL in a line in your code.

Something like

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

It may not be very difficult, but it works. The downside is that the compiler cannot validate your SQL syntax. A slightly better solution is Prepaired Statementments , where you specify a parametric template. This way you can do things like:

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

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

Then in your code later, when you want to change the parameter you are doing:

 s.setString(1, "CA"); 

Microsoft has a built-in query language for .net called LINQ . For databases, you use LINQ to SQL , which allows you to embed queries directly in your code.