java向oracle存取clob和blob-
String DRIVER = "oracle.jdbc.driver.OracleDriver";
/**
* ORACLE连接用URL
*/
private static final String URL = "jdbc:oracle:thin:@test2000:1521:orac";
/**
* 用户名
*/
private static final String USER = "user";
/**
* 密码
*/
private static final String PASSWORD = "pswd";
/**
* 数据库连接
*/
private static Connection conn = null;
/**
* SQL语句对象
*/
private static Statement stmt = null;
/**
* @roseuid 3EDA089E02BC
*/
public LobPros()
{
}
//----------------------插入CLOB对象------------------------
/**
* 往数据库中插入一个新的CLOB对象
*
* @param infile - 数据文件
* @throws java.lang.Exception
* @roseuid 3EDA04A902BC
*/
public static void clobInsert(String infile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 插入一个空的CLOB对象 */
stmt.executeUpdate("INSERT INTO TEST_CLOB VALUES ('111', EMPTY_CLOB())");
/* 查询此CLOB对象并锁定 */
ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR UPDATE");
while (rs.next()) {
/* 取出此CLOB对象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/* 向CLOB对象中写入数据 */
BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
BufferedReader in = new BufferedReader(new FileReader(infile));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
in.close();
out.close();
}
/* 正式提交 */
conn.commit();
} catch (Exception ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
conn.setAutoCommit(defaultCommit);
}
//----------------------修改CLOB对象------------------------
/**
* 修改CLOB对象（是在原CLOB对象基础上进行覆盖式的修改）
*
* @param infile - 数据文件
* @throws java.lang.Exception
* @roseuid 3EDA04B60367
*/
public static void clobModify(String infile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 查询CLOB对象并锁定 */
ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR UPDATE");
while (rs.next()) {
/* 获取此CLOB对象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/* 进行覆盖式修改 */
BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
BufferedReader in = new BufferedReader(new FileReader(infile));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
in.close();
out.close();
}
/* 正式提交 */
conn.commit();
} catch (Exception ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
conn.setAutoCommit(defaultCommit);
}
//----------------------替换CLOB对象------------------------
/**
* 替换CLOB对象（将原CLOB对象清除，换成一个全新的CLOB对象）
*
* @param infile - 数据文件
* @throws java.lang.Exception
* @roseuid 3EDA04BF01E1
*/
public static void clobReplace(String infile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 清空原CLOB对象 */
stmt.executeUpdate("UPDATE TEST_CLOB SET CLOBCOL=EMPTY_CLOB() WHERE ID='111'");
/* 查询CLOB对象并锁定 */
ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR UPDATE");
while (rs.next()) {
/* 获取此CLOB对象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/* 更新数据 */
BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
BufferedReader in = new BufferedReader(new FileReader(infile));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
in.close();
out.close();
}
/* 正式提交 */
conn.commit();
} catch (Exception ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
conn.setAutoCommit(defaultCommit);
}
//----------------------读取CLOB对象------------------------
/**
* CLOB对象读取
*
* @param outfile - 输出文件名
* @throws java.lang.Exception
* @roseuid 3EDA04D80116
*/
public static void clobRead(String outfile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 查询CLOB对象 */
ResultSet rs = stmt.executeQuery("SELECT * FROM TEST_CLOB WHERE ID='111'");
while (rs.next()) {
/* 获取CLOB对象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/* 以字符形式输出 */
BufferedReader in = new BufferedReader(clob.getCharacterStream());
BufferedWriter out = new BufferedWriter(new FileWriter(outfile));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
out.close();
in.close();
}
} catch (Exception ex) {
conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
conn.setAutoCommit(defaultCommit);
}
//----------------------插入BLOB对象------------------------
/**
* 向数据库中插入一个新的BLOB对象
*
* @param infile - 数据文件
* @throws java.lang.Exception
* @roseuid 3EDA04E300F6
*/
public static void blobInsert(String infile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 插入一个空的BLOB对象 */
stmt.executeUpdate("INSERT INTO TEST_BLOB VALUES ('222', EMPTY_BLOB())");
/* 查询此BLOB对象并锁定 */
ResultSet rs = stmt.executeQuery("SELECT BLOBCOL FROM TEST_BLOB WHERE ID='222' FOR UPDATE");
while (rs.next()) {
/* 取出此BLOB对象 */
oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL");
/* 向BLOB对象中写入数据 */
BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());
BufferedInputStream in = new BufferedInputStream(new FileInputStream(infile));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
in.close();
out.close();
PreparedStatement pstmt = conn.prepareStatement("UPDATE TEST_BLOB SET BLOBCOL=? WHERE ID='222'");
pstmt.setBlob(1, blob);
pstmt.executeUpdate();
pstmt.close()
}
/* 正式提交 */
conn.commit();
} catch (Exception ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
conn.setAutoCommit(defaultCommit);
}
//----------------------修改BLOB对象------------------------
/**
* 修改BLOB对象（是在原BLOB对象基础上进行覆盖式的修改）
*
* @param infile - 数据文件
* @throws java.lang.Exception
* @roseuid 3EDA04E90106
*/
public static void blobModify(String infile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 查询BLOB对象并锁定 */
ResultSet rs = stmt.executeQuery("SELECT BLOBCOL FROM TEST_BLOB WHERE ID='222' FOR UPDATE");
while (rs.next()) {
/* 取出此BLOB对象 */
oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL");
/* 向BLOB对象中写入数据 */
BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());
BufferedInputStream in = new BufferedInputStream(new FileInputStream(infile));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
in.close();
out.close();
}
/* 正式提交 */
conn.commit();
} catch (Exception ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
conn.setAutoCommit(defaultCommit);
}
//----------------------替换BLOB对象------------------------
/**
* 替换BLOB对象（将原BLOB对象清除，换成一个全新的BLOB对象）
*
* @param infile - 数据文件
* @throws java.lang.Exception
* @roseuid 3EDA0505000C
*/
public static void blobReplace(String infile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 清空原BLOB对象 */
stmt.executeUpdate("UPDATE TEST_BLOB SET BLOBCOL=EMPTY_BLOB() WHERE ID='222'");
/* 查询此BLOB对象并锁定 */
ResultSet rs = stmt.executeQuery("SELECT BLOBCOL FROM TEST_BLOB WHERE ID='222' FOR UPDATE");
while (rs.next()) {
/* 取出此BLOB对象 */
oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL");
/* 向BLOB对象中写入数据 */
BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());
BufferedInputStream in = new BufferedInputStream(new FileInputStream(infile));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
in.close();
out.close();
}
/* 正式提交 */
conn.commit();
} catch (Exception ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
conn.setAutoCommit(defaultCommit);
}
//----------------------读取BLOB对象------------------------
/**
* BLOB对象读取
*
* @param outfile - 输出文件名
* @throws java.lang.Exception
* @roseuid 3EDA050B003B
*/
public static void blobRead(String outfile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 查询BLOB对象 */
ResultSet rs = stmt.executeQuery("SELECT BLOBCOL FROM TEST_BLOB WHERE ID='222'");
while (rs.next()) {
/* 取出此BLOB对象 */
oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL");
/* 以二进制形式输出 */
BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(outfile));
BufferedInputStream in = new BufferedInputStream(blob.getBinaryStream());
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
in.close();
out.close();
}
/* 正式提交 */
conn.commit();
} catch (Exception ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
conn.setAutoCommit(defaultCommit);
}
/**
* 建立测试用表格
* @throws Exception
*/
public static void createTables() throws Exception {
try {
stmt.executeUpdate("CREATE TABLE TEST_CLOB ( ID NUMBER(3), CLOBCOL CLOB)");
stmt.executeUpdate("CREATE TABLE TEST_BLOB ( ID NUMBER(3), BLOBCOL BLOB)");
} catch (Exception ex) {
}
}
/**
* @param args - 命令行参数
* @throws java.lang.Exception
* @roseuid 3EDA052002AC
*/
public static void main(String[] args) throws Exception
{
/* 装载驱动,建立数据库连接 */
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL,USER,PASSWORD);
stmt = conn.createStatement();
/* 建立测试表格 */
createTables();
/* CLOB对象插入测试 */
clobInsert("c:/clobInsert.txt");
clobRead("c:/clobInsert.out");
/* CLOB对象修改测试 */
clobModify("c:/clobModify.txt");
clobRead("c:/clobModify.out");
/* CLOB对象替换测试 */
clobReplace("c:/clobReplace.txt");
clobRead("c:/clobReplace.out");
/* BLOB对象插入测试 */
blobInsert("c:/blobInsert.doc");
blobRead("c:/blobInsert.out");
/* BLOB对象修改测试 */
blobModify("c:/blobModify.doc");
blobRead("c:/blobModify.out");
/* BLOB对象替换测试 */
blobReplace("c:/blobReplace.doc");
blobRead("c:/bolbReplace.out");
/* 关闭资源退出 */
conn.close();
System.exit(0);
}
}
上文转自： 转自:http://apps.hi.baidu.com/share/detail/23946991
下文链接：http://www.2cto.com/database/201108/99654.html
Oracle的Blob字段比较特殊，他比long字段的性能要好很多，可以用来保存例如图片之类的二进制数据。
写入Blob字段和写入其它类型字段的方式非常不同，因为Blob自身有一个cursor，你必须使用cursor对blob进行操作，因而你在写入Blob之前，必须获得cursor才能进行写入，那么如何获得Blob的cursor呢？
这需要你先插入一个empty的blob，这将创建一个blob的cursor，然后你再把这个empty的blob的cursor用select查询出来，这样通过两步操作，你就获得了blob的cursor，可以真正的写入blob数据了。
看下面的JDBC的demo，把oraclejdbc.jar这个二进制文件写入数据库表javatest的content字段(这是一个blob型字段)
import java.sql.*;
import java.io.*;
import oracle.sql.*;
public class WriteBlob {
public static void main(String[] args) {
try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","fankai","fankai");
conn.setAutoCommit(false);
BLOB blob = null;
PreparedStatement pstmt = conn.prepareStatement("insert into javatest(name,content) values(?,empty_blob())");
pstmt.setString(1,"fankai");
pstmt.executeUpdate();
pstmt.close();
pstmt = conn.prepareStatement("select content from javatest where name= ? for update");
pstmt.setString(1,"fankai");
ResultSet rset = pstmt.executeQuery();
if (rset.next()) blob = (BLOB) rset.getBlob(1);
String fileName = "oraclejdbc.jar";
File f = new File(fileName);
FileInputStream fin = new FileInputStream(f);
System.out.println("file size = " + fin.available());
pstmt = conn.prepareStatement("update javatest set content=? where name=?");
OutputStream out = blob.getBinaryOutputStream();
int count = -1, total = 0;
byte[] data = new byte[(int)fin.available()];
fin.read(data);
out.write(data);
/*
byte[] data = new byte[blob.getBufferSize()]; 另一种实现方法,节省内存
while ((count = fin.read(data)) != -1) {
total += count;
out.write(data, 0, count);
}
*/
fin.close();
out.close();
pstmt.setBlob(1,blob);
pstmt.setString(2,"fankai");
pstmt.executeUpdate();
pstmt.close();
conn.commit();
conn.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
e.printStackTrace();
} catch (IOException e) {
System.err.println(e.getMessage());
}
}
}
仔细看上例，分三步：
1、插入空blob
into javatest(name,content) values(?,empty_blob());
2、获得blob的cursor
select content from javatest where name= ? for update;
注意！！！必须加for update，这将锁定该行，直至该行被修改完毕，保证不产生并发冲突。
3、update javatest set content=? where name=
用cursor往数据库写数据
这里面还有一点要提醒大家：
JDK1.3带的JDBC2.0规范是不完善的，只有读Blob的接口，而没有写Blob的接口，JDK1.4带的JDBC3.0加入了写Blob的接口。你可以使用JDBC3.0的接口，也可以直接使用Oracle的JDBC的API，我在上例中使用了Oracle的JDBC的API。
另外要注意的是：
java.sql.Blob
oracle.sql.BLOB
注意看blob的大小写，是不一样的。写程序的时候不要搞混了。
