Java 简单mysql数据库实现增删查改——（含步骤）

客官°小女子只卖身不卖艺 2022-05-16 23:47 172阅读 0赞

工具：eclipse  
　　　mysql-5.5.40-win64  
　　　MySQL连接驱动：mysql-connector-java-5.1.7-bin.jar  
加载驱动：  
　　1. 在工程目录中创建lib文件夹，将下载好的JDBC放到该文件夹下，如下图所示：  
　　![这里写图片描述][70]  
　　2. 右键工程名，在java build path中的Libraries分页中选择Add JARs…，选择刚才添加的JDBC，如下图：  
　　![这里写图片描述][70 1]  
　　连接数据库并读取数据：  
　　数据库名称：day2  
　　数据表名称：book  
　　端口号：3306  
　　用户名：root  
　　密码：root

package mysql;
    
    import java.io.*;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Scanner;
    public class Mysql {
    
        static String driver="com.mysql.jdbc.Driver"; 
        static String url="jdbc:mysql://localhost:3306/day2";
        static String use="root";
        static String password="root";
        static int number;
        static Connection conn;
    
        public static void displayMainUI() {
            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("5) 导出数据");
            System.out.println("6) 导入数据");
        }
    
        public static void Cha() throws Exception {
            System.out.println("查看图书");
            //3.操作数据库，实现增删改查
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT id, sname, price FROM book;");
            //如果有数据，rs.next()返回true
            while(rs.next()){
                System.out.println("id: " + rs.getInt("id")
                        + " 书名: " + rs.getString("sname") 
                        + " 价格："+rs.getInt("price"));
            }        
        }
    
        public static void add() throws Exception {
           System.out.println("增加图书");
           Scanner ms = new Scanner(System.in);
           System.out.println("请输入id:");
           String number = ms.next();
           System.out.println("请输入sname:");
           String sname = ms.next();
           System.out.println("请输入price:");
           int price = ms.nextInt();
           Statement stmt = conn.createStatement();
           String sql = "insert into book values ("+number+",\"" + sname
                   + "\", " + price + ");"; 
           System.out.println("sql 语法: " + sql);
           stmt.execute(sql);   
    
        }
    
        public static void Gai() throws Exception {
            Cha();
            System.out.println("修改图书");
            Statement stmt = conn.createStatement();
            Scanner ms = new Scanner(System.in);
            System.out.println("请输入id:");
            int id = ms.nextInt();      
            System.out.println("请输入sname:");
            String sname = ms.next();
            System.out.println("请输入price:");
            int price = ms.nextInt();       
            String sql = "update book set price = " + price 
                    + ", sname = \"" + sname 
                    + "\" where id = " + id;
            System.out.println("sql 语法: " + sql);
            stmt.execute(sql);
        }
    
        public static void delete() throws Exception {
            System.out.println("已经输入4");
            //3.操作数据库，实现增删改查
            Statement stmt = conn.createStatement();
            stmt.execute("delete from book");       
        }
    
        public static void daochu()  throws Exception {
            System.out.println("导出数据");
            String output = "";
            output = output + "id\t书名\t价格\n\t";
    
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT id, sname, price FROM book;");
            //如果有数据，rs.next()返回true
            while(rs.next()){
                output = output + rs.getInt("id") + "\t"
                        + rs.getString("sname") + "\t"
                        +rs.getInt("price") + "\n\t";
            }  
    // System.out.println(output);
            try {
                BufferedWriter out = new BufferedWriter(new FileWriter("e:\\book.txt"));
                out.write(output);
                out.close();
            } catch (Exception e) {
                System.out.println("就知道会出现问题");
                System.out.println(e);
                return;
            }
            System.out.println("成功导出数据!");
        }
    
        public static void daorru() {
            System.out.println("导入数据");
        }
    
        public static void main(String args[]) throws Exception
        { 
            displayMainUI();
            Scanner ms = new Scanner(System.in);
            //1.加载驱动程序
            Class.forName(driver);
            //2. 获得数据库连接
            conn = DriverManager.getConnection(url, use, password);
    
            while (true) {
                int a = ms.nextInt();
    
                switch(a) {
                case 1:
                    Cha();//查看图书
                    break;
                case 2:
                    add();//增加图书
                    break;
                case 3:
                    Gai();//修改图书
                    break;
                case 4:
                    delete();//删除图书
                    break;
                case 5:
                    daochu();//导出数据
                    break;
                case 6:
                    daorru();//导入数据
                    break;
                }
            }
    
        }
    }

运行结果：  
![这里写图片描述][70 2]  
![这里写图片描述][70 3]  
　　![这里写图片描述][70 4]  
　　![这里写图片描述][70 5]  
![这里写图片描述][70 6]  
![这里写图片描述][70 7]  
![这里写图片描述][70 8]  
**mysql安装教程及安装包：[https://download.csdn.net/download/m0\_37738838/10512164][https_download.csdn.net_download_m0_37738838_10512164]**  
**源代码工程及效果图下载：[https://download.csdn.net/download/m0\_37738838/10529500][https_download.csdn.net_download_m0_37738838_10529500]**

[70]: /images/20220517/b7016e3b85d14e4b8af1955be49d2a59.png
[70 1]: /images/20220517/92677c96026d42a9856f08abfbc9632a.png
[70 2]: /images/20220517/d8523758c6cd4db9bd7dc12efb7455e0.png
[70 3]: /images/20220517/8240e03775934d33903aa400591e10a0.png
[70 4]: /images/20220517/77397271930241e99ce5d43062bac220.png
[70 5]: /images/20220517/3cd0b8ffdb9947ce926800144635971a.png
[70 6]: /images/20220517/1b4880f62ada4faeaa905f2472ae30dc.png
[70 7]: /images/20220517/dc29d5c59f404491bbf404b09d23b4ee.png
[70 8]: /images/20220517/a324e5c170554af3a30fe60fb1f6da6a.png
[https_download.csdn.net_download_m0_37738838_10512164]: https://download.csdn.net/download/m0_37738838/10512164
[https_download.csdn.net_download_m0_37738838_10529500]: https://download.csdn.net/download/m0_37738838/10529500