java基础 - 综合训练二

需求： （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("<li>本站数据：");
        //截取结束的时间
        int endIndex = string.indexOf("</li><li>参考数据1");
        int length = "<li>本站数据：".length();

        stringResult = string.substring(beginIndex+length,endIndex);
        return stringResult;
    }


}

SaveAndCompareImpl

public class SaveAndCompareImpl implements SaveAndCompare{
// 对比然后保存
    @Override
    public void compareAndSave() {

        List<OrclTable> list_orcl = new ArrayList<OrclTable>(); 

        List<SqlTable> list_sql = new ArrayList<SqlTable>(); 

        List<DifferentTable> list_different = new ArrayList<DifferentTable>();

        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_sql.size();i++){

            if(list_sql.get(i).getProductID() ==  list_orcl.get(i).getProduct_ID()  ){
                if(list_orcl.get(i).getStock() != list_sql.get(i).getStock()){

                        differentTable.setProduct_ID(list_sql.get(i).getProductID());

                        differentTable.setStock_orcl(list_orcl.get(i).getStock());

                        differentTable.setStock_sql(list_sql.get(i).getStock());

                        sqldao.insertDifferent(differentTable);



                }

            }
        }

    }

    //查询Different 表
    @Override
    public void quarryDifferent() {
        SqlDaoImpl sqldao= new  SqlDaoImpl();
        List<DifferentTable> list =sqldao.findDifferent();
        System.out.println("ID       STOCK_SQL  STOCK_ORCL");
        for(int i = 0; i<list.size();i++){

            System.out.printf("%-10s",list.get(i).getProduct_ID());
            System.out.printf("%-10s", list.get(i).getStock_sql());
            System.out.printf("%-10s", list.get(i).getStock_orcl());
            System.out.println();
        }
    }


}

TimerTarget

public class TimerTarget extends TimerTask{

    OutputStream oStream = null;

    List<DifferentTable> list = new ArrayList<DifferentTable>();



//  在构造函数里面传入输出流，输出到文件
     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<list.size();i++){
                int str1=list.get(i).getProduct_ID();
                int str2=list.get(i).getStock_orcl();
                int str3=list.get(i).getStock_sql();
                String str="ID=" +str1+"    STOCK_SQL="+str3+"  STOCK_ORCL="+str2;
                oStream.write(str.getBytes());
            }
            oStream.write("\r\n----------------------------------------------\r\n".getBytes());
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

}

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<DifferentTable> list = new SqlDaoImpl().findDifferent();

                    pwriter.println("您好这是查询的结果：\n"+"ID       STOCK_SQL  STOCK_ORCL");
                    for(int i = 0; i<list.size();i++){
                        pwriter.printf("%-10s",list.get(i).getProduct_ID());
                        pwriter.printf("%-10s", list.get(i).getStock_sql());
                        pwriter.printf("%-10s", list.get(i).getStock_orcl());

                        pwriter.flush();
                    }
                    flag = true;

                }else if ("更新".equals(string)){
                    if(flag == true){
                    SqlDaoImpl sqlDaoImpl = new SqlDaoImpl();
                    List<DifferentTable> list_different = new SqlDaoImpl().findDifferent();
                    for(int i =0 ; i<list_different.size();i++){
                        sqlDaoImpl.updateOrcl(list_different.get(i));
                    }
                    pwriter.println("恭喜你，更新成功 ");
                    pwriter.flush();
                    }else{
                        pwriter.println("请先进行对Differe表的查询");
                        pwriter.flush();
                    }

                }else if("IP查询".equals(string)) {
                    pwriter.println("请输入你要查询的IP");
                    pwriter.flush();
                    String stringip=scanner.nextLine();
                    String str1 =  new IPfindImpl().findAdressbyip(stringip);
                    String str2 = new IPfindImpl().indexStr(str1);
                    System.out.println(str2);
                    pwriter.println("您好，你所查询的IP地址在:"+str2);

                }else{
                    pwriter.println("您好，您的输入有误。请重新输入");
                    pwriter.flush();
                }
                pwriter.flush();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }


    }
}

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<SqlTable> findSql();
    public List<DifferentTable> findDifferent();
    public List<OrclTable> 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<SqlTable> findSql() {
        Connection connection =DButil.getConnection() ;
        String sql ="select * from huweihui";
        Statement statement =null;
        ResultSet rs =null;
        List<SqlTable> list= new ArrayList<SqlTable>();
        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<DifferentTable> findDifferent() {
        Connection connection = DButil.getConnection();
        Statement statement = null;
        ResultSet rs = null;
        List<DifferentTable> list = new ArrayList<DifferentTable>();
        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<OrclTable> findOrcl() {
        Connection connection = DButil.getConnection();
        Statement statement= null;
        ResultSet rs = null;
        List<OrclTable> list = new ArrayList<OrclTable>() ;
        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);
    }



    }
@Override
public 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();
            }
        }

    }


}

转载于:https://www.cnblogs.com/ithuhui/p/5921178.html