[FIXED] Converting CLOB to String removes all new line characters

Issue

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.

Solution

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).

Answered By - Andreas
Answer Checked By - Mary Flores (JavaFixing Volunteer)