教务管理系统连接数据库（Java连接Mysql）

 运用switch-case,实现基本的学生数据库功能：

1.建立连接需要mysql-connector-java-连接包

 

2.这里的sql语句都应该使用preparedstament，preparedstament是预编译的，对于批量的处理可以提高效率。

3.statement，适用于只执行一次性存储的时候，一次性存储时，preparedstatement会消耗更多的资源

4.statement和preparedstatement的区别在于statement每执行一次，相关数据库都要执行SQL语句的编译处理，preparedstatement是采用预编译的方法，支持批量处理。

package 教务管理系统JDBC;

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

import static java.lang.System.exit;
import static java.time.Clock.system;

public class Test01 {
    public static void main(String[] args) {
        ResultSet resultSet=null;
        Connection connection=null;
        Scanner scanner=null;
        PreparedStatement preparedStatement=null;
        PreparedStatement preparedStatement2=null;
        try {
            //1.注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接
            String url = "jdbc:mysql://127.0.0.1:3306/educationmanage";
            scanner=new Scanner(System.in);
            System.out.println("请输入用户名：");
            String user=scanner.next();
            System.out.println("请输入密码");
            String password=scanner.next();
            connection=  DriverManager.getConnection(url,user,password);
            System.out.println("-------------连接成功");
           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("-------------退出系统:           7");
               System.out.println("-------------请输入功能对应数字");
               int n=scanner.nextInt();
               //3.创建执行sql语句的对象
               String sql="";
               String sql2="";
               switch (n) {
                   case 1:
                       sql = "select * from student";
                       break;
                   case 2:
                       sql = "select * from student where sname=?";
                       break;
                   case 3:
                       sql ="SELECT kcname,tname,skcscore,kccredit from " +
                               "choise,student,course,teacher WHERE \n" +
                               "student.sid=choise.ssid AND choise.skcid=course.kcid AND " +
                               "course.kcteacher=teacher.tid AND student.sname=?";
                       break;
                   case 4:
                       sql="SELECT class.* FROM class,student WHERE" +
                           " class.classname=student.sclass AND student.sname=?";
                        break;
                   case 5:
                       sql="SELECT kcid,kcname,tname,kccredit FROM course,teacher " +
                               "WHERE course.kcteacher=teacher.tid";
                       break;
                   case 6:
                       sql=sql="SELECT kcid,kcname,tname,kccredit FROM course,teacher " +
                               "WHERE course.kcteacher=teacher.tid";
                       sql2="INSERT into choise VALUES(?,?,0)";
                       break;

                   case 7:
                       System.out.println("谢谢使用，再见");
                       exit(0);
               }
               //4.执行sql语句
               preparedStatement=connection.prepareStatement(sql);
               switch(n){
                   case 1:resultSet=preparedStatement.executeQuery();
                       while (resultSet.next()){
                           String sid=resultSet.getString("sid");
                           String sname=resultSet.getString("sname");
                           String ssex=resultSet.getString("ssex");
                           String sage=resultSet.getString("sage");
                           String sclass=resultSet.getString("sclass");
                           String smajor=resultSet.getString("smajor");
                           String sscore=resultSet.getString("sscore");
                           System.out.println("学号："+sid+"\t"+"姓名："+sname+"\t"+"性别："+ssex+"\t"+"年龄："+sage+"\t"
                                   +"班级："+sclass+"\t"+"专业："+smajor+"\t"+"所得学分："+sscore);
                       }
                       break;
                   case 2: System.out.println("请输入要查询的学生名");
                       String name=scanner.next();
                       preparedStatement.setObject(1,name);
                       resultSet=preparedStatement.executeQuery();
                       while (resultSet.next()){
                           String sid=resultSet.getString("sid");
                           String sname=resultSet.getString("sname");
                           String ssex=resultSet.getString("ssex");
                           String sage=resultSet.getString("sage");
                           String sclass=resultSet.getString("sclass");
                           String smajor=resultSet.getString("smajor");
                           String sscore=resultSet.getString("sscore");
                           System.out.println("学号："+sid+"\t"+"姓名："+sname+"\t"+"性别："+ssex+"\t"+"年龄"+sage+"\t"
                                   +"班级："+sclass+"\t"+"专业："+smajor+"\t"+"所得学分："+sscore);
                       }
                       break;
                   case 3:System.out.println("请输入要查询的学生名");
                       String name2=scanner.next();
                       preparedStatement.setObject(1,name2);
                       resultSet=preparedStatement.executeQuery();
                   while (resultSet.next()){
                       String kcname=resultSet.getString("kcname");
                       String tname=resultSet.getString("tname");
                       String skcscore=resultSet.getString("skcscore");
                       String kccredit=resultSet.getString("kccredit");
                       System.out.println("课程名"+kcname+"\t"+"教师名："+tname+"\t"+"取得分数"+skcscore+"\t"+"课程学分"+kccredit);
                   }
                      break;
                   case 4:System.out.println("请输入要查询的学生名");
                       String name3=scanner.next();
                       preparedStatement.setObject(1,name3);
                       resultSet=preparedStatement.executeQuery();
                       while(resultSet.next()){
                           String classid=resultSet.getString("classid");
                           String classname=resultSet.getString("classname");
                           String classnumber=resultSet.getString("classnumber");
                           String classinstructor=resultSet.getString("classinstructor");
                           System.out.println("班级id："+classid+"\t"+"班级名："+classname+"\t"+"班级人数"+classnumber+
                                   "\t"+"辅导员："+classinstructor);
                       }
                       break;
                   case 5:resultSet=preparedStatement.executeQuery();
                        while (resultSet.next()){
                            String kcid=resultSet.getString("kcid");
                            String kcname=resultSet.getString("kcname");
                            String tname=resultSet.getString("tname");
                            String kccredit=resultSet.getString("kccredit");
                            System.out.println("课程号："+kcid+"\t"+"课程名："+kcname+"\t"+"课程教师："+tname+"\t" +
                                    "课程学分："+kccredit);
                        }
                        break;
                   case 6:resultSet=preparedStatement.executeQuery();
                       while (resultSet.next()){
                           String kcid=resultSet.getString("kcid");
                           String kcname=resultSet.getString("kcname");
                           String tname=resultSet.getString("tname");
                           String kccredit=resultSet.getString("kccredit");
                           System.out.println("课程号："+kcid+"\t"+"课程名："+kcname+"\t"+"课程教师："+tname+"\t" +
                                   "课程学分："+kccredit);
                       }
                       preparedStatement2=connection.prepareStatement(sql2);
                       System.out.println("请输入你的学号：");
                       String xuehao=scanner.next();
                       preparedStatement2.setObject(1,xuehao);
                       System.out.println("请输入你要选择的课程号：");
                       String kchao=scanner.next();
                       preparedStatement2.setObject(2,kchao);
                       preparedStatement2.executeUpdate();
                       System.out.println("选课成功！");
                       break;
               }
           }

        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            System.out.println("用户名或密码错误");
            throw new RuntimeException(e);
        } finally {
            try {
                resultSet.close();
                preparedStatement.close();
                connection.close();
                scanner.close();
                preparedStatement2.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

 运行结果如下