oracle blob 与 mysql blob_mysql和Oracle在对clob和blob字段的处理

一、MySQL与Oracle数据库如何处理Clob，Blob数据类型

(1)不通数据库中对应clob,blob的类型如下：

MySQL中：clob对应text，blob对应blob

DB2/Oracle中：clob对应clob，blob对应blob

(2)domain中对应的类型：

clob对应String，blob对应byte[]

clob对应java.sql.Clob，blob对应java.sql.Blob

(3)hibernate配置文件中对应类型：

clob-->clob ，blob-->binary

也可以直接使用数据库提供类型，例如：oracle.sql.Clob，oracle.sql.Blob

二、jdbc操作clob(以oracle为例)

首先操作clob/blob不像操作varchar类型那样简单，插入步骤一般分为两步：第一步插入一个空值，第二步锁住此行，更新clob/blob字段。

//插入空值

conn.setAutoCommit(false);

String sql = "INSERT INTO T_FILE(NAME, FILE_CONTENT) VALUES ('Jambhala', EMPTY_CLOB())";

PreparedStatement pstmt = conn.prepareStatement(sql);

pstmt.executeUpdate();

//锁住此行

String sql_lockstr = "SELECT FILE_CONTENT FROM T_FILE WHERE NAME='Jambhala' FOR UPDATE";

pstmt = conn.prepareStatement(sql_lockstr);

ResultSet rs = pstmt.executeQuery();

oracle.sql.Clob clob = (oracle.sql.Clob)rs.getClob(1);

java.io.OutputStream writer = clob.getAsciiOutputStream();

byte[] temp = newFileContent.getBytes();

writer.write(temp);

writer.flush();

writer.close();

pstmt.close();

读取内容：

oracle.sql.Clob clob = rs.getClob("FILE_CONTENT");

if(clob != null){

Reader is = clob.getCharacterStream();

BufferedReader br = new BufferedReader(is);

String s = br.readLine();

while(s != null){

content += s+"
";

s = br.readLine();

}

}

三、jdbc操作blob

conn.setAutoCommit(false);

String sql = "INSERT INTO T_PHOTO(NAME, PHOTO) VALUES ('Jambhala', EMPTY_BLOB())";

pstmt = conn.prepareStatement(sql);

pstmt = conn.executeUpdate();

sql = "SELECT PHOTO FROM T_PHOTO WHERE NAME='Jambhala'";

pstmt = conn.prepareStatement(sql);

rs = pstmt.executeQuery(sql);

if(rs.next()){

oracle.sql.Blob blob = (oracle.sql.Blob)rs.getBlob(1);

}

//write to a file

File file=new File("C:\\test.rar");

FileInputStream fin = new FileInputStream(file);

OutputStream out = blob.getBinaryOutputStream();

int count=-1,total=0;

byte[] data = new byte[blob.getBufferSize()];

while((count=fin.read(data)) != -1){

total += count;

out.write(data, 0, count);

}

四、hibernate处理clob

MyFile file = new MyFile();

file.setName("Jambhala");

file.setContent(Hibernate.createClob(""));

session.save(file);

session.flush();

session.refresh(file, LockMode.UPGRADE);

oracle.sql.Clob clob = (oracle.sql.Clob)file.getContent();

Writer pw = clob.getCharacterOutputStream();

pw.write(longText); //写入长文本

pw.close();

session.close();

五、使用hibernate处理blob

原理基本相同：

Photo photo = new Photo();

photo.setName("Jambhala");

photo.setPhoto(Hibernate.createBlob(""));

session.save(photo);

session.flush();

session.refresh(photo, LockMode.UPGRADE); //锁住此对象

oracle.sql.Blob blob = photo.getPhoto(); //取得此blob的指针

OutputStream out = blob.getBinaryOutputStream();

//写入一个文件

File f = new File("C:\\test.rar");

FileInputStream fin = new FileInputStream(f);

int count=-1,total=0;

byte[] data = new byte[(int)fin.available()];

out.write(data);

fin.close();

out.close();

session.flush();

String DRIVER = "oracle.jdbc.driver.OracleDriver";

//Oracle连接用URL

private static final String URL = "jdbc:oracle:thin:@testora:1521:orac";

//用户名

private static final String USER = "scott";

//密码

private static final String PASSWORD = "pswd";

//数据库连接

private static Connection conn = null;

//SQL语句对象

private static Statement stmt = null;

//@roseuid 3EDA089E02BC

public LobPros(){}

//往数据库中插入一个新的Clob对象

//@param infile 数据文件

//@throws java.lang.Exception

//@roseuid 3EDA089E02BC

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 e){

//出错回滚

conn.rollback();

throw e;

}

//恢复原提交状态

conn.setAutoCommit(defaultCommit);

}

//修改Clob对象(是在原Clob对象基础上进行覆盖式的修改)

//@param infile 数据文件

//@throws java.lang.Exception

//@roseuid 3EDA089E02BC

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 e){

//出错回滚

conn.rollback();

throw e;

}

//恢复原提交状态

conn.setAutoCommit(defaultCommit);

}

//替换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 e){

//出错回滚

conn.rollback();

throw e;

}

//恢复原提交状态

conn.setAutoCommit(defaultCommit);

}

//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 e){

conn.rollback();

throw e;

}

//恢复原提交状态

conn.setAutoCommit(defaultCommit);

}

//向数据库中插入一个新的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();

}

//正式提交

conn.commit();

} catch (Exception e) {

//出错回滚

conn.rollback();

throw e;

}

//恢复原提交状态

conn.setAutoCommit(defaultCommit);

}

//修改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 e) {

//出错回滚

conn.rollback();

throw e;

}

//恢复原提交状态

conn.setAutoCommit(defaultCommit);

}

//替换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 e) {

//出错回滚

conn.rollback();

throw e;

}

//恢复原提交状态

conn.setAutoCommit(defaultCommit);

}

//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 e) {

//出错回滚

conn.rollback();

throw e;

}

//恢复原提交状态

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 e) { }

}

//@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);

}

分享到：

2012-08-25 18:50

浏览 29581

评论

1 楼

Power_Song

2015-04-18