需求
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 包括了比较数据，更新数据，插入数据差异的表 和写入文件
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 @Override100 public void run() {101 // TODO Auto-generated method stub102 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 block130 e.printStackTrace();131 }132 }133 }134 135 }
ORACLE函数的方法
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的方法函数
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 block48 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 block80 e.printStackTrace();81 }finally {82 try {83 84 pst.close();85 con.close();86 87 } catch (SQLException e) {88 // TODO Auto-generated catch block89 e.printStackTrace();90 }91 92 }93 94 95 }96 97 }
Oracle JDBC
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
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 block25 e.printStackTrace();26 }27 28 return con;29 }30 31 }