devbox
在线问答5
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Java连接数据库（JDBC）实现学生信息管理系统（增删改查）_java写数据库管理系统

作者：在线问答5 | 2024-07-05 13:18:00

赞

踩

java写数据库管理系统

首先从MySQL官网上下载mysql-connector-j-8.0.33.jar包，链接：https://dev.mysql.com/downloads/connector/j/，jar包版本必须和你本身的mysql版本相匹配！

下载图片中我标记的那个，下载压缩包到随便文件夹中，自己能找到即可，然后解压到文件夹中

 打开该文件夹

 打开IDEA ，创建一个lib目录，将jar文件复制粘贴lib中

将lib目录设置为library

 这样数据库就配置好了！

下面开始用代码连接数据库

  1. import java.sql.Connection;
  2. import java.sql.DriverManager;
  3. import java.sql.SQLException;
  4.  
  5.  
  6. public class databaseConnection {
  7.     public static void conn(){
  8.         Connection connection;
  9.         String url = "jdbc:mysql://localhost:3306/bookstore";   //数据库连接的url地址
  10.         String user = "root";   //数据库用户名
  11.         String password = "123456";  //数据库密码
  12.         try {
  13.             //加载数据库驱动
  14.             Class.forName("com.mysql.cj.jdbc.Driver");
  15.             System.out.println("数据库驱动加载成功！");
  16.         }catch (ClassNotFoundException e) {
  17.             e.printStackTrace();
  18.         }
  19.         try{
  20.             //通过DriverManager获取数据库连接
  21.             connection = DriverManager.getConnection(url,user,password);
  22.             System.out.println("数据库连接成功！");
  23.         }catch (SQLException e) {
  24.             e.printStackTrace();
  25.         }
  26.     }
  27.     public static void main(String[] args) {
  28.         databaseConnection.conn();   //静态方法：直接使用类名.方法名调用
  29.     }
  30. }

上面代码中，url为连接数据库时的地址，loaclhost为主机名，3306为端口号，bookstore为数据库名，user为创建数据库的用户名，password为密码！加载数据库驱动和连接数据库是编译会报错，需要try catch把异常环绕。

 这样，数据库就连接成功了！

下面开始书写学生信息管理系统

先创建数据库，然后建表

