基础示例:
package com.shared.utils;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
public class MainTest {
public static void main(String[] args) {
//解析excel中的变量
parseExcelVar();
}
public static void parseExcelVar(){
//测试读取模板
List<Map<String, Object>> varList = readExcelModel();
Connection conn = connJdbc();
try {
conn.setAutoCommit(false);
} catch (SQLException e1) {
System.out.println("conn禁止自动提交!");
e1.printStackTrace();
}
//清空表数据
String sql = "TRUNCATE TABLE E5_BATCH_DOWNLOAD_MODEL";
Statement stmt = null;
try {
stmt = conn.createStatement();
} catch (SQLException e1) {
System.out.println("conn创建statement异常!");
e1.printStackTrace();
}
try {
stmt.execute(sql);
System.out.println("清空数据表完成!");
} catch (SQLException e1) {
System.out.println("statement执行sql异常!");
e1.printStackTrace();
}
//插入数据
sql = "INSERT INTO E5_BATCH_DOWNLOAD_MODEL VALUES(?,?,?,?,?,?,?,?)";
PreparedStatement preStmt = null;
try {
preStmt = conn.prepareStatement(sql);
} catch (SQLException e) {
System.out.println("创建Statement异常!");
e.printStackTrace();
}
String preID = getTimeStamp();
int index = 0;
Iterator<Map<String, Object>> varItor = varList.iterator();
while(varItor.hasNext()){
Map<String, Object> varMap = varItor.next();
try {
preStmt.setString(1, preID+index);
preStmt.setString(2, varMap.get("varCode")+"");
preStmt.setString(3, "");
preStmt.setInt(4, index);
preStmt.setInt(5, Integer.parseInt(varMap.get("sheetIndex")+""));
preStmt.setString(6, varMap.get("sheetName")+"");
preStmt.setInt(7, Integer.parseInt(varMap.get("rowIndex")+""));
preStmt.setInt(8, Integer.parseInt(varMap.get("colIndex")+""));
preStmt.addBatch();
} catch (SQLException e) {
System.out.println("prepareStatement写入参数异常!");
e.printStackTrace();
}
index++;
}
try {
preStmt.executeBatch();
} catch (SQLException e) {
System.out.println("执行批量更新异常!");
e.printStackTrace();
}
try {
preStmt.clearBatch();
preStmt.close();
conn.commit();
conn.close();
System.out.println("数据插入完成!");
} catch (SQLException e) {
System.out.println("提交更新及清理对象异常!");
e.printStackTrace();
}
}
public static List<Map<String, Object>> readExcelModel(){
//获取模板路径
String modelPath = "/Users/wangjian/Downloads/upload/政企体系报表模板.xlsx";
ExcelUtils excelUtils = new ExcelUtils(modelPath);
ToHtml toHtml = excelUtils.excelToHtml();
List<Map<String, Object>> varList = toHtml.getVarList();
Iterator<Map<String, Object>> varItor = varList.iterator();
while(varItor.hasNext()){
Map<String, Object> varMap = varItor.next();
System.out.println("sheetIndex:"+varMap.get("sheetIndex")+" sheetName:"+varMap.get("sheetName")+
" varCode:"+varMap.get("varCode")+" rowIndex:"+varMap.get("rowIndex")+" colIndex:"+varMap.get("colIndex"));
}
return varList;
}
public static Connection connJdbc(){
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("找到jdbc驱动类!");
} catch (ClassNotFoundException e) {
System.out.println("无法找到jdbc驱动类!");
e.printStackTrace();
}
String url = "jdbc:oracle:thin:@20.26.20.66:1521:qdfx";
String user = "qdfx";
String password = "qdfx_123";
Connection conn = null;
try {
conn = DriverManager.getConnection(url, user, password);
System.out.println("建立数据库连接!");
} catch (SQLException e) {
System.out.println("无法建立数据库连接!");
e.printStackTrace();
} finally {
if(conn == null){
System.out.println("异常----建立数据库连接!");
}
}
return conn;
}
public static String getTimeStamp(){
SimpleDateFormat sdf = new SimpleDateFormat("yyMMddHHmmssSSS");
return sdf.format(new Date());
}
}