使用java代码备份Oracle数据库时遇到Clob与Blob的解决办法
(用代码生成sql语句)
将Clob转化为字符串
/**
* Clob类型 转String 单引号转双引号方便insert插入
* @param clob
* @return
* @throws SQLException
* @throws IOException
*/
public static String ClobToString(Clob clob) {
String reString = "";
if(clob != null){
Reader is = null;
BufferedReader br = null;
try {
is = clob.getCharacterStream();
br = new BufferedReader(is);
String s = br.readLine();
StringBuffer sb = new StringBuffer();
while (s != null) {// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
sb.append(s);
s = br.readLine();
}
reString = sb.toString();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (br != null) {
br.close();
}
if (is != null) {
is.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
return reString.replaceAll("'", "''");//防止字段值内有单引号导致sql语句无法执行
}
将Blob转化为16进制字符串
/**
* byte数组转换为十六进制的字符串
*/
public static String conver16HexStr(byte [] b) {
StringBuffer result = new StringBuffer();
if(b != null){
for(int i = 0;i<b.length;i++)
{
if((b[i]&0xff)<0x10)
result.append("0");
result.append(Long.toString(b[i]&0xff, 16));
}
}
return result.toString().toUpperCase();
}
防止sql运行时报字符串过长错误
DECLARE
blobValue BLOB;--声明变量 前面是变量名称后面是类型
blobValue2 BLOB;--多个变量可以用;分隔
clobValue CLOB;
BEGIN
blobValue := HexToRaw('转化后的16进制字符串(可为空)');--给变量赋值
blobValue2 := 'xxx';
clobValue := '转化后的字符串(不用函数直接赋值就可)';
INSERT INTO tableName VALUES ('23134124',blobValue,blobValue2,clobValue);
COMMIT;
END;
/--两个DECLARE之间需要用/分隔
DECLARE
……