/**將execl數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫 * @author trsli * */ public class CreateDBTable { private static Connection conn; private static String sql=""; private static StringBuffer buf=new StringBuffer(); static{ conn=DBConnection.getConnection(); } public static void main(String[] args) { //數(shù)據(jù).xls文件路徑 System.out.println("輸入文件路徑："); String filename=new Scanner(System.in).nextLine(); //獲取需要插入數(shù)據(jù)庫的數(shù)據(jù)內(nèi)容 Object[][] contents=new PoiUtil().getmessage(filename); //獲取數(shù)據(jù)庫創(chuàng)建表格的字段名 Object[] titles=new PoiUtil().gettitles(filename); System.out.println("輸入數(shù)表格名："); String fname=new Scanner(System.in).nextLine(); try { String ziduan=""; String blank=""; PreparedStatement stmt=conn.prepareStatement(sql); buf.append("create table "+fname+"( id int primary key auto_increment"); //拼接字符串，主要是為了完全實現(xiàn)動態(tài)創(chuàng)建數(shù)據(jù)表格以及后期插入數(shù)據(jù) <span style="color:#ff6666;">for(int i=0;i<titles.length;i++){ buf.append(","+titles[i]+" varchar(20)"); if(i==titles.length-1){//字符串末尾沒有“，” ziduan+=titles[i]+""; blank+="?"; }else{ ziduan+=titles[i]+","; blank+="?,"; } } </span> //sql數(shù)據(jù)拼裝完成 sql="insert into "+fname+"("+ziduan+") values ("+ blank+")"; buf.append(" );"); stmt.executeUpdate(buf.toString()); System.out.println("預(yù)處理..."); try { Thread.sleep(2000);//用于保證數(shù)據(jù)庫建表操作完成 System.out.println("建表成功..."); } catch (InterruptedException e) { // TODO Auto-generated catch block e.printStackTrace(); } <span style="color:#ff6666;">for(int i=0;i<contents.length;i++){ Object[] content=contents[i];//獲取一行數(shù)據(jù) PreparedStatement insert=conn.prepareStatement(sql); for(int j=0;j<content.length;j++){ insert.setString(j+1, content[j].toString());//預(yù)加載數(shù)據(jù) } insert.executeUpdate();//插入數(shù)據(jù) System.out.println("第"+i+"行成功..."); try { Thread.sleep(500); } catch (InterruptedException e) { e.printStackTrace(); } </span> } System.out.println("插入數(shù)據(jù)完成"); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } |