JAVA实现Excel表数据导入ORACLE数据库_秋夜平静

JAVA实现Excel表数据导入ORACLE数据库(2009-04-19 09:06:05)

分类：JAVA

在一个JAVA应用中，如果要把己知的一张Excel表数据导入一个己知的ORACLE表中，
可以根据ODBC读取这张Excel表等操作来完成相关功能。具体代码如下：

public class InsertData {

public static void main(String[] args) {
  Connection myConnSDB = null;
  PreparedStatement psInsertData = null;
  int InsertData = 0;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   myConnSDB = DriverManager.getConnection(
     "jdbc:oracle:thin:@192.168.1.202:1521:ORCL", "system",
     "ORCL");
   System.out.println(myConnSDB);

  } catch (Exception e) {
   e.printStackTrace();
  }

  try {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   Connection con = DriverManager.getConnection("jdbc:odbc:Book1");
   Statement st = con.createStatement();
   ResultSet rs = st.executeQuery("SELECT * FROM [Sheet1$]");
   psInsertData = myConnSDB.prepareStatement("INSERT INTO test1"
     + " (NAME,PASS)" + " VALUES " + " (?,?) ");

   while (rs.next()) {
    psInsertData.clearParameters();
    psInsertData.setString(1, rs.getString(1));
    psInsertData.setString(2, rs.getString(2));
    psInsertData.executeUpdate();

   }
   System.out.println("" + InsertData);
   st.close();
   con.close();
  } catch (Exception ex) {
   System.err.print("Exception: ");
   System.err.println(ex.getMessage());
   ex.printStackTrace();
  } finally {
   try {
    if (psInsertData != null) {
     psInsertData.close();
    }
    if (myConnSDB != null) {
     myConnSDB.close();
    }

   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
}
}

在一个WEB应用中,根据一个Excel表，一个ORACLE表，导入ORACLE中，这里用到了连接池，jxl.jar和commons-dbutils-1.1.jar。

1.TOMCAT中连接池的代码如下：

2.业务连接代码如下：

package test;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import jxl.Sheet;
import jxl.Workbook;

import org.apache.commons.dbutils.QueryRunner;

public class XlsToOra {

private String xls;

private String table;
private DataSource ds;
public XlsToOra() {
}

public XlsToOra(String xls, String table) {
this.xls = xls;
this.table = table;
}

public String getTable() {
return table;
}

public void setTable(String table) {
this.table = table;
}

public String getXls() {
return xls;
}

public void setXls(String xls) {
this.xls = xls;
}

public void init() {
  Context initContext;
  try {
   initContext = new InitialContext();
   ds = (DataSource) initContext.lookup("java:/comp/env/oracleds");
   System.out.println(ds);
  } catch (NamingException e) {
   e.printStackTrace();
  }
}

public boolean toOracle() throws Exception {
  Workbook book = Workbook.getWorkbook(new File(xls));
  Sheet sheet = book.getSheet(0);
  int rows = sheet.getRows();
  int cols = sheet.getColumns();
  String sql = "INSERT INTO " + table + " VALUES (";
  for (int i = 0; i < cols - 1; i++) {
   sql += "?,";
  }
  sql += "?)";
  init();
  QueryRunner queryRun = new QueryRunner(ds);
  for (int i = 1; i < rows; i++) {
   String param[] = new String[cols];
   for (int j = 0; j < cols; j++) {
    param[j] = sheet.getCell(j, i).getContents();
   }
   queryRun.update(sql, param);
  }
  return false;
}

public List getAttribute() throws Exception {
  List list = null;
  String sql = "SELECT * FROM " + table;
  PreparedStatement ps = getConnection().prepareStatement(sql);
  ResultSet rs = ps.executeQuery();
  ResultSetMetaData metaData = rs.getMetaData();
  int len = metaData.getColumnCount();
  for (int i = 1; i <= len; i++) {
   System.out.print(metaData.getColumnName(i) + "\t");
  }
  while (rs.next()) {
   System.out.println();
   for (int i = 1; i <= len; i++) {
    System.out.print(rs.getObject(i) + "\t\t");
   }
  }
  return list;
}

public static Connection getConnection() throws Exception {
  Connection con = null;
  Class.forName("oracle.jdbc.driver.OracleDriver");
  con = DriverManager.getConnection(
    "jdbc:oracle:thin:@192.168.1.202:1521:ORCL", "system", "ORCL");
  return con;
}