excel导入数据库的时候,需要剔除掉电话尾数4、7的。若数量大于20个,就随机剔除掉20个;否则就全部删除。(号码要在导入数据库前剔除掉)
这是导入的那段代码,现在的问题是能查出来,但是剔除不掉4、7尾数的号码。
public boolean dealExcelContent(String path,String city)throws Exception{
boolean isSuccess = false;
Connection conn = null;
PreparedStatement psmt = null;
int counter = 0; // 处理总条数
try {
InputStream is = new FileInputStream(path);
POIFSFileSystem fs = new POIFSFileSystem(is);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
int tatalRownum = sheet.getLastRowNum();
HSSFRow row = sheet.getRow(0);
conn = super.getConn();
conn.setAutoCommit(false);
String sql="INSERT INTO orfpb_fuse_phone( "
+ "market_id, phone, remark)"
+ "VALUES ( ?, ?, ?);";
psmt = conn.prepareStatement(sql.toString());
List<String> castList = new ArrayList<String>();// 保存手机尾数为4和7的数据
List<String> rightList = new ArrayList<String>(); //保存符合要求的数据
for(int i=1;i<=tatalRownum;i++){
row = sheet.getRow(i);
counter++;
psmt.setString(1, ExcelUtils.getCellFormatValue(row.getCell(0)).trim());
psmt.setString(2, ExcelUtils.getCellFormatValue(row.getCell(1)).trim());
//取得i行的第二列
String cellValue = ExcelUtils.getCellFormatValue(row.getCell(1)).trim();
if("4".equals(cellValue.substring(10))||"7".equals(cellValue.substring(10))){
castList.add(cellValue);
}
// else{
// rightList.add(cellValue);
// }
psmt.setString(3, ExcelUtils.getCellFormatValue(row.getCell(2)).trim());
//psmt.setString(4, ExcelUtils.getCellFormatValue(row.getCell(3)).trim());
//psmt.setString(5, ExcelUtils.getCellFormatValue(row.getCell(4)).trim());
int num = castList.size();
if(num < 20 ){
castList.removeAll(castList);
}else{
for(int j=0;j<=num && j<20;j++){
castList.remove(j);
}
}
psmt.addBatch();
//批处理100条
if (counter % 1000 == 0) {
psmt.executeBatch();
}
System.out.println(counter);
}
psmt.executeBatch();
conn.commit();
isSuccess = true;
} catch (Exception e) {
System.out.println("出错:" + counter);
conn.rollback();
e.printStackTrace();
}finally{
try {
if (psmt != null) {
psmt.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e2) {
e2.printStackTrace();
}
}
return isSuccess;
}
------解决方案--------------------