Java jdbc oracle10g

基础示例：

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