通过JAVA（jdbc）动态简单维护MYSQL数据库中的表（学生管理系统）

最新推荐文章于 2023-02-25 22:34:06 发布

仍丶旧

最新推荐文章于 2023-02-25 22:34:06 发布

阅读量542

点赞数 1

文章标签：数据库 java sql

本文链接：https://blog.csdn.net/m0_68497327/article/details/125884556

版权

通过JAVA（jdbc）动态简单维护MYSQL数据库中的表（学生管理系统）

一：在MYSQL中创建一张表

建表过程：

CREATE TABLE class(

class_id CHAR(30) PRIMARY KEY,

class_name VARCHAR(30) NOT NULL,

class_major VARCHAR(30) NOT NULL,

student_number INT

);

CREATE TABLE student(

sid CHAR(30) NOT NULL,

sname VARCHAR(30)NOT NULL,

ssex VARCHAR(30) NOT NULL,

sage INT,

sclass_id CHAR(30),

PRIMARY KEY(sid,sclass_id),

foreign key(sclass_id) references class(class_id)

);

CREATE TABLE course(

course_id CHAR(30) PRIMARY KEY,

course_name VARCHAR(30) NOT NULL,

course_hour INT,

course_credit INT

);

create table sc(

sid CHAR(30),

course_id CHAR(30) ,

score Double,

foreign key(sid) references student(sid),

foreign key(course_id) references course(course_id),

primary key(sid,course_id)

);

CREATE TABLE teacher(

teachar_id CHAR(30) PRIMARY KEY,

teachar_title VARCHAR(30) NOT NULL,

teachar_name VARCHAR(30) NOT NULL,

teachar_age INT,

teachar_sex CHAR(30)

);

CREATE TABLE tc(

teachar_id CHAR(30) NOT NULL,

course_id CHAR(30) NOT NULL,

class_time date NOT NULL,

primary key(teachar_id,course_id),

foreign key(teachar_id) references teacher(teachar_id),

foreign key(course_id) references course(course_id)

);

二：通过jdbc访问数据库详细步骤

1将jdbc驱动程序加载到内存

Class.forName("驱动全限定名");

2 用驱动程序管理器从驱动程序中获取一个连接对象

Connection conn= DriverManager.getConnection("root,user,password");

3编写一个字符串的SQL语句

String sql = "MYSQL语句";

4通过conn连接对象，来创建一个ps的Statement的语句对象，把sql赋给ps

PreparedStatement ps = conn.prepareStatement(sql);

5使用结果集中的数据

插入：ps.setString(1,id);

获取：ps.getString(1,id);

6执行sql语句

ps.execute();执行返回多个结果集

ps.executeUpdate();返回整数，表示受影响的行数，比如（create,drop,Insert,update,delete)

ps.executeQuery();产生单个结果集，比如（Select)

7关闭所有JDBC对象

conn.close();
ps.close();

三：对数据库中的表进行增删查改

package studentmanagesystem;

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

/**
 * @Author: 仍·旧
 * @Date 2022/5/26  20:04
 **/
/*
* class_id班级号
* class_name班级名
* class_major专业
* student_number学生人数
 */
public class studentcs {
    private final static String classname = "com.mysql.cj.jdbc.Driver";
    private final static String url = "jdbc:mysql://localhost:3306/text8";
    private final static String user = "root";
    private final static String password = "*******";
    public final static Scanner sc = new Scanner(System.in);

