需求： （JDBC&Socket& 定时器&多线程&URL 的简单综合练习）

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

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

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

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

5.创建Socket的服务端和客户端（可以允许多个客户端连接到服务端）

服务端向客户端提供三个功能

①查询sql表和orcl表两个表的差异

②更新orcl表的数据

6.③通过IP查询IP的归属地

PS(因为个人电脑没有ORACLE 重新在电脑做这个训练的时候 用了MYsql的表来替代了ORACLE的表)

bo

FileTimerTask

public interface FileTimerTask {    public void fileTimerTask();    public void fileWrite();}

IPfind

public interface IPfind {

public String findAdressbyip(String ip);

public String indexStr(String string);

}

SaveAndCompare

public interface SaveAndCompare {

// 查询Different表的数据

public void quarryDifferent();

// 查询差异并保存到Different表里面

public void compareAndSave();

}

bo.impl

IPfindImpl

public class IPfindImpl implements IPfind{
           private Scanner scannerWrite = null;    private Scanner scannerRead = null;    //通过URL打开资源链接获得资源    public IPfindImpl() {    }    @Override    public String findAdressbyip(String stringIp) {        String stringTotal="";        String stringTemp="";        try {            //定义URL地址            URL url = new URL("http://ip138.com/ips138.asp?ip="+stringIp+"&action=2");            //对影响到远程资源连接的参数进行操作            URLConnection connection = url.openConnection();            //读取资源            InputStream iStream = connection.getInputStream();            scannerRead = new Scanner(iStream);            while(scannerRead.hasNextLine()){                stringTemp = scannerRead.nextLine();                stringTotal =stringTotal+stringTemp+"\n";            }        } catch (MalformedURLException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        }        return stringTotal;    }    @Override    //用于截取网站查询结果并用字符串存取结果    public String indexStr(String string) {        String stringResult = "";        //截取开始位置        int beginIndex=string.indexOf("
本站数据：");        //截取结束的时间        int endIndex = string.indexOf("
参考数据1");        int length = "
本站数据：".length();        stringResult = string.substring(beginIndex+length,endIndex);        return stringResult;    }}

SaveAndCompareImpl

public class SaveAndCompareImpl implements SaveAndCompare{// 对比然后保存    @Override    public void compareAndSave() {        List
     
       list_orcl = new ArrayList
      
       ();         List
       
         list_sql = new ArrayList
        
         ();         List
         
           list_different = new ArrayList
          
           (); SqlDaoImpl sqldao= new SqlDaoImpl(); list_orcl = sqldao.findOrcl(); list_sql = sqldao.findSql(); list_different = sqldao.findDifferent(); DifferentTable differentTable = new DifferentTable();// 在这里进行比较并把数据放入list_different for(int i =0 ; i
           
