jdbc批量将excel表格里面的数据导入到mysql表里面

public void batchInsertData(final HSSFSheet sheetAt) {

String s="    SELECT" +

"      count(*)" +

"      " +

"    FROM" +

"     INFORMATION_SCHEMA.COLUMNS" +

"    where" +

"    table_schema ='database'" +

"    AND" +

"    table_name  = 'Sheet1'";

final Integer num = jdbcTemplate.queryForObject(s,Integer.class);

System.out.println(num);

String sql = "";

StringBuilder stringBuilder = new StringBuilder();

stringBuilder.append("insert into Sheet1 values(");

for (int i = 0; i < num-1; i++) {

stringBuilder.append("?,");

}

stringBuilder.append("?);");

String strInsert = stringBuilder.toString();

jdbcTemplate.batchUpdate(strInsert, new BatchPreparedStatementSetter() {

public int getBatchSize() {

return sheetAt.getPhysicalNumberOfRows()-1;

}

 

public void setValues(PreparedStatement ps, int i) throws SQLException {

HSSFRow row = sheetAt.getRow(i+1);

System.out.println(i+"条");

for (int k = 0; k < num; k++) {

ps.setString(k+1, (row.getCell(k)==null?"":row.getCell(k).getStringCellValue()));

}

 

}

});

}