Skip to main content

Embedding SQL in JAVA

10 replies [Last post]
joeys
Offline
Joined: 2004-04-07

One annoying thing with Java is the difficulty to embed SQL statements that span multiple lines of text. It would be nice if a String enclosed in double quotes could span multiple lines of code. This would improve readability and maintainability.

For Example if I wantted to embed the following SAL statement in java

Select * FROM A
INNER JOIN B ON A.ID = B.ID
INNER JOIN C ON B.ID = C.ID
INNER JOIN D ON C.ID = D.ID
INNER JOIN E ON D.ID = E.ID

I would have to do one of the following things

String sqlString = “Select * FROM A”
+ “INNER JOIN B ON A.ID = B.ID”
+ “INNER JOIN C ON B.ID = C.ID”
+ “INNER JOIN D ON C.ID = D.ID”
+ “INNER JOIN E ON D.ID = E.ID”;

or

String sqlString = “Select * FROM A INNER JOIN B ON A.ID = B.ID INNER JOIN C ON B.ID = C.ID INNER JOIN D ON C.ID = D.ID INNER JOIN E ON D.ID = E.ID“;

Would it be nice to just be able to do the following ?

String sqlString = “
Select * FROM A
INNER JOIN B ON A.ID = B.ID
INNER JOIN C ON B.ID = C.ID
INNER JOIN D ON C.ID = D.ID
INNER JOIN E ON D.ID = E.ID
“;

Reply viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
joeys
Offline
Joined: 2004-04-07

C# has a feature like this, by placing the '@' character before the ‘”’ character.

For Example in C# code
string a = @”
AAAAA
BBBB
“;

fuerte
Offline
Joined: 2004-11-22

Yes. There is a feature request for this:

Add support for vertabim string literals
http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=4472509

Sun thinks that "This is yet another request for a syntactic sugar to save some user from typing. It's not worth it.".

I think that this is very important, this is perhaps the most annoying "feature" in Java. It's not only SQL, but regular expressions as well, where you have to double every \ character.

My suggestion is the following:

string a = @"SELECT 1,
2,
3
FROM t"@;

so that the string can have embedded " characters as well. @" in the beginning and "@ in the end.

sarnoth
Offline
Joined: 2003-06-12

I like the idea of a different type of string literal that does not use escaping. Regular expressions and calling external programs on windows are good examples of string that could benefit. As a simple example of a windows command, "dir \"C:\\Program Files\"" is not very readable in source today.
However in the interest of readability I would rather see something that looks more "string-like" than @". The back quote character (`) could be used, for example. It is used rarely enough that it should not be a problem that it can't be included in the new type of string literal. It should also be easier on parsers than the alternative since @ already has significance and ` currently does not.

`dir "C:\Program Files"`

I never really had a problem with multi-line strings. I don't really have a problem with adding them either, if we are going to define a new type of string literal. However, I think that if they are added some rules will need to be defined to make them easier to deal with. The biggest sticking point that I see is line returns. Source files have different types of line returns depending on where they are written, and they can change from one type to another as they move. I think that line returns in a multi-line string litteral should be defined to be a specific type regardless of the format of the source file.

fuerte
Offline
Joined: 2004-11-22

Yes, ` would probably be easier to implement. The problem with ` is that it is a bit difficult to see, it looks like rubbish on screen, and also it requires three keystrokes in Finnish keyboard to write it: First Shift+´ and then space. @" takes four keystrokes, AltGr+2 and Shift+2. How about forward ´ then, is it used, I think not? It only takes two keystrokes, ´ and space in Finnish keyboard. Oddly § takes only one keystroke.

I think that the line returns in multi-line strings are not so big problem, because usually it does not make any difference if it is \n or \r\n or \r, it is just white space in SQL grammar, and also it probably prints correctly no matter which one it is. I think that it should be either defined as \n always or it could use the system property line.separator (but then the regular string literal opcode is not enough, because the line feeds should be converted).

joeys
Offline
Joined: 2004-04-07

The compiler should accept all character in between the double quotes and not force the developer to embedding escape characters.

for example
String value = "\nA A A A\nB B B\n”

Should also be able to write is as

String value = "
A A A A
B B B
";

of course the ‘”’ character would still need to be escaped. " AAA\"A\""

aviadbd
Offline
Joined: 2004-07-04

Also, and against the example you gave of

"
A A A A
B B B
"

How would the compiler know if there's a space after the last A or B?

Just a thought.

Aviad.

fuerte
Offline
Joined: 2004-11-22

> Also, and against the example you gave of
>
> "
> A A A A
> B B B
> "
>
> How would the compiler know if there's a space after
> the last A or B?

It does not make any difference, it is just white space (in SQL). And the compiler knows, because it can read the source code byte by byte. It knows if there is a space or not.

jwenting
Offline
Joined: 2003-12-02

not annoying at all.
The only language I used that does what you want is Python and I must say I didn't much care for it. Makes it far harder to figure out where string literals end.

tackline
Offline
Joined: 2003-06-19

You don't use editors with lexical highlighting?

aviadbd
Offline
Joined: 2004-07-04

Well, in C++ you could do:

"SELECT * "
"FROM mytable "
"WHERE a = a "
"AND b = b"

Which was readable. Also, it's not just about readability. Having a + sign there is just forcing a call to the append() method. Or maybe it doesn't with optimization? I don't really know. But it just looks ugly with all the + signs.

Aviad.