package testOracleT; import java.sql.*; import readExcel.*; import java.util.Map; public class Test { /** * @param args * @throws SQLException */ public static void main(String[] args) throws SQLException{ Connection con = Connecter.createConnection(); PreparedStatement pre = null; ResultSet result = null; String sql ="insert into student values(?,?)"; int re = 0; try{ con.setAutoCommit(false);//事物开始 pre = (PreparedStatement) con.prepareStatement(sql); Map<Integer, User> maps= TestMaps.GetData(); for (Integer key : maps.keySet()) { pre.setString(1, maps.get(key).getName()); pre.setInt(2, maps.get(key).getId()); re = pre.executeUpdate(); if(re < 0){ //插入失败 con.rollback(); //回滚 System.out.println("已存在"); } con.commit(); //插入正常 // return re; } } catch(Exception e){ e.printStackTrace(); } con.close(); pre.close(); } }
package readExcel; import java.io.*; import java.util.*; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; public class Test { public static void main(String[] args){ Map<Integer, User> t= GetData(); System.out.println(t.size()); } public static Map<Integer, User> GetData(){ Map<Integer, User> maps = new HashMap<Integer, User>() ;
User users =null;jxl.Workbook readwb = null; try { //构建Workbook对象, 只读Workbook对象 //直接从本地文件创建Workbook InputStream instream = new FileInputStream("E:/test/name.xls"); readwb = Workbook.getWorkbook(instream); //Sheet的下标是从0开始 //获取第一张Sheet表 Sheet readsheet = readwb.getSheet(0); //获取Sheet表中所包含的总列数 int rsColumns = readsheet.getColumns(); //获取Sheet表中所包含的总行数 int rsRows = readsheet.getRows(); //获取指定单元格的对象引用 for (int i =1; i < rsRows; i++) { users = new User(); for (int j = 0; j < rsColumns; j++) { Cell cell = readsheet.getCell(j, i); if(j==0){ users.setId(Integer.parseInt(cell.getContents())); }else{ users.setName(cell.getContents()); } System.out.print(cell.getContents()); } System.out.println(users); maps.put(i,users); } System.out.println("共"+rsColumns+"列信息,"+rsRows+"行信息"); System.out.println(); } catch (Exception e) { e.printStackTrace(); } finally { readwb.close(); } System.out.println(maps);return maps;}}
写得时候遇到几个问题:1.从excel读取数据,存入User对象再put到Map集合时,每读取一组数据,需要更新User对象。不然到最后Map数组只剩下User对象最后一次更新的数据。所以把User users = new User(); 放在循环内部,更新创建对象。 导入Oacle数据库时,每提交一次,要提交事务,所以事务提交代码放在遍历Map集合内部。