Can Eclipse or SQL Developer automatically unquote Java Strings?

Question

I'm aware that SQL Developer can craft Java Strings using the Advanced Format option (Ctrl+Shift+F7), but can it or Eclipse do the reverse? Is there a quick command to evaluate (or at least unquote/unwrap) Java Strings?

Given this:

"SELECT * " +
"FROM table " +
"ORDER BY status"

I want this:

SELECT *
FROM table
ORDER BY status

Ampt · Answer 1 · 2013-06-05T15:09:26.783

1

You could do a simple replace, where you look for quotes and replace them with nothing, then do the same with the + symbol. It's often easier to remove items than it is to add them, hence the handy feature for adding quotes.

Code Example

String.replaceAll("\"", "");
String.replaceAll("+", "");

If you want to be able to do it in one line, you could use regex

String pattern = "[\+\"]";
// Use the following on any string you want to format.
String.replaceAll(pattern, ""));

edited Jun 05 '13 at 15:09

answered Jun 05 '13 at 14:56

Ampt

117
6

Yes, I currently do two search-and-replace operations, one for `" +` and one for `"`, just looking for a single operation. – quietmint Jun 05 '13 at 14:57
Hmm... You could always create a regex with just + and " and then replace both of those in one statement, but I don't know if it's really going to save you any lines of code or trouble over the 2 lines above. – Ampt Jun 05 '13 at 14:59
Yeah, probably not really any time saving here. Note that removing `+` is unsafe because `(+)` is an operator in Oracle (outer join). – quietmint Jun 05 '13 at 15:39
If your code is formatted the same way every time, you could tell it to look for `"\" +"` so it will only pick up `+` when it sees a `"` followed by a space, and then the `+` – Ampt Jun 05 '13 at 17:07

score 0 · Answer 2 · answered May 19 '16 at 13:01

An alternate solution is to store the query in a SQL file and consume it as a Resource. Similarily to images (which we don't store as Base64 Encoded strings) we can store them externally to our Java (which we should because they aren't Java, they are SQL)

private String getTextResource(String Resource){
    InputStream in = getClass().getResourceAsStream(Resource);
    BufferedReader reader = new BufferedReader(new InputStreamReader(in));
    String line = null;
    ArrayList<String> lines = new ArrayList<String>();
    try {
        while((line = reader.readLine()) != null){
            lines.add(line);
        }
    } catch (IOException e) {
        return null;
    }
    return Joiner.on("\n").join(lines);
}

public float getCurrentRiskAssessmentFactor(){
    String sql = getTextResource("MyComplexSQL.sql");
    return db.getQueryStatment(sql).get(0);
}

Using this technique allows you to maintain complex queries in a testable format. The sql files can be openned and tested in your favourite SQL editor, or run (independantly) against the database. This avoids transcription errors, and effort, when switching between tools.

I worked with one developer who went as far as to abstract the entire DB access so that you could only specify SQL resources, not pass in raw SQL.

Also, install Eclipse DTP. This will allow you to run the queries directly from Eclipse (or at least give you syntax highlighting).

http://www.eclipse.org/datatools/downloads.php

Optimization Tip: If you do this, it is probably advisable to use some sort of Lazy Loading/Caching mechanism for the SQL

Can Eclipse or SQL Developer automatically unquote Java Strings?

2 Answers2