- 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);
- }