将CLOB转换为字符串会删除所有换行符。

huangapple go评论98阅读模式
英文:

Converting CLOB to String removes all new line characters

问题

我有一个启用了AES加密的H2数据库。它有一个CLOB类型的列。该列包含带有换行字符的长文本(有时候)。当我在H2控制台中检查表时,输出显示插入了所有换行符,但当我查询该表时,就没有换行字符。我正在使用以下代码将CLOB对象转换为String对象:

  1. public String clobToString(Clob data) {
  2. StringBuilder sb = new StringBuilder();
  3. try {
  4. Reader reader = data.getCharacterStream();
  5. BufferedReader br = new BufferedReader(reader);
  6. String line;
  7. while(null != (line = br.readLine())) {
  8. sb.append(line);
  9. }
  10. br.close();
  11. } catch (SQLException | IOException e) {
  12. e.printStackTrace();
  13. }
  14. return sb.toString();
  15. }

我认为这里出了一些问题。是否有其他有效的方法来进行CLOB到String的转换。

英文:

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:

  1. public String clobToString(Clob data) {
  2. StringBuilder sb = new StringBuilder();
  3. try {
  4. Reader reader = data.getCharacterStream();
  5. BufferedReader br = new BufferedReader(reader);
  6. String line;
  7. while(null != (line = br.readLine())) {
  8. sb.append(line);
  9. }
  10. br.close();
  11. } catch (SQLException | IOException e) {
  12. e.printStackTrace();
  13. }
  14. return sb.toString();
  15. }

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

答案1

得分: 5

从数据库的CLOB列中获取一个String值,可以使用以下方法之一:

  1. String text = resultSet.getString("MyClobColumn");
  1. String text = clob.getSubString(1, (int) clob.length());
  1. String text;
  2. try (Reader reader = clob.getCharacterStream()) {
  3. StringBuilder sb = new StringBuilder();
  4. char[] buf = new char[8192];
  5. for (int len; (len = reader.read(buf)) != -1; )
  6. sb.append(line, 0, len);
  7. text = sb.toString();
  8. }

或者在Java 10+中使用:

  1. String text;
  2. try (Reader reader = clob.getCharacterStream()) {
  3. StringWriter sw = new StringWriter();
  4. reader.transferTo(sw);
  5. text = sw.toString();
  6. }

当然,这三种方法都假设文本适合于String,即它的长度不超过约21亿字符,或者仅约10亿字符,这取决于Java版本和所使用的字符(非Latin1)。

英文:

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

  • Use resultSet.getString():

    1. String text = resultSet.getString("MyClobColumn");
  • If the data is already taken from the ResultSet as a Clob (using getClob()), use clob.getSubString():

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

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

    Or with Java 10+:

    1. String text;
    2. try (Reader reader = clob.getCharacterStream()) {
    3. StringWriter sw = new StringWriter();
    4. reader.transferTo(sw);
    5. text = sw.toString();
    6. }

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

huangapple
  • 本文由 发表于 2020年9月7日 20:15:54
  • 转载请务必保留本文链接:https://go.coder-hub.com/63777444.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定