`

How To Handle CLOBs Easily in JDBC?

阅读更多

How To Handle CLOBs Easily in JDBC?

Prior to Oracle JDBC 10g, to manipulate the CLOB data in JDBC, Oracle extension class oracle.sql.CLOB was used. But now, Oracle JDBC 10g has a few enhancements that simplifies the CLOB manipulation in JDBC applications. This enables handling of large data using some of the available standard APIs, instead of using the Oracle extension classes. These could be thought as shortcuts for inserting and retrieving CLOB data from the database.

The enhanced standard APIs for manipulating CLOBs are the setString() and getString() of the PreparedStatement and ResultSet objects respectively. By default, the method preparedStatement.setString() will allow processing of the strings up to 32765 bytes. In order to insert data greater than 32765 bytes, a newly introduced Connection property - SetBigStringTryClob can be set. This forces the preparedStatement.setString() to use another newly introduced method, OraclePreparedStatement.setStringForClob() instead. This is actually done internally, transparent to the user.

Nevertheless, the newly introduced method OraclePreparedStatement.setStringForClob() alone can also be used instead of the standard APIs. This method makes the check on the data size internally again.

ResultSet.getString() can still be used to read the CLOB column. For the getString() and the setString()operations, the size limit for the string to be read or inserted is the one imposed by Java, that is, a positive int; the smallest being 0 or 1 byte.

Note: Do not use the setString() to bind large data to VARCHAR and LONG database columns, since it may truncate the data or cause errors.

In summary, PreparedStatement.setString() comes handy for processing the CLOB data, by just setting the Connection property SetBigStringTryClob. However, handling very large amounts of data this way may not be a wise; streaming the data is a better alternative.

Following is the code snippet to set the Connection property to process large data using the standard APIs. Refer the full source code in the file: ClobManipulationIn10g.java

import java.sql.Connection;
import java.sql.DriverManager;
import oracle.jdbc.OracleDriver;
import java.util.Properties;
..........		  

// Load the database details into the variables.
String url      = "jdbc:oracle:thin:@localhost:1521:orcl";
String user     = "scott";
String password = "tiger";

// Create the properties object that holds all database details
Properties props = new Properties();
props.put("user", user );
props.put("password", password);
props.put("SetBigStringTryClob", "true");

// Load the Oracle JDBC driver class.
DriverManager.registerDriver(new OracleDriver());     
 
// Get the database connection 
Connection conn = DriverManager.getConnection( this.url, this.props );
          

The code snippet to create an Oracle database table with a CLOB column in it.

// SQL statement 
CREATE TABLE clob_tab (clob_col CLOB);

Once the Connection property - SetBigStringTryClob is set, use the standard preparedStatement.setString() method for binding data more than 32765 bytes.
PreparedStatement pstmt = conn.prepareStatement(
                                  "INSERT INTO clob_tab VALUES(?)");

// Read a big file(larger than 32765 bytes). 
// Note: method readFile() not listed here. 
// It can be any method that reads a file.
String str = this.readFile("bigFile.txt");

// The string data is automatically transformed into a CLOB and 
// inserted into the database column. 
// Make sure that the Connection property - 'SetBigStringTryClob' is 
// set to true for the insert to happen.
pstmt.setString(1, str);
pstmt.executeUpdate();

Instead of the standard APIs, Oracle extension APIs can be used. OraclePreparedStatement.setStringForClob() can be used for binding data greater than 32765 bytes.
import java.sql.*;
import java.io.*;
import java.util.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;
..........

// Create SQL query to insert CLOB data and other columns in the database.
String sql = "INSERT INTO clob_tab VALUES(?)";
      
// Read a big file(larger than 32765 bytes). 
// Note: method readFile() not listed here. 
// It can be any method that reads a file.
String str = this.readFile("bigFile.txt");

// Create the OraclePreparedStatement object
opstmt = (OraclePreparedStatement)conn.prepareStatement(sql);

// Use the new method to insert the CLOB data (for data greater or lesser than 32 KB)
opstmt.setStringForClob(1,str);

// Execute the OraclePreparedStatement
opstmt.executeUpdate();
...........

Following is the code snippet that demonstrates the use of the standard ResultSet.getString() method, enhanced now to read more than 32765 bytes:

.....
// Create a PreparedStatement object
PreparedStatement pstmt = null;
    
// Create a ResultSet to hold the records retrieved.
ResultSet rset = null;
.......


// Create SQL query statement to retrieve records having CLOB data from 
// the database.
String sqlCall = "SELECT clob_col FROM clob_tab";
pstmt= conn.prepareStatement(sqlCall);

// Execute the PrepareStatement
rset = pstmt.executeQuery();
     
String clobVal = null; 
      
// Get the CLOB value larger than 32765 bytes from the resultset
while (rset.next()) {
  clobVal = rset.getString(1);
  System.out.println("CLOB length: "+clobVal.length());     
}
分享到:
评论

相关推荐

    sqljdbc_1.2

    描述中的“jdbc for microsoft server 2005, connect to tomcat and so on.”意味着这个驱动程序不仅适用于SQL Server 2005,还可以与流行的Java应用服务器如Tomcat集成,用于在Java环境中处理数据库连接。...

    Oracle Database 10g PL-SQL Programming

    - **Variables and Constants**: How to declare and use variables and constants in PL/SQL. - **Operators**: Overview of arithmetic, relational, logical, and concatenation operators. - **Control ...

    使用Jdbc4操作Blob,Clob

    当涉及到处理大型对象(LOBs)如Blobs(Binary Large Objects)和Clobs(Character Large Objects)时,JDBC4提供了一种更为便捷的方式来操作这些数据类型。这篇博客“使用Jdbc4操作Blob,Clob”将深入讲解如何利用...

    plsqldev14.0.0.1961x32多语言版+sn.rar

    CLOBs and BLOBs are now fetched on demand to improve query performance. Single record view will now show column comments if available. The EXCEL and CSV export filenames can now include substitution ...

    plsqldev14.0.0.1961x64多语言版+sn.rar

    CLOBs and BLOBs are now fetched on demand to improve query performance. Single record view will now show column comments if available. The EXCEL and CSV export filenames can now include substitution ...

    Sybase SQL Studio 6.0.3程序员指南

    在技术层面上,SQL Studio提供了丰富的数据库管理功能,包括交互式SQL(ISQL)用于执行查询和管理任务,用户界面(UI)如GNOME和KDE集成,支持Java的JDBC用于数据库连接,以及对大型对象如BLOBs和CLOBs的处理。...

    你应该知道的10件关于Java 6的事情

    同时,Java 6还更新了JDBC 4.0,支持将XML作为SQL类型,改进了对二进制大对象(BLOBs)和字符大对象(CLOBs)的支持,使得数据处理更加高效和便捷。 #### 4. More Desktop APIs 为了改善桌面应用开发体验,Java 6...

    数据库informix实用手册,入门指南,培训素材

    - **特殊数据类型**:讨论Informix特有的数据类型,如CLOBS(大对象)和BLOBS(二进制大对象)。 4. **表与索引** - **创建与管理表**:如何定义表结构,设置约束,以及如何进行表的操作,如ALTER TABLE和DROP ...

    ODBC 2.0 Programmer's Manual

    此外,ODBC 2.0 还支持更多的数据类型,如大对象(BLOBs 和 CLOBs)等,进一步扩展了其应用范围。 #### 三、ODBC 的组成 ODBC 主要由以下几个部分组成: 1. **驱动程序管理器**:负责加载和卸载数据库驱动程序,...

    Microsoft_Programming.Microsoft.ADO.NET.2.0.Applications.Advanced.Topics.ebook-LRN.pdf

    详细讲解了如何处理大型对象,如二进制大对象(BLOBs)和字符大对象(CLOBs),这对于处理多媒体和文本数据至关重要。 #### 15. XML数据的处理 最后一部分介绍了如何在ADO.NET中操作XML数据,包括序列化、解析和...

    Hibernate文档

    介绍了如何处理大对象(BLOBs 和 CLOBs)。 - **3.4.4 二级缓存和查询缓存** 讲解了 Hibernate 中的缓存机制。 - **3.4.5 替换查询语言** 介绍了如何自定义 Hibernate 查询语言。 - **3.4.6 Hibernate 统计...

    Informix介绍PPT学习教案.pptx

    此外,它还包括4GL/EGL编程语言支持,智能大对象(Blobs/Clobs),分布式高可用性功能,如ER+HDR和ISTAR,以及Java在服务器中的集成。 Informix的发展路线图显示,随着时间的推移,Informix不断优化其OLTP引擎,...

    《Apress 专业奥瑞可之SQL 第2版》2013英文零售版

    其次,书中详细讲解了如何在Oracle中处理复杂的数据类型,如BLOBs(二进制大对象)和CLOBs(字符大对象),以及如何使用日期和时间数据类型。这涵盖了数据类型的选择、存储和查询,以及与这些类型相关的函数和操作。...

    instantclient-sqlplus-windows.x64-11.2.0.4.0

    Oracle Instant Client 11.2.0.4.0还支持某些高级特性,例如高级安全选项、优化的网络协议、以及对大文件对象(BLOBs、CLOBs)的支持。对于开发者来说,它可以无缝集成到各种编程语言中,比如通过ODBC驱动连接到...

    ODAC9.3.8.FullSource.D7-XE6.rar

    2. **全面支持Oracle特性**:ODAC支持Oracle的各种高级特性,如PL/SQL过程、触发器、游标、BLOBs、CLOBs、NCLOBs、BFILEs等。此外,它还支持Oracle的分区表、物化视图、索引组织表等高级数据库结构。 3. **组件集**...

    OCI程序员参考手册9i.rar

    - `OCILobLocatorAssign` 处理大对象(LOB)数据,包括 BFILEs, BLOBs, CLOBs, 和 NCLOBs。 6. **PL/SQL块处理**: - `OCIDescribeAny` 可以描述一个 PL/SQL 块或过程。 - `OCICallStmt` 和 `OCIParse` 一起用于...

    oarcle数据库

    Oracle数据库支持多种数据类型,包括数值、字符串、日期时间以及复杂的数据结构,如BLOBs(Binary Large OBjects)和CLOBs(Character Large OBjects)。 DML(Data Manipulation Language)是Oracle数据库中用于...

    ODAC开发控件

    5. **BLOB和CLOB支持**:对于大型对象(BLOBs和CLOBs),ODAC提供了专门的组件来处理,如TBlobField,可以方便地读写二进制和文本大对象。 6. **批处理操作**:ODAC允许开发人员进行批量插入、更新和删除操作,提高...

Global site tag (gtag.js) - Google Analytics