Java对数据库的操作实践(一) 学生管理系统_学生管理系统第一部分案例描述案例目的学习使用java+jdbc对数据库的数据-优快云博客

此文章为博主原创文章，未得博主允许不得随意转载，如需转载请联系博主

本文链接：https://blog.youkuaiyun.com/weixin_46067095/article/details/117191475

本文主要是对数据库方面进行案例复习，对数据库不是很理解的同学可以看看，最好自己试着敲一遍，一定会有帮助的。

数据库版（一）

为了复习和巩固前面所学习的知识，我做了这个练习，遇到了问题，加深了自己的一些理解。
本文适用于Java初学者，也欢迎大佬指正其中所包含的不足，谢谢！
如有疑问，请及时提出，或者私信我，
如有意向，也可私信加微信共勉！
这只是第二版，没有使用数据库连接池，对大家熟悉数据操作很有帮助，另外，上一版为数组版，大家可以进入我的主页进行浏览，觉得还行，请点个赞再走趴。
后续会更新数据库连接池版，敬请期待！

需求：设置一个学生管理系统

1.添加学生信息
2.显示学生信息
3.删除学生信息
4.修改学生信息
5.查看学生信息
6.排序
7.退出

思路：

创建相关数据库和student表。
创建学生类，导入连接数据库。
编写sql语句，实现相关功能。

问题与感想

由于方法中的参数stmt传递过来直接用，并没有为其赋值，导致空指针异常。res = stmt.executeQuery(sql);
执行查询时使用executeQuery(String sql)方法，而其他的增删改查使用executeUpdate()。excute()方法都可以执行，但是返回值为布尔值。
在实现相关操作，注意将Statement，Connection，ResultSet对象传入，以便后面调用方法关闭。
在退出时，需要关闭资源。

Manage.java

package cn.xlb.db.dao;

import cn.xlb.db.domain.Student;
import cn.xlb.db.util.JDBCUtil;

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

