JAVA Mysql练习1

最新推荐文章于 2024-07-27 13:31:57 发布

Oooooooo0ooo

最新推荐文章于 2024-07-27 13:31:57 发布

阅读量122

点赞数 2

分类专栏： java 文章标签： java 开发语言

本文链接：https://blog.csdn.net/Oooooooo0ooo/article/details/137649473

版权

java 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

package MySql;
import java.util.Scanner;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
public class main {
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        int choice;

        do {
            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.print("请输入选项（1-5）：");

            choice = scanner.nextInt();

            switch (choice) {
                case 1://新增
                    System.out.println("执行新增操作，请依次输入序号、姓名、电话:");
                    String NO = scanner.next();
                    String name = scanner.next();
                    String number = scanner.next();
                    DatabaseManager.insert(NO,name,number);
                    break;
                case 2://根据序号删除
                    System.out.println("执行删除操作，请输入需要删除的序号:");
                    NO = scanner.next();
                    DatabaseManager.delete(NO);
                    break;
                case 3://更新操作
                    System.out.println("执行更新操作，请依次输入序号、姓名、电话:");
                    NO = scanner.next();
                    name = scanner.next();
                    number = scanner.next();
                    DatabaseManager.update(NO, name, number);
                    break;
                case 4://查询操作
                    System.out.println("执行查询操作:");
                    DatabaseManager.squery();
                    break;
                case 5://退出
                    System.out.println("退出程序。");
                    break;
                default:
                    System.out.println("无效的选项，请重新输入。");

            }
            System.out.println();
        } while (choice != 5); //选择5退出循环
        scanner.close();
    }
}

在package包中创建main类

package MySql;
import javax.swing.*;
import java.util.Scanner;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
public class DatabaseManager {
    //定义MySQL的数据库驱动程序
    public static final String DBC="com.mysql.cj.jdbc.Driver";
    //定义MySQL数据库的连接地址，user为数据库名
    static final String DB_URL = "jdbc:mysql://localhost:3306/es";
    //数据库的用户名
    static final String USER = "root";
    //数据库的密码
    static final String PASS = "123456";
    public static void main(String[] args){
        // TODO Auto-generated method stub
        Connection conn = null;//数据库连接
        Statement stmt = null;//数据库操作
        String sql;
        try{
            // 注册 JDBC 驱动
            Class.forName(DBC);

            // 打开链接
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            //建表
            stmt = conn.createStatement();
            String sql1 = "create table contact(NO char(20),name varchar(20),number char(80),primary key(NO))";
            int result = stmt.executeUpdate(sql1);// executeUpdate语句会返回一个受影响的行数，如果返回-1就没有成功
            if(result != -1){
                System.out.println("创建数据表成功>_<!");
                sql = "insert into contact(NO,name,number) values('1','ly','18088888888')";
                result = stmt.executeUpdate(sql);
                sql = "insert into contact(NO,name,number) values('2','nr','19099999999')";
                result = stmt.executeUpdate(sql);
            }

        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }finally{
            // 关闭资源
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {}// 什么都不做
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }

    }
    //新增方法
    public static void insert(String NO,String name,String number) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            String sql = "INSERT INTO contact(NO, name, number) VALUES (?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, NO);
            pstmt.setString(2, name);
            pstmt.setString(3, number);
            int result = pstmt.executeUpdate();
            if (result != 0) {
                System.out.println("新增数据成功！");
            }
        } catch (SQLException e) {
            System.out.println("MySQL操作错误");
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

   //删除方法
    public static void delete(String NO) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            String sql = "DELETE FROM contact WHERE NO=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, NO); //设置第一个参数的值
            int result = pstmt.executeUpdate();
            // 执行 DELETE 语句
            int rowsDeleted = pstmt.executeUpdate();
            // 输出受影响的行数
            System.out.println(rowsDeleted + " row deleted.");
        } catch (SQLException e) {
            System.out.println("MySQL操作错误");
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //更新方法
    public static void update(String NO,String name,String number) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            String sql = "UPDATE contact SET name = ? ,number = ? WHERE NO =?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, name);
            pstmt.setString(2, number);
            pstmt.setString(3, NO);
            int result = pstmt.executeUpdate();
            if (result != 0) {
                System.out.println("修改数据成功！");
            }
        } catch (SQLException e) {
            System.out.println("MySQL操作错误");
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //查询方法
    public static void squery() {
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
            stmt = conn.createStatement();
            String sql = "SELECT * FROM contact";
            ResultSet rs = stmt.executeQuery(sql);
            // 展开结果集数据库
            while(rs.next()){
                // 通过字段检索

                String name = rs.getString("name");
                String NO = rs.getString("NO");
                String number = rs.getString("number");

                // 输出数据
                System.out.print("序号：" + NO);
                System.out.print(",姓名：" + name);
                System.out.print(",电话：" + number);
                System.out.print("\n");
            }
            // 完成后关闭
            rs.close();
            stmt.close();
            conn.close();
        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }finally{
            // 关闭资源
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException se2) {
            }// 什么都不做
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }


}

在databasemanager中创建了contact表并插入两条数据。

之后定义相关方法，在main中调用。