Demo_java基础 - 综合训练

题目要求

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 +"
";
                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 JDBC
public 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;
    }

}