1、vo-》数据库表模型
public class POJO implements Serializable {
对应表的属性信息
}
2、DAO-》处理接口
public interface POJODAO {
public boolean POJOFromExcelToDataBase(业务参数);}
2、Impl-》接口实现类
public class POJODAOImpl implements POJODAO {
privateConnectionconn=DataBase.getConnection();
publicbooleanPOJOFromExcelToDataBase(业务参数){
Statementsm=null;
ReadExcelreadExcel=newReadExcel();//创建读取excel实例
try{
conn.setAutoCommit(false);
sm=conn.createStatement();
List<String>sqlList=readExcel.ReadExcelToSql(excelPath,+业务参数).getSql();//读取excel返回SQL
for(Stringsql:sqlList){
sm.addBatch(sql);
}
int[]successRun=sm.executeBatch();
if(successRun.length==sqlList.size()){
conn.commit();
returntrue;
}else{
conn.rollback();
returnfalse;
}
}catch(SQLExceptione){
try{
conn.rollback();
returnfalse;
}catch(SQLExceptione1){
//TODOAuto-generatedcatchblock
e1.printStackTrace();
}
}finally{
try{
sm.close();
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
}
returnfalse;
}}
3、读取Excel文件信息 返回制定的Sql集合
public class ReadExcel {
privateSimpleDateFormatformat=newSimpleDateFormat("yyyy-MM-dd");
publicSqlCollectionReadExcelToSql(StringexcelPath,+业务参数){
SqlCollectionsqlCollection=newSqlCollection();//创建封装sql的集合
sqlCollection.setTableName(POJO.class.getName());
Field[]fields=POJO.class.getDeclaredFields();
StringID;
StringRCid="000000000000000000000000000000";
StringRCFIRST;
StringRCKEEP="000000000000000000000000000000";
StringBufferwrite=newStringBuffer();
StringargDate=format.format(newDate());
try{
WorkbookworkBook=Workbook.getWorkbook(newFile(excelPath));
Sheetsheet=workBook.getSheet(0);
Cellcell=null;
intcolumns=sheet.getColumns();
introws=sheet.getRows();
System.out.println("columns:"+columns+"rows:"+rows);
for(introw=1;row<rows;row++){
for(intcolumn=0;column<columns;column++){
cell=sheet.getCell(column,row);
if(column==0){
RCFIRST=cell.getContents();
ID=UUID.randomUUID().toString();
if(null!=RCFIRST&&!"".equals(RCFIRST)){
RCID=ID;
RCKEEP=RCFIRST;
write.append("row:").append(row).append("")
.append(RCKEEP);
}
sqlCollection.addData(fields[0].getName(),ID);
sqlCollection.addData(fields[1].getName(),RCID);
sqlCollection.addData(fields[2].getName(),registerID);
sqlCollection.addData(fields[3].getName(),baseID);
sqlCollection.addData(fields[4].getName(),courseID);
sqlCollection.addData(fields[5].getName(),argDate);
sqlCollection.addData(fields[6].getName(),String.valueOf(row));
sqlCollection.addData(fields[7].getName(),RCID);
sqlCollection.addData(fields[8].getName(),RCKEEP);
}else{
Stringarg0;
arg0=(null==cell.getContents()
||"".equals(cell.getContents())?"#":cell
.getContents());
System.out.println("column="+column);
System.out.println(fields[column+8].getName());
sqlCollection.addData(fields[column+8].getName(),
arg0);
write.append("").append(arg0);
}
}
sqlCollection.addSql();
System.out.println(write.toString());
write=newStringBuffer("");
}
}catch(BiffExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}catch(IOExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
returnsqlCollection;
}4、将Excel读取出来的信息进行封装成SQL语句
public class SqlCollection implements Serializable {
privateList<String>sql=null;
privateMap<String,String>dataKT=null;
privateStringtableName;
publicList<String>getSql(){
if(null==sql||sql.isEmpty())
returnnull;
returnsql;
}
publicvoidaddSql(){
if(null==sql||sql.isEmpty()){
sql=newArrayList<String>();
}
sql.add(this.toString());
dataKT.clear();
}
publicvoidaddData(StringcolumnData,Stringdata){
if(null==dataKT){
dataKT=newLinkedHashMap<String,String>();
}
dataKT.put(columnData,data);
}
publicvoidsetTableName(StringtableName){
intbeginIndex=tableName.lastIndexOf(".");
this.tableName=tableName.substring(beginIndex+1);
}
publicStringtoString(){
StringBuffersqlData=newStringBuffer();
StringBuffercolumnData=newStringBuffer();
StringBufferdata=newStringBuffer();
sqlData.append("insertinto").append(this.tableName);
columnData.append("(");
data.append("values('");
Iterator<Entry<String,String>>it=dataKT.entrySet().iterator();
while(it.hasNext()){
Entry<String,String>entry=it.next();
if(it.hasNext()){
columnData.append(entry.getKey()).append(",");
data.append(entry.getValue()).append("','");
}else{
columnData.append(entry.getKey()).append(")");
data.append(entry.getValue()).append("')");
}
}
sqlData.append(columnData).append(data);
returnsqlData.toString();
}
publicStringbuilderSql(List<POJO>rcs){
StringBuffersql=newStringBuffer("select*fromPOJO-》表名称where1<>1");
returnsql.toString();
}
}