CS架构连接数据库学生管理系统-JDBC

DBUtil.java 连接数据库

package com.student.util;
import java.sql.*;

public class DBUtil {

    private  static String driverName = "com.mysql.cj.jdbc.Driver";
    private  static String url = "jdbc:mysql://127.0.0.1:3306/studb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8";
    private  static String username = "root";
    private  static String password = "root";
    //获取连接
    public static Connection getConn() {
        Connection connection = null;
        try {
            Class.forName(driverName);
            connection = DriverManager.getConnection(url,username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    //释放资源
    public static void closeAll(Connection conn, Statement stmt, ResultSet rs){

        if (rs!= null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}

Student.java

package com.student.po;
import java.util.ArrayList;
public class Student extends ArrayList<Student> {
    private int id;
    private String name;
    private String sex;
    private String phone;
    private String birthplace;

    public Student() {
    
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

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

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getBirthplace() {
        return birthplace;
    }

    public void setBirthplace(String birthplace) {
        this.birthplace = birthplace;
    }

    public Student(int id,String name, String sex, String phone, String birthplace) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.phone = phone;
        this.birthplace = birthplace;
    }

    public Student(String name, String sex, String phone, String birthplace) {
        this.name = name;
        this.sex = sex;
        this.phone = phone;
        this.birthplace = birthplace;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", phone='" + phone + '\'' +
                ", birthplace='" + birthplace + '\'' +
                '}';
    }
}

StudentManager.java 界面类

package com.student.view;
import com.student.dao.IStudentDao;
import com.student.dao.StudentDaolmplApache;
import com.student.po.Student;
import java.sql.SQLException;
import java.util.List;
import java.util.Scanner;

/*
    界面类
 */
public class StudentManager {
    //调用Dao层的添加方法
    static IStudentDao studentDao = new StudentDaolmpl();
    //类变量
    static Scanner sc = new Scanner(System.in);

    public static void main(String[] args) throws SQLException {
        while (true) {
            System.out.println("------学生管理系统-----");
            System.out.println("1.增 2.删 3.改 4.查 5.所有 0.退出");
            System.out.println("请输入操作：");
            int choose = sc.nextInt();

            if (choose == 1) {
                addStudent();
            } else if (choose == 2) {
                dropStudent();
            } else if (choose == 3) {
                modifyStudent();
            } else if (choose == 4) {
                findStudents();
            } else if (choose == 5) {
                showAllStudents();
            } else if (choose == 0) {
                sysExit();
            } else {
                System.out.println("您的输入有误！");
            }
        }
    }
//添加
    private static void addStudent() {
        System.out.println("-----添加------");
        System.out.println("姓名：");
        String name = sc.next();
        System.out.println("性别：");
        String sex = sc.next();
        System.out.println("电话：");
        String phone = sc.next();
        System.out.println("籍贯：");
        String birthplace = sc.next();

        //封装student对象
        Student student = new Student(name, sex, phone, birthplace);
        int row = studentDao.addStudent(student);
        //验证添加成功
        if (row != 0) {
            System.out.println("添加成功！");
        } else {
            System.out.println("添加失败！");
        }
    }
    //查询全
    private static void showAllStudents() {
        System.out.println("----查询----");
        List<Student> allStudents = studentDao.findAllStudents();
        //循环打印出来学生信息
        for (Student student : allStudents){
            System.out.println(student);
        }
    }
    //删除
    private static void dropStudent() throws SQLException {
        System.out.println("---删除---");
        System.out.println("请输入要删除学号：");
        int id = sc.nextInt();
        Student stu = studentDao.findStudenById(id);
        System.out.println(stu);
        System.out.println("确认删除？ 1.是  0.否");
        int isDel = sc.nextInt();
        if (isDel == 1){
            //执行删除
           int row =studentDao.dropStudent(id);
           if (row != 0){
               System.out.println("删除成功！");
           }else {
               System.out.println("删除失败！");
           }
        }
    }
    //修改
    private static void modifyStudent() {
        System.out.println("----修改----");
        System.out.println("----请输入修改学号：----");
        int id = sc.nextInt();
        Student student = studentDao.findStudenById(id);
        if (student != null) {
            System.out.println(student);
            System.out.println("请输入学生信息：");
            System.out.println("姓名：");
            String name = sc.next();
            System.out.println("性别：");
            String sex = sc.next();
            System.out.println("电话：");
            String phone = sc.next();
            System.out.println("籍贯：");
            String birthplace = sc.next();
            //构造一个学生对象
            Student stu = new Student(id,name,sex,phone,birthplace);
            int row = studentDao.modifyStudent(stu);
            if (row != 0){
                System.out.println("修改成功！");
            }else {
                System.out.println("修改失败！");
            }
        }else {
            System.out.println("没有此学号！");
        }
    }
    //查询单个
    private static void findStudents(){
        System.out.println("----查询单个----");
        System.out.println("----请输入关键字：----");
        String keyword = sc.next();
        List<Student> students = studentDao.findStudents(keyword);
        if (students.size() == 0){
            System.out.println("暂无数据");
            return;
        }
        //循环打印出来学生信息
        for (Student student : students){
            System.out.println(student);
        }
    }

//退出
    private static void sysExit() {
        System.out.println("退出系统！");
        System.exit(0);//系统退出
    }
}

IStudentDao.java 接口类

package com.student.dao;
import com.student.po.Student;
import java.sql.SQLException;
import java.util.List;

/*
       接口类 声明方法
 */
public interface IStudentDao {
    //添加学生
    public int addStudent(Student student);
    //修
    public int modifyStudent(Student student);
    //删
    public int dropStudent(int id) throws SQLException;
    //查
    public List<Student> findStudents(String keyword);
    //所有
    public List<Student> findAllStudents();

    Student findStudenById(int id);
}

StudentDaolmpl.java 实现类

package com.student.dao;
import com.student.po.Student;
import com.student.util.DBUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
//Dao   原生的
public class StudentDaolmpl implements IStudentDao {

//增
    @Override
    public int addStudent(Student student) {
        int row = 0;
        PreparedStatement pstnt = null;
        Connection conn = DBUtil.getConn();
        String sql = "insert into t_student values (null,?,?,?,?)";
                try{
                    pstnt = conn.prepareStatement(sql);
                    pstnt.setString(1,student.getName());
                    pstnt.setString(2,student.getSex());
                    pstnt.setString(3,student.getPhone());
                    pstnt.setString(4,student.getBirthplace());

                    row = pstnt.executeUpdate();

                } catch (SQLException e) {
                    e.printStackTrace();
                }finally {
                    DBUtil.closeAll(conn,pstnt,null);
                }
        return row;
    }
//改
    @Override
    public int modifyStudent(Student student) {
        int row = 0;
        Connection conn = DBUtil.getConn();
        PreparedStatement pstmt = null;
        String sql ="update t_student set name = ?, sex = ?, phone = ?,birthplace = ? where id =  ? ";
        try{
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,student.getName());
            pstmt.setString(2,student.getSex());
            pstmt.setString(3,student.getPhone());
            pstmt.setString(4,student.getBirthplace());
            pstmt.setInt(5,student.getId());
            row = pstmt.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,pstmt,null);
        }
        return  row;
    }
//删
    @Override
    public int dropStudent(int id) {
        int row = 0;
        Connection conn = DBUtil.getConn();
        PreparedStatement pstmt = null;
        String sql ="delete from t_student where id =  ? ";
        try{
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            row = pstmt.executeUpdate();

        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,pstmt,null);
        }
        return  row;
    }
//查单
    @Override
    public List<Student> findStudents(String keyword) {
        List<Student> studentList = null;
        Connection conn = DBUtil.getConn();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql ="select * from t_student where name like ? or sex like ? or phone like ? or birthplace like ?";
        try{
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,"%"+keyword+"%");
            pstmt.setString(2,"%"+keyword+"%");
            pstmt.setString(3,"%"+keyword+"%");
            pstmt.setString(4,"%"+keyword+"%");
            rs = pstmt.executeQuery();
            //遍历结果集rs 把结果集数据复制到studentList
            studentList = new ArrayList<Student>();
            while (rs.next()){
                //封装成对象
                Student s = new Student();
                s.setId(rs.getInt(1));
                s.setName(rs.getString(2));
                s.setSex(rs.getString(3));
                s.setPhone(rs.getString(4));
                s.setBirthplace(rs.getString(5));
                //把对象放在集合里
                studentList.add(s);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,pstmt,null);
        }
        return  studentList;
    }
//查所有
    @Override
    public List<Student> findAllStudents() {
        List<Student> studentList = null;
        Connection conn = DBUtil.getConn();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql ="select * from t_student";
        try{
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            studentList = new ArrayList<Student>();
            while (rs.next()){
                Student s = new Student();
                s.setId(rs.getInt(1));
                s.setName(rs.getString(2));
                s.setSex(rs.getString(3));
                s.setPhone(rs.getString(4));
                s.setBirthplace(rs.getString(5));
                studentList.add(s);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,pstmt,null);
        }
        return  studentList;
    }

    @Override
    public Student findStudenById(int id) {
        Student s = null;
        Connection conn = DBUtil.getConn();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql ="select * from t_student where id =  ? ";
        try{
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            rs = pstmt.executeQuery();
            if (rs.next()){
                //封装成对象
                s = new Student();
                s.setId(rs.getInt(1));
                s.setName(rs.getString(2));
                s.setSex(rs.getString(3));
                s.setPhone(rs.getString(4));
                s.setBirthplace(rs.getString(5));
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,pstmt,null);
        }
        return  s;
    }
}