EXCEL向ORCL数据库导数据的相关程序

package impExcel;
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.UUID;
import javax.swing.JOptionPane;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ImpExcel {

        private static Connection conn = null;
        private static Statement st = null;
        private static String drive = "oracle.jdbc.driver.OracleDriver";
        private static String DBurl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
        private static String name = "username"; // 数据库账号
        private static String pwd = "password"; // 数据库,密码
        {
        	try {
				Class.forName(drive);
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}
        }
        public static void main(String[] args) {
                  readExcelAndImp("c:/filePath.xls","tableName");
        	
        }
        
        /**
         * 创建数据库表，并校验当前表是否存在
         * @param tableName 数据库表名
         * @param createTableSql 建表sql
         */
        public static boolean createTable(String tableName,String createTableSql){
        	try {
            	DatabaseMetaData dMetaData = getConn().getMetaData();
            	ResultSet rs = dMetaData.getTables(null, null, "%", new String[]{"TABLE"});
            	boolean tableIsExists = false;
            	while (rs.next()){
            		if(rs.getString("TABLE_NAME").toUpperCase().equals(tableName.toUpperCase())){
            			tableIsExists=true;
            		}
            	}
            	rs.close();
            	if(tableIsExists){
            		int a = JOptionPane.showConfirmDialog(null, "数据库已经存在的表"+tableName+",确定删除吗？",
                            "温馨提示", JOptionPane.YES_NO_OPTION);
                    if (a == 0) {
                            try {
                            	excuteSql("drop table " + tableName + "");
                            	excuteSql(createTableSql);
                            	return true;
                            } catch (Exception e1) {
                                 e1.printStackTrace();
                            }
                    } else {
                        int aa = JOptionPane.showConfirmDialog(null,
                                "是否继续添加到原来表单裏面", "温馨提示", JOptionPane.YES_NO_OPTION);
                            if (aa == 1) {
                                    return false;
                            }else{
                            	return true;
                            }
                    }
            	}else{
            		excuteSql(createTableSql);
            		return true;
            	}
            } catch (Exception e) {
                   e.printStackTrace();
            }
        	return false;
        }
        
        /**
         * 读取excel并导入数据库
         * @param filePath excel文件路径
         * @param TableName 数据库表名
         */
        public static void readExcelAndImp(String filePath,String TableName) {
                File filename = new File(filePath);
                Workbook wb = null;
                String create = "create table " + TableName + "( uuid varchar(255)";
                String sql = "insert into " + TableName + "(";
                String parameter = "uuid";
                String value = "";
                String insert = "";
                try {
                        wb = Workbook.getWorkbook(filename);
                        Sheet sheet = wb.getSheet(0);// 第1个sheet
                        Cell cell = null;
                        int row = sheet.getRows();// 总行数
                        int col = sheet.getColumns();// 总列数
                        for (int i = 0; i < col; i++) {
                            create += ","+sheet.getCell(i, 0).getContents() + " varchar(255)";
                            parameter += ","+sheet.getCell(i, 0).getContents();
                        }
                        create += ")";
                        System.out.println("数据库生成表语句---" + create);
                        boolean createTableSuccess = createTable(TableName, create);
                        if(!createTableSuccess)return;
                        
                        st = getConn().createStatement();
                        long readStrTime = System.currentTimeMillis();
                        for (int i = 1; i < row; i++) {
                                value = "'"+UUID.randomUUID().toString()+"'";
                                for (int j = 0; j < col; j++) {
                                        cell = sheet.getCell(j, i);
                                        value += ",'" + cell.getContents() + "'";
                                }
                                insert = sql + parameter + ") values(" + value + ")";
                        		st.addBatch(insert);
//                        		System.out.println("添加语句----"+i+"---" + insert);
                        		if(i%1000==0){
                        			System.out.println("加载数据 "+i+" 条！");
                        		}
                        		if(i==row-1){
                        			System.out.println("加载数据 "+i+" 条！");
                        			long readEndTime = System.currentTimeMillis();
                        			System.out.println("数据读取完毕，耗时"+((readEndTime-readStrTime)/1000)+"秒!");
                        		}
                        }
                        System.out.println("开始入库......");
                        long startTime = System.currentTimeMillis();
                        st.executeBatch();
                        st.close();
                        long endTime = System.currentTimeMillis();
                        System.out.println("入库完成,耗时"+((endTime-startTime)/1000)+"秒,总计"+((endTime-readStrTime)/1000)+"秒!");
                } catch (IOException e) {
                        e.printStackTrace();
                } catch (BiffException e) {
                        e.printStackTrace();
				} catch (SQLException e) {
					e.printStackTrace();
				}finally{
					close(conn,st,null);
				}
        }

        /**
         * 获取数据库连接
         * @return
         */
        public static Connection getConn() {
                try {
            			if(conn==null||conn.isClosed()){
            				conn = DriverManager.getConnection(DBurl, name, pwd);
            			}
                } catch (Exception e) {
                        e.printStackTrace();
                        JOptionPane.showMessageDialog(null, "数据库连接错误");
                }
                return conn;
        }

        
        /**
         * 关闭数据库连接
         * @param conn 数据库连接
         * @param st statement对象 可为null
         * @param rs 结果集 可为null
         */
        public static void close(Connection conn,Statement st,ResultSet rs){
        	if(rs!=null)
        	{
        		try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}finally{
					if(st!=null)
					{
						try {
							st.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}finally
						{
							if(conn!=null)
							{
								try {
									conn.close();
								} catch (SQLException e) {
									e.printStackTrace();
								}
							}
						}
					}
				}
        	}else if(rs==null)
        	{
        		if(st!=null){
        			try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}finally
					{
						if(conn!=null)
						{
							try {
								conn.close();
							} catch (SQLException e) {
								e.printStackTrace();
							}
						}
					}
        		}else if(st==null)
        		{
        			try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
        		}
        	}else if(st==null&&rs==null){
        		try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
        	}
        }

        /**
         * 向数据库插入一条数据，并返回成功结果数，同时关闭数据库连接
         * @param st statement对象
         * @param sql 语名
         * @return
         */
        public static int excuteSql(String sql) {
                int result = 0;
                try {
                	    st = getConn().createStatement();
                        result = st.executeUpdate(sql);
                } catch (Exception e) {
                        System.out.println("添加失败");
                } finally {
                		try {
							st.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}finally{
							close(conn,st,null);
						}
                }
                return result;
        }
}