【Java】基于Jdbc的学生管理系统

基于Jdbc的学生管理系统

正文

这是一个简单的Jdbc连接数据库模板，从登录数据库到删库跑路。
可以简单看看，写的比较简单。

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class TestSystem {
    public static void main(String[] args) {
//        update("wei");这些注释掉的都是测试
//        delete(24);
//        select(3);
//        Student student = new Student(null, "2019002", "2019002", "Henry", 2L, 100, false, 185, BigDecimal.valueOf(3.53), LocalDateTime.now());
//        System.out.println(add(student));
        while (true) {
            System.out.println("please input the username you have");
            Scanner scanner = new Scanner(System.in);
            String username = scanner.nextLine();
            System.out.println("please input the password you have");
            Scanner scanner1 = new Scanner(System.in);
            String password = scanner1.nextLine();
            if (login(username,password)==1) {
                show();
            }else {
                System.out.println("sorry");
            }
        }
    }
    //学生管理系统增删查改测试。
    //功能
    public static void show(){
        while (true) {
            System.out.println("please select the function you wanna:");
            System.out.println("1 for add");
            System.out.println("2 for delete");
            System.out.println("3 for select");
            System.out.println("4 for set");
            Scanner scanner=new Scanner(System.in);
            int button=scanner.nextInt();
            switch (button){
                case 1:
                    break;
                case 2:
                    break;
                case 3:
                    break;
                case 4:
                    break;
            }
        }
    }
    //登
    public static int login(String username, String password) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.Connect();
            String loginsql = "update tb_students_info set islogin=true where username=? and password=?";
            preparedStatement = connection.prepareStatement(loginsql);
            preparedStatement.setString(1, username);
            preparedStatement.setString(2, password);
            int a=preparedStatement.executeUpdate();
            return a;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(resultSet, preparedStatement, connection);
        }
        return 0;
    }
    //学生管理系统增删查改测试。
    //增
    public static int add(Student student) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JdbcUtils.Connect();
            String sql = "INSERT INTO tb_students_info VALUE(?,?,?,?,?,?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1,null);
            preparedStatement.setString(2, student.getUsername());
            preparedStatement.setString(3, student.getPassword());
            preparedStatement.setString(4, student.getName());
            preparedStatement.setLong(5, student.getDeptId());
            preparedStatement.setInt(6, student.getAge());
            preparedStatement.setBoolean(7, student.getSex());
            preparedStatement.setInt(8, student.getHeight());
            preparedStatement.setBigDecimal(9, student.getMoney());
            java.sql.Date loginDate = new java.sql.Date(Date.from(student.getLogin_date().atZone(ZoneOffset.ofHours(8)).toInstant()).getTime());
            preparedStatement.setDate(10, loginDate);
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(null, preparedStatement, connection);
        }
        return 0;
    }
    //    删
    public static void delete(int id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.Connect();
            String sql = "delete from tb_students_info where id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            int i = preparedStatement.executeUpdate();
            System.out.println(i);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(resultSet, preparedStatement, connection);
        }
    }
    //查
    public static void select(int num) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.Connect();
            String sql = "select  * from  tb_students_info where id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, num);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                System.out.println(
                        resultSet.getInt(1) + "\t"
                                + resultSet.getString(2) + "\t"
                                + resultSet.getString(3) + "\t"
                                + resultSet.getString(4) + "\t"
                                + resultSet.getInt(5) + "\t"
                                + resultSet.getInt(6) + "\t"
                                + resultSet.getInt(7) + "\t"
                                + resultSet.getInt(8) + "\t"
                                + resultSet.getBigDecimal(9) + "\t"
                                + resultSet.getDate(10)
                );
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(resultSet, preparedStatement, connection);
        }
    }
    //改
    public static void update(String name) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.Connect();
            String sql = "update tb_students_info set name=? where id=22";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            int i = preparedStatement.executeUpdate();
            System.out.println(i);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(resultSet, preparedStatement, connection);
        }
    }
}

学生类

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.Date;
public class Student {
    private Long id;
    private String username;
    private String password;
    private String name;
    private Long deptId;
    private int age;
    private Boolean sex;
    private int height;
    private BigDecimal money;
    private LocalDateTime login_date;
    private boolean islogin;
    public Student() {
    }
    public Student(Long id, String username, String password, String name, Long deptId, int age, Boolean sex, int height, BigDecimal money, LocalDateTime login_date,Boolean islogin) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.name = name;
        this.deptId = deptId;
        this.age = age;
        this.sex = sex;
        this.height = height;
        this.money = money;
        this.login_date = login_date;
        this.islogin=islogin;
    }
    public BigDecimal getMoney() {
        return money;
    }
    public void setMoney(BigDecimal money) {
        this.money = money;
    }
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Long getDeptId() {
        return deptId;
    }
    public void setDeptId(Long deptId) {
        this.deptId = deptId;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public Boolean getSex() {
        return sex;
    }
    public void setSex(Boolean sex) {
        this.sex = sex;
    }
    public int getHeight() {
        return height;
    }
    public void setHeight(int height) {
        this.height = height;
    }
    public LocalDateTime getLogin_date() {
        return login_date;
    }
    public void setLogin_date(LocalDateTime login_date) {
        this.login_date = login_date;
    }
    public boolean isIslogin() {
        return islogin;
    }
    public void setIslogin(boolean islogin) {
        this.islogin = islogin;
    }
    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", name='" + name + '\'' +
                ", deptId=" + deptId +
                ", age=" + age +
                ", sex=" + sex +
                ", height=" + height +
                ", money=" + money +
                ", login_date=" + login_date +
                ", islogin=" + islogin +
                '}';
    }
}

