Java 操作Oracle数据库Clob字段

来源：互联网发布：昆仑数据科技知乎编辑：程序博客网时间：2024/05/06 01:11

package com.cicc.dataloader;

/**
* @author fenglei
*
*/
import java.io.BufferedReader;
import java.io.CharArrayReader;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.PrintWriter;
import java.io.StringReader;

import oracle.sql.CLOB;

import org.apache.poi.hssf.usermodel.HSSFCell; // cell
import org.apache.poi.hssf.usermodel.HSSFRow; //row
import org.apache.poi.hssf.usermodel.HSSFSheet; //sheet
import org.apache.poi.hssf.usermodel.HSSFWorkbook; //excel
import java.sql.*;
import java.util.HashMap;
import java.util.Iterator;
import java.sql.Clob;

public class LoadRawData {

public final static String FILE_PATH = "E:\\data.xls";

public static void main(String[] args) {
try {

LoadRawData.extractTeamData(FILE_PATH);
} catch (Exception ex) {
ex.printStackTrace();
}
}

// team
public static void extractTeamData(String fileName) throws Exception {
FileInputStream fileInput = new FileInputStream(fileName);
HSSFWorkbook wb = new HSSFWorkbook(fileInput);
HSSFSheet sheet = wb.getSheet("PE_RAW_TEAM"); // name of sheet

int rowNum = sheet.getLastRowNum();
System.out.println("Total row number:" + rowNum);
HSSFRow row = sheet.getRow(0);
int columnNum = row.getLastCellNum();
System.out.println("Total column number:" + columnNum);

String allColumns = "";
String allValues = "";

String teamId = "";
String resume = "";
for (int i = 0; i <= rowNum; i++) {
row = sheet.getRow(i);

if (i == 0) { // first line is column name
StringBuffer sb = new StringBuffer();
for (int j = 0; j <= columnNum; j++) {
HSSFCell cell = row.getCell(j);

String colName = getCellValue(cell);
sb.append(colName + ",");

}
allColumns = sb.toString().substring(0,
sb.toString().length() - 2);
// System.out.println("All columns:" + allColumns); // columns
continue;

}

if (null != row && i > 0) {
StringBuffer sb = new StringBuffer();
// System.out.println("");
for (int k = 0; k < columnNum; k++) {
HSSFCell cell = row.getCell(k);
String cellVal = getCellValue(cell);

if (k == 3 || k == 15) { // process
// date
// column
if (!"".equals(cellVal.trim())) {

sb.append("to_date('1900-01-01','yyyy-mm-dd')+"
+ cellVal + "-2,");
} else {
sb.append("'" + cellVal + "',");
}
}
// sb.append(cellVal + ",");
else {

if (cellVal.contains("'")) { // special char
cellVal = cellVal.replace("'", "||'''||'");
}
if (k == 18) {
resume = cellVal;
cellVal = "empty_clob()"; // not process resume here
sb.append(cellVal+",");
continue;
}
if (k == 0) {
teamId = cellVal;
sb.append("?"+",");
continue;
}
sb.append("'" + cellVal + "',");
}
}
allValues = sb.toString().substring(0,
sb.toString().length() - 1);
// System.out.println("All values:" + allValues); // values
// string
}

System.out.println("insert into PE_RAW_TEAM(" + allColumns
+ ",ID,CREATE_DATE,IS_ACTIVE" + ") values(" + allValues
+ ",companyseq.nextval,sysdate,1)");
String sqlStr = "insert into PE_RAW_TEAM(" + allColumns
+ ",ID,CREATE_DATE,IS_ACTIVE) values(" + allValues
+ ",companyseq.nextval,sysdate,1)";

// loadData(sqlStr);
System.out.println("update teamId=" + teamId + " resume");
insertClob(teamId, resume, sqlStr);
}

fileInput.close();
}

public static boolean insertClob(String teamId, String strClob, String sql) {
boolean suc = false;
// CLOB使用的是oracle.sql.CLOB貌似JDBC接口的java.sql.Clob支持读入，但不支持写入Clob对象，Blob也是。。。
// 这里需要注意的是，在给HH调试bug的时候，它出的错误是，无法把oracle.sql.CLOB对象转换成oracle.sql.CLOB，因为它连接数据库是用
// 了Tomcat配置的数据源，所以在tomcat的common/lib目录下面有一个classes12.jar或者是ojdbc.jar，它的项目里面因为要引用oracle.sql.jar
// 所以在项目lib目录下它也放了一个jar，这样造成了jar包之间的冲突，解决方法就是，将Web应用lib目录下的oracle驱动给移除掉（不是从构建路径上给
// remove掉，而是删除，如果remove不起作用，试过。。。）,然后外部引用common/lib目录下面的oracle驱动
CLOB clob = null;
// 插入一个空的Clob对象，这是一个Cursor
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

String url = "jdbc:oracle:thin:@192.168.142.43:1521:rawp";
String userName = "irkm";
String password = "irkm";
conn = DriverManager.getConnection(url, userName, password);
// 禁用自动提交事务
conn.setAutoCommit(false);
ps = conn.prepareStatement(sql);
ps.setString(1, teamId);
ps.executeUpdate();
ps.close();

// 查询并获得这个cursor,并且加锁
sql = "SELECT resume FROM pe_raw_team WHERE team_id=? for update";
ps = conn.prepareStatement(sql);
ps.setString(1, teamId);
rs = ps.executeQuery();
if (rs.next()) {
clob = (CLOB) rs.getClob(1);
}

// 获得clob对象的输出流
PrintWriter pw = new PrintWriter(clob.getCharacterOutputStream());
pw.write(strClob);
// 一定要flush(),否则不会将String对象写入到Clob当中去。。。
pw.flush();
ps.close();

// 更新clob对象
sql = "UPDATE pe_raw_team set resume =? where team_id=?";
ps = conn.prepareStatement(sql);
ps.setClob(1, clob);
ps.setString(2, teamId);
ps.executeUpdate();
ps.close();
conn.commit();
pw.close();
} catch (Exception e) {
e.printStackTrace();
}

return suc;
}

