Is there any way I can write (copy-paste) nicely-formatted SQL queries in Java string literals using Eclipse?

Question

When I wish to use SQL queries in Java, I usually hold them in final String variables. Now, when the string is too large, it goes out of page breadth, and we either have to manually break it (In eclipse, go to a particular place in the string, and place enter, and do it for each of the resulting smaller part), or we can set the formatter in Eclipse to allow only (say) 100 characters per line. But the string is not broken in a logical manner.

I can format a query nicely in SQL Developer (say), but if I paste that in Java, I will have to manually set all the end quotes, and + symbols to make it a proper Java string. I just want to know a way to have a properly formatted SQL query copy-pasted directly into a Java file. I am using Eclipse.

Perhaps when a query is formatted like :

SELECT
  *
 FROM
  something
 WHERE
  id=4;

then when its pasted inside a java string, we can have it like :

"SELECT" +
  " *" +
 " FROM" +
  " something" + 
 " WHERE";
  id=4;

Lukas Eder · Answer 1 · 2013-10-13T07:04:07.460

19

Here's a creative solution if you don't mind the extra whitespace in generated SQL output:

Step 1: Paste

enter image description here

Paste your formatted SQL statement verbatim into your Java file

Step 2: Write opening quotes

enter image description here

Notice the highlighted button, the sixth from the left. That's the awesome "Block Selection Mode" (Alt-Shift-A on Windows). It lets you write opening quotes on each selected line at the same position

Step 3: Write closing quotes and concatenation

enter image description here

Apply the same technique for the closing quotes and the concatenation sign (+)

Step 4: Fix the semi-colon

enter image description here

No comment needed here.

edited Oct 13 '13 at 07:04

answered Oct 12 '13 at 12:46

Lukas Eder

211,314
129
689
1,509

1

It would be good to mention that block selection can also be toggled (as can anything) using Ctrl+3 (Cmd+3 on Mac) and typing *block selection*. Also useful e.g. for Show Whitespace Characters. – robinst Oct 12 '13 at 13:26
Yes, thanks @robinst. I always forget about Ctrl+3. I hardly get beyond Ctrl+1 :-) – Lukas Eder Oct 12 '13 at 13:36

score 14 · Accepted Answer · edited May 23 '17 at 12:09

14

In Eclipse, in Window > Preferences under Java > Editor > Typing check the "Escape text when pasting into a string literal" checkbox.

It will format the plain text:

line1
line2
line3

to:

private final String TEXT = "line1\r\n" + 
            "line2\r\n" + 
            "line3";

This is the output on Eclipse Helios. A similar question: Surround with quotation marks

edited May 23 '17 at 12:09

Community

1
1

answered Oct 18 '12 at 07:43

yiannis

1,421
12
21

score 6 · Answer 3 · answered Oct 18 '12 at 07:09

When you are in SQLDeveloper, select the query and use Ctrl + F7 to format it. Select it again and use Ctrl + Shift + F7 to advance format it, choose Clipboard for output destination, output type as desired type and click Apply. Now, paste it in Eclipse editor and see the difference.

I'm using Version 3.1.07 of SQLDeveloper.

score 4 · Answer 4 · answered Oct 18 '12 at 08:00

4

Actually a really good question, I often wonder about that as well. One tip I can give you is using the following:

//@formatter:off
    private static final String QUERY = 
    "SELECT t.* " +
      "FROM table t " +
     "WHERE t.age > 18";
//@formatter:on

It does not convert a SQL query into a Java String literal, but it keeps Eclipse from reformatting your String awkwardly unreadable.

answered Oct 18 '12 at 08:00

Does this need an additional plugin or config? Doesn't work for me. – Dave Richardson Oct 18 '12 at 08:59
There is an option in the java code formatter preferences that you have to turn on for this to work. – SpaceTrucker Oct 18 '12 at 09:08
Indeed, you need to check this option in your checkstyle preferences of Eclipse. – Oct 18 '12 at 17:08

score 2 · Answer 5 · edited Jul 11 '15 at 14:25

2

I know that I'm late to this party, but for anyone using large queries, the block mode in Eclipse can be very difficult to work with, because it slows way down if you have a query with more than 100 lines or so (depending on your machine).

I found this site and it is very simple and very fast. Post your code into a window, add a prefix (") and add a suffix (" +) and click save as. It will add the prefix and suffix to each line quite quickly.

Here's the online tool:

http://textmechanic.co/Add-Prefix-Suffix-to-Text.html

edited Jul 11 '15 at 14:25

George Chalhoub

14,968
3
38
61

answered Apr 28 '14 at 16:59

thebbk

31
3

thanks, must admit I even thought about creating smthn similar :) – skwisgaar Feb 28 '17 at 22:38

score 1 · Answer 6 · answered Oct 18 '12 at 07:47

1

For DbVisualizer, you may use Ctrl + Alt + K to format your sql to desired pattern.

answered Oct 18 '12 at 07:47

Drogba

4,186
4
23
31

Is there any way to keep DbVisualizer from putting nearly every word of the query on its own line? – Noumenon Jan 10 '17 at 21:19

score 1 · Answer 7 · edited May 23 '17 at 11:46

1

This is quite close: how to paste your SQL indented with leading whitespace: https://stackoverflow.com/a/121513/1665128

String sql =
    "SELECT\n" + 
    "  *\n" + 
    " FROM\n" + 
    "  something\n" + 
    " WHERE\n" + 
    "  id=4;";

edited May 23 '17 at 11:46

Community

1
1

answered Oct 18 '12 at 09:09

full.stack.ex

1,747
2
11
13

score 1 · Answer 8 · answered Nov 04 '21 at 16:29

1

If you're using Java 15+ use a text block:

private final String someSql = """
      Select 
        colA,
        colB 
      From 
        myTable
      Where
        something = 'abc'
""";

answered Nov 04 '21 at 16:29

DenverDave

55
7

Is there any way I can write (copy-paste) nicely-formatted SQL queries in Java string literals using Eclipse?

8 Answers8

Step 1: Paste

Step 2: Write opening quotes

Step 3: Write closing quotes and concatenation

Step 4: Fix the semi-colon

Linked