java基础 - 综合训练

来源：互联网发布：前置反冲洗过滤器知乎编辑：程序博客网时间：2024/05/02 04:29

题目要求

1、在mysql与oracle数据库里每个数据库中建立两张表，两张表都插入给数据库，分别把ID，产品的库存，两个数据；

2、使用JDBC把同一个仓库的产品进行两个数据库的比较，把不同的数据定时查询一次，并把数据存取到本地的生成.txt文件一份；

3、把异常的数据以mysql的标准进行更新，并写一个程序用来记录库存变动日志记录的表，并把变动表的数据记录到oracle数据库中；

4、每天定时9点从变动中的数据库表里通过java程序找到前一天变动商品的汇总信息。

用于程序运行测试

//用于程序运行测试public class StarTest {        public static void main(String[] args) {        //建立一个定时器，定时任务执行，这里设定一小时进行一次        Filewrite filewrite = new Filewrite();        Timer timerfile = new Timer();        timerfile.schedule(filewrite,0,3600000);    }}

Filewrite 包括了比较数据，更新数据，插入数据差异的表和写入文件

//Filewrite 包括了比较数据，更新数据，插入数据差异的表 和写入文件public class Filewrite extends TimerTask{    //输出流    OutputStream os =null;    //输入流    InputStream in=null;    //建立一个对象实现Mysql函数的调用     MysqlFunction mysql= new MysqlFunction();    //调用函数 查询表different （当时为了理解记住compare()方法和最后run()方法 所以分开了创建map）    Map map = mysql.getFindNotArg("different");    String str = "";    //oracle方法对像    OracleFunction oraclefunction =new OracleFunction ();    MysqlFunction mysqlfunction =new MysqlFunction();    Map maporacle= oraclefunction.getFindNotArg();    Map mapmysql=mysqlfunction.getFindNotArg("huweihui");    Map mapdifferent = mysqlfunction.getFindNotArg("different");    public Filewrite(){        try {            //文件写入路径            os=new FileOutputStream("E:/test/ttt.txt",true);        } catch (FileNotFoundException e) {            e.printStackTrace();        }    }    public void compare (){        OutputStream os =null;        //用迭代器进行读取，并再嵌套一个迭代器，在里面进行比较找到ID相同库存量不同的数据并更新oracle的表，并记录在新表different        //着重记住迭代器for循环遍历读取        //给出第二种格式 数据和这里无关 看格式        /*Map map = new MysqlFunction().getFindNotArg("huweihui");        Iterator iter = map.entrySet().iterator();        while(iter.hasNext()){        Map.Entry entry = (Map.Entry) iter.next();        int key =(int) entry.getKey();        int values = (int) entry.getValue();        System.out.println(key   +"    "+  values);        }*/        Set keyset = mapmysql.keySet();        Set keyset2 = maporacle.keySet();for (Iterator iterator = keyset.iterator();iterator.hasNext();){            int key = (int) iterator.next();            int values = (int) mapmysql.get(key);            for (Iterator iterator2 = keyset2.iterator();iterator2.hasNext();){                int key2 = (int ) iterator2.next();                int values2 =( int ) maporacle.get(key2);                if (key == key2){                    if(values!=values2){                        System.out.println("id same num difference");                        //插入到different表                        mysqlfunction.insertTest(key2, values-values2);                        //更新oracle表的数据                        oraclefunction.updata(key,values);                    }                        //oraclefunction.up                    }            }        }    }    //重载run方法       @Override    public void run() {        // TODO Auto-generated method stub        Set set = map.keySet();        String date= new Date(System.currentTimeMillis()).toLocaleString();        try{            str="  this is the record for different ";            os.write(str.getBytes());            //迭代器写入文件            for(Iterator iter = set.iterator();iter.hasNext();){                int key= (int) iter.next();                int values = (int) map.get(key);                 str ="ID: " +key +" num: "+values+ "      time :"+date +"\r\n";                System.out.println(str);                os.write(str.getBytes());                os.flush();            }        //  os.write();        }catch (Exception e){            e.printStackTrace();        }finally {            try {                os.close();            } catch (IOException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }        }    }}

