如何在 Java 中将 CLOB 类型转换为字符串?
javaobject oriented programmingprogramming
CLOB 通常代表字符大对象，SQL Clob 是一种内置数据类型，用于存储大量文本数据。使用此数据类型，您可以存储最多 2,147,483,647 个字符的数据。
JDBC API 的 java.sql.Clob 接口代表 CLOB 数据类型。由于 JDBC 中的 Clob 对象是使用 SQL 定位器实现的，因此它包含指向 SQL CLOB(而不是数据)的逻辑指针。
MySQL 数据库使用四个变量(即 TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT)支持此数据类型。
将 CLOB 数据类型转换为字符串
- 使用 PresparedStatement 接口的 getClob() 或 getCharacterStream() 方法从表中检索 Clob 值。
Reader r = clob.getCharacterStream();
- 从检索到的字符流中逐个读取每个字符，并将它们附加到 StringBuilder 或 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++;
- 最后，显示或存储获得的字符串。
System.out.println(buffer.toString());
示例
让我们使用以下查询 − 在 MySQL 数据库中创建一个名为 technologies_data 的表
CREATE TABLE Technologies (Name VARCHAR(255), Type VARCHAR(255), Article LONGTEXT);
表 Article 的第三列存储 CLOB 类型的数据。
以下 JDBC 程序最初在 technologies_data 表中插入 5 条记录，将文本文件(其内容)存储到 article 列(CLOB 类型)。
然后，它检索表的记录并显示文章的名称和内容。在这里，我们尝试将检索到的 CLOB 的数据转换为字符串并显示它。
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 {
//注册驱动程序
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//获取连接
String mysqlUrl = "jdbc:mysql://localhost/sampledatabase";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
System.out.println("Connection established......");
//创建 Statement 对象
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(" ");
}
}
}
输出
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.
