In Eclipse, how can get the SQL text when it's in a Java String?

Question

I have a very long SQL statement inside of a Java String. In eclipse, how can I easily get the SQL statement's text without the Java String syntax? I've been manually removing it, but in IntelliJ Idea, it's possible for one to get the text without having to do any manual formatting work to remove the Java String syntax (such as " and + characters). Does Eclipse have a similar feature?

As an added bonus, I would ask if it were also possible to get the string with the newlines put in place as it was formatted in the IDE, even if no \n characters were present in the string itself.

Answer 1

Depending on your SQL client, it could have this feature. I know that TOAD has this feature built-in. I also remember Squirrel having it.

So when pasted into the SQL editor of TOAD, the code is automatically extracted from the Java string syntax. I remember that it also worked with constructs like StringBuffer/StringBuilder.

See: http://dev.toadformysql.com/webhelp/Content/Editor/Convert_SQL_Embedded_SQL.htm

Or you can create a simple regexp working for your sample code, for example this jsfiddle. It works with a regex for sql like:

"select something \r\n" +
"  from dual " +
 "where 1 = 1\r\n"; 

The jsfiddle should output

select something
  from dual
 where 1=1

Still it is not beautifully formatted (even if with the <pre> tag it should preserve spaces), but your SQL IDE should help you with that, no?

Answer 2

(we are using Kepler)

1. This StringUtils plugin looks quite good: http://marketplace.eclipse.org/content/stringutils-plugin (click screenshots).

   • not found in Eclipse market place dialog for install
   • downloaded the master ZIP from their GIT repo, but did not go on in favor of solution 5.

2. Together with the Java Multi-Line String Editor plugin it will be really easy to write/read/debug such SQL statements: https://marketplace.eclipse.org/content/java-multi-line-string-editor

   • not found in Eclipse market place dialog for install

3. Another nice solution seems the Multiline annotation where one can maintain the string in the javadoc (which will be transformed in a properly formatted string on compile time): https://github.com/benelog/multiline

4. externalizing large Strings/SQLs in separate *.sql files (and reading them e.g. from the classpath) would be the 2nd best option if it is appropriate:

   • https://stackoverflow.com/a/878648/1915920
   • https://stackoverflow.com/a/878776/1915920

5. The best solution for us was to use Groovy (THE "Java scripting extension") alongside our Java code:

   • you need to install the Groovy plugin https://github.com/groovy/groovy-eclipse/wiki
   • just saying New -> Groovy Class added the Groovy Nature automatically to our project and after that, you can implement your SQLs as constants or enums similar to this:
     • supports almost everything (even between the Java-Groovy barriers) that Java (Editors) support:
       • code highlighting
       • refactoring (renaming), but only from within Groovy file
       • auto-completion/CTRL+rightclick-navigation
       • Javadoc hinting

src/my/pkg/SQLs.groovy:

package my.pkg

class SQLs {

  /** comment X */
  static final String SEL_X = ```
    select
      bla,
      foo
    from
      bar
    where
      x in (1,2,3)
  ```
}

src/my/pkg/SqlExec.java:

// ...
stmt.executeQuery( SQLs.getSEL_X() ) ;  /* Groovy auto-created this getSEL_X() 
                                        interface method transparently/auto-
                                        suggested, but the "comment X" is not 
                                        hinted */

and more proper for our use case was to use the SQLs as an enum:

enum SQLs {

  /** comment X */
  SEL_X(```
    select
      ...
      x in (1,2,3)
  ```)

  String sql

  SQLs( String sql ) { this.sql = sql }
}

which can be accessed within src/my/pkg/SqlExec.java as this:

// the JavaDoc comment "comment X" works and the getSql() is transparently 
// auto-generated by Groovy
stmt.executeQuery( SQLs.SEL_X.getSql() ) ;

And more sugar with debugging it all within Eclipse with DTP :-)

=> so debugging these SQLs will be as easy as:

1. SQLs.groovy file => Open with ... => Other => SQL Editor (from the very nice Eclipse Data Tools Platform (DTP) plugin: https://eclipse.org/datatools/)

   • you can always switch between editors as you like (Eclipse remembers the last used per file)

2. selecting the proper connection for this file from the custom pre-configured db connections (must be open/connected, if not done already)

3. marking to-be-debugged SQL + ALT+X

4. debug query results as tabbed or text-only results ...