ORACLE函数的方法

//ORACLE函数的方法public class OracleFunction {    //查询表的方法    public HashMap getFindNotArg (){        JdbcOracle jdbc =new JdbcOracle();        Connection con =null;        Statement  st = null;        ResultSet result =null;        String sql = "";        HashMap map =new HashMap();        try {            sql = "select * from THREE_STOCK";            con = jdbc.getConnection();            st = con.createStatement();            result = st.executeQuery(sql);            while(result.next()){                int id = result.getInt("PRODUCT_ID");                int num = result.getInt("STOCK");                //System.out.println("PRODUCT_ID:"+id+ "  STOCK:"+num);                map.put(id,num);            }        }catch(Exception e){            e.printStackTrace();        }finally {            try {                st.close();                result.close();                con.close();            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }        }        return map;    }    //更新数据的方法    public void updata(int id,int num){        JdbcOracle jdbcOracle = new JdbcOracle();        Connection connection = null;         Statement statement = null;        ResultSet rs =null;        String sql = "";        try {            connection = jdbcOracle.getConnection();            statement = connection.createStatement();            sql="update THREE_STOCK set STOCK= "            +num            +"where PRODUCT_ID = "            +id;            System.out.println("update successful ");            statement.executeUpdate(sql);        } catch (Exception e) {            // TODO: handle exception            e.printStackTrace();        }finally {            try {                rs.close();                statement.close();                connection.close();            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }        }    }    /*public static void main(String[] args) {        Map map=new OracleFunction().getFindNotArg();        Set keyset2 = map.keySet();        for (Iterator iterator2 = keyset2.iterator();iterator2.hasNext();){            int key2 = (int ) iterator2.next();            int values2 =( int ) map.get(key2);            System.out.println(key2+ "           "+values2);        }    }*/}

Mysql的方法函数

//Mysql的方法函数public class MysqlFunction {    public Map getFindNotArg (String tablename){        JdbcMysql jdbc =new JdbcMysql();        Connection con =null;        Statement  st = null;        ResultSet result =null;        String sql = "";        Map map = new LinkedHashMap();  ;        try {            sql = "select * from "+ tablename;            con = jdbc.getConnection();            st = con.createStatement();            result = st.executeQuery(sql);            while(result.next()){                int id = result.getInt("ID");                int num = result.getInt("STOCK");//              System.out.println("商品ID:"+id+ "  库存数量:"+num);                map.put(id, num);            }        }catch(Exception e){            e.printStackTrace();        }finally {            try {                st.close();                con.close();                result.close();            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }        }        return map;    }    public void insertTest(int id ,int num){        JdbcMysql jdbc=new JdbcMysql();        Connection con=null;        PreparedStatement pst=null;        String sql;        int result = 0;        try {            System.out.println("congratulation , inserted successedful ");            con=jdbc.getConnection();            sql="insert into different values(?,?)";            pst=con.prepareStatement(sql);            pst.setInt(1, id);            pst.setInt(2, num);             pst.executeUpdate();        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }finally {            try {                pst.close();                con.close();            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }        }    }}

Oracle JDBC

//Oracle JDBCpublic class JdbcOracle {    static {        try {            Class.forName("oracle.jdbc.driver.OracleDriver");        } catch (ClassNotFoundException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }    }    public Connection getConnection(){        String url = "jdbc:oracle:thin:@192.168.15.59:1521:orcl";        String user = "C##msuser02";        String password ="Password1";        Connection con =null;        try {            con =DriverManager.getConnection(url, user, password);        }catch (Exception e){            e.printStackTrace();        }        return con;    }}

MySQLJDBC

//MySQLJDBC public class JdbcMysql {    static{        try {            Class.forName("com.mysql.jdbc.Driver");        } catch (ClassNotFoundException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }    }    public Connection getConnection(){        String url="jdbc:mysql://127.0.0.1:3306/test?useSSL=false";        String user="hui";        String password="123456";        Connection con=null;        try {            con = DriverManager.getConnection(url, user, password);        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        return con;    }}

0 0