java resultset.getstring("col_name") query

Question

I have a simple query regarding ResultSet.getString() method in java for JDBC.
Suppose the value in the Database column is having a \ which is javas escape character e.g. \n or \t etc.
When i retrieve the value as getString() i see one more escape character is getting added and the actual meaning of this \n is now a string literal only.
So i had to unescape java and then use it properly.

String s= rs.getString("col_name");

When s contains `\n':

System.out.println(s)

output:

\n

After unescaping java using apache common StringEscapeUtils
output:

System.out.println("hi"+s+"hello");
hi
hello

My question is who is adding this extra \ before unescaping?? Sorry if it is a dumb question.

Looks like JDBC is escaping all special charactres to make sure the text is displayed the way it is stored in DB. So if you have *hello\nworld* in DB it will become `hello\\nworld` when fetched. Looks the way it should be. — svz, Apr 05 '13 at 07:05
@svz JDBC does not escape anything in the `ResultSet`. __That__ is the way it should be. Otherwise many apps would have problems to know what is really in the DB and what some arbitrary layer has changed. — A.H., Apr 05 '13 at 09:34

A.H. · Accepted Answer · 2013-04-05T09:30:59.663

The JDBC driver does no escaping in the ResultSet. It would be very bad if it does. Look at this simple example:

The database table x does contain one column value. There are two rows: One with a two character string ('\' and 'n') and one witch a one character string (the newline character). I've added a string-length to the output for clarification.

select *, length(value) from x;
 value | length 
-------+--------
 \n    |      2
      +|      1
       |

This table is read with JDBC:

    Connection db = DriverManager.getConnection( /* these parameters are up to you */ );

    Statement st = db.createStatement();
    ResultSet rs = st.executeQuery("select value, length(value) from x");
    while(rs.next()){
        String value = rs.getString(1);
        int length = rs.getInt(2);

        System.out.println("length="+length+", value='"+value+"'");
    }

You see: no explicit escaping anywhere in the code so far. And the output is:

length=2, value='\n'
length=1, value='
'

You see: There is still nothing escaped - neither by the code nor by JDBC.

BUT

Things get a bit murky if you mix in Java literals: If you do something like this:

st.execute("insert into x values ('\n')");

then guess what happened? You have another row with one character!

length=2, value='\n'
length=1, value='
'
length=1, value='
'

This is because the Java compiler translated the two characters '\n' into one newline character. Hence the JDBC driver and the database only see one character.

But if you would have read some userinput and the user would have typed \ and n then nobody would de-escape this and the row would have contained two characters.

Next step

You say, you do explicit de-escaping using StringEscapeUtils. Then this would happen:

The row with one character will pass unaltered from the DB up to the screen.
The row with two characters will be changed by StringEscapeUtils into a string containing one characters. After that both row look like the same to you.

Summary

Do not confuse String-Literal escaping by the compiler with escaping by JDBC (which won't happen).

Do not add unnecessary escaping layers.

unescaping is required because for my case i want it to act as **\n** and not as literal. — Anubhab, Apr 05 '13 at 10:41
@Anubhab That might be, but is it relevant for the question? The question is about JDBC. If your _display logic_ acts differently than your _edit logic_ then it is out of scope of the Q. — A.H., Apr 05 '13 at 11:01
Fine..My last question is if Resultset has return `\\` and `n` then why does Syso prints it as `\n` why not as newline?? — Anubhab, Apr 05 '13 at 11:07
As already said: The _only_ one doing the '\n' conversion is the compiler at compile time and only for String literals. Everyone else does not touch the data. Hence also not `System.out.println` at runtime. — A.H., Apr 05 '13 at 11:24

JB Nizet · Answer 2 · 2013-04-05T07:43:23.447

1

getString() doesn't escape anything.

If you have an EOL character (written as "\n" in a Java String literal, but being a single character in the string, whose numeric value is 10) in the string stored in the database, then getString() will return a String containing this character. Printing this String will result in two lines.

If you have a \ character followed by a n character (which would thus be written as "\\n" in a Java String literal, but would only result in two characters in the string, \ and n), then getString() will return a String containing these two characters. Printing this String will result in \n to be printed.

In short, \n is used to be able to add a newline character in a String literal, in Java source code. But this is translated by the compiler to a String containing just one newline character. Not to a String containing a backslash followed by a n. At runtime, these escape sequences don't exist anymore.

edited Apr 05 '13 at 07:43

answered Apr 05 '13 at 07:35

JB Nizet

678,734
91
1,224
1,255

Fixed. StackOverflow seems to transform double backslashes into a single one. – JB Nizet Apr 05 '13 at 07:44
I got your point but my question is who is adding the extra `\\` then?? – Anubhab Apr 05 '13 at 07:58
@Anubhab Or the database really contains two characters '\' and 'n' which `StringEscapeUtils` dutifully translated into the newline character. – A.H. Apr 05 '13 at 09:02

java resultset.getstring("col_name") query

2 Answers2

Linked