How to convert CLOB type to string in Java?

CLOB Typically stands for Character Large Object, SQL Clob is a built-in data type used to store large amounts of text data. Using this data type, you can store up to 2,147,483,647 characters of data.

The java.sql.Clob interface of the JDBC API represents the CLOB data type. Because the Clob object in JDBC is implemented using SQL locators, it holds a logical pointer to the SQL CLOB (not the data).

MySQL The database provides support for this data type using four variables, namely TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT.

Convert the CLOB data type to a string

• Retrieve the Clob value from the table using the PreparedStatement interface's getClob() or getCharacterStream() method.

Reader r = clob.getCharacterStream();

• Read each character one by one from the retrieved character stream and append it to a StringBuilder or StringBuffer.

int j = 0;
StringBuffer buffer = new StringBuffer();
int ch;
while ((ch = r.read())!=-1) {
   buffer.append(""+(char)ch);
}
System.out.println(buffer.toString());
j++;

• Finally display or store the obtained String.

System.out.println(buffer.toString());

Example

Let us create a table named technologies_data in MySQL database using the following query -

CREATE TABLE Technologies (Name VARCHAR(255), Type VARCHAR(255), Article LONGTEXT);

Table Article The third column stores CLOB type data.

The following JDBC program initially inserts 5 records into an Article column (CLOB) in the Technologies_data table that stores a text file (its contents).

It then retrieves the table's records and displays the article's name and content. Here we try to convert the retrieved CLOB data to String and display it.

import java.io.FileReader;
import java.io.Reader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class ClobToString {
   public static void main(String args[]) throws Exception {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/sampledatabase";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Creating a Statement object
      Statement stmt = con.createStatement();
      //Inserting values
      String query = "INSERT INTO Technologies_data VALUES (?, ?, ?)";
      PreparedStatement pstmt = con.prepareStatement(query);
      pstmt.setString(1, "JavaFX");
      pstmt.setString(2, "Java Library");
      FileReader reader = new FileReader("E:\images\javafx_contents.txt");
      pstmt.setClob(3, reader);
      pstmt.execute();
      pstmt.setString(1, "CoffeeScript");
      pstmt.setString(2, "Scripting Language");
      reader = new FileReader("E:\images\coffeescript_contents.txt");
      pstmt.setClob(3, reader);
      pstmt.execute();
      pstmt.setString(1, "Cassandra");
      pstmt.setString(2, "NoSQL Database");
      reader = new FileReader("E:\images\cassandra_contents.txt");
      pstmt.setClob(3, reader);
      pstmt.execute();
      //Retrieving the data
      ResultSet rs = stmt.executeQuery("select * from Technologies_data");
      System.out.println("Contents of the table are: ");
      while(rs.next()) {
         System.out.println("Article: "+rs.getString("Name"));
         Clob clob = rs.getClob("Article");
         Reader r = clob.getCharacterStream();
         StringBuffer buffer = new StringBuffer();
         int ch;
         while ((ch = r.read())!=-1) {
            buffer.append(""+(char)ch);
         }
         System.out.println("Contents: "+buffer.toString());
         System.out.println(" ");
      }
   }
}

Output

Connection established......
Contents of the table are:
Article: JavaFX
Contents: JavaFX is a Java library using which you can develop Rich Internet Applications. By using Java technology, these applications have a browser penetration rate of 76%.
Article: CoffeeScript
Contents: CoffeeScript is a lightweight language based on Ruby and Python which transcompiles (compiles from one source language to another) into JavaScript. It provides better syntax avoiding the quirky parts of JavaScript, still retaining the flexibility and beauty of the language.
Article: Cassandra
Contents: Apache Cassandra is a highly scalable, high-performance distributed database designed to handle large amounts of data across many commodity servers,
providing high availability with no single point of failure. It is a type of NoSQL database. Let us first understand what a NoSQL database does.

The above is the detailed content of How to convert CLOB type to string in Java?. For more information, please follow other related articles on the PHP Chinese website!