首先创建student表

  1. CREATE TABLE `student` (
  2.   `学号` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  3.   `姓名` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  4.   `性别` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  5.   `出生日期` date DEFAULT NULL,
  6.   `地区` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  7.   `民族` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '汉',
  8.   `班级编号` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  9.   PRIMARY KEY (`学号`) USING BTREE
  10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

插入相应数据

  1. INSERT INTO `student` VALUES ('2013110101', '张晓勇', '男', '1997-12-11', '山西', '汉', 'AC1301');
  2. INSERT INTO `student` VALUES ('2013110103', '王一敏', '女', '1998-01-01', '河北', '汉', 'AC1301');
  3. INSERT INTO `student` VALUES ('2013110201', '江山', '女', '1996-09-17', '内蒙古', '锡伯', 'AC1302');
  4. INSERT INTO `student` VALUES ('2013110202', '李明', '男', '1996-01-14', '广西', '壮', 'AC1302');
  5. INSERT INTO `student` VALUES ('2013310101', '黄菊', '女', '1995-09-30', '北京', '汉', 'IS1301');
  6. INSERT INTO `student` VALUES ('2013310103', '吴昊', '男', '1995-11-18', '河北', '汉', 'IS1301');
  7. INSERT INTO `student` VALUES ('2014210101', '刘涛', '男', '1997-04-03', '湖南', '侗', 'CS1401');
  8. INSERT INTO `student` VALUES ('2014210102', '郭志坚', '男', '1997-02-21', '上海', '汉', 'CS1401');
  9. INSERT INTO `student` VALUES ('2014310101', '王林', '男', '1996-10-09', '河南', '汉', 'IS1401');
  10. INSERT INTO `student` VALUES ('2014310102', '李怡然', '女', '1996-12-31', '辽宁', '汉', 'IS1401');

然后是score

  1. CREATE TABLE `score` (
  2.   `学号` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  3.   `课程号` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  4.   `成绩` float(5,2) DEFAULT NULL,
  5.   PRIMARY KEY (`学号`,`课程号`) USING BTREE
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

插入数据

  1. INSERT INTO `score` VALUES ('2013110101', '11003', '90.00');
  2. INSERT INTO `score` VALUES ('2013110101', '21001', '86.00');
  3. INSERT INTO `score` VALUES ('2013110103', '11003', '89.00');
  4. INSERT INTO `score` VALUES ('2013110103', '21001', '86.00');
  5. INSERT INTO `score` VALUES ('2013110201', '11003', '78.00');
  6. INSERT INTO `score` VALUES ('2013110201', '21001', '92.00');
  7. INSERT INTO `score` VALUES ('2013110202', '11003', '82.00');
  8. INSERT INTO `score` VALUES ('2013110202', '21001', '85.00');
  9. INSERT INTO `score` VALUES ('2013310101', '21004', '83.00');
  10. INSERT INTO `score` VALUES ('2013310101', '31002', '68.00');
  11. INSERT INTO `score` VALUES ('2013310103', '21004', '80.00');
  12. INSERT INTO `score` VALUES ('2013310103', '31002', '76.00');
  13. INSERT INTO `score` VALUES ('2014210101', '21002', '93.00');
  14. INSERT INTO `score` VALUES ('2014210101', '21004', '89.00');
  15. INSERT INTO `score` VALUES ('2014210102', '21002', '95.00');
  16. INSERT INTO `score` VALUES ('2014210102', '21004', '88.00');
  17. INSERT INTO `score` VALUES ('2014310101', '21001', '79.00');
  18. INSERT INTO `score` VALUES ('2014310101', '21004', '80.00');
  19. INSERT INTO `score` VALUES ('2014310102', '21001', '91.00');
  20. INSERT INTO `score` VALUES ('2014310102', '21004', '87.00');

再是course表

  1. CREATE TABLE `course` (
  2.   `课程号` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  3.   `课程名` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  4.   `学分` int NOT NULL,
  5.   `学时` int NOT NULL,
  6.   `学期` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  7.   `前置课` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  8.   PRIMARY KEY (`课程号`) USING BTREE
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

插入数据

  1. INSERT INTO `course` VALUES ('11003', '管理学', '2', '32', '2', null);
  2. INSERT INTO `course` VALUES ('11005', '会计学', '3', '48', '2', null);
  3. INSERT INTO `course` VALUES ('21001', '计算机基础', '3', '48', '1', null);
  4. INSERT INTO `course` VALUES ('21002', 'OFFICE高级应用', '3', '48', '2', '21001');
  5. INSERT INTO `course` VALUES ('21004', '程序设计', '4', '64', '2', '21001');
  6. INSERT INTO `course` VALUES ('21005', '数据库', '4', '64', '4', '21004');
  7. INSERT INTO `course` VALUES ('21006', '操作系统', '4', '64', '5', '21001');
  8. INSERT INTO `course` VALUES ('31001', '管理信息系统', '3', '48', '3', '21004');
  9. INSERT INTO `course` VALUES ('31002', '信息系统_分析与设计', '2', '32', '4', '31001');
  10. INSERT INTO `course` VALUES ('31005', '项目管理', '3', '48', '5', '31001');

最后是class

  1. CREATE TABLE `class` (
  2.   `班级编号` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  3.   `班级名称` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  4.   `院系` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  5.   `年级` int DEFAULT NULL,
  6.   `人数` int DEFAULT NULL,
  7.   PRIMARY KEY (`班级编号`) USING BTREE
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

插入数据

  1. INSERT INTO `class` VALUES ('AC1301', '会计13-1班', '会计学院', '2013', '35');
  2. INSERT INTO `class` VALUES ('AC1302', '会计13-2班', '会计学院', '2013', '35');
  3. INSERT INTO `class` VALUES ('CS1401', '计算机14-1班', '计算机学院', '2014', '35');
  4. INSERT INTO `class` VALUES ('IS1301', '信息系统13-1班', '信息学院', '2013', null);
  5. INSERT INTO `class` VALUES ('IS1401', '信息系统14-1班', '信息学院', null, '30');

建完表后

开始写Java代码

类比较多，一个一个来

先写主类StudentInformationManageSystems

  1. package StudentInformationManageSystem;
  2.  
  3. import java.util.Scanner;
  4.  
  5. public class StudentInformationManageSystems {
  6.     public static void main(String[] args) {
  7.             System.out.println("-------------欢迎来到学生信息管理系统---------");
  8.             StudentMain.studentmains();
  9.             while (true){
  10.                 Scanner input = new Scanner(System.in);
  11.                 int scan = input.nextInt();
  12.                 switch (scan){
  13.                     case 1:
  14.                         StudentAdd.add();
  15.                         System.out.println("-----------------------");
  16.                         StudentMain.studentmains();
  17.                         break;
  18.                     case 2:
  19.                         StudentQuery.query();
  20.                         System.out.println("-----------------------");
  21.                         StudentMain.studentmains();
  22.                         break;
  23.                     case 3:
  24.                         StudentUpdate.update();
  25.                         System.out.println("-----------------------");
  26.                         StudentMain.studentmains();
  27.                         break;
  28.                     case 4:
  29.                         StudentDelete.delete();
  30.                         System.out.println("-----------------------");
  31.                         StudentMain.studentmains();
  32.                         break;
  33.                     case 5:
  34.                         StudentClass.stuclass();
  35.                         System.out.println("-----------------------");
  36.                         StudentMain.studentmains();
  37.                         break;
  38.                     case 6:
  39.                         StudentCourse.course();
  40.                         System.out.println("-----------------------");
  41.                         StudentMain.studentmains();
  42.                         break;
  43.                     case 7:
  44.                         StudentScore.score();
  45.                         System.out.println("-----------------------");
  46.                         StudentMain.studentmains();
  47.                         break;
  48.                     case 8:
  49.                         System.exit(0);
  50.                     default:
  51.                         System.out.println("无效的输入！");
  52.                         System.exit(0);
  53.                 }
  54.             }
  55.         }
  56.     }
  57.

按顺序来，再写studentmains

  1. package StudentInformationManageSystem;
  2.  
  3. public class StudentMain {
  4.  
  5.     public static void studentmains(){
  6.         System.out.println("1.增加学生信息");
  7.         System.out.println("2.查询学生信息");
  8.         System.out.println("3.修改学生信息");
  9.         System.out.println("4.删除学生信息");
  10.         System.out.println("5.查询学生所在班级");
  11.         System.out.println("6.查询学生所选课程");
  12.         System.out.println("7.查询学生所课程成绩");
  13.         System.out.println("8.退出");
  14.         System.out.println("-------------------");
  15.         System.out.println("请输入你的选择:");
  16.     }
  17. }

然后写StudentAdd

  1. package StudentInformationManageSystem;
  2.  
  3. import java.sql.Connection;
  4. import java.sql.DriverManager;
  5. import java.sql.SQLException;
  6. import java.sql.Statement;
  7. import java.util.Scanner;
  8.  
  9. public class StudentAdd {
  10.     public static void add() {
  11.         Connection connection;
  12.         try {
  13.             Class.forName("com.mysql.cj.jdbc.Driver");
  14.             String url = "jdbc:mysql://localhost:3306/bookstore?useSSL=false";
  15.             String name = "root";
  16.             String password = "123456";
  17.             connection = DriverManager.getConnection(url, name, password);
  18.             Statement stmt = connection.createStatement();
  19.  
  20.             Scanner input = new Scanner(System.in);
  21.             System.out.println("请输入学号：");
  22.             int id = input.nextInt();
  23.             System.out.println("请输入姓名：");
  24.             String studnetname = input.next();
  25.             System.out.println("请输入性别：");
  26.             String gender = input.next();
  27.             System.out.println("请输入出生日期：");
  28.             String brithday = input.next();
  29.             System.out.println("请输入地区：");
  30.             String area = input.next();
  31.             System.out.println("请输入民族：");
  32.             String nation = input.next();
  33.             System.out.println("请输入班级编号：");
  34.             String classId = input.next();
  35.             String sql = "insert into bookstore.student(学号, 姓名, 性别, 出生日期, 地区, 民族, 班级编号) " +
  36.                     "values(" + id + ",'" + studnetname + "','" + gender + "','" + brithday + "','" + area + "','" + nation + "','" + classId + "') ";
  37.             stmt.executeUpdate(sql);
  38.             System.out.println("学生添加成功！");
  39.             stmt.close();
  40.             connection.close();
  41.         } catch (SQLException | ClassNotFoundException exception) {
  42.             throw new RuntimeException(exception);
  43.         }
  44.     }
  45. }

然后是StudentQuery

  1. package StudentInformationManageSystem;
  2.  
  3. import java.sql.*;
  4.  
  5. public class StudentQuery {
  6.     public static void query(){
  7.         Connection connection;
  8.         try {
  9.             //加载驱动
  10.             Class.forName("com.mysql.cj.jdbc.Driver");
  11.             //获取连接
  12.             String url = "jdbc:mysql://localhost:3306/bookstore?useSSL=false";
  13.             String name = "root";
  14.             String password = "123456";
  15.             connection = DriverManager.getConnection(url,name,password);
  16.             Statement stmt = connection.createStatement();
  17.             String sql = "select * from student";
  18.             ResultSet resultSet = stmt.executeQuery(sql);
  19.             while(resultSet.next()){
  20.                 int id = resultSet.getInt("学号");
  21.                 String Studentname = resultSet.getString("姓名");
  22.                 String gender = resultSet.getString("性别");
  23.                 String brithday = resultSet.getString("出生日期");
  24.                 String area = resultSet.getString("地区");
  25.                 String nation = resultSet.getString("民族");
  26.                 String classId = resultSet.getString("班级编号");
  27.                 System.out.println("学号:"+id+"  姓名:"+Studentname+" 性别:"+gender+" 出生日期:"
  28.                         +brithday+" 地区:"+area+" 民族:"+nation+" 班级编号:"+classId);
  29.             }
  30.             stmt.close();
  31.             connection.close();
  32.         } catch (SQLException | ClassNotFoundException ex) {
  33.             throw new RuntimeException(ex);
  34.  
  35.         }
  36.     }
  37. }

再是StudentUpdate

  1. package StudentInformationManageSystem;
  2.  
  3. import java.sql.Connection;
  4. import java.sql.DriverManager;
  5. import java.sql.SQLException;
  6. import java.sql.Statement;
  7. import java.util.Scanner;
  8.  
  9. public class StudentUpdate {
  10.     public static void update(){
  11.         Connection connection;
  12.         try {
  13.             Class.forName("com.mysql.cj.jdbc.Driver");
  14.             String url = "jdbc:mysql://localhost:3306/bookstore?useSSL=false";
  15.             String name = "root";
  16.             String password = "123456";
  17.             connection = DriverManager.getConnection(url, name, password);
  18.             Statement stmt = connection.createStatement();
  19.             Scanner input = new Scanner(System.in);
  20.             System.out.println("请输入要修改学生的学号:");
  21.             int id = input.nextInt();
  22.             System.out.println("请输入要修改的列:");
  23.             String colum = input.next();
  24.             System.out.println("请输入要修改的值:");
  25.             String value = input.next();
  26.             String sql = "UPDATE bookstore.student SET " + colum + " = '" + value + "' WHERE 学号 = " + id;
  27.             stmt.executeUpdate(sql);
  28.             System.out.println("学生修改成功！");
  29.             stmt.close();
  30.             connection.close();
  31.         }catch (SQLException | ClassNotFoundException e) {
  32.             throw new RuntimeException(e);
  33.         }
  34.     }
  35. }

然后是StudentDelete

  1. package StudentInformationManageSystem;
  2.  
  3. import java.sql.Connection;
  4. import java.sql.DriverManager;
  5. import java.sql.SQLException;
  6. import java.sql.Statement;
  7. import java.util.Scanner;
  8.  
  9. public class StudentDelete {
  10.     public static void delete(){
  11.         Connection connection;
  12.         try {
  13.             String url = "jdbc:mysql://localhost:3306/bookstore?useSSL=false";
  14.             String name = "root";
  15.             String password = "123456";
  16.             connection = DriverManager.getConnection(url, name, password);
  17.             Statement stmt = connection.createStatement();
  18.             Scanner input = new Scanner(System.in);
  19.             System.out.println("请输入要删除的学号:");
  20.             int id = input.nextInt();
  21.             String sql = "delete from bookstore.student where 学号 = " + id;
  22.             stmt.executeUpdate(sql);
  23.             System.out.println("学生删除成功！");
  24.             stmt.close();
  25.             connection.close();
  26.         } catch (SQLException exception) {
  27.             throw new RuntimeException(exception);
  28.         }
  29.     }
  30. }

再是StudentClass

  1. package StudentInformationManageSystem;
  2.  
  3. import java.sql.*;
  4. import java.util.Scanner;
  5.  
  6. public class StudentClass {
  7.     public static void stuclass() {
  8.         Connection connection;
  9.         try {
  10.             Class.forName("com.mysql.cj.jdbc.Driver");
  11.             String url = "jdbc:mysql://localhost:3306/bookstore?useSSL=false";
  12.             String name = "root";
  13.             String password = "123456";
  14.             connection = DriverManager.getConnection(url, name, password);
  15.             Statement stmt = connection.createStatement();
  16.             Scanner input = new Scanner(System.in);
  17.             System.out.println("请输入学生姓名：");
  18.             String stuname = input.next();
  19.             String sql = "select * from bookstore.class where 班级编号 = (select 班级编号 from bookstore.student where 姓名 = '"+stuname+"')";
  20.             ResultSet resultSet = stmt.executeQuery(sql);
  21.             System.out.println("班级编号     |     班级名称     |     院系     |     年级     |     人数     |");
  22.             while (resultSet.next()){
  23.                 String classid = resultSet.getString("班级编号");
  24.                 String classname = resultSet.getString("班级名称");
  25.                 String college= resultSet.getString("院系");
  26.                 String grade = resultSet.getString("年级");
  27.                 String popluations = resultSet.getString("人数");
  28.                 System.out.println(classid+"   |   "+classname+"   |" +
  29.                         "   "+college+"   |   "+grade+"   |   "+popluations+"   |   ");
  30.             }
  31.             stmt.close();
  32.             connection.close();
  33.         } catch (SQLException | ClassNotFoundException exception) {
  34.             throw new RuntimeException(exception);
  35.         }
  36.     }
  37. }

然后是StudentCourse

  1. package StudentInformationManageSystem;
  2.  
  3. import java.sql.*;
  4. import java.util.Scanner;
  5.  
  6. public class StudentCourse {
  7.     public static void course() {
  8.         Connection connection;
  9.         try {
  10.             Class.forName("com.mysql.cj.jdbc.Driver");
  11.             String url = "jdbc:mysql://localhost:3306/bookstore?useSSL=false";
  12.             String name = "root";
  13.             String password = "123456";
  14.             connection = DriverManager.getConnection(url, name, password);
  15.             Statement stmt = connection.createStatement();
  16.             Scanner input = new Scanner(System.in);
  17.             System.out.println("请输入学生姓名：");
  18.             String stuname = input.next();
  19.             String sql = "select * from bookstore.course where 课程号 =" +
  20.                     " any (select 课程号 from bookstore.score where 学号 = any (select 学号 from bookstore.student where 姓名 = '"+stuname+"'))";
  21.             ResultSet resultSet = stmt.executeQuery(sql);
  22.             System.out.println("课程号   |   课程名   |   学分   |   学时   |   学期   |");
  23.             while (resultSet.next()){
  24.                 String coureid = resultSet.getString("课程号");
  25.                 String courename = resultSet.getString("课程名");
  26.                 String score = resultSet.getString("学分");
  27.                 String time = resultSet.getString("学时");
  28.                 String term = resultSet.getString("学期");
  29.                 System.out.println(coureid+"   |   "+courename+"   |   "+score+"   |   "+time+"   |   "+term+"   |   ");
  30.             }
  31.             stmt.close();
  32.             connection.close();
  33.         } catch (SQLException | ClassNotFoundException exception) {
  34.             throw new RuntimeException(exception);
  35.         }
  36.     }
  37. }

再是StudentScore

  1. package StudentInformationManageSystem;
  2.  
  3. import java.sql.*;
  4. import java.util.Scanner;
  5.  
  6. public class StudentScore {
  7.     public static void score() {
  8.         Connection connection;
  9.         try {
  10.             Class.forName("com.mysql.cj.jdbc.Driver");
  11.             String url = "jdbc:mysql://localhost:3306/bookstore?useSSL=false";
  12.             String name = "root";
  13.             String password = "123456";
  14.             connection = DriverManager.getConnection(url, name, password);
  15.             Statement stmt = connection.createStatement();
  16.             Scanner input = new Scanner(System.in);
  17.             System.out.println("请输入学生姓名：");
  18.             String stuname = input.next();
  19.             System.out.println("请输入课程名：");
  20.             String coursename = input.next();
  21.             String sql = "select 成绩 from score where 学号 = any (select 学号 from student where 姓名 = '"+stuname+"')and" +
  22.                     " 课程号 = any (select 课程号 from course where 课程名 = '"+coursename+"')";
  23.             ResultSet resultSet = stmt.executeQuery(sql);
  24.             while (resultSet.next()){
  25.                 int score = resultSet.getInt("成绩");
  26.                 System.out.println("----------------");
  27.                 System.out.println("成绩："+score);
  28.                 System.out.println("----------------");
  29.             }
  30.         } catch (SQLException | ClassNotFoundException exception) {
  31.                 throw new RuntimeException(exception);
  32.         }
  33.     }
  34. }

这样整个代码就完成了，最后呈上运行结果

 

 截图太大了，这样我就不一个一个呈现了，大家做出来以后自行操作吧，有什么问题可以在评论区询问，我看到都会一一回复，最后祝大家敲代码愉快！

ps：文章有什么错误可以帮我纠正，谢谢大家！

声明：本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号