相信很多朋友在oracle資料入庫的時候經常會遇到大字段處理的問題，在經過多次的處理這個問題之後小編逐漸進行了總結，好啦不廢話啦直接進擊主題。（此處的資料庫連接配接，朋友們可以去搜集其他資料，此處隻是集中針對大字段的存儲更新問題）。
其中blob的jar包是oracle.sql.BLOB，大家自行在網上搜尋下載下傳即可。
一、blob資料的處理
字段屬性變動前都會使用empty_blob()進行該屬性的初始化，之後再使用流的方式将需要變動的資料寫入資料庫。
1、插入方法
/*
* pidColumnName - 主鍵對應的列名
* pidColumnValue - 主鍵值
* blobColumnName - blob類型的列名
* blobColumnValue - blob的值
* tableName - 資料表的表名
* */
public boolean insertSqlByPid(Map<String, String> param) {
String pidColumnName = param.get("pidColumnName").toUpperCase();
String pidColumnValue = param.get("pidColumnValue");
String blobColumnName = param.get("blobColumnName").toUpperCase();
String blobColumnValue = param.get("blobColumnValue");
String tableName = param.get("tableName").toUpperCase();
Connection con;
try {
con = DriverManager.getConnection(url, username, password);
con.setAutoCommit(false);
String sql1 = "insert into "+tableName+"("+pidColumnName+","+blobColumnName+") values('"+pidColumnValue+"',empty_blob())";
Statement statement = con.createStatement();
boolean b2 = statement.execute(sql1);
String sql2 = "select "+blobColumnName+" from "+tableName+" where "+pidColumnName+"='"+pidColumnValue+"' for update";
PreparedStatement stmt = con.prepareStatement(sql2);
ResultSet rs = stmt.executeQuery();
OutputStream outStream = null;
if (rs.next()) {
BLOB blob = (BLOB) rs.getBlob(1);
outStream = blob.getBinaryOutputStream();
outStream.write(blobColumnValue.getBytes(), 0,blobColumnValue.getBytes().length);
}
outStream.flush();
outStream.close();
con.commit();
con.close();
return true;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
}
2、更新方法
/*
* pidColumnName - 主鍵對應的列名
* pidColumnValue - 主鍵值
* blobColumnName - blob類型的列名
* blobColumnValue - blob的值
* tableName - 資料表的表名
* */
public boolean updateSqlByPid(Map<String, String> param) {
String pidColumnName = param.get("pidColumnName").toUpperCase();
String pidColumnValue = param.get("pidColumnValue");
String blobColumnName = param.get("blobColumnName").toUpperCase();
try {
blobColumnName = new String(blobColumnName.getBytes(),"UTF-8");
} catch (UnsupportedEncodingException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
String blobColumnValue = param.get("blobColumnValue");
String tableName = param.get("tableName").toUpperCase();
Connection con;
try {
con = DriverManager.getConnection(url, username, password);
con.setAutoCommit(false);
String sql1 = "update "+tableName+" set "+blobColumnName+" = empty_blob() where "+pidColumnName+" = '"+pidColumnValue+"'";
Statement statement = con.createStatement();
boolean b1 = statement.execute(sql1);
String sql2 = "select "+blobColumnName +" as BLOB_NAME from "+tableName+" where "+pidColumnName+"='"+pidColumnValue+"' for update";
PreparedStatement stmt = con.prepareStatement(sql2);
ResultSet rs = stmt.executeQuery();
OutputStream outStream = null;
if (rs.next()) {
BLOB blob = (BLOB) rs.getBlob("BLOB_NAME");
outStream = blob.getBinaryOutputStream();
outStream.write(blobColumnValue.getBytes("UTF-8"), 0,blobColumnValue.getBytes("UTF-8").length);
}
outStream.flush();
outStream.close();
con.commit();
con.close();
return true;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
}
3、查詢方法
/*
* pidColumnName - 主鍵對應的列名
* pidColumnValue - 主鍵值
* blobColumnName - blob類型的列名
* blobColumnValue - blob的值
* tableName - 資料表的表名
* */
public String selectSqlByPid(Map<String, String> param) {
String blobInfo = null;
String pidColumnName = param.get("pidColumnName").toUpperCase();
String pidColumnValue = param.get("pidColumnValue");
String blobColumnName = param.get("blobColumnName").toUpperCase();
String tableName = param.get("tableName").toUpperCase();
Connection con;
try {
con = DriverManager.getConnection(url, username, password);
con.setAutoCommit(false);
String sql3 = "select "+blobColumnName+" from "+tableName+" where "+pidColumnName+"='"+pidColumnValue+"' ";
PreparedStatement stmt2 = con.prepareStatement(sql3);
ResultSet rs2 = stmt2.executeQuery();
if (rs2.next()) {
BLOB blob = (BLOB) rs2.getBlob(1);
blobInfo = new String(blob.getBytes((long)1, (int)blob.length()),"UTF-8");
}
con.close();
return blobInfo;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
return blobInfo;
}
}
二、clob資料的處理
此處clob 的處理與blob屬性的處理是略不相同的，但是原理還是一樣的，此處是通過reader流轉為byte數組再轉為String資料傳回
1、查詢方法
/*
* pidColumnName - 主鍵對應的列名
* pidColumnValue - 主鍵值
* clobColumnName - clob類型的列名
* clobColumnValue - clob的值
* tableName - 資料表的表名
* */
public String selectSqlByPid(Map<String, String> param) {
String clobInfo = null;
String pidColumnName = param.get("pidColumnName").toUpperCase();
String pidColumnValue = param.get("pidColumnValue");
String clobColumnName = param.get("clobColumnName").toUpperCase();
String tableName = param.get("tableName").toUpperCase();
Reader inStream =null;
Connection con;
try {
con = DriverManager.getConnection(url, username, password);
con.setAutoCommit(false);
String sql = "select "+clobColumnName+" from "+tableName+" where "+pidColumnName+"='"+pidColumnValue+"' ";
PreparedStatement stmt = con.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
Clob clob = rs.getClob(1);
if(clob==null){
System.out.println("照片資訊為空");
}else{
//clob解碼編譯為base64
String pic = clob.getSubString((long)1, (int) clob.length());
byte[] binary = (new sun.misc.BASE64Decoder()).decodeBuffer(pic.replaceAll(" ", ""));
//照片存到本地
String path = "D:/zhks/picture/temp/";
File temp = new File(path);
if(!temp.exists()) {
temp.mkdirs();
}
String filename = path+"/"+pidColumnValue+".jpg";
FileOutputStream fos = null;
fos = new FileOutputStream(filename);
fos.write(binary, 0, binary.length);
fos.close();
clobInfo = filename;
}
}
con.commit();
con.close();
return clobInfo;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
return clobInfo;
}
}
2、更新方法
/*
* pidColumnName - 主鍵對應的列名
* pidColumnValue - 主鍵值
* clobColumnName - clob類型的列名
* clobColumnValue - clob的值
* tableName - 資料表的表名
* */
public boolean updateByPid(Map<String, String> param) {
String pidColumnName = param.get("pidColumnName").toUpperCase();
String pidColumnValue = param.get("pidColumnValue");
String clobColumnName = param.get("clobColumnName").toUpperCase();
try {
clobColumnName = new String(clobColumnName.getBytes(),"UTF-8");
} catch (UnsupportedEncodingException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
String clobColumnValue = param.get("clobColumnValue");
String tableName = param.get("tableName").toUpperCase();
Connection con;
try {
con = DriverManager.getConnection(url, username, password);
con.setAutoCommit(false);
String sql1 = "update "+tableName+" set "+clobColumnName+" = empty_clob() where "+pidColumnName+" = '"+pidColumnValue+"'";
Statement statement = con.createStatement();
boolean b1 = statement.execute(sql1);
String sql2 = "select "+clobColumnName +" as CLOB_NAME from "+tableName+" where "+pidColumnName+"='"+pidColumnValue+"' for update";
PreparedStatement stmt = con.prepareStatement(sql2);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob("BLOB_NAME");
Writer outStream = clob.getCharacterOutputStream();
char[] c = clobColumnValue.toCharArray();
outStream.write(c, 0, c.length);
outStream.flush();
outStream.close();
}
con.commit();
con.close();
return true;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
}
![蘋果新版本,ios14更新體驗流程[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)