需求
1、在mysql与oracle数据库里每个数据库中建立两张表,两张表都插入给数据库,分别把ID,产品的库存,两个数据;
2、使用JDBC把同一个仓库的产品进行两个数据库的比较,把不同的数据定时查询一次,并把数据存取到本地的生成.txt文件一份;
3、把异常的数据以mysql的标准进行更新,并写一个程序用来记录库存变动日志记录的表,并把变动表的数据记录到oracle数据库中;
4、每天定时9点从变动中的数据库表里通过java程序找到前一天变动商品的汇总信息。
用于程序运行测试
1 //用于程序运行测试 2 public class StarTest { 3 public static void main(String[] args) { 4 5 6 //建立一个定时器,定时任务执行,这里设定一小时进行一次 7 8 Filewrite filewrite = new Filewrite(); 9 10 Timer timerfile = new Timer(); 11 12 timerfile.schedule(filewrite,0,3600000); 13 14 15 } 16 17 }
Filewrite 包括了比较数据,更新数据,插入数据差异的表 和写入文件
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
1 //Filewrite 包括了比较数据,更新数据,插入数据差异的表 和写入文件 2 public class Filewrite extends TimerTask{ 3 4 //输出流 5 OutputStream os =null; 6 //输入流 7 InputStream in=null; 8 //建立一个对象实现Mysql函数的调用 9 MysqlFunction mysql= new MysqlFunction(); 10 //调用函数 查询表different (当时为了理解记住compare()方法和最后run()方法 所以分开了创建map) 11 Map map = mysql.getFindNotArg("different"); 12 13 String str = ""; 14 15 //oracle方法对像 16 OracleFunction oraclefunction =new OracleFunction (); 17 18 MysqlFunction mysqlfunction =new MysqlFunction(); 19 20 Map maporacle= oraclefunction.getFindNotArg(); 21 22 Map mapmysql=mysqlfunction.getFindNotArg("huweihui"); 23 24 Map mapdifferent = mysqlfunction.getFindNotArg("different"); 25 26 27 public Filewrite(){ 28 try { 29 //文件写入路径 30 os=new FileOutputStream("E:/test/ttt.txt",true); 31 32 33 } catch (FileNotFoundException e) { 34 35 e.printStackTrace(); 36 } 37 38 39 } 40 41 public void compare (){ 42 43 44 OutputStream os =null; 45 46 //用迭代器进行读取,并再嵌套一个迭代器,在里面进行比较找到ID相同库存量不同的数据并更新oracle的表,并记录在新表different 47 48 49 //着重记住迭代器for循环遍历读取 50 //给出第二种格式 数据和这里无关 看格式 51 /*Map map = new MysqlFunction().getFindNotArg("huweihui"); 52 53 Iterator iter = map.entrySet().iterator(); 54 while(iter.hasNext()){ 55 Map.Entry entry = (Map.Entry) iter.next(); 56 57 int key =(int) entry.getKey(); 58 int values = (int) entry.getValue(); 59 60 System.out.println(key +" "+ values); 61 }*/ 62 63 Set keyset = mapmysql.keySet(); 64 Set keyset2 = maporacle.keySet(); 65 66 67 for (Iterator iterator = keyset.iterator();iterator.hasNext();){ 68 69 int key = (int) iterator.next(); 70 71 int values = (int) mapmysql.get(key); 72 73 74 for (Iterator iterator2 = keyset2.iterator();iterator2.hasNext();){ 75 int key2 = (int ) iterator2.next(); 76 77 int values2 =( int ) maporacle.get(key2); 78 79 80 if (key == key2){ 81 if(values!=values2){ 82 System.out.println("id same num difference"); 83 //插入到different表 84 mysqlfunction.insertTest(key2, values-values2); 85 //更新oracle表的数据 86 oraclefunction.updata(key,values); 87 } 88 //oraclefunction.up 89 } 90 91 } 92 93 } 94 95 96 } 97 98 //重载run方法 99 @Override 100 public void run() { 101 // TODO Auto-generated method stub 102 Set set = map.keySet(); 103 String date= new Date(System.currentTimeMillis()).toLocaleString(); 104 try{ 105 str=" this is the record for different "; 106 107 os.write(str.getBytes()); 108 //迭代器写入文件 109 for(Iterator iter = set.iterator();iter.hasNext();){ 110 111 int key= (int) iter.next(); 112 113 int values = (int) map.get(key); 114 115 str ="ID: " +key +" num: "+values+ " time :"+date +"\r\n"; 116 System.out.println(str); 117 118 os.write(str.getBytes()); 119 120 os.flush(); 121 } 122 // os.write(); 123 }catch (Exception e){ 124 e.printStackTrace(); 125 }finally { 126 try { 127 os.close(); 128 } catch (IOException e) { 129 // TODO Auto-generated catch block 130 e.printStackTrace(); 131 } 132 } 133 } 134 135 }
ORACLE函数的方法
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
1 //ORACLE函数的方法 2 public class OracleFunction { 3 4 //查询表的方法 5 public HashMap getFindNotArg (){ 6 JdbcOracle jdbc =new JdbcOracle(); 7 8 Connection con =null; 9 10 Statement st = null; 11 12 ResultSet result =null; 13 14 String sql = ""; 15 16 HashMap map =new HashMap(); 17 18 try { 19 sql = "select * from THREE_STOCK"; 20 21 con = jdbc.getConnection(); 22 23 st = con.createStatement(); 24 25 result = st.executeQuery(sql); 26 27 28 while(result.next()){ 29 int id = result.getInt("PRODUCT_ID"); 30 31 int num = result.getInt("STOCK"); 32 33 //System.out.println("PRODUCT_ID:"+id+ " STOCK:"+num); 34 35 map.put(id,num); 36 } 37 }catch(Exception e){ 38 39 e.printStackTrace(); 40 41 }finally { 42 try { 43 st.close(); 44 result.close(); 45 con.close(); 46 } catch (SQLException e) { 47 // TODO Auto-generated catch block 48 e.printStackTrace(); 49 } 50 } 51 52 return map; 53 54 } 55 //更新数据的方法 56 public void updata(int id,int num){ 57 JdbcOracle jdbcOracle = new JdbcOracle(); 58 59 Connection connection = null; 60 61 Statement statement = null; 62 63 ResultSet rs =null; 64 65 String sql = ""; 66 67 try { 68 connection = jdbcOracle.getConnection(); 69 70 statement = connection.createStatement(); 71 72 sql="update THREE_STOCK set STOCK= " 73 +num 74 +"where PRODUCT_ID = " 75 +id; 76 77 System.out.println("update successful "); 78 79 statement.executeUpdate(sql); 80 81 82 83 } catch (Exception e) { 84 // TODO: handle exception 85 e.printStackTrace(); 86 }finally { 87 try { 88 rs.close(); 89 statement.close(); 90 connection.close(); 91 } catch (SQLException e) { 92 // TODO Auto-generated catch block 93 e.printStackTrace(); 94 } 95 } 96 97 } 98 99 100 101 102 103 104 105 /*public static void main(String[] args) { 106 Map map=new OracleFunction().getFindNotArg(); 107 Set keyset2 = map.keySet(); 108 for (Iterator iterator2 = keyset2.iterator();iterator2.hasNext();){ 109 int key2 = (int ) iterator2.next(); 110 111 int values2 =( int ) map.get(key2); 112 113 System.out.println(key2+ " "+values2); 114 } 115 }*/ 116 }
Mysql的方法函数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
1 //Mysql的方法函数 2 3 4 public class MysqlFunction { 5 6 public Map getFindNotArg (String tablename){ 7 JdbcMysql jdbc =new JdbcMysql(); 8 9 Connection con =null; 10 11 Statement st = null; 12 13 ResultSet result =null; 14 15 String sql = ""; 16 17 Map map = new LinkedHashMap(); 18 ; 19 try { 20 sql = "select * from "+ tablename; 21 22 con = jdbc.getConnection(); 23 24 st = con.createStatement(); 25 26 result = st.executeQuery(sql); 27 28 while(result.next()){ 29 int id = result.getInt("ID"); 30 31 int num = result.getInt("STOCK"); 32 33 // System.out.println("商品ID:"+id+ " 库存数量:"+num); 34 35 map.put(id, num); 36 } 37 }catch(Exception e){ 38 39 e.printStackTrace(); 40 41 }finally { 42 try { 43 st.close(); 44 con.close(); 45 result.close(); 46 } catch (SQLException e) { 47 // TODO Auto-generated catch block 48 e.printStackTrace(); 49 } 50 } 51 return map; 52 } 53 54 55 56 public void insertTest(int id ,int num){ 57 JdbcMysql jdbc=new JdbcMysql(); 58 Connection con=null; 59 PreparedStatement pst=null; 60 String sql; 61 int result = 0; 62 try { 63 64 System.out.println("congratulation , inserted successedful "); 65 con=jdbc.getConnection(); 66 67 sql="insert into different values(?,?)"; 68 69 pst=con.prepareStatement(sql); 70 71 pst.setInt(1, id); 72 73 pst.setInt(2, num); 74 75 pst.executeUpdate(); 76 77 78 } catch (SQLException e) { 79 // TODO Auto-generated catch block 80 e.printStackTrace(); 81 }finally { 82 try { 83 84 pst.close(); 85 con.close(); 86 87 } catch (SQLException e) { 88 // TODO Auto-generated catch block 89 e.printStackTrace(); 90 } 91 92 } 93 94 95 } 96 97 }
Oracle JDBC
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
1 //Oracle JDBC 2 public class JdbcOracle { 3 static { 4 try { 5 Class.forName("oracle.jdbc.driver.OracleDriver"); 6 } catch (ClassNotFoundException e) { 7 // TODO Auto-generated catch block 8 e.printStackTrace(); 9 } 10 11 } 12 13 14 15 public Connection getConnection(){ 16 String url = "jdbc:oracle:thin:@192.168.15.59:1521:orcl"; 17 18 String user = "C##msuser02"; 19 20 String password ="Password1"; 21 22 Connection con =null; 23 24 try { 25 con =DriverManager.getConnection(url, user, password); 26 }catch (Exception e){ 27 e.printStackTrace(); 28 } 29 30 return con; 31 } 32 33 }
MySQLJDBC
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
1 //MySQLJDBC 2 public class JdbcMysql { 3 static{ 4 try { 5 Class.forName("com.mysql.jdbc.Driver"); 6 } catch (ClassNotFoundException e) { 7 // TODO Auto-generated catch block 8 e.printStackTrace(); 9 } 10 } 11 12 public Connection getConnection(){ 13 String url="jdbc:mysql://127.0.0.1:3306/test?useSSL=false"; 14 15 String user="hui"; 16 17 String password="123456"; 18 19 Connection con=null; 20 21 try { 22 con = DriverManager.getConnection(url, user, password); 23 } catch (SQLException e) { 24 // TODO Auto-generated catch block 25 e.printStackTrace(); 26 } 27 28 return con; 29 } 30 31 }