/*
@author XiHai ShengGe
*/
public class Manage {
    public static void main(String[] args) throws SQLException {

        Connection conn = null;
        Statement stmt = null;
        ResultSet res = null;
        List<Student> list = null;
        //获取数据库连接
        try {
            conn = JDBCUtil.getConnection();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        //开始进行操作，避免程序使用一次后就退出
        System.out.println("欢迎进入学生管理系统\n" +
                "1.添加学生信息\n" +
                "2.显示学生信息\n" +
                "3.删除学生信息\n" +
                "4.修改学生信息\n" +
                "5.查询学生信息\n" +
                "6.排序\n" +
                "7.退出\n");
        //创建Scanner对象，以免循环体中重复创建
        Scanner s = new Scanner(System.in);
        Manage manage = new Manage();
        while (true) {
            System.out.println("请输入你要进行的操作：");
            int i = s.nextInt();
            switch (i) {
                case 1:
                    manage.add(s,conn,stmt);
                    break;
                case 2:
                    manage.show(conn, list,stmt,res);
                    break;
                case 3:
                    manage.del(s,conn,res);
                    break;
                case 4:
                    manage.update(s,stmt,conn,res);
                    break;
                case 5:
                    manage.inquire(s,stmt,conn,res);
                    break;
                case 6:
                    manage.sort(s,conn,stmt,res,list);
                    break;
                case 7:
                    //释放资源
                    JDBCUtil.close(conn,stmt,res);
                    System.exit(0);
                    break;
                default:
                    System.out.println("请输入1~7之间的数");
            }
        }
    }

    private void sort(Scanner s, Connection conn, Statement stmt, ResultSet res, List<Student> list) {
        //这里的排序，就是使用SQL语句获取出排序的值,默认是按升序排列
        String sql1 = "select * from student order by number";
        String sql2 = "select * from student order by grade";
        try {
            System.out.println("请输入排序方式(1表示按学号排序，2表示按成绩排序)");
            int i = s.nextInt();
            stmt = conn.createStatement();
            switch (i){
                case 1:
                    res = stmt.executeQuery(sql1);
                    break;
                case 2:
                    res = stmt.executeQuery(sql2);
                    break;
                default:
                    System.out.println("请输入1或2");
            }
            //创建List集合用来封装Student对象
            list = new ArrayList<Student>();
            Student student = null;
            while (res.next()) {
                int number = res.getInt("number");
                String name = res.getString("name");
                int grade = res.getInt("grade");

                student = new Student();
                student.setNumber(number);
                student.setName(name);
                student.setGrade(grade);

                list.add(student);
            }
            if (list != null) {
                for (Student stu : list) {
                    System.out.println("学号：" + stu.getNumber() + " 姓名：" + stu.getName() + " 成绩：" + stu.getGrade());
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    private void update(Scanner s, Statement stmt, Connection conn, ResultSet res) {
        System.out.print("请输入需要更新的学生学号：");
        int number = s.nextInt();
        String sql1 = "select * from student where number  = '"+ number +"'";
        try {
            stmt = conn.createStatement();
            res = stmt.executeQuery(sql1);
            if (res.next()){
                System.out.println("请输入更改后的姓名");
                String name = s.next();
                System.out.println("请输入更改后的成绩");
                int grade = s.nextInt();
                String sql = "update student set name = '"+ name +"' ,grade = '"+ grade+"' where number = '" + number +"'";
                int i = stmt.executeUpdate(sql);
                if (i>0){
                    System.out.println("更新成功");
                }
            }else {
                System.out.println("没有该学号的学生");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

    private void inquire(Scanner s, Statement stmt, Connection conn, ResultSet res) {
        System.out.print("请输入要查询的学号：");
        int number = s.nextInt();
        String sql = "select * from student where number = '" + number +"';";
        try {
            //这里没有为stmt赋值导致空指针异常
            stmt = conn.createStatement();
            res = stmt.executeQuery(sql);
            if (res.next()){
                int num = res.getInt("number");
                String name = res.getString("name");
                int grade = res.getInt("grade");

                Student student = new Student();
                student.setNumber(num);
                student.setName(name);
                student.setGrade(grade);

                System.out.println("学号：" + student.getNumber() + " 姓名：" + student.getName() + " 成绩：" + student.getGrade());
            }else {
                System.out.println("查询失败");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    private void del(Scanner s, Connection conn, ResultSet res) {
        System.out.println("请输入需要删除学生信息的学号");
        int number = s.nextInt();
        System.out.println("您确定要删除吗？(1是2否)");
        int n = s.nextInt();
        switch (n){
            case 1:
                String sql1 = "select * from student where number  = ?";
                String sql = "delete from student where number = ?";
                try {
                    PreparedStatement pstmt = conn.prepareStatement(sql1);
                    pstmt.setInt(1,number);
                    res = pstmt.executeQuery();
                    if (res.next()){
                        pstmt = conn.prepareStatement(sql);
                        pstmt.setInt(1,number);
                        int i = pstmt.executeUpdate();
                        if (i>0){
                            System.out.println("删除成功");
                        }else {
                            System.out.println("删除失败");
                        }
                    }else {
                        System.out.println("没有该学号的学生");
                    }
                    pstmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
                break;
            case 2:
                System.out.println("已退出删除操作");
                break;
            default:
                System.out.println("请输入1或2");

        }
    }

    private static void show(Connection conn, List<Student> list,Statement stmt,ResultSet res) throws SQLException {
        String sql = "select * from student";
        stmt = conn.createStatement();
        res = stmt.executeQuery(sql);
        //创建List集合用来封装Student对象
        list = new ArrayList<Student>();
        Student student = null;
        while (res.next()) {
            int number = res.getInt("number");
            String name = res.getString("name");
            int grade = res.getInt("grade");

            student = new Student();
            student.setNumber(number);
            student.setName(name);
            student.setGrade(grade);

            list.add(student);
        }
        if (list != null) {
            for (Student s : list) {
                System.out.println("学号：" + s.getNumber() + " 姓名：" + s.getName() + " 成绩：" + s.getGrade());
            }
        }
    }

    public void add(Scanner s,Connection conn,Statement stmt){
        System.out.print("请输入需要添加的学号：");
        int number = s.nextInt();
        System.out.print("请输入需要添加的姓名：");
        String name = s.next();
        System.out.print("请输入需要添加的成绩：");
        int grade = s.nextInt();
        String sql = "insert into student values(?,?,?)";
        //由于number是主键的所以不用查重
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,number);
            pstmt.setString(2,name);
            pstmt.setInt(3,grade);
            int i = pstmt.executeUpdate();
            //不能用executeQuery()方法
            if (i > 0){
                System.out.println("添加成功");
            }
            pstmt.close();
        } catch (SQLException throwables) {
            //throwables.printStackTrace();
            System.out.println("插入错误，请检查学号是否相同");
        }
    }
}

JDBCUtil.java

package cn.xlb.db.util;

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

/*
@author XiHai ShengGe
*/
public class JDBCUtil {
    private static String url;
    private static String user;
    private static String password;
    private static String driver;

    static {
        try {
            //创建属性集，用来获取配置文件中用于连接数据库的键值对
            Properties pro = new Properties();
            //获取该类的类加载器
            ClassLoader classLoader = JDBCUtil.class.getClassLoader();
            //加载资源
            URL res = classLoader.getResource("jdbc.properties");
            String path = res.getPath();
            //从文件字节输入流中读取属性列表
            pro.load(new FileReader(path));
            url = pro.getProperty("url");
            password = pro.getProperty("password");
            user = pro.getProperty("user");
            driver = pro.getProperty("driver");
            //返回与具有给定字符串名称的类或接口关联的 类对象。
            Class.forName(driver);
        }catch (IOException e){
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //获取数据库连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,user,password);
    }

    //关闭资源
    public static void close(Connection conn,Statement stmt,ResultSet res){
        //close(null,stmt,conn);
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (stmt != null){
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (res != null){
            try {
                res.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

Student.java

package cn.xlb.db.domain;

/*
@author XiHai ShengGe
*/
public class Student {
    private int number;
    private String name;
    private int grade;

    public Student() {
    }

    public Student(int number, String name, int grade) {
        this.number = number;
        this.name = name;
        this.grade = grade;
    }

    public int getNumber() {
        return number;
    }

    public void setNumber(int number) {
        this.number = number;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getGrade() {
        return grade;
    }

    public void setGrade(int grade) {
        this.grade = grade;
    }

    @Override
    public String toString() {
        return "Student{" +
                "number=" + number +
                ", name='" + name + '\'' +
                ", grade='" + grade + '\'' +
                '}';
    }
}