JDBC小小模拟01

骑猪看日落 2021-09-25 08:44 267阅读 0赞

### 文章目录 ###

*  题目
 *  分层
 *   *  客户端
     *  服务端
     *  创建Book表:id name author price
     *  客户端
     *   *  测试类（主类）
         *  BookView
         *  BookProxy
         *  BookService
         *  Book类
     *  服务器
     *   *  Server类
         *  BookDao类
         *  BookService
         *  Book
 *  第二种写法
 *   *  测试类
     *  BookView类
     *  BookDao类
     *  Book类

# 题目 #

使用jdk的动态代理,ServerSocket.Socket,IO流JDBC,反射实现2个工程之间远程通讯.实现图书的增删改查  
需要创建Book类:id name author price  
客户端:在控制台写菜单,调用代理类的方法(客户端不能访问数据库)  
服务器:在接口实现类使用JDBC实现数据库的增删改查

# 分层 #

## 客户端 ##

1.  测试类（主类）
2.  view:用户界面(console)
3.  proxy:代理
4.  service:接口
5.  domain:Book类  
    ![在这里插入图片描述][20210511222323187.png_pic_center]

## 服务端 ##

1.  controller:控制层，接收请求参数，调用dao层(访问层)的方法
2.  dao:data access object 数据访问对象层 直接通过jdbc访问数据库
3.  service:接口
4.  domain:实体类 Book类  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h5cGVydGV4dDEyMw_size_16_color_FFFFFF_t_70_pic_center]

## 创建Book表:id name author price ##

