用poi方式将EXCEL表中的数据存到数据库里（涉及到多对多的表）

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.mdcl.mocha.system.hibernate.JDBCHelper;

public class Test2 {

public static Set set = new HashSet();

private static String sql_insert_xzy= "insert into code_code values(?,?,?,?,?,?,?,?)";

public static String sql_query_dzy = "select id, describe from code_dzy where flag='1'";

public static String sql_query_wlcm = "select id,describe from code_wlcm";

public static Map map_dzy = new HashMap();
public static Map map_wlcm = new HashMap();
public static void readExcel(){
HSSFWorkbook workbook;
try {
workbook = new HSSFWorkbook(new FileInputStream("G:/study/read excel to database/MBS_master_data_20120530.xls"));

HSSFSheet sheet = workbook.getSheet("Sheet1");

int rows = sheet.getLastRowNum();

for(int rowNum=1;rowNum<rows;rowNum++){
HSSFRow aRow = sheet.getRow(rowNum);

// HSSFCell idCell = aRow.getCell((short)0);
HSSFCell wlcmCell = aRow.getCell((short)1);
HSSFCell yijiCell = aRow.getCell((short)2);
HSSFCell erjiCell = aRow.getCell((short)3);


// String str_idCell=(idCell==null?"":(idCell.getStringCellValue()));
String str_wlcmCell = (wlcmCell==null?"":(wlcmCell.getStringCellValue()));
String str_yijiCell = (yijiCell==null?"":(yijiCell.getStringCellValue()));
String str_erjiCell = (erjiCell==null?"":(erjiCell.getStringCellValue()));
// System.out.println(str_idCell+"str_idCell");
// System.out.println(str_wlcmCell+"str_wlcmCell");
// System.out.println(str_yijiCell+"str_yijiCell");
// System.out.println(str_erjiCell+"str_erjiCell");
String wlcm_id = (String)map_wlcm.get(str_wlcmCell.trim());
String dzy_id = (String)map_dzy.get(str_yijiCell.trim());
set.add(wlcm_id+"|"+dzy_id+"|"+str_erjiCell.trim());
}
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

for(Iterator i = set.iterator();i.hasNext();){
String sp = (String)i.next();
String []t= sp.split("\\|");
System.out.println(t[0]+"~~~~~0");
System.out.println(t[1]+"~~~~~1");
System.out.println(t[2]+"~~~~~2");

}
}
public void init_dzy_data(){
JDBCHelper jdbcHelper = new JDBCHelper();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
String id = null;
String describe = null;
try{
conn = jdbcHelper.getConnection();
ps = conn.prepareStatement(sql_query_dzy);
rs = ps.executeQuery();
while(rs.next()){
id = rs.getString("id");
describe = rs.getString("describe");
map_dzy.put(describe, id);
}
}catch(SQLException sqle){
sqle.getStackTrace();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
conn.close();
rs.close();
ps.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
public void init_wlcm_data(){
JDBCHelper jdbcHelper = new JDBCHelper();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
String id = null;
String describe = null;
try{
conn = jdbcHelper.getConnection();
ps = conn.prepareStatement(sql_query_wlcm);
rs = ps.executeQuery();
while(rs.next()){
id = rs.getString("id");
describe = rs.getString("describe");
map_wlcm.put(describe,id);
}
}catch(SQLException sqle){
sqle.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
rs.close();
conn.close();
ps.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
public void insert_xzy(){
JDBCHelper jdbcHelper = new JDBCHelper();
Connection conn = null;
PreparedStatement ps = null;
// ResultSet rs = null;
try{
conn = jdbcHelper.getConnection();
conn.setAutoCommit(false);
ps = conn.prepareStatement(sql_insert_xzy);
int order_id = 1;
for(Iterator iterator = set.iterator();iterator.hasNext();){
String str_value = (String)iterator.next();
String str[] = str_value.split("\\|");
ps.setString(1, order_id+"");
ps.setString(2, str[1]);
ps.setString(3,str[2]);
ps.setInt(4, order_id);
ps.setString(5, "");
ps.setObject(6, "2012-06-13", java.sql.Types.DATE);
ps.setObject(7, "9999-12-13", java.sql.Types.DATE);
ps.setString(8, str[0]);
ps.addBatch();
order_id++;
}
int[] flag = ps.executeBatch();
conn.commit();
System.out.println("flag保存"+flag);
// ps.clearBatch();
}catch(SQLException sqle){
sqle.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
ps.clearBatch();
conn.close();
// rs.close();
// conn.setAutoCommit(true);
ps.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
public static void main(String[] args){
Test2 test = new Test2();
test.init_dzy_data();
test.init_wlcm_data();
readExcel();
test.insert_xzy();
}
}