Java mysql学生管理系统(简易)源

学校要求搞了一个小项目、实习生写的代码还有很多不足，但是基本功能都实现

懂得都懂，还缺少优化！各位可以自己加功能

(想要完整代码直接拖到下面复制)

1.第一步搭建mysql

首先我是采用了小皮面板8.0来启动mysql(下载)

mysql版本采用的是5.7.26、直接无脑点击开启mysql

2.打开项目目录、在scr同目录创建一个 libs文件夹、并且（下载密码6666）两个mysql.jar的包

这步是导入依赖

3.打开idea打开libs包、右键两个.jar的包、选择add to project library

 

 硬件就导入完毕了！

2.实现基础代码

2-1.首先要实现一些基础功能(这里我创建了一个StudentManager.java）

2-2菜单代码（ps:当flag=false的时候将跳出死循环）

import java.util.Scanner;

public class studentManager {
    //主类型
    public static void main(String[] args){
        Scanner sc = new Scanner(System.in);
        boolean flag = true;

        while (flag){
            //执行菜单
            displaymenu();
            /*用户输入的值*/
            String choice = sc.next();
            switch (choice){
                case "1":
                    System.out.println("添加学生");
                    break;
                case "2":
                    System.out.println("删除学生");
                    break;
                case "3":
                    System.out.println("修改学生");
                    break;
                case "4":
                    System.out.println("查看学生");
                    break;
                case "5":
                    System.out.println("退出");
                    flag = false;
                    break;
                default:
                    System.out.println("输入的值有误");
                    break;
            }

        }

    }
    //菜单
    private static void displaymenu() {
        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("请输入你的选择");
    }
}

写完的代码的预览图

这样就完成了第一步

 3.嵌入mysql和创建数据表

3.1创建mysql的表、注意id一定要选择自增

 3.2mysql连接方法、这个一定要先放进去，增删改查都需要用到他

private static Connection mysqlConnection() throws SQLException {
        /*格式：地址值:端口 数据库名字 ssl验证 时区*/
        String url = "jdbc:mysql://localhost:3306/heima?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";

        /*写入数据库账号密码*/
        Connection conn = DriverManager.getConnection(url, "root", "123456");

        /*返回*/
        return conn;
    }

3.3查询学生信息方法

/*查询学生数据*/
    private static void qureystudent() throws SQLException {
        mysqlQuery();
    }
//判断数据库是否有值、返回boolean类型、丢入一个sql语句
    public static boolean selectcheck(String sql) throws SQLException {
        //假设不存在
        boolean flag = false;
        //连接数据库，创建方法
        Connection conn = mysqlConnection();
        Statement  smt = null;
        smt = conn.createStatement();

        /*执行sql语句，获取boolean值*/
        ResultSet rs = smt.executeQuery(sql);
        if (rs.next()){
            flag = true;
            return flag;
        }
        return flag;
    }
//数据表查询
    public static void mysqlQuery() throws SQLException{
        Connection conn = mysqlConnection();
        Statement  smt = null;
        smt = conn.createStatement();
        //根据id进行降序
        String sql = "SELECT * FROM itheima order by id desc";
        boolean flag = selectcheck(sql);
        if (!flag){
            System.out.println("暂未有数据，请先添加");
            return;
        }

        ResultSet rs = smt.executeQuery(sql);
        //循环打印
        while(rs.next()){

            // 通过字段检索
            int id  = rs.getInt("id");
            String sid  = rs.getString("sid");
            String name = rs.getString("name");
            int age = rs.getInt("age");
            String brithday = rs.getString("brithday");

            // 输出数据
            System.out.print(" 编号:" + id);
            System.out.print(" 学号: " + sid);
            System.out.print(" 名字: " + name);
            System.out.print(" 年龄: " + age);
            System.out.print(" 生日: " + brithday);
            System.out.print("\n");

        }
        // 完成后关闭
        rs.close();
        smt.close();

    }

3.4新增学生数据方法

/*添加学生数据*/
    private static void addstudent() throws SQLException{
        Scanner scanner = new Scanner(System.in);

        System.out.println("请输入学生的学号");
        String sid = scanner.next();

        System.out.println("请输入学生的名字");
        String name = scanner.next();

        System.out.println("请输入学生的年龄");
        int age = scanner.nextInt();

        System.out.println("请输入学生的生日");
        String birthday = scanner.next();

        mysqlInsert(sid,name,age,birthday);

    }
//添加判断是否存在学号、传入一个student的学号
    public static boolean mysqlcheck(String student) throws SQLException {
        //假设不存在
        boolean flag = false;
        /*连接数据库*/
        Connection conn = mysqlConnection();
        //Statement执行sql语句，先设置为空
        Statement  smt = null;
        smt = conn.createStatement();

        /*mysql语句*/
        String sql = "SELECT * FROM itheima";
        /*将返回的值传回rs*/
        ResultSet rs = smt.executeQuery(sql);

        /*返回值是0或1，查看sid是否重复*/
        while(rs.next()){
            String sid  = rs.getString("sid");
            if (student.equals(sid)) {
                return true;
            }
        }

        return flag;
    }
//数据表添加、接收要添加的数据
    public static void mysqlInsert(String sid, String name, int age, String brithday) throws SQLException {
        //连接数据库
        Connection conn = mysqlConnection();
        //PreparedStatement操作数据库
        PreparedStatement smt = null;

        /*判断学号是否已经存在*/
        boolean flag = mysqlcheck(sid);
        if (flag){
            System.out.println("学号已存在，请换一个学号");
            return;
        }

        /*如果不存在继续往下执行*/
        String sql = "insert into itheima(sid,name,age,brithday) values(?,?,?,?)";
        smt = conn.prepareStatement(sql);
        smt.setString(1, sid);
        smt.setString(2, name);
        smt.setInt(3, age);
        smt.setString(4, brithday);
        int result =smt.executeUpdate();// 返回值0或者1

        /*人性化反馈*/
        if (result==1){
            System.out.println("添加学生数据成功");
        }else{
            System.out.println("添加学生数据失败");
        }
        //关闭资源，释放内存
        smt.close();
    }

3.5删除学生信息方法

/*删除学生数据*/
    private static void deletestudent() throws SQLException {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入你要删除的id");
        int deletsid = sc.nextInt();

        mysqldelete(deletsid);

    }
//删除功能、丢入要删除的id
    public static void mysqldelete(int id) throws SQLException{
        //连接数据库
        Connection conn = mysqlConnection();
        PreparedStatement smt = null;

        //写入要删除的id
        String sql = "DELETE FROM itheima where id=?";
        smt = conn.prepareStatement(sql);
        smt.setInt(1, id);
        int result =smt.executeUpdate();

        if (result==0){
            System.out.println("删除失败，不存在的编号: "+id);
            return;
        }else{
            System.out.println("删除成功编号: "+id);
        }
    }

3.6修改学生信息的方法

/*修改学生数据*/
    public static void updateStudent() throws SQLException {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入要修改的编号:");
        int id = sc.nextInt();

        System.out.println("请输⼊新的学⽣学号:");
        String sid = sc.next();

        System.out.println("请输⼊新的学⽣姓名:");
        String name = sc.next();

        System.out.println("请输⼊新的学⽣年龄:");
        int age = sc.nextInt();

        System.out.println("请输⼊新的学⽣⽣⽇:");
        String birthday = sc.next();

        mysqlupdate(id,sid,name,age,birthday);
    }
//添加判断是否存在学号、传入一个student的学号
    public static boolean mysqlcheck(String student) throws SQLException {
        //假设不存在
        boolean flag = false;
        /*连接数据库*/
        Connection conn = mysqlConnection();
        //Statement执行sql语句，先设置为空
        Statement  smt = null;
        smt = conn.createStatement();

        /*mysql语句*/
        String sql = "SELECT * FROM itheima";
        /*将返回的值传回rs*/
        ResultSet rs = smt.executeQuery(sql);

        /*返回值是0或1，查看sid是否重复*/
        while(rs.next()){
            String sid  = rs.getString("sid");
            if (student.equals(sid)) {
                return true;
            }
        }

        return flag;
    }
    //修改功能
    public static void mysqlupdate(int id,String sid,String name,int age,String brithday) throws SQLException{
        //连接数据库
        Connection conn = mysqlConnection();
        PreparedStatement smt = null;

        /*调用mysqlcheck方法判断sid是否重复*/
        boolean flag = mysqlcheck(sid);
        if (flag){
            System.out.println("学号已存在，请换一个学号");
            return;
        }

        /*将传入的值放进语句里面*/
        String sql = "update itheima set sid=?,name=?,age=?,brithday=? where id=?";
        smt = conn.prepareStatement(sql);
        smt.setString(1, sid);
        smt.setString(2, name);
        smt.setInt(3,age);
        smt.setString(4,brithday);
        smt.setInt(5,id);

        /*人性化提示*/
        int result =smt.executeUpdate();// 返回值0或者1
        if(result>0) {
            System.out.println("修改成功");
        }else {
            System.out.println("修改失败");
        }

    }

4.完完整整的代码，可以直接用(类名需要改一下)

import java.sql.*;
import java.util.Scanner;

public class studentManager {
    //主类型
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Scanner sc = new Scanner(System.in);
        boolean flag = true;

        while (flag){
            //执行菜单
            displaymenu();
            /*用户输入的值*/
            String choice = sc.next();
            switch (choice){
                case "1":
                    System.out.println("添加学生");
                    /*添加数据*/
                    addstudent();
                    break;
                case "2":
                    System.out.println("删除学生");
                    deletestudent();
                    break;
                case "3":
                    System.out.println("修改学生");
                    updateStudent();
                    break;
                case "4":
                    System.out.println("查看学生");
                    qureystudent();
                    break;
                case "5":
                    System.out.println("退出");
                    flag = false;
                    break;
                default:
                    System.out.println("输入的值有误");
                    break;
            }

        }

    }
    //菜单
    private static void displaymenu() {
        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("请输入你的选择");
    }
    /*修改学生数据*/
    public static void updateStudent() throws SQLException {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入要修改的编号:");
        int id = sc.nextInt();

        System.out.println("请输⼊新的学⽣学号:");
        String sid = sc.next();

        System.out.println("请输⼊新的学⽣姓名:");
        String name = sc.next();

        System.out.println("请输⼊新的学⽣年龄:");
        int age = sc.nextInt();

        System.out.println("请输⼊新的学⽣⽣⽇:");
        String birthday = sc.next();

        mysqlupdate(id,sid,name,age,birthday);
    }
    /*删除学生数据*/
    private static void deletestudent() throws SQLException {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入你要删除的id");
        int deletsid = sc.nextInt();

        mysqldelete(deletsid);

    }
    /*查询学生数据*/
    private static void qureystudent() throws SQLException {
        mysqlQuery();
    }
    /*添加学生数据*/
    private static void addstudent() throws SQLException{
        Scanner scanner = new Scanner(System.in);

        System.out.println("请输入学生的学号");
        String sid = scanner.next();

        System.out.println("请输入学生的名字");
        String name = scanner.next();

        System.out.println("请输入学生的年龄");
        int age = scanner.nextInt();

        System.out.println("请输入学生的生日");
        String birthday = scanner.next();

        mysqlInsert(sid,name,age,birthday);

    }


    /*查询用Statement和executeQuery*/
    /*修改用PreparedStatement和executeUpdate*/


    //数据库连接地址  标注成员函数可能抛出的sql异常 Connection数据库连接资源、方便其他方法调用
    private static Connection mysqlConnection() throws SQLException {
        /*格式：地址值:端口 数据库名字 ssl验证 时区*/
        String url = "jdbc:mysql://localhost:3306/heima?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";

        /*写入数据库账号密码*/
        Connection conn = DriverManager.getConnection(url, "root", "123456");

        /*返回*/
        return conn;
    }

    //数据表添加、接收要添加的数据
    public static void mysqlInsert(String sid, String name, int age, String brithday) throws SQLException {
        //连接数据库
        Connection conn = mysqlConnection();
        //PreparedStatement操作数据库
        PreparedStatement smt = null;

        /*判断学号是否已经存在*/
        boolean flag = mysqlcheck(sid);
        if (flag){
            System.out.println("学号已存在，请换一个学号");
            return;
        }

        /*如果不存在继续往下执行*/
        String sql = "insert into itheima(sid,name,age,brithday) values(?,?,?,?)";
        smt = conn.prepareStatement(sql);
        smt.setString(1, sid);
        smt.setString(2, name);
        smt.setInt(3, age);
        smt.setString(4, brithday);
        int result =smt.executeUpdate();// 返回值0或者1

        /*人性化反馈*/
        if (result==1){
            System.out.println("添加学生数据成功");
        }else{
            System.out.println("添加学生数据失败");
        }
        //关闭资源，释放内存
        smt.close();
    }

    //添加判断是否存在学号、传入一个student的学号
    public static boolean mysqlcheck(String student) throws SQLException {
        //假设不存在
        boolean flag = false;
        /*连接数据库*/
        Connection conn = mysqlConnection();
        //Statement执行sql语句，先设置为空
        Statement  smt = null;
        smt = conn.createStatement();

        /*mysql语句*/
        String sql = "SELECT * FROM itheima";
        /*将返回的值传回rs*/
        ResultSet rs = smt.executeQuery(sql);

        /*返回值是0或1，查看sid是否重复*/
        while(rs.next()){
            String sid  = rs.getString("sid");
            if (student.equals(sid)) {
                return true;
            }
        }

        return flag;
    }

    //数据表查询
    public static void mysqlQuery() throws SQLException{
        Connection conn = mysqlConnection();
        Statement  smt = null;
        smt = conn.createStatement();
        //根据id进行降序
        String sql = "SELECT * FROM itheima order by id desc";
        boolean flag = selectcheck(sql);
        if (!flag){
            System.out.println("暂未有数据，请先添加");
            return;
        }

        ResultSet rs = smt.executeQuery(sql);
        //循环打印
        while(rs.next()){

            // 通过字段检索
            int id  = rs.getInt("id");
            String sid  = rs.getString("sid");
            String name = rs.getString("name");
            int age = rs.getInt("age");
            String brithday = rs.getString("brithday");

            // 输出数据
            System.out.print(" 编号:" + id);
            System.out.print(" 学号: " + sid);
            System.out.print(" 名字: " + name);
            System.out.print(" 年龄: " + age);
            System.out.print(" 生日: " + brithday);
            System.out.print("\n");

        }
        // 完成后关闭
        rs.close();
        smt.close();

    }

    //判断数据库是否有值、返回boolean类型、丢入一个sql语句
    public static boolean selectcheck(String sql) throws SQLException {
        //假设不存在
        boolean flag = false;
        //连接数据库，创建方法
        Connection conn = mysqlConnection();
        Statement  smt = null;
        smt = conn.createStatement();

        /*执行sql语句，获取boolean值*/
        ResultSet rs = smt.executeQuery(sql);
        if (rs.next()){
            flag = true;
            return flag;
        }
        return flag;
    }

    //删除功能、丢入要删除的id
    public static void mysqldelete(int id) throws SQLException{
        //连接数据库
        Connection conn = mysqlConnection();
        PreparedStatement smt = null;

        //写入要删除的id
        String sql = "DELETE FROM itheima where id=?";
        smt = conn.prepareStatement(sql);
        smt.setInt(1, id);
        int result =smt.executeUpdate();

        if (result==0){
            System.out.println("删除失败，不存在的编号: "+id);
            return;
        }else{
            System.out.println("删除成功编号: "+id);
        }
    }

    //修改功能
    public static void mysqlupdate(int id,String sid,String name,int age,String brithday) throws SQLException{
        //连接数据库
        Connection conn = mysqlConnection();
        PreparedStatement smt = null;

        /*调用mysqlcheck方法判断sid是否重复*/
        boolean flag = mysqlcheck(sid);
        if (flag){
            System.out.println("学号已存在，请换一个学号");
            return;
        }

        /*将传入的值放进语句里面*/
        String sql = "update itheima set sid=?,name=?,age=?,brithday=? where id=?";
        smt = conn.prepareStatement(sql);
        smt.setString(1, sid);
        smt.setString(2, name);
        smt.setInt(3,age);
        smt.setString(4,brithday);
        smt.setInt(5,id);

        /*人性化提示*/
        int result =smt.executeUpdate();// 返回值0或者1
        if(result>0) {
            System.out.println("修改成功");
        }else {
            System.out.println("修改失败");
        }

    }
    
}