mysql clob_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 > binay

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

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

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

//插入空值

conn.setAutoCommit(false);

String sql = "insert into file(name,file_content) values("jack",EMPTY_CLOB());

PreparedStatement pstmt = conn.prepareStatement(sql);

pstmt.executeUpdate();

//锁住此行

String sql = "select file_content from file where name='jack' for update";

PreparedStatement pstmt = conn.prepareStatement(sql);

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(null!=clob)

{

Reader is = clob.getCharacterStream();

BufferedReader br = new BufferedReader(is);

String s = br.readLine();

while (s != null)

{

content += s + "
";

s = br.readLine();

}

}

3、jdbc操作blob

conn.setAutoCommit(false);

String sql = "insert into photo(name,photo) values("jack",empty_blob());

pstmt = conn.prepareStatement(sql);

pstmt = conn.executeUpdate();

//

sql = "select photo from photo where name='jack'";

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

}

4、hibernateth处理clob

MyFile file = new Myfile();

file.setName("jack");

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();

5、使用hibernate处理blob：

原理基本相同：

Photo photo = new Photo();

photo.setName("jack");

photo.setPhoto(hibernate.createB