public static String getCellValue(HSSFCell cell) {
String value = "";
if (null != cell) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // numeric
// System.out.println(cell.getNumericCellValue() + " ");
value = new Double(cell.getNumericCellValue()).toString();
break;
case HSSFCell.CELL_TYPE_STRING: // string
// System.out.println(cell.getStringCellValue() + " ");
value = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
// System.out.println(cell.getBooleanCellValue() + " ");
value = new Boolean(cell.getBooleanCellValue()).toString();
break;
case HSSFCell.CELL_TYPE_FORMULA: // formula
// System.out.println(cell.getCellFormula() + " ");
value = cell.getCellFormula();
break;
case HSSFCell.CELL_TYPE_BLANK: // null
// System.out.println(" ");
value = "";
break;
case HSSFCell.CELL_TYPE_ERROR: // error
// System.out.println(" ");
value = "error";
break;
default:
// System.out.println("n/a");
break;
}
} else {
// System.out.println("");
return "";
}
return value;
}

}
/**
* 输出Clob对象
* @param userid
*/
public String readClob(String userid){
String test_clob="";
CLOB clob=null;
StringBuffer sb=new StringBuffer();
String sql="SELECT * FROM test_clob WHERE userid='"+userid+"'";
try {
ps=conn.prepareStatement(sql);
rst=ps.executeQuery();
if(rst.next()){
clob=(CLOB)rst.getClob(2);
}
Reader reader=clob.getCharacterStream();
char[] buffer=new char[1024];
int length=0;
while((length=reader.read(buffer))!=-1){
sb.append(buffer, 0, length);
}
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
test_clob=sb.toString();
return test_clob;
}
public static void main(String[] args){
new ClobTest().inserClob("4","我是Clob，哈哈");
String test=new ClobTest().readClob("4");
System.out.println(test);
}