1、vo-》数据库表模型
public class POJO implements Serializable {
对应表的属性信息
}
2、DAO-》处理接口
public interface POJODAO {
public boolean POJOFromExcelToDataBase(业务参数);
}
2、Impl-》接口实现类
public class POJODAOImpl implements POJODAO {
private Connection conn = DataBase.getConnection();
public boolean POJOFromExcelToDataBase(业务参数) {
Statement sm = null;
ReadExcel readExcel = new ReadExcel(); //创建读取excel实例
try {
conn.setAutoCommit(false);
sm = conn.createStatement();
List<String> sqlList = readExcel.ReadExcelToSql(excelPath,+业务参数).getSql();//读取excel 返回SQL
for (String sql : sqlList) {
sm.addBatch(sql);
}
int[] successRun = sm.executeBatch();
if (successRun.length == sqlList.size()) {
conn.commit();
return true;
}else{
conn.rollback();
return false;
}
} catch (SQLException e) {
try {
conn.rollback();
return false;
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
} finally {
try {
sm.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return false;
}
}
3、读取Excel文件信息 返回制定的Sql集合
public class ReadExcel {
private SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
public SqlCollection ReadExcelToSql(String excelPath, +业务参数) {
SqlCollection sqlCollection = new SqlCollection();//创建封装sql的集合
sqlCollection.setTableName(POJO.class.getName());
Field[] fields = POJO.class.getDeclaredFields();
String ID;
String RCID = "000000000000000000000000000000";
String RCFIRST;
String RCKEEP = "000000000000000000000000000000";
StringBuffer write = new StringBuffer();
String argDate = format.format(new Date());
try {
Workbook workBook = Workbook.getWorkbook(new File(excelPath));
Sheet sheet = workBook.getSheet(0);
Cell cell = null;
int columns = sheet.getColumns();
int rows = sheet.getRows();
System.out.println("columns:" + columns + " rows:" + rows);
for (int row = 1; row < rows; row++) {
for (int column = 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 {
String arg0;
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 = new StringBuffer("");
}
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return sqlCollection;
}
4、将Excel读取出来的信息进行封装成SQL语句
public class SqlCollection implements Serializable {
private List<String> sql = null;
private Map<String, String> dataKT = null;
private String tableName;
public List<String> getSql() {
if (null == sql || sql.isEmpty())
return null;
return sql;
}
public void addSql() {
if (null == sql || sql.isEmpty()) {
sql = new ArrayList<String>();
}
sql.add(this.toString());
dataKT.clear();
}
public void addData(String columnData, String data) {
if (null == dataKT) {
dataKT = new LinkedHashMap<String, String>();
}
dataKT.put(columnData, data);
}
public void setTableName(String tableName) {
int beginIndex = tableName.lastIndexOf(".");
this.tableName = tableName.substring(beginIndex+1);
}
public String toString() {
StringBuffer sqlData = new StringBuffer();
StringBuffer columnData = new StringBuffer();
StringBuffer data = new StringBuffer();
sqlData.append("insert into ").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);
return sqlData.toString();
}
public String builderSql(List<POJO> rcs){
StringBuffer sql = new StringBuffer("select * from POJO-》表名称 where 1<>1");
return sql.toString();
}
}