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

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 StudentDaolmplApache();
    //类变量
    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();
        //根据id找到数据，然后打印出来
        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();
    //id
    Student findStudenById(int id);
}

StudentDaolmplApache.java 实现类

package com.student.dao;
import com.student.po.Student;
import com.student.util.DBUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class StudentDaolmplApache implements IStudentDao {
//增
    @Override
    public int addStudent(Student student) {
        int row = 0;
        QueryRunner qr = new QueryRunner();
        String sql ="insert into t_student values (null,?,?,?,?)";
        try {
            row = qr.update(DBUtil.getConn(),sql,student.getName(),student.getSex(),student.getPhone(),student.getBirthplace());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }
//改
    @Override
    public int modifyStudent(Student student) {
        int row = 0;
        QueryRunner qr = new QueryRunner();
        String sql ="update t_student set name = ?, sex = ?, phone = ?,birthplace = ? where id =  ? ";
        try {
            row = qr.update(DBUtil.getConn(),sql,student.getName(),student.getSex(),student.getPhone(),student.getBirthplace(),student.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }
//删
    @Override
    public int dropStudent(int id) throws SQLException {
        int row = 0;
        QueryRunner qr = new QueryRunner();
        String sql ="delete from t_student where id =  ? ";
        try {
            row = qr.update(DBUtil.getConn(),sql,id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }
//查单
    @Override
    public List<Student> findStudents(String keyword) {
        ArrayList<Student> stu = new ArrayList<Student>();
        QueryRunner qr = new QueryRunner();
        String sql ="select * from t_student where name like ? or sex like ? or phone like ? or birthplace like ?";
      
        try {
            stu = qr.query(DBUtil.getConn(),sql,new BeanListHandler<Student>(Student.class),"%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return stu;
    }
//查所有
    @Override
    public List<Student> findAllStudents() {
        ArrayList<Student> stuList = new ArrayList<Student>();
        QueryRunner qr = new QueryRunner();
        String sql ="select * from t_student";
        try {
            stuList = (ArrayList<Student>) qr.query(DBUtil.getConn(),sql,new BeanListHandler<Student>(Student.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return stuList;
    }
    //找到id
    @Override
    public Student findStudenById(int id) {
        QueryRunner qr = new QueryRunner();
        String sql ="select * from t_student where id =  ? ";
        Student stu = null;
        try {
            stu = qr.query(DBUtil.getConn(),sql,new BeanHandler<Student>(Student.class),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return stu;
    }
}