Retrieving CLOB Values with getClob() Method

JDBC for MySQL - Herong's Tutorial Examples

∟MySQL CLOB (Character Large Object) - TEXT

∟Retrieving CLOB Values with getClob() Method

This section describes how to retrieve CLOB values with the ResultSet.getClob() method.

If you like to work with java.sql.Clob objects, you can retrieve CLOB values with the getClob() method on ResultSet objects. The Clob object offers some interesting methods:

length() - Returns the number of characters in the Clob object. The return value has a type of "long". You may need to convert it to "int" to be used in other methods.
getSubString(long pos, int length) - Returns a substring of characters from the Clob object with a specified starting position and length. Note the start position is "long" value, but the length is "int" value.
getCharacterStream() - Returns a Reader object from the Clob object so that you can read the content as a stream.
free() - Releases the resources that the Clob object holds. This was added in JDBC 4.0 (Java 1.6).

Here is my test program on getClob() method:

/* MySqlClobGetClob.java
 * Copyright (c) HerongYang.com. All Rights Reserved.
 */
import java.io.*;
import java.sql.*;
public class MySqlClobGetClob {
  public static void main(String [] args) {
    Connection con = null;
    try {
      com.mysql.cj.jdbc.MysqlDataSource ds
        = new com.mysql.cj.jdbc.MysqlDataSource();
      ds.setServerName("localhost");
      ds.setPortNumber(3306);
      ds.setDatabaseName("HerongDB");
      ds.setUser("Herong");
      ds.setPassword("TopSecret");
      ds.setServerTimezone(java.util.TimeZone.getDefault().getID());
      con = ds.getConnection();

// Retrieving CLOB value with getClob()
      Statement sta = con.createStatement();
      ResultSet res = sta.executeQuery("SELECT * FROM Article");
      int i = 0;
      while (res.next() && i<3) {
        i++;
        System.out.println("Record ID: "+res.getInt("ID"));
        System.out.println("   Subject = "+res.getString("Subject"));
        Clob bodyOut = res.getClob("Body");
        int length = (int) bodyOut.length();
        System.out.println("   Body Size = "+length);
        String body = bodyOut.getSubString(1, length);
        if (body.length() > 100) body = body.substring(0,100);
        System.out.println("   Body = "+body+"...");
        bodyOut.free(); // new in JDBC 4.0
      }
      res.close();

      sta.close();
      con.close();
    } catch (Exception e) {
      System.err.println("Exception: "+e.getMessage());
      e.printStackTrace();
    }
  }
}

The output confirms that the getClob() method and Clob objects are not hard to use:

herong> java -cp .:mysql-connector-java.jar \
   MySqlClobGetClob.java

Record ID: 1
   Subject = Test on INSERT statement
   Body Size = 84
   Body = A BLOB (Binary Large OBject) is a large chunk of data which
is stored in a database....
Record ID: 2
   Subject = Test of the setString() method
   Body Size = 304
   Body = He is wonderful and strange and who knows how old he is, he
thought. Never have I had such a strong ...
Record ID: 5
   Subject = Test of setCharacterStream() methods
   Body Size = 2218
   Body = /* MySqlClobSetCharacterStream.java
 * Copyright (c) HerongYang.com. All Rights Reserved.
 */
...