    public static void main(String[] args) {

              while (true){
                  System.out.println("1:增加班级\n2:增加学生信息\n3:录入成绩\n4:查询学生信息\n5:修改学生信息");
                  int a=sc.nextInt();
                  if(a==1){
                      addclass();
                      continue;
                  }else if(a==2){
                      addstudent();
                      continue;
                  }else if(a==3){
                      addgrade();
                      continue;
                  }else if(a==4){
                      findstudent();
                      continue;
                  }else  if(a==5){
                      updata();
                      continue;
                  }
              }
    }
    public static void addclass(){
        try {
            Class.forName(classname);
            Connection conn = DriverManager.getConnection(url, user, password);
            while (true) {
                System.out.println("请输入班级号，班级名，专业，学生人数");
                System.out.println("输入1进行插入，输入2退出");
                int a = sc.nextInt();
                if (a == 1) {
                    System.out.println("请输入班级号");
                    String id = sc.next();
                    System.out.println("请输入班级名");
                    String name = sc.next();
                    System.out.println("请输入专业");
                    String major = sc.next();
                    System.out.println("请输入学生人数");
                    String number = sc.next();
                    String sql = "insert into class values(?,?,?,?)";
                    PreparedStatement ps = conn.prepareStatement(sql);
                    ps.setString(1,id);
                    ps.setString(2,name);
                    ps.setString(3,major);
                    ps.setString(4,number);
                    ps.executeUpdate();
                    System.out.println("插入完毕");
                    ps.close();
                    continue;
                }else if(a==2){
                    break;
                }

            }
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


    }
    //sid CHAR(30) NOT NULL,
    //sname VARCHAR(30)NOT NULL,
    //ssex VARCHAR(30) NOT NULL,
    //sage INT,
    //sclass_id CHAR(30),
    //PRIMARY KEY(sid,sclass_id),
    public static void addstudent(){
        try {
            Class.forName(classname);
            Connection conn=DriverManager.getConnection(url,user,password);
            while (true) {
                System.out.println("请输入学生学号，姓名，性别，年龄，班级号");
                System.out.println("输入1进行插入，输入2退出");
                int a = sc.nextInt();
                if(a==1) {
                    System.out.println("学生学号");
                    String id=sc.next();
                    System.out.println("姓名");
                    String name=sc.next();
                    System.out.println("性别");
                    String sex=sc.next();
                    System.out.println("年龄");
                    int age=sc.nextInt();
                    System.out.println("班级号");
                    String cid=sc.next();
                    String sql = "insert into student values (?,?,?,?,?)";
                    PreparedStatement ps = conn.prepareStatement(sql);
                    ps.setString(1,id);
                    ps.setString(2,name);
                    ps.setString(3,sex);
                    ps.setInt(4,age);
                    ps.setString(5,cid);
                    ps.executeUpdate();
                    System.out.println("插入完毕！");
                    ps.close();
                    continue;
                }else if(a==2) {
                    break;
                }
            }
            conn.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
    //sid  CHAR(30),
    //course_id CHAR(30) ,
    //score Double,
    public static void addgrade(){
        try {
            Class.forName(classname);
            Connection conn=DriverManager.getConnection(url,user,password);
            while (true) {
                System.out.println("请输入学生学号，课程号，成绩");
                System.out.println("输入1进行插入，输入2退出");
                int a = sc.nextInt();
                if(a==1) {
                    System.out.println("学生学号");
                    String sid=sc.next();
                    System.out.println("课程号");
                    String cid=sc.next();
                    System.out.println("成绩");
                    Double score=sc.nextDouble();
                    String sql = "insert into sc values (?,?,?)";
                    PreparedStatement ps = conn.prepareStatement(sql);
                    ps.setString(1,sid);
                    ps.setString(2,cid);
                    ps.setDouble(3,score);
                    ps.executeUpdate();
                    System.out.println("插入完毕！");
                    ps.close();
                }else if(a==2){
                    break;
                }
            }
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
     public static void findstudent(){
         try {
             Class.forName(classname);
             Connection conn=DriverManager.getConnection(url,user,password);
             System.out.println("请输入你要查询的学生学号");
             String id=sc.next();
             String sql="select a.sid,a.sname,d.class_name,d.class_major,c.course_name,b.score,c.course_hour FROM student AS a INNER JOIN sc AS b ON a.sid=b.sid INNER JOIN course AS c ON b.course_id=c.course_id INNER JOIN class AS d ON d.class_id=a.sclass_id where a.sid=?";
             //输入ps
             PreparedStatement ps=conn.prepareStatement(sql);
             //输出rs

             ps.setString(1,id);
             ps.executeQuery();
             ResultSet rs=ps.executeQuery();

             while (rs.next()){
                 System.out.println("学号："+rs.getString("sid")+"\t"+"姓名："+
                         rs.getNString("sname")+"\t" +
                         "专业："+rs.getString("class_major")+"\t" +
                         "班级："+rs.getString("class_name")+"\t" +
                         "课程名："+rs.getString("course_name")+"\t" +
                         "课程成绩："+rs.getDouble("score")+"\t" +
                         "课程课时："+rs.getInt("course_hour"));
             }
             System.out.println("查询完毕！");
             ps.close();
             conn.close();
         } catch (ClassNotFoundException e) {
             e.printStackTrace();
         } catch (SQLException throwables) {
             throwables.printStackTrace();
         }
     }
     public static void updata(){
         try {
             Class.forName(classname);
             Connection conn=DriverManager.getConnection(url,user,password);
             while (true) {
                 System.out.println("1:修改学号\n2:修改姓名\n3:修改性别\n4:修改年龄\n5:修改班级号\n6删除改学生信息");
                 int a = sc.nextInt();
                 if (a == 1) {
                     String sql = "update student set sid=? where sid=?";
                     PreparedStatement ps=conn.prepareStatement(sql);
                     System.out.println("请输入要修改学生的学号");
                     String sid=sc.next();
                     System.out.println("请输入新的学号");
                     String sid1=sc.next();
                     ps.setString(2,sid);
                     ps.setString(1,sid1);
                     ps.executeUpdate();
                     System.out.println("学号修改完毕");
                     ps.close();
                     conn.close();
                 } else if (a == 2) {
                     String sql = "updata student set sname=? where ssid=?";
                 } else if (a == 3) {
                     String sql = "updata student set ssex=? where ssid=?";
                 } else if (a == 4) {
                     String sql = "updata student set sage=? where ssid=?";
                 } else if (a == 5) {
                     String sql = "updata student set sclass_id=? where ssid=?";
                 } else if (a == 6) {
                     String sql = "delete from student where sid=?";
                 }
             }
         } catch (ClassNotFoundException e) {
             e.printStackTrace();
         } catch (SQLException throwables) {
             throwables.printStackTrace();
         }
     }
}

运行结果：