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

Jdbc实现学生管理系统

正文

• 基于JDBC的学生管理系统
• 内有增删改查等功能

写的很简单，随便看看可以帮助回忆数据库的基础操作

package 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));//增加测试
    }
    //学生管理系统增删查改测试。
    //增
    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);
        }
    }
    //下面是个jdbc封装工具类，可以让数据库的连接和资源的释放更加方便
    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;
    }
}
}