由于LOB的特殊性,使得增加记录变的烦琐,创建一个记录至少需要两次以上的操作。
一般情况下为:
http://blog.guoshuang.com/blue/showlog.asp?cat_id=28&log_id=285
观察它的动作,就因为无法创建LOB实例而造成如此烦琐的步骤,如果能够再添加记录前
产生LOB实例,那么就可以一步到位的使用动态SQL进行更新,便于封装。以下是代码,
还存在一些问题,需要修改。CLOB的实现不完善。
/**
* 创建表后插入一条记录
* @throws Exception
*/
public static void createTable() throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
String sql = "CREATE TABLE FORUM.systemp(blobid BLOB,clobid CLOB)";
conn = PoolFactory.newInstance();
//添加一张表,再添加一条记录
pstmt = conn.prepareStatement(sql);
pstmt.executeUpdate();
sql = "insert into systemp (blobid,clobid) values(empty_blob(),empty_clob())";
pstmt = conn.prepareStatement(sql);
pstmt.executeUpdate();
pstmt.close();
conn.close();
}
/**
* 更新值为空
* @throws Exception
*/
private static void updateSystemp() throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
conn = PoolFactory.newInstance();
String sql =
"update systemp set blobid=empty_blob(),clobid=empty_clob()";
pstmt = conn.prepareStatement(sql);
pstmt.executeUpdate();
pstmt.close();
conn.close();
}
/**
*
* @param content String
* @return Clob
* @throws Exception
*/
public static Clob retClob(Connection conn,String content) throws Exception {
return (Clob) retBClob(conn, false, false, content);
}
/**
*
* @param Connection conn 连接
* @param isBlob boolean 需要实例化的类型,TRUE返回BLOB实例,FALSE返回CLOB实例
* @param isFile boolean 是否是文件
* @param content String 需要转化的内容,或文件名
* @return Object 把content进行BLOB,CLOB实例化。
* @throws Exception
*/
public static Object retBClob(Connection conn,boolean isBlob, boolean isFile, String content) throws
Exception {
// updateSystemp();
PreparedStatement pstmt = null;
ResultSet rs = null;
int columnIndex = 2;
if (isBlob) {
columnIndex = 1;
}
conn.setAutoCommit(false);
try {
pstmt = conn.prepareStatement("select * from systemp FOR UPDATE");
rs = pstmt.executeQuery();
if (rs.next()) {
return fillContent(rs.getObject(columnIndex), isBlob, isFile, content);
}
}
catch (SQLException ex) {
throw new Exception("please establish the systemp table!/n that one column is Blob and the other is Clob!",
ex);
}
finally {
if (rs != null)
rs.close();
if (pstmt != null)
pstmt.close();
}
throw new Exception("please insert into one record to table systemp!");
}
/**
*
* @param fileName String 文件路径,包含文件名
* @return InputStream 读入文件的IO流
* @throws Exception
*/
private static InputStream getFileStream(String fileName) throws Exception {
File f = new File(fileName);
InputStream fis = new FileInputStream(f);
return fis;
}
private static Object fillContent(Object o, boolean isBlob, boolean isFile,
String content) throws Exception {
OutputStream out = null;
InputStream fis = null;
out = distinctOutputStream(o, isBlob);
fis = distinctInputStream(isFile, content);
// int count = -1, total = 0;
byte[] data = new byte[(int) fis.available()];
fis.read(data);
out.write(data);
/*
byte[] data = new byte[blob.getBufferSize()]; 另一种实现方法,节省内存
while ((count = fin.read(data)) != -1) {
total += count;
out.write(data, 0, count);
}
*/
fis.close();
out.close();
return o;
}
/**
* 根据BLOB,CLOB创建相应的输出流
* @param o Object
* @param isBlob boolean
* @return OutputStream
* @throws Exception
*/
private static OutputStream distinctOutputStream(Object o, boolean isBlob) throws
Exception {
OutputStream out = null;
if (isBlob) {
out = ( (BLOB) o).getBinaryOutputStream();
}
else {
out = ( (CLOB) o).getAsciiOutputStream();
}
return out;
}
/**
* 是否为文件,是则返回文件流,否则返回STRING流
* @param isFile boolean
* @param content String 文件名或STRING流
* @return InputStream
* @throws Exception
*/
private static InputStream distinctInputStream(boolean isFile, String content) throws
Exception {
InputStream fis = null;
if (isFile) {
fis = getFileStream(content);
}
else {
fis = new ByteArrayInputStream(content.getBytes());
// fis = new StringInputStream(content);
}
return fis;
}