CREATE TABLE `book` (  
`id` int(11) NOT NULL AUTO\_INCREMENT,  
`name` varchar(255) DEFAULT NULL,  
`author` varchar(255) DEFAULT NULL,  
`price` decimal(10,2) DEFAULT NULL,  
PRIMARY KEY (`id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_0900\_ai\_ci;

## 客户端 ##

### 测试类（主类） ###

/** * 测试类(主类) */
    public class TestMain { 
        public static void main(String[] args) { 
            BookView bookView = new BookView();
            bookView.start();
        }
    }

### BookView ###

import domain.Book;
    import proxy.BookProxy;
    import service.BookService;
    
    import java.util.List;
    import java.util.Scanner;
    
    public class BookView { 
        BookService proxy = (BookService) BookProxy.getProxy(BookService.class,"127.0.0.1",9999);
        private Scanner sc = new Scanner(System.in);
        public void start(){ 
            while(true){ 
                menu();
                int i = sc.nextInt();
                switch (i){ 
                    case 1:
                        add();
                        break;
                    case 2:
                        update();
                        break;
                    case 3:
                        del();
                        break;
                    case 4:
                        list();
                        break;
                    case 0:
                        System.exit(0);
                        break;
                    default: break;
                }
            }
        }
        public void menu(){ 
            System.out.println("=======欢迎来到图书管理系统=======");
            System.out.println("请选择你的功能");
            System.out.println("1.增加图书");
            System.out.println("2.修改图书");
            System.out.println("3.删除图书");
            System.out.println("4.图书列表");
            System.out.println("0.退出程序");
        }
        //添加图书的方法
        public void add(){ 
            System.out.println("请输入名称:");
            String name = sc.next();
            System.out.println("请输入作者:");
            String author = sc.next();
            System.out.println("请输入价格:");
            double price = sc.nextDouble();
            boolean b = proxy.add(new Book(name, author, price));
            System.out.println(b?"添加成功":"添加失败");
        }
        //修改图书的方法
        public void update(){ 
            System.out.println("请输入编号:");
            int id = sc.nextInt();
            System.out.println("请输入名称:");
            String name = sc.next();
            System.out.println("请输入作者:");
            String author = sc.next();
            System.out.println("请输入价格:");
            double price = sc.nextDouble();
            boolean b = proxy.update(new Book(id, name, author, price));
            System.out.println(b?"修改成功":"修改失败");
        }
        //删除图书的方法
        public void del(){ 
            System.out.println("请输入编号:");
            int id = sc.nextInt();
            boolean b = proxy.del(id);
            System.out.println(b?"删除成功":"删除失败");
        }
        //图书列表
        public void list(){ 
            List<Book> list = proxy.list();
            for (Book book : list) { 
                System.out.println(book);
            }
        }
    }

### BookProxy ###

import java.io.ObjectInputStream;
    import java.io.ObjectOutputStream;
    import java.lang.reflect.InvocationHandler;
    import java.lang.reflect.Method;
    import java.lang.reflect.Proxy;
    import java.net.Socket;
    
    /* 1.代理类 */
    public class BookProxy { 
        public static Object getProxy(Class clz,String ip,int port){ 
            return Proxy.newProxyInstance(clz.getClassLoader(), new Class[]{ clz}, new InvocationHandler() { 
                @Override
                public Object invoke(Object proxy, Method method, Object[] args) throws Throwable { 
                    //1.创建Socket对象
                    Socket socket = new Socket(ip, port);
                    //io
                    ObjectOutputStream out = new ObjectOutputStream(socket.getOutputStream());
                    ObjectInputStream in = new ObjectInputStream(socket.getInputStream());
                    //write
                    out.writeUTF(method.getName());//方法名称
                    out.writeObject(method.getParameterTypes());//方法的参数类型
                    out.writeObject(args);//参数列表
                    //刷新一下流
                    out.flush();
                    //read
                    Object result = in.readObject();
                    //关流(关socket,in和out随之关闭)
                    socket.close();
                    return result;
                }
            });
        }
    }

### BookService ###

import domain.Book;
    
    import java.util.List;
    
    /* 增删改查 */
    public interface BookService { 
        public boolean add(Book book);
        public boolean update(Book book);
        public boolean del(int id);
        public List<Book> list();
    }

### Book类 ###

import java.io.Serializable;
    
    
    public class Book implements Serializable { 
        private static final long serialVersionUID = -8966856978012369060L;
        private int id;
        private String name;
        private String author;
        private double price;
    
        public Book() { 
        }
    
        public Book(String name, String author, double price) { 
            this.name = name;
            this.author = author;
            this.price = price;
        }
    
        public Book(int id, String name, String author, double price) { 
            this.id = id;
            this.name = name;
            this.author = author;
            this.price = price;
        }
    
        public int getId() { 
            return id;
        }
    
        public void setId(int id) { 
            this.id = id;
        }
    
        public String getName() { 
            return name;
        }
    
        public void setName(String name) { 
            this.name = name;
        }
    
        public String getAuthor() { 
            return author;
        }
    
        public void setAuthor(String author) { 
            this.author = author;
        }
    
        public double getPrice() { 
            return price;
        }
    
        public void setPrice(double price) { 
            this.price = price;
        }
    
        @Override
        public String toString() { 
            return id+"\t"+name+"\t"+author+"\t"+price;
        }
    }

## 服务器 ##

### Server类 ###

import service.BookService;
    import service.impl.BookServiceImpl;
    
    import java.io.IOException;
    import java.io.ObjectInputStream;
    import java.io.ObjectOutputStream;
    import java.lang.reflect.InvocationTargetException;
    import java.lang.reflect.Method;
    import java.net.ServerSocket;
    import java.net.Socket;
    import java.util.concurrent.ExecutorService;
    import java.util.concurrent.Executors;
    
    
    public class Server { 
        public static void main(String[] args) { 
            try { 
                //创建ServerSocket
                ServerSocket serverSocket = new ServerSocket(9999);
                //创建线程池
                ExecutorService pool = Executors.newCachedThreadPool();
                //创建接口实现类对象
                BookService bookService = new BookServiceImpl();
                //无限循环
                while(true){ 
                    Socket socket = serverSocket.accept();
                    pool.execute(new Runnable() { 
                        @Override
                        public void run() { 
                            try { 
                                //得到输入输出流
                                ObjectInputStream in = new ObjectInputStream(socket.getInputStream());
                                ObjectOutputStream out = new ObjectOutputStream(socket.getOutputStream());
                                //read
                                String methodName = in.readUTF();//方法名
                                Class[] paramTypes = (Class[])in.readObject();//方法的参数类型
                                Object[] args = (Object[])in.readObject();//方法的实参值
                                //反射调用
                                Method method = bookService.getClass().getDeclaredMethod(methodName, paramTypes);
                                method.setAccessible(true);
                                Object result = method.invoke(bookService, args);
                                //out
                                out.writeObject(result);
                                //close
                                socket.close();
                            } catch (IOException | ClassNotFoundException | NoSuchMethodException e) { 
                                e.printStackTrace();
                            } catch (IllegalAccessException e) { 
                                e.printStackTrace();
                            } catch (InvocationTargetException e) { 
                                e.printStackTrace();
                            }
                        }
                    });
                }
            } catch (Exception e) { 
                e.printStackTrace();
            }
        }
    }

### BookDao类 ###

import domain.Book;
    
    import java.sql.*;
    import java.util.ArrayList;
    import java.util.List;
    
    /* BookDao使用JDBC完成增删改查 */
    public class BookDao { 
        private Connection connection;
        private Statement statement;
        private ResultSet rs;
        static{ 
            try { 
                Class.forName("com.mysql.jdbc.Driver");
            } catch (ClassNotFoundException e) { 
                e.printStackTrace();
            }
        }
        /* 得到连接 */
        public void getConnection(){ 
            try { 
                connection = DriverManager.getConnection("jdbc:mysql:///test1","root","123456");
            } catch (SQLException throwables) { 
                throwables.printStackTrace();
            }
        }
        /* close */
        public void close(){ 
            if(rs!=null){ 
                try { 
                    rs.close();
                } catch (SQLException throwables) { 
                    throwables.printStackTrace();
                }
    
            }
    
            if(statement!=null){ 
                try { 
                    statement.close();
                } catch (SQLException throwables) { 
                    throwables.printStackTrace();
                }
    
            }
    
            if(connection!=null){ 
                try { 
                    connection.close();
                } catch (SQLException throwables) { 
                    throwables.printStackTrace();
                }
    
            }
        }
        public boolean add(Book book) { 
            getConnection();
            try { 
                statement = connection.createStatement();
                //sql
                String sql ="insert into book(name,author,price) values('"+book.getName()+"','"+book.getAuthor()+"',"+book.getPrice()+")";
                //执行sql
                int rows = statement.executeUpdate(sql);//返回插入了几条
                //返回
                return rows > 0;
            } catch (SQLException e) { 
                e.printStackTrace();
            }
            finally { 
                close();
            }
            return false;
        }
    
    
        public boolean update(Book book){ 
            getConnection();
            try { 
                statement = connection.createStatement();
                //sql
                String sql ="update book set name ='"+book.getName()+"',author ='"+book.getAuthor()+"',price= "+book.getPrice()+"where id="+book.getId();
                //执行sql
                int rows = statement.executeUpdate(sql);//返回插入了几条
                //返回
                return rows > 0;
            } catch (SQLException e) { 
                e.printStackTrace();
            }
            finally { 
                close();
            }
            return false;
        }
    
        public boolean del(int id){ 
            getConnection();
            try { 
                statement = connection.createStatement();
                //sql
                String sql ="delete from book where id="+id;
                //执行sql
                int rows = statement.executeUpdate(sql);//返回插入了几条
                //返回
                return rows > 0;
            } catch (SQLException e) { 
                e.printStackTrace();
            }
            finally { 
                close();
            }
            return false;
        }
    
        public List<Book> list(){ 
            List<Book> list = new ArrayList<>();
            getConnection();
            try { 
                 statement = connection.createStatement();
                 rs = statement.executeQuery("select * from book");
                 while(rs.next()){ 
                     list.add(new Book(rs.getInt("id"),
                             rs.getString("name"),
                             rs.getString("author"),
                             rs.getDouble("price")));
                 }
            } catch (SQLException throwables) { 
                throwables.printStackTrace();
            }finally { 
                close();
            }
            return list;
        }
    }

### BookService ###

### Book ###

import java.io.Serializable;
    
    public class Book implements Serializable { 
        private static final long serialVersionUID = -8966856978012369060L;
        private int id;
        private String name;
        private String author;
        private double price;
    
        public Book() { 
        }
    
        public Book(String name, String author, double price) { 
            this.name = name;
            this.author = author;
            this.price = price;
        }
    
        public Book(int id, String name, String author, double price) { 
            this.id = id;
            this.name = name;
            this.author = author;
            this.price = price;
        }
    
        public int getId() { 
            return id;
        }
    
        public void setId(int id) { 
            this.id = id;
        }
    
        public String getName() { 
            return name;
        }
    
        public void setName(String name) { 
            this.name = name;
        }
    
        public String getAuthor() { 
            return author;
        }
    
        public void setAuthor(String author) { 
            this.author = author;
        }
    
        public double getPrice() { 
            return price;
        }
    
        public void setPrice(double price) { 
            this.price = price;
        }
    
        @Override
        public String toString() { 
            return id+"\t"+name+"\t"+author+"\t"+price;
        }
    }

# 第二种写法 #

1.  测试类（主类）
2.  view:用户界面(console)
3.  proxy:代理
4.  domain:Book类  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h5cGVydGV4dDEyMw_size_16_color_FFFFFF_t_70_pic_center 1]

## 测试类 ##

public class Main { 
        public static void main(String[] args) { 
            BookView bookView = new BookView();
            bookView.start();
        }
    }

## BookView类 ##

import dao.BookDao;
    import domain.Book;
    
    import java.util.List;
    import java.util.Scanner;
    
    
    public class BookView { 
        private Scanner scanner = new Scanner(System.in);
        private BookDao bookDao = new BookDao();
        public void start(){ 
            while(true){ 
            menu();
                System.out.println("请输入功能编号:");
                int i = scanner.nextInt();
                switch (i){ 
                    case 1:
                        add();
                        break;
                    case 2:
                        update();
                        break;
                    case 3:
                        del();
                        break;
                    case 4:
                        list();
                        break;
                    default:
                        break;
                }
            }
        }
    
        private void del() { 
            System.out.println("请输入编号:");
            int id = scanner.nextInt();
            boolean b = bookDao.del(id);
            System.out.println(b?"删除成功":"删除失败");
        }
    
        private void list() { 
            List<Book> list = bookDao.list();
            for (Book book : list) { 
                System.out.println(book);
            }
        }
    
        private void update() { 
            System.out.println("请输入编号:");
            int id = scanner.nextInt();
            System.out.println("请输入名称:");
            String name = scanner.next();
            System.out.println("请输入作者:");
            String author = scanner.next();
            System.out.println("请输入价格:");
            double price = scanner.nextDouble();
            Book book = new Book(id,name, author, price);
            boolean b = bookDao.update(book);
            System.out.println(b?"修改成功":"修改失败");
        }
    
        private void add() { 
            System.out.println("请输入名称:");
            String name = scanner.next();
            System.out.println("请输入作者:");
            String author = scanner.next();
            System.out.println("请输入价格:");
            double price = scanner.nextDouble();
            Book book = new Book(name, author, price);
            boolean b = bookDao.add(book);
            System.out.println(b?"添加成功":"添加失败");
        }
    
        private void menu(){ 
            System.out.println("========图书管理系统========");
            System.out.println("1.添加图书");
            System.out.println("2.修改图书");
            System.out.println("3.删除图书");
            System.out.println("4.图书列表");
        }
    }

## BookDao类 ##

import domain.Book;
    
    import java.sql.*;
    import java.util.ArrayList;
    import java.util.List;
    
    /* BookDao使用JDBC完成增删改查 */
    public class BookDao { 
        private Connection connection;
        private Statement statement;
        private ResultSet rs;
        static{ 
            try { 
                Class.forName("com.mysql.jdbc.Driver");
            } catch (ClassNotFoundException e) { 
                e.printStackTrace();
            }
        }
        /* 得到连接 */
        public void getConnection(){ 
            try { 
                connection = DriverManager.getConnection("jdbc:mysql:///test1","root","123456");
            } catch (SQLException throwables) { 
                throwables.printStackTrace();
            }
        }
        /* close */
        public void close(){ 
            if(rs!=null){ 
                try { 
                    rs.close();
                } catch (SQLException throwables) { 
                    throwables.printStackTrace();
                }
                ;
            }
    
            if(statement!=null){ 
                try { 
                    statement.close();
                } catch (SQLException throwables) { 
                    throwables.printStackTrace();
                }
                ;
            }
    
            if(connection!=null){ 
                try { 
                    connection.close();
                } catch (SQLException throwables) { 
                    throwables.printStackTrace();
                }
    
            }
        }
        public boolean add(Book book) { 
            getConnection();
            try { 
                statement = connection.createStatement();
                //sql
                String sql ="insert into book(name,author,price) values('"+book.getName()+"','"+book.getAuthor()+"',"+book.getPrice()+")";
                //执行sql
                int rows = statement.executeUpdate(sql);//返回插入了几条
                //返回
                return rows > 0;
            } catch (SQLException e) { 
                e.printStackTrace();
            }
            finally { 
                close();
            }
            return false;
        }
    
    
        public boolean update(Book book){ 
            getConnection();
            try { 
                statement = connection.createStatement();
                //sql
                String sql ="update book set name ='"+book.getName()+"',author ='"+book.getAuthor()+"',price= "+book.getPrice()+"where id="+book.getId();
                //执行sql
                int rows = statement.executeUpdate(sql);//返回插入了几条
                //返回
                return rows > 0;
            } catch (SQLException e) { 
                e.printStackTrace();
            }
            finally { 
                close();
            }
            return false;
        }
    
        public boolean del(int id){ 
            getConnection();
            try { 
                statement = connection.createStatement();
                //sql
                String sql ="delete from book where id="+id;
                //执行sql
                int rows = statement.executeUpdate(sql);//返回插入了几条
                //返回
                return rows > 0;
            } catch (SQLException e) { 
                e.printStackTrace();
            }
            finally { 
                close();
            }
            return false;
        }
    
        public List<Book> list(){ 
            List<Book> list = new ArrayList<>();
            getConnection();
            try { 
                 statement = connection.createStatement();
                 rs = statement.executeQuery("select * from book");
                 while(rs.next()){ 
                     list.add(new Book(rs.getInt("id"),
                             rs.getString("name"),
                             rs.getString("author"),
                             rs.getDouble("price")));
                 }
            } catch (SQLException throwables) { 
                throwables.printStackTrace();
            }finally { 
                close();
            }
            return list;
        }
    }

## Book类 ##

public class Book { 
       private int id;
       private String name;
       private String author;
       private double price;
    
        public Book() { 
        }
    
        public Book(int id, String name, String author, double price) { 
            this.id = id;
            this.name = name;
            this.author = author;
            this.price = price;
        }
    
        public Book(String name, String author, double price) { 
            this.name = name;
            this.author = author;
            this.price = price;
        }
    
        public int getId() { 
            return id;
        }
    
        public void setId(int id) { 
            this.id = id;
        }
    
        public String getName() { 
            return name;
        }
    
        public void setName(String name) { 
            this.name = name;
        }
    
        public String getAuthor() { 
            return author;
        }
    
        public void setAuthor(String author) { 
            this.author = author;
        }
    
        public double getPrice() { 
            return price;
        }
    
        public void setPrice(double price) { 
            this.price = price;
        }
    
        @Override
        public String toString() { 
            return id+"\t"+name+"\t"+author+"\t"+price;
        }
    }

[20210511222323187.png_pic_center]: /images/20210923/bd5f022b0a5e49349dc0d873815512c3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h5cGVydGV4dDEyMw_size_16_color_FFFFFF_t_70_pic_center]: /images/20210923/57ad69ce272748dfb47bd4ca90fda5c0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h5cGVydGV4dDEyMw_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20210923/33788e692251499391bd4b911020a857.png