CODEWITHSUNDEEP
javastringh2java-11clob
Prashant Prakash
Prashant Prakash

Reputation: 135

Converting CLOB to String removes all new line characters

I have an H2 database with AES encryption enabled. It has a column of type CLOB. The column contains long texts with new line characters(some times). When I check the table in H2 console I get outputs with all the new lines inserted but when I query the table, there are no new line characters. I am using this code to convert a CLOB object to String object:

public String clobToString(Clob data) {
    StringBuilder sb = new StringBuilder();
    try {
        Reader reader = data.getCharacterStream();
        BufferedReader br = new BufferedReader(reader);

        String line;
        while(null != (line = br.readLine())) {
            sb.append(line);
        }
        br.close();
    } catch (SQLException | IOException e) {
        e.printStackTrace();
    }
    return sb.toString();
}

I think something is going here. Is there any other efficient way of doing this CLOB to String conversion.

Upvotes: 2

Views: 3495

Answers (1)

Andreas
Andreas

Reputation: 159086

To get a String value from a CLOB column in the database, use one of the following:

  • Use resultSet.getString():

    String text = resultSet.getString("MyClobColumn");

  • If the data is already taken from the ResultSet as a Clob (using getClob()), use clob.getSubString():

    String text = clob.getSubString(1, (int) clob.length());

  • If you for some obscure reason have a Reader (from resultSet.getCharacterStream() or clob.getCharacterStream()), copy the characters in blocks, and remember to use try-with-resources, e.g.

    String text;
try (Reader reader = clob.getCharacterStream()) {
    StringBuilder sb = new StringBuilder();
    char[] buf = new char[8192];
    for (int len; (len = reader.read(buf)) != -1; )
        sb.append(line, 0, len);
    text = sb.toString();
}

    Or with Java 10+:

    String text;
try (Reader reader = clob.getCharacterStream()) {
    StringWriter sw = new StringWriter();
    reader.transferTo(sw);
    text = sw.toString();
}

All three of course assumes that the text will fit in a String, i.e. that it is less than about 2.1 billion characters, or only about 1 billion characters depending on Java version and characters used (non-Latin1).

Upvotes: 6

Related Questions