Jdbc工具类

    //下面是个jdbc封装工具类，可以让数据库的连接和资源的释放更加方便
    public class JdbcUtils {
    Connection connection = null;
    //注意我链接的数据库名是db9.用户名root，密码123456
    public static final String url = "jdbc:mysql://localhost:3306/db9?useUnicode=true&characterEncoding=UTF-8&userSSL=false&serverTimezone=GMT%2B8";
    public static final String user = "root";
    public static final String psd = "123456";

//    开始连接
    public static Connection Connect() {
        try {
        //这里是连接mysql8.0.16时注册驱动语句
            Class.forName("com.mysql.cj.jdbc.Driver");
            return DriverManager.getConnection(url, user, psd);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
        }
        return null;
    }
//      释放资源，注意这里释放资源的顺序
    public static void close(ResultSet resultSet,PreparedStatement preparedStatement,Connection connection) {
        try {
            if (resultSet!=null){
                resultSet.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        try {
            if (preparedStatement!=null){
                preparedStatement.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        try {
            if (connection!=null){
                connection.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    public static String sout(String resultSet) {
        return null;
    }
}

下文是我的数据库表

/*
Navicat MySQL Data Transfer

Source Server         : fiveGods
Source Server Version : 80016
Source Host           : localhost:3306
Source Database       : db9

Target Server Type    : MYSQL
Target Server Version : 80016
File Encoding         : 65001

Date: 2021-12-19 09:14:27
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for tb_students_info
-- ----------------------------
DROP TABLE IF EXISTS `tb_students_info`;
CREATE TABLE `tb_students_info` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id\r\n',
  `username` varchar(255) NOT NULL DEFAULT '' COMMENT '用户名',
  `password` varchar(255) NOT NULL DEFAULT '' COMMENT '密码',
  `name` varchar(255) NOT NULL DEFAULT '' COMMENT '姓名',
  `dept_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '部门id',
  `age` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
  `sex` bit(1) NOT NULL DEFAULT b'0' COMMENT '性别  0 男  1 女',
  `height` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '身高',
  `money` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '存款',
  `login_date` datetime DEFAULT NULL COMMENT '登陆时间',
  `islogin` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_students_info
-- ----------------------------
INSERT INTO `tb_students_info` VALUES ('3', '2019002', '2019002', 'Henry', '1', '1', '\0', '185', '0.00', '2021-12-08 23:27:08', '0');
INSERT INTO `tb_students_info` VALUES ('4', '2019003', '2019003', 'Jane', '1', '22', '\0', '162', '100.00', '2021-12-08 23:27:44', '0');
INSERT INTO `tb_students_info` VALUES ('5', '2019004', '2019004', 'Jim', '6', '21', '\0', '175', '-50.00', '2021-12-08 23:27:47', '0');
INSERT INTO `tb_students_info` VALUES ('6', '2019005', '2019005', 'John', '5', '25', '\0', '172', '200.00', '2021-12-04 23:27:52', '0');
INSERT INTO `tb_students_info` VALUES ('7', '2019006', '2019006', 'Lily', '1', '0', '\0', '165', '0.00', '2021-11-30 23:27:57', '0');
INSERT INTO `tb_students_info` VALUES ('8', '2019007', '2019007', 'Susan', '1', '20', '\0', '170', '0.00', '2021-11-02 23:28:01', '0');
INSERT INTO `tb_students_info` VALUES ('9', '2019008', '2019008', 'Thomas', '4', '35', '\0', '178', '0.00', '2021-12-03 23:28:06', '0');
INSERT INTO `tb_students_info` VALUES ('10', '2019009', '2019009', 'Tom', '3', '15', '\0', '165', '0.00', '2021-12-26 23:28:10', '0');
INSERT INTO `tb_students_info` VALUES ('11', '2019010', '2019010', 'Jerry', '1', '15', '\0', '170', '0.00', '2021-12-08 23:27:47', '0');
INSERT INTO `tb_students_info` VALUES ('22', '2019012', '2019012', 'wei', '1', '18', '\0', '178', '0.00', '2021-12-11 00:00:00', '0');
INSERT INTO `tb_students_info` VALUES ('23', '2019012', '2019012', '王五', '1', '18', '\0', '178', '100.00', '2021-12-11 00:00:00', '0');
INSERT INTO `tb_students_info` VALUES ('25', '2019002', '2019002', 'Henry', '1', '1', '\0', '185', '3.53', '2021-12-13 00:00:00', '0');
INSERT INTO `tb_students_info` VALUES ('26', '2019002', '2019002', 'Henry', '1', '1', '\0', '185', '3.53', '2021-12-13 00:00:00', '0');
INSERT INTO `tb_students_info` VALUES ('27', '2019002', '2019002', 'Henry', '1', '1', '\0', '185', '3.53', '2021-12-13 00:00:00', '0');