Java连接数据库（学生管理系统案例，可以实现增删改查）

首先，需要做一个准备工作——下载jar包，这个包是用来支持数据库的连接的

官网的下载链接：MySQL :: Download Connector/J

点击链接进入页面：

选择画红框的下载按钮。

与此同时，打开IDEA开发工具，在当前项目目录下新建一个lib目录文件夹用来存放第三方jar包，这样做方便管理和引用。下载完之后将jar包放进lib文件夹中，像这样：

然后点击左上角的文件—>项目结构，找到这个页面并按顺序点击红框里的按钮：

点击+号开始导入，找到刚刚保存jar的那个文件夹选择jar，在前面打上对号然后点击导入 。

这样的话就可以愉快的连接数据库了~

编写过程：

第1步，加载数据库的驱动

Class.forName("com.mysql.jdbc.Driver");

打出这一句后会报错，这时候按照软件提示来就可以，抛出或者用try...catch环绕

然后String一个数据库的表的地址：

String dburl="jdbc:mysql://localhost:3306/xs?useSSL=false&characterEncoding=utf-8"

这段文字的大致意思就是"jdbc:mysql://localhost:端口号/表名?是否使用SSL&字体设定=UTF-8"

再String数据库的账号和密码

String dbName = "root"; //数据库账号，默认是root
String dbPass = "******"; //数据库密码，输自己的

然后调用Connection方法进行连接

connection = DriverManager.getConnection(dburl, dbName, dbPass);

通过Connection对象获取Statement对象

statement = connection.createStatement();

到了这一步就可以定义sql语句了

String sql = "select * from users";

然后开始执行sql语句

statement.executeQuery(sql);

到这儿就是连接成功了

连接成功之后就可以为所欲为了，具体各种方法的实现我想就是通过更改每个方法中的sql语句来实现的

下面开始介绍add——添加方法

public static void addStudent() throws ClassNotFoundException, SQLException {
        //数据库地址
        String dburl = "jdbc:mysql://localhost:3306/xs?useSSL=false&characterEncoding=utf-8";
        //数据库的用户名和密码
        String user = "root";
        String password = "1234";
        //注册jdbc驱动
        Class.forName("com.mysql.jdbc.Driver");
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入学生id:");
        int id = scanner.nextInt();
        System.out.println("请输入学生姓名:");
        String name = scanner.next();
        //打开链接--连接mysql
        Connection connection = DriverManager.getConnection(dburl, user, password);
        Statement statement = connection.createStatement();
        String sql = "INSERT INTO stu_info values("+id+",'"+name+"')";
        //执行sql语句
        statement.executeUpdate(sql);
        System.out.println("添加成功");
        connection.close();

    }

del——删除方法

public static void delStudent() throws ClassNotFoundException, SQLException {
        //数据库地址
        String dburl = "jdbc:mysql://localhost:3306/xs?useSSL=false&characterEncoding=utf-8";
        //数据库的用户名和密码
        String user = "root";
        String password = "1234";
        //注册jdbc驱动
        Class.forName("com.mysql.jdbc.Driver");
        System.out.println("连接数据库中……");
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入要删除学生的id");
        int delId = scanner.nextInt();
        //打开链接
        Connection connection = DriverManager.getConnection(dburl, user, password);
        Statement statement = connection.createStatement();
        String sql = "delete from stu_info where id = " +delId+"";
        statement.executeUpdate(sql);
        System.out.println("删除成功");
        connection.close();

    }

showAll——展示所有学生信息

public static void showAll() throws ClassNotFoundException, SQLException{
        //数据库地址
        String dburl = "jdbc:mysql://localhost:3306/xs?useSSL=false&characterEncoding=utf-8";
        //数据库的用户名和密码
        String user = "root";
        String password = "1234";
        //注册jdbc驱动
        Class.forName("com.mysql.jdbc.Driver");
        //打开链接
        Connection connection = DriverManager.getConnection(dburl, user, password);
        Statement statement = connection.createStatement();
        //定义sql语句
        String sql = "SELECT * FROM xs.stu_info;";
        //statement.executeUpdate(sql);
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            System.out.println("ID:" + id);
            System.out.println("名字:" + name);
            System.out.println("==============");
        }
    }

