load2db.java

可以load到DB自动补上字段列完成crud

package qc;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Iterator;
import java.util.LinkedHashMap;

import tool.BatchUpdate;
import tool.DBUtils;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

public class load2db {
private int rows=0;
private BatchUpdate bu=new BatchUpdate();
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		load2db QCDa=new load2db();
		QCDa.updateDB("c:\\test1.xls");
	
		
		
	}
	public void updateDB(String filePath){
		Workbook rwb = null;
		InputStream excelStream = null;
		int rowid=0;
		
		try {
			
			excelStream = new FileInputStream(filePath);
			rwb = Workbook.getWorkbook(excelStream);
			Sheet st = rwb.getSheet(0);
			Cell[] rowCells = st.getRow(rowid);
			rowid++;
			String[] rowString = new String[rowCells.length];
			LinkedHashMap<String, Integer> HMFileHead=new LinkedHashMap<String, Integer>();
			String sqlinsert0="";
			for (int i = 0; i < rowCells.length; i++){
				rowString[i] = rowCells[i].getContents().toUpperCase().trim();
				rowString[i]=rowString[i].replace(" ", "_");
				if (rowString[i].length()>10) {
					rowString[i]=rowString[i].substring(0,10);
				}
				if (!rowString[i].equals("")) {
					HMFileHead.put(rowString[i],i);
					sqlinsert0+=",\""+rowString[i]+"\"";
				}
				
			}
			String sql="select * from qc_daiso where 1=0";
			String[] TableField=DBUtils.getFieldsName(sql);
			LinkedHashMap<String, Integer> HMTableHead=new LinkedHashMap<String, Integer>();
			for (int i = 0; i < TableField.length; i++){
				HMTableHead.put(TableField[i],i);
			}
			
			for (Iterator iter = HMFileHead.keySet().iterator(); iter.hasNext();) {
				String key = (String) iter.next();
				if (!HMTableHead.containsKey(key)) {
					String sqlAlter="alter table QC_DAISO add \""+key+"\" varchar2(500)";
					System.out.println(sqlAlter);
					DBUtils.executeUpdate(sqlAlter);
				}
			}
			////
			String sqlinsert1="insert into QC_DAISO (filename"+sqlinsert0+") values ";
			String sqlinsert2="";
			String sqlupdate1="update QC_DAISO set ";
			String sqlupdate2=" where headid= ";
			for (int i = 1; i < st.getRows(); i++) {
				rowCells = st.getRow(i);
				String insert3="";
				String update3="";
				String headid="";
				if (rowCells.length==0) {
					continue;
				}
				for (int j = 0; j < rowString.length; j++) {
					if (rowString[j].equals("")) {
						continue;
					}
					String value="";
					if (rowCells.length>=j+1) {
						 value=rowCells[j].getContents().trim().replace("'", "");
					}else{
						value="";
					}
					
					
					insert3+=",'"+value+"'";
					if (rowString[j].equals("HEADID")) {
						headid=value;
					}else{
						update3+=",\""+rowString[j]+"\"='"+value+"'";
					}
					
				}
				if (!headid.equals("")) {
					String sqli=sqlinsert1+"('"+filePath+"'"+insert3+" )";
					String sqlu=sqlupdate1+" filename='"+filePath+"'"+update3+sqlupdate2+"'"+headid+"'";
					String select="select count(*) cnt from qc_daiso where headid='"+headid+"'";
					String cnt=DBUtils.selectOneField(select);
					rows++;
					if (cnt.equals("0")) {
						bu.upsqls(sqli);
						//System.out.println();
					}else{
						bu.upsqls(sqlu);
					}
				}
				
				
				
			}
			
			
			
			
		} catch (Exception e) {
			
			e.printStackTrace();
			System.out.println(rows);
			// TODO: handle exception
		}
		bu.upend();
	}
}