前一篇文章我写得迷迷糊糊的，现在重新优化了一下。有朋友提出说为什么不在sql那边做一个存储过程呢且数据结构都是一样的？，由于技术有限，而且两张表的数据结构是差不多的，一个是在oracle 一个是在mysql 的，
1 // add by jim Sys_Ocd 2015-6-3 2 public void sysncPortal_Ocd() { 3 4 Connection ocd_query_erpConn = null; 5 Connection to_portalConn = null; 6 // add by jim 20150527 add OCD_Test 7 String form_form_ocd_configuration = "select org_vdc from OCD_Test";// 从mysql拿对应到资源池的数据 8 String form_erp_ocd_sql = "select org_cid,vms,provider_vdc,org_vdc,cpu,ram,storage,storage_plus from OCD_Test ";// 从erp拿数据 9 String portal_ocd_Insert_sql = "insert into OCD_Test(org_cid,vms,provider_vdc,org_vdc,cpu,ram,storage,storage_plus) values(?,?,?,?,?,?,?,?)"; 10 String portal_ocd_Update_sql = "update OCD_Test set org_cid=?,vms=?,provider_vdc=?,cpu=?,ram=?,storage=?,storage_plus=? where org_vdc =?"; 11 12 log.info(form_erp_ocd_sql); 13 14 List ocdIdList = new ArrayList(); 15 PreparedStatement o_query_erp = null; 16 PreparedStatement o_update_ocd = null; 17 PreparedStatement o_insert_ocd = null; 18 String org_cid = ""; 19 int vms = 0; 20 String provider_vdc = ""; 21 String org_vdc = ""; 22 String cpu = ""; 23 String ram = ""; 24 int storage = 0; 25 int storage_plus; 26 ResultSet rs_from = null; 27 //计数 28 int count_insert = 0; 29 int count_update = 0; 30 31 try { 32 // 连接portal 33 to_portalConn = this.getConnection( 34 datasource_to_portal[0].getDrv(), 35 datasource_to_portal[0].getUri(), 36 datasource_to_portal[0].getUid(), 37 datasource_to_portal[0].getPwd()); 38 // 连接erp 39 ocd_query_erpConn = this.getConnection(datasource_from[0].getDrv(), 40 datasource_from[0].getUri(), datasource_from[0].getUid(), 41 datasource_from[0].getPwd()); 42 System.out.println(datasource_to_portal[0].getDrv() 43 + datasource_to_portal[0].getUri() 44 + datasource_to_portal[0].getUid() 45 + datasource_to_portal[0].getPwd()); 46 System.out.println(datasource_from[0].getDrv() 47 + datasource_from[0].getUri() + datasource_from[0].getUid() 48 + datasource_from[0].getPwd()); 49 50 // 预编译sql语句 51 o_query_erp = ocd_query_erpConn.prepareStatement(form_erp_ocd_sql); 52 // update的方法是根据客户的ocd的名字去更新的，对应的Vcenter的OCD中 53 o_update_ocd = to_portalConn 54 .prepareStatement(portal_ocd_Update_sql); 55 o_insert_ocd = to_portalConn 56 .prepareStatement(portal_ocd_Insert_sql); 57 58 // 将portal上的数据添加到集合准备验证 59 Statement o_to_portal_ = to_portalConn.createStatement(); 60 ResultSet ocd_configuration = o_to_portal_ 61 .executeQuery(form_form_ocd_configuration); 62 while (ocd_configuration.next()) { 63 String tmp = ocd_configuration.getString(1); 64 ocdIdList.add(tmp); 65 } 66 67 // 加载数据 68 rs_from = o_query_erp.executeQuery(); 69 if (rs_from != null) { 70 while (rs_from.next()) { 71 // select 72 // org_cid,vms,provider_vdc,org_vdc,cpu,ram,storage,storage_plus 73 // from OCD_Test 74 org_cid = UI18n.getUnicode(rs_from.getString(1), "gbk"); 75 vms = rs_from.getInt(2); 76 provider_vdc = UI18n 77 .getUnicode(rs_from.getString(3), "gbk"); 78 org_vdc = UI18n.getUnicode(rs_from.getString(4), "gbk"); 79 // System.out.println(rs_from.getString(4)); 80 // System.out.println(org_vdc); 81 cpu = rs_from.getString(5); 82 ram = rs_from.getString(6); 83 storage = rs_from.getInt(7); 84 storage_plus = rs_from.getInt(8); 85 86 // 判断是否集合是否存在。存在 update 不存在 insert 87 if (!ocdIdList.contains(org_vdc)) { 88 // insert into 89 // OCD_Test(org_cid,vms,provider_vdc,org_vdc,cpu,ram,storage,storage_plus) 90 // values(?,?,?,?,?,?,?,?)"; 91 o_insert_ocd.setString(1, org_cid); 92 o_insert_ocd.setInt(2, vms); 93 o_insert_ocd.setString(3, provider_vdc); 94 o_insert_ocd.setString(4, org_vdc); 95 o_insert_ocd.setString(5, cpu); 96 o_insert_ocd.setString(6, ram); 97 o_insert_ocd.setInt(7, storage); 98 o_insert_ocd.setInt(8, storage_plus); 99 int result = o_insert_ocd.executeUpdate(); 100 o_insert_ocd.clearParameters(); 101 count_insert += result; 102 log.info(count_insert 103 + " INERT OCD_CONFIGURATION: org_vdc=" 104 + org_vdc + " OK!"); 105 } else { 106 107 // update OCD_Test set 108 // ,org_cid=?,vms=?,provider_vdc=?,cpu=?,ram=?,storage=?,storage_plus=? 109 // where org_vdc =?"; 110 o_update_ocd.setString(1, org_cid); 111 o_update_ocd.setInt(2, vms); 112 o_update_ocd.setString(3, provider_vdc); 113 o_update_ocd.setString(4, cpu); 114 o_update_ocd.setString(5, ram); 115 o_update_ocd.setInt(6, storage); 116 o_update_ocd.setInt(7, storage_plus); 117 o_update_ocd.setString(8, org_vdc); 118 119 // System.out.println(o_update_ocd.toString()); 120 count_update += o_update_ocd.executeUpdate(); 121 log.info("UPDATE tbl_customer: org_vdc=" + org_vdc 122 + " OK!"); 123 124 } 125 } 126 } 127 128 } catch (Exception e) { 129 if (ocd_query_erpConn != null) { 130 try { 131 ocd_query_erpConn.rollback(); 132 } catch (SQLException e1) { 133 // TODO Auto-generated catch block 134 e1.printStackTrace(); 135 } 136 } 137 138 e.printStackTrace(); 139 } finally { 140 try { 141 if (ocd_query_erpConn != null) 142 ocd_query_erpConn.close(); 143 144 if (to_portalConn != null) 145 to_portalConn.close(); 146 147 } catch (SQLException e) { 148 // TODO Auto-generated catch block 149 e.printStackTrace(); 150 } 151 } 152 153 }