quary——根据输入id进行查询

public static void quary() throws ClassNotFoundException, SQLException{
        //数据库地址
        String dburl = "jdbc:mysql://localhost:3306/xs?useSSL=false&characterEncoding=utf-8";
        //数据库的用户名和密码
        String user = "root";
        String password = "1234";
        //注册jdbc驱动
        Class.forName("com.mysql.jdbc.Driver");
        //打开链接
        Connection connection = DriverManager.getConnection(dburl, user, password);
        Statement statement = connection.createStatement();

        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入要查询的ID");
        int qid = scanner.nextInt();
        //定义sql语句
        String sql = "SELECT * FROM xs.stu_info where id =" + qid +"";

        //statement.executeUpdate(sql);
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            System.out.println("ID:" + id);
            System.out.println("名字:" + name);
        }

modify——修改

public static void modify() throws ClassNotFoundException, SQLException{
        //数据库地址
        String dburl = "jdbc:mysql://localhost:3306/xs?useSSL=false&characterEncoding=utf-8";
        //数据库的用户名和密码
        String user = "root";
        String password = "1234";
        //注册jdbc驱动
        Class.forName("com.mysql.jdbc.Driver");
        System.out.println("连接数据库中……");

        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入修改前的学生id:");
        int id1 = scanner.nextInt();

        System.out.println("请输入修改后的学生id:");
        int id2 = scanner.nextInt();
        System.out.println("请输入修改后学生姓名:");
        String name2 = scanner.next();

        //打开链接--连接mysql
        Connection connection = DriverManager.getConnection(dburl, user, password);
        Statement statement = connection.createStatement();

        String sql = "UPDATE stu_info SET id = "+id2+",name = '"+name2+"' WHERE id = "+id1+";";
        //执行sql语句
        statement.executeUpdate(sql);

        System.out.println("修改成功");
        connection.close();
    }

到这儿就算是大功告成了，如果想要添加更多的学生信息的话还可以在MySQL的表中添加，然后在Java的sql语句中进行具体的修改

然后再放上整体的一个代码吧

import com.mysql.jdbc.Driver;
import java.sql.*;
import java.util.Scanner;

