- 浏览: 285945 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
86614009:
如何在service层,如何获取绑定到当前线程的entitna ...
使用spring的OpenEntityManagerInView -
yajunyajun2011:
好帖子 怎么没人顶呢
Java 正则表达式最大,最小匹配问题 -
xtuali:
能说明一下,你的nutch是哪个版本的吗?谢谢!
搜索引擎Nutch源代码研究之一 网页抓取(1) -
dongmusic:
需要学习这么多的东西,吐血中...
如何提高Java开发能力 -
jiminsc:
cool
LDAP 验证、添加、修改、删除(转)
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()
andgetString()
of thePreparedStatement
andResultSet
objects respectively. By default, the methodpreparedStatement.setString()
will allow processing of the strings up to 32765 bytes. In order to insert data greater than 32765 bytes, a newly introducedConnection
property- SetBigStringTryClob
can be set. This forces thepreparedStatement.setString()
to use another newly introduced method,OraclePreparedStatement.setStringForClob()
instead. This is actually done internally, transparent to the user.
Nevertheless, the newly introduced methodOraclePreparedStatement.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 thegetString()
and thesetString()
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 theConnection
propertySetBigStringTryClob
. 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 theConnection
property -SetBigStringTryClob
is set, use the standardpreparedStatement.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()); }
发表评论
-
文件存储到 Oracle 的存储过程用的外部BLOB的方式
2012-04-25 17:24 888一个存储图片文件的过程,任何开发工具都可以通过调用过程把图片文 ... -
Linux 安装 Mysql
2012-03-06 16:37 749一、引言 想使用Linux已经很长时间了,由于没有硬性任 ... -
sql查看oracle tablespace剩余空间
2012-01-01 10:46 1646select a.TABLESPACE_NAME, a.BYT ... -
oracle报错ID(一)
2011-12-06 08:55 1893ORA-00001: 违反唯一约束 ... -
ORACLE错误一览表
2011-06-03 16:52 684ORA-00001: 违反唯一约束 ... -
Oracle 使用总结(收藏于论坛)
2011-05-08 20:20 710一.Oracle数据库中常用的 ... -
oracle中scott/tiger、sys、SYSDBA、system都是什么用
2011-03-07 21:33 1216scott 是个演示用户,是让你学习ORACLE用的 SYS ... -
Oracle 一次插入多条记录的方法
2011-03-04 13:01 1130原来一次插入多条记录是用这个方法,需要用到dual 表, ... -
为Oracle字段建索引
2011-03-04 09:55 1197一、查看表中所有的索引,注意表名必须要大写SQL> se ... -
查看及删除oracle序列的方法
2011-03-04 09:37 1014一、查看所有序列 select * from u ... -
Oracle 9i主键自增长
2011-03-02 20:50 810<!--StartFragment --> ... -
Oracle连接池
2011-03-01 18:00 1131Oracle连接池 public class Conn ... -
jdbc连接数据库操作
2011-02-28 13:27 829好久没有操作数据库,今天上午连接Oracle的,竟然不知道怎么 ... -
常用的Oracle命令
2011-01-14 16:33 1014ORACLE数据库维护 01. ORACLE数 ... -
oracle的blob,clob的读写
2010-10-26 19:32 1074JDBC驱动程序的类型: JDBC-ODBC桥;部分本地API ... -
将文档以BLOB类型存入Oracle数据库中
2010-10-26 16:06 1210头痛了两天的问题终于得到解决了,特此写下(原创): c ... -
向oracle中插入BLOB对象
2010-10-25 20:19 1486package oracle.otnsamples.jdbc. ... -
Blob、InputStream、byte 互转
2010-10-24 10:55 1294来自 sukyle的专栏 在我们的程序开发当中,经常会用到j ... -
转:java操纵主要数据库的lob类型数据
2010-10-24 10:54 861文章分类:Java编程 Clob和blob的操作主要分为 ... -
将clob 转换成 String
2010-10-24 10:53 1117将clob 转换成 String 文章分类:Java编程 ...
相关推荐
描述中的“jdbc for microsoft server 2005, connect to tomcat and so on.”意味着这个驱动程序不仅适用于SQL Server 2005,还可以与流行的Java应用服务器如Tomcat集成,用于在Java环境中处理数据库连接。...
- **Variables and Constants**: How to declare and use variables and constants in PL/SQL. - **Operators**: Overview of arithmetic, relational, logical, and concatenation operators. - **Control ...
当涉及到处理大型对象(LOBs)如Blobs(Binary Large Objects)和Clobs(Character Large Objects)时,JDBC4提供了一种更为便捷的方式来操作这些数据类型。这篇博客“使用Jdbc4操作Blob,Clob”将深入讲解如何利用...
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 ...
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 ...
在技术层面上,SQL Studio提供了丰富的数据库管理功能,包括交互式SQL(ISQL)用于执行查询和管理任务,用户界面(UI)如GNOME和KDE集成,支持Java的JDBC用于数据库连接,以及对大型对象如BLOBs和CLOBs的处理。...
同时,Java 6还更新了JDBC 4.0,支持将XML作为SQL类型,改进了对二进制大对象(BLOBs)和字符大对象(CLOBs)的支持,使得数据处理更加高效和便捷。 #### 4. More Desktop APIs 为了改善桌面应用开发体验,Java 6...
- **特殊数据类型**:讨论Informix特有的数据类型,如CLOBS(大对象)和BLOBS(二进制大对象)。 4. **表与索引** - **创建与管理表**:如何定义表结构,设置约束,以及如何进行表的操作,如ALTER TABLE和DROP ...
此外,ODBC 2.0 还支持更多的数据类型,如大对象(BLOBs 和 CLOBs)等,进一步扩展了其应用范围。 #### 三、ODBC 的组成 ODBC 主要由以下几个部分组成: 1. **驱动程序管理器**:负责加载和卸载数据库驱动程序,...
详细讲解了如何处理大型对象,如二进制大对象(BLOBs)和字符大对象(CLOBs),这对于处理多媒体和文本数据至关重要。 #### 15. XML数据的处理 最后一部分介绍了如何在ADO.NET中操作XML数据,包括序列化、解析和...
介绍了如何处理大对象(BLOBs 和 CLOBs)。 - **3.4.4 二级缓存和查询缓存** 讲解了 Hibernate 中的缓存机制。 - **3.4.5 替换查询语言** 介绍了如何自定义 Hibernate 查询语言。 - **3.4.6 Hibernate 统计...
此外,它还包括4GL/EGL编程语言支持,智能大对象(Blobs/Clobs),分布式高可用性功能,如ER+HDR和ISTAR,以及Java在服务器中的集成。 Informix的发展路线图显示,随着时间的推移,Informix不断优化其OLTP引擎,...
其次,书中详细讲解了如何在Oracle中处理复杂的数据类型,如BLOBs(二进制大对象)和CLOBs(字符大对象),以及如何使用日期和时间数据类型。这涵盖了数据类型的选择、存储和查询,以及与这些类型相关的函数和操作。...
Oracle Instant Client 11.2.0.4.0还支持某些高级特性,例如高级安全选项、优化的网络协议、以及对大文件对象(BLOBs、CLOBs)的支持。对于开发者来说,它可以无缝集成到各种编程语言中,比如通过ODBC驱动连接到...
2. **全面支持Oracle特性**:ODAC支持Oracle的各种高级特性,如PL/SQL过程、触发器、游标、BLOBs、CLOBs、NCLOBs、BFILEs等。此外,它还支持Oracle的分区表、物化视图、索引组织表等高级数据库结构。 3. **组件集**...
- `OCILobLocatorAssign` 处理大对象(LOB)数据,包括 BFILEs, BLOBs, CLOBs, 和 NCLOBs。 6. **PL/SQL块处理**: - `OCIDescribeAny` 可以描述一个 PL/SQL 块或过程。 - `OCICallStmt` 和 `OCIParse` 一起用于...
Oracle数据库支持多种数据类型,包括数值、字符串、日期时间以及复杂的数据结构,如BLOBs(Binary Large OBjects)和CLOBs(Character Large OBjects)。 DML(Data Manipulation Language)是Oracle数据库中用于...
5. **BLOB和CLOB支持**:对于大型对象(BLOBs和CLOBs),ODAC提供了专门的组件来处理,如TBlobField,可以方便地读写二进制和文本大对象。 6. **批处理操作**:ODAC允许开发人员进行批量插入、更新和删除操作,提高...