JDBC 对oracle 的clob ，blob类型的操作方式

最新推荐文章于 2024-10-02 11:52:29 发布

LEE-MCICI

最新推荐文章于 2024-10-02 11:52:29 发布

阅读量1.5k

点赞数

分类专栏： oracle 文章标签： jdbc oracle 数据库 dao java sql

oracle 专栏收录该内容

54 篇文章 1 订阅

订阅专栏

感谢：http://idealab.javaeye.com/blog/383409

利用JDBC操作Oracle CLOB和BLOB类型数据

关键字: jdbc oracle clob blob

对LOB数据（包括CLOB BLOB NCLOB BFILE类型）操作的插入操作步骤：插入空值-->获取空值列及更新锁-->更新LOB字段。

通过查询操作得到的LOB类型数据处理要点：首先利用LOB字段数据获取InputStream或OutputStream对象，然后根据需要对其进行操作，若需提取数据则获取InputStream对象，若需更新LOB字段数据，则获取OutputStream对象。

程序分为两层，1)JdbcUtil提供基本数据库访问及事务管理，2)DAO层调用JdbcUtil来提供DAO服务。

包含CLOB数据的content_text表：

Sql代码

/*==============================================================*/
/* Table: content_text */
/*==============================================================*/
CREATE TABLE content_text (
pk_content_text INTEGER NOT null,
text_type VARCHAR2(25) NOT null,
text_content CLOB NOT null,
CONSTRAINT PK_CONTENT_TEXT PRIMARY KEY (pk_content_text)
)

包含BLOB数据的content_bin表：

Sql代码

/*==============================================================*/
/* Table: content_bin */
/*==============================================================*/
CREATE TABLE content_bin (
pk_content_bin INTEGER NOT null,
bin_type VARCHAR2(25) NOT null,
bin_content CLOB NOT null,
CONSTRAINT PK_CONTENT_BIN PRIMARY KEY (pk_content_bin)
);

=========================JdbcUtil================
-------向content_text表中插入数据：

Java代码

public int insertText(ContentText text) {
int insertkey = -1;
try {
// 利用序列sequence向数据库索取content_text表的自增长主键
ResultSet rs = executeQuery("select seq_content_text.nextval as pk_content_text from dual");
rs.first();
insertkey = rs.getInt(1);
rs.close();
// 插入空CLOB
String insertEmpty = "insert into content_text values(" + insertkey
+ ",'" + text.getTextType() + "',empty_clob())";
boolean insertResult = executeUpdate(insertEmpty);
if (insertResult == false) {
throw new SQLException();
}
// 获取CLOB类型列text_content，并获取更新锁，锁定当前行直至更新结束
String getForUpdate = "select text_content from content_text where pk_content_text = "
+ insertkey + " for update";
rs = executeQuery(getForUpdate);
rs.first();
CLOB content = (CLOB) rs.getClob("text_content");
rs.close();
content.setString(1L, text.getContent());
// 更新text_content列
String updateSQL = "update content_text set text_content = ? where pk_content_text = ?";
PreparedStatement pst = conn.prepareStatement(updateSQL);
pst.setClob(1, content);
pst.setInt(2, insertkey);
pst.execute();
pst.close();
} catch (SQLException e) {
try {
conn.rollback();
conn.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
System.out.println("Insert text failed!");
}
return insertkey;
}

------------向content_bin表中插入数据：

Java代码

public int insertBin(ContentBin bin) {
int insertkey = -1;
try {
// 利用序列sequence向数据库索取content_bin表的自增长主键
ResultSet rs = executeQuery("select seq_content_bin.nextval from dual");
boolean good = rs.first();
System.out.println(good);
insertkey = rs.getInt(1);
rs.close();
// 插入空BLOB
String insertEmpty = "insert into content_bin values(" + insertkey
+ ",'" + bin.getBinType() + "','" + bin.getBinFilename()
+ "',empty_blob())";
boolean result = executeUpdate(insertEmpty);
System.out.println("插入空值：" + result);
// 获取空BLOB字段，并获取更新锁
String getEmpty = "select bin_content from content_bin where pk_content_bin="
+ insertkey + " for update";
ResultSet forupdateRs = executeQuery(getEmpty);
forupdateRs.first();
BLOB blob = (BLOB) forupdateRs.getBlob(1);
//由文件系统获取文件输入流
File file = bin.getContent();
System.out.println(file.getAbsolutePath());
FileInputStream fin = new FileInputStream(file);
System.out.println(fin.toString());
// 获取BLOB的输出流，并从文件输入流获取数据，写入BLOB的输出流，完成数据库外的BLOB更新
OutputStream out = blob.setBinaryStream(0);
byte[] data = new byte[blob.getBufferSize()];
System.out.println(data.length);
int count = -1, total = 0;
while ((count = fin.read(data)) != -1) {
total += count;
out.write(data, 0, count);
}
fin.close();
out.close();
// 利用新的BLOB更新数据库
String updateBin = "update content_bin set bin_content=? where pk_content_bin=?";
PreparedStatement pstmt = conn.prepareStatement(updateBin);
pstmt.setBlob(1, blob);
pstmt.setInt(2, insertkey);
pstmt.execute();
pstmt.close();
} catch (SQLException e) {
try {
conn.rollback();
conn.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return insertkey;
}

=============================DAO层====================
---------查询content_text表

Java代码

public ContentText getById(int id) {
jdbc.openConn();
ContentText text = new ContentText();
String sql = "select pk_content_text,text_type,text_content from content_text where pk_content_text = "
+ id;
ResultSet rs = jdbc.executeQuery(sql);
try {
rs.first();
text.setContentId(rs.getInt("pk_content_text"));
text.setTextType(rs.getString("text_type"));
CLOB clob = (CLOB) rs.getClob(3);
char[] chars = new char[(int) clob.getLength()];//(int)clob.length();
clob.getCharacterStream().read(chars);
String content = new String(chars);
text.setContent(content);
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
jdbc.commitAndCloseConn();
return text;
}

--------------查询content_bin表

Java代码

public ContentBin getById(int id) {
ContentBin bin = new ContentBin();
jdbc.openConn();
String sql = "select pk_content_bin,bin_type,bin_filename,bin_content from content_bin where pk_content_bin="+ id;
ResultSet rs = jdbc.executeQuery(sql);
try {
rs.first();
bin.setContentId(rs.getInt(1));
bin.setBinType(rs.getString(2));
String filename = rs.getString(3);
bin.setBinFilename(filename);
//利用字段bin_filename值构造文件
File file = new File(filename);
FileOutputStream sout = new FileOutputStream(file);
BLOB blob = (BLOB) rs.getBlob(4);
InputStream in = blob.getBinaryStream();//获取BLOB数据的输入数据流
//经BLOB输入数据流读取数据，并将其写入文件
byte[] b = new byte[256];
int off = 0;
int len = b.length;
for (int i = in.read(b); i != -1;) {
sout.write(b);
System.out.println(i);
i = in.read(b);
}
in.close();
sout.close();
bin.setContent(file);
} catch (SQLException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
jdbc.commitAndCloseConn();
return bin;
}