public class jdbcPro2 {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        while (true) {
            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-退出系统");
            System.out.println("请输入指令:");
            Scanner scanner = new Scanner(System.in);
            String cmd = scanner.nextLine();
            switch (cmd) {
                case "1":
                    addStudent();
                    break;
                case "2":
                    delStudent();
                    break;
                case "3":
                    showAll();
                    break;
                case "4":
                    quary();
                    break;
                case "5":
                    modify();
                    break;
                case "6":
                    System.out.println("谢谢使用");
                    System.exit(0);

                    Connection connection = null;
                    Statement statement = null;
                    ResultSet set = null;
                    try {
                        //1.加载数据库驱动
                        Class.forName("com.mysql.jdbc.Driver");
                        //2.通过DriverManager获取数据库连接
                        String dburl = "jdbc:mysql://localhost:3306/xs?useSSL=false&characterEncoding=utf-8";
                        String dbName = "root"; //数据库账号
                        String dbPass = "1234"; //数据库密码
                        connection = DriverManager.getConnection(dburl, dbName, dbPass);
                        //3.通过Connection对象获取Statement对象
                        statement = connection.createStatement();
                    } catch (ClassNotFoundException e) {
                        System.out.println("错误提示:数据库驱动加载失败");
                        e.printStackTrace();
                    } catch (SQLException e) {
                        System.out.println("错误提示:数据库操作失败");
                        e.printStackTrace();
                    } finally {
                        if (set != null) {
                            try {
                                set.close();
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                        }
                        if (connection != null) {
                            try {
                                connection.close();
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                        }
                        if (statement != null) {
                            try {
                                statement.close();
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                        }
                    }
            }
        }
    }

    public static void addStudent() throws ClassNotFoundException, SQLException {
        //数据库地址
        String dburl = "jdbc:mysql://localhost:3306/xs?useSSL=false&characterEncoding=utf-8";
        //数据库的用户名和密码
        String user = "root";
        String password = "1234";
        //注册jdbc驱动
        Class.forName("com.mysql.jdbc.Driver");

        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入学生id:");
        int id = scanner.nextInt();
        System.out.println("请输入学生姓名:");
        String name = scanner.next();

        //打开链接--连接mysql
        Connection connection = DriverManager.getConnection(dburl, user, password);
        Statement statement = connection.createStatement();

        String sql = "INSERT INTO stu_info values("+id+",'"+name+"')";
        //执行sql语句
        statement.executeUpdate(sql);

        System.out.println("添加成功");
        connection.close();

    }
    public static void delStudent() throws ClassNotFoundException, SQLException {
        //数据库地址
        String dburl = "jdbc:mysql://localhost:3306/xs?useSSL=false&characterEncoding=utf-8";
        //数据库的用户名和密码
        String user = "root";
        String password = "1234";
        //注册jdbc驱动
        Class.forName("com.mysql.jdbc.Driver");
        System.out.println("连接数据库中……");

        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入要删除学生的id");
        int delId = scanner.nextInt();

        //打开链接
        Connection connection = DriverManager.getConnection(dburl, user, password);
        Statement statement = connection.createStatement();

        String sql = "delete from stu_info where id = " +delId+"";

        statement.executeUpdate(sql);

        System.out.println("删除成功");
        connection.close();

    }
    public static void showAll() throws ClassNotFoundException, SQLException{
        //数据库地址
        String dburl = "jdbc:mysql://localhost:3306/xs?useSSL=false&characterEncoding=utf-8";
        //数据库的用户名和密码
        String user = "root";
        String password = "1234";
        //注册jdbc驱动
        Class.forName("com.mysql.jdbc.Driver");
        //打开链接
        Connection connection = DriverManager.getConnection(dburl, user, password);
        Statement statement = connection.createStatement();
        //定义sql语句
        String sql = "SELECT * FROM xs.stu_info;";

        //statement.executeUpdate(sql);
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            System.out.println("ID:" + id);
            System.out.println("名字:" + name);
            System.out.println("==============");
        }
    }
    public static void quary() throws ClassNotFoundException, SQLException{
        //数据库地址
        String dburl = "jdbc:mysql://localhost:3306/xs?useSSL=false&characterEncoding=utf-8";
        //数据库的用户名和密码
        String user = "root";
        String password = "1234";
        //注册jdbc驱动
        Class.forName("com.mysql.jdbc.Driver");
        //打开链接
        Connection connection = DriverManager.getConnection(dburl, user, password);
        Statement statement = connection.createStatement();

        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入要查询的ID");
        int qid = scanner.nextInt();
        //定义sql语句
        String sql = "SELECT * FROM xs.stu_info where id =" + qid +"";

        //statement.executeUpdate(sql);
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            System.out.println("ID:" + id);
            System.out.println("名字:" + name);
        }
    }
    public static void modify() throws ClassNotFoundException, SQLException{
        //数据库地址
        String dburl = "jdbc:mysql://localhost:3306/xs?useSSL=false&characterEncoding=utf-8";
        //数据库的用户名和密码
        String user = "root";
        String password = "1234";
        //注册jdbc驱动
        Class.forName("com.mysql.jdbc.Driver");
        System.out.println("连接数据库中……");

        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入修改前的学生id:");
        int id1 = scanner.nextInt();

        System.out.println("请输入修改后的学生id:");
        int id2 = scanner.nextInt();
        System.out.println("请输入修改后学生姓名:");
        String name2 = scanner.next();

        //打开链接--连接mysql
        Connection connection = DriverManager.getConnection(dburl, user, password);
        Statement statement = connection.createStatement();

        String sql = "UPDATE stu_info SET id = "+id2+",name = '"+name2+"' WHERE id = "+id1+";";
        //执行sql语句
        statement.executeUpdate(sql);

        System.out.println("修改成功");
        connection.close();
    }
}