             list =sqldao.findDifferent(); System.out.println("ID STOCK_SQL STOCK_ORCL"); for(int i = 0; i
           
          
         
        
       
      
     

TimerTarget

public class TimerTarget extends TimerTask{
         OutputStream oStream = null;    List
     
       list = new ArrayList
      
       ();//  在构造函数里面传入输出流，输出到文件     public TimerTarget() {         try {            oStream = new FileOutputStream("E:/test/ttt.txt",true);        } catch (FileNotFoundException e) {            e.printStackTrace();        }     }    @Override    public void run() {        //获取时间 记录        String date = new Date(System.currentTimeMillis()).toLocaleString();        list = new SqlDaoImpl().findDifferent();        try {            oStream.write(date.getBytes());            oStream.write("\r\n ----------------------------------------------\r\n".getBytes());            for(int i=0;i
      
     

control

ManagerController

public class ManagerController {    public static void main(String[] args) {        //比较然后保存数据        new SaveAndCompareImpl().compareAndSave();        //查询Different表并打印出来        new SaveAndCompareImpl().quarryDifferent();    }}

SocketClient

public class SocketClient {    public static void main(String[] args) {        try {            Socket socket = new Socket("127.0.0.1", 10001);            AcceptThread acceptThread = new AcceptThread(socket);            SendThread sendThread = new SendThread(socket);            acceptThread.start();            sendThread.start();        } catch (IOException e) {            e.printStackTrace();        }    }}class AcceptThread extends Thread{    private Socket socket = null;    private InputStream inputStream= null;    private Scanner scanner;    public AcceptThread() {    }    public AcceptThread(Socket socket){        this.socket= socket;    }    @Override    public void run(){        String string = "";        super.run();        try {            inputStream = socket.getInputStream();            scanner = new Scanner(inputStream);            while(true){                string = scanner.next();                    System.out.println(string);             }        } catch (IOException e) {            e.printStackTrace();        }    }}class SendThread extends Thread {    private Socket socket = null;    private PrintWriter pwrite=null;    private Scanner scanner ;    public SendThread(Socket socket){        this.socket=socket;    }    public SendThread(){    }    @Override    public void run(){            super.run();            String string="";            try {                pwrite= new PrintWriter(socket.getOutputStream());                scanner = new Scanner(System.in);                while (true){                    string =scanner.next();                    System.out.println("client send: "+string);                    pwrite.println(string);                    pwrite.flush();                }            } catch (IOException e) {                e.printStackTrace();            }        }    }

SocketServer

public class SocketServer {    private static ServerSocket serverSocket = null;    private static boolean flag = true;    public static void main(String[] args)  {        try {            serverSocket = new ServerSocket(10001);            while(flag){                Socket socket=serverSocket.accept();                ServerThread thread = new ServerThread(socket);                thread.start();            }        } catch (IOException e) {            e.printStackTrace();        }    }    public void stop(){        flag = false;    }}class ServerThread extends Thread{    private Socket socket;    private Scanner scanner;    private InputStream inputStream;    private PrintWriter pwriter;    public ServerThread() {    }    public ServerThread(Socket socket){        this.socket=socket;    }//  public void accept(){}    @Override    public void run(){        super.run();        try {            //accept message            inputStream = socket.getInputStream();            pwriter =  new PrintWriter(socket.getOutputStream());            scanner = new Scanner(inputStream);            String string="";            boolean flag = false;            while(true){                string = scanner.nextLine();                 //send message                if("查询".equals(string)){                    new SqlDaoImpl().deleteDifferent();                    //查询Different表并打印出来                    new SaveAndCompareImpl().compareAndSave();                    List
     
       list = new SqlDaoImpl().findDifferent();                    pwriter.println("您好这是查询的结果：\n"+"ID       STOCK_SQL  STOCK_ORCL");                    for(int i = 0; i
      
        list_different = new SqlDaoImpl().findDifferent();                    for(int i =0 ; i
      
     

TimerTaskController

public class TimerTaskController {    public static void main(String[] args) {        //定时器任务        TimerTarget timerTarget = new TimerTarget();        Timer timer = new Timer();        timer.schedule(timerTarget, 0, 3600000);    }}

dao

SqlDao

public interface SqlDao {//  查询用表    public List
     
       findSql();    public List
      
        findDifferent();    public List
       
         findOrcl();//  功能性用表//  更新表    public void updateOrcl(DifferentTable dif);//  插入表    public void insertDifferent(DifferentTable dif);//  删除表的数据    public void deleteDifferent();}
       
      
     

dao.impl

SqlDaoImpl

public class SqlDaoImpl implements SqlDao{
     /* * 查询 SQL表 huweihui 信息 * @see dao.SqlDao#findSql() */    @Override    public List
     
       findSql() {        Connection connection =DButil.getConnection() ;        String sql ="select * from huweihui";        Statement statement =null;        ResultSet rs =null;        List
      
        list= new ArrayList
       
        ();        try {            statement  = connection.createStatement();            rs = statement.executeQuery(sql);            while(rs.next()){                SqlTable sqltable= new SqlTable(rs);                list.add(sqltable);            }        } catch (SQLException e) {            e.printStackTrace();        }finally{            DButil.close(rs, statement, connection);        }        return list;    }/* * 查询 差异表 different * @see dao.SqlDao#findDifferent() */    @Override    public List
        
          findDifferent() {        Connection connection = DButil.getConnection();        Statement statement = null;        ResultSet rs = null;        List
         
           list = new ArrayList
          
           (); String sql = "select * from different"; try { statement = connection.createStatement(); rs = statement.executeQuery(sql); while(rs.next()){ DifferentTable dif= new DifferentTable(rs); list.add(dif); } } catch (SQLException e) { e.printStackTrace(); }finally{ DButil.close(rs, statement, connection); } return list; }/* * 查询ORCL表 但是因为没法连接ORCL 暂时用SQL表来测试 * @see dao.SqlDao#findOrcl() */ @Override public List
           
             findOrcl() { Connection connection = DButil.getConnection(); Statement statement= null; ResultSet rs = null; List
            
              list = new ArrayList
             
              () ; String sql ="select * from test "; try { statement = connection.createStatement(); rs = statement.executeQuery(sql); while (rs.next()){ OrclTable orcl = new OrclTable(rs); list.add(orcl); } } catch (SQLException e) { e.printStackTrace(); }finally{ DButil.close(rs, statement, connection); } return list; } //更新数据到Oracel 表 @Override public void updateOrcl(DifferentTable dif) { Connection connection = DButil.getConnection(); PreparedStatement pst = null; int rs = 0; String sql = "update test set STOCK = ? where ID = ?"; try { pst = connection.prepareStatement(sql); pst.setInt(1, dif.getStock_sql()); pst.setInt(2, dif.getProduct_ID()); rs=pst.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } }//插入数据到Different表@Override public void insertDifferent(DifferentTable dif) { Connection connection = DButil.getConnection(); PreparedStatement pst = null; int rs = 0 ; String sql = "insert into different values (?,?,?)"; try { pst = connection.prepareStatement(sql); pst.setInt(1, dif.getProduct_ID()); pst.setInt(2, dif.getStock_orcl()); pst.setInt(3, dif.getStock_sql()); //记录更新的数量 rs=pst.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally{ DButil.close(null, pst, connection); } }@Overridepublic void deleteDifferent() { Connection connection = DButil.getConnection(); Statement statement = null ; String sql = "delete from different"; ResultSet rs = null; try { statement = connection.createStatement(); rs = statement.executeQuery(sql); } catch (SQLException e) { e.printStackTrace(); }finally{ DButil.close(rs, statement, connection); }}}
             
            
           
          
         
        
       
      
     

model

DifferenTable

public class DifferentTable {    private int product_ID;    private int stock_sql;    private int stock_orcl;    public DifferentTable(){    }    public DifferentTable(ResultSet rs){        try {            this.product_ID = rs.getInt("ID");            this.stock_orcl = rs.getInt("STOCK_ORCL");            this.stock_sql = rs.getInt("STOCK_SQL");        } catch (SQLException e) {            e.printStackTrace();        }    }    public DifferentTable(int product_ID,int stock_sql,int stock_orcl){        this.product_ID=product_ID;        this.stock_orcl=stock_orcl;        this.stock_sql=stock_sql;    }    public int getProduct_ID() {        return product_ID;    }    public void setProduct_ID(int product_ID) {        this.product_ID = product_ID;    }    public int getStock_sql() {        return stock_sql;    }    public void setStock_sql(int stock_sql) {        this.stock_sql = stock_sql;    }    public int getStock_orcl() {        return stock_orcl;    }    public void setStock_orcl(int stock_orcl) {        this.stock_orcl = stock_orcl;    }}

OrclTable

public class OrclTable {    private int product_ID;    private int stock;    public OrclTable(){    }    public OrclTable(int product_ID,int stock){        this.product_ID = product_ID;        this.stock=stock;    }    public OrclTable(ResultSet rs){        try {            this.product_ID = rs.getInt("ID");            this.stock = rs.getInt("STOCK");        } catch (SQLException e) {            e.printStackTrace();        }    }    public int getProduct_ID() {        return product_ID;    }    public void setProduct_ID(int product_ID) {        this.product_ID = product_ID;    }    public int getStock() {        return stock;    }    public void setStock(int stock) {        this.stock = stock;    }}

SqlTable

public class SqlTable {    private int productID;    private int stock;    public SqlTable(){    }    public SqlTable(int productID , int stock){        this.productID = productID;        this.stock=stock;    }    public SqlTable(ResultSet rs){        try {            this.productID = rs.getInt("ID");            this.stock=rs.getInt("stock");        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }    }    public int getProductID() {        return productID;    }    public void setProductID(int productID) {        this.productID = productID;    }    public int getStock() {        return stock;    }    public void setStock(int stock) {        this.stock = stock;    }}

util

DButil

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