Java学习-用JDBC通过CURD实现简单的业务，用户登陆，学生管理

矫情吗；* 2024-04-08 11:07 23阅读 0赞

上一篇已经学完了JDBC，这篇讲一下基本的CURD实现简单的业务。

##### 基本准备 #####

1.  数据表两张分别是user表和学生表student1结构如下  
    ![在这里插入图片描述][6dab05a8f46a481ba91ccf92aa0cb67f.png]  
    分别对应，用户id，用户名，密码，错误次数，状态（是否锁定）  
    ![在这里插入图片描述][3dbfe1ab7f2049859adb810aab40c670.png]  
    学生表，分别对应，学号，姓名，性别，年龄，地址  
    mysql的jar包  
    数据插入

SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for student1
    -- ----------------------------
    DROP TABLE IF EXISTS `student1`;
    CREATE TABLE `student1` (
      `sid` int(11) NOT NULL AUTO_INCREMENT,
      `sname` varchar(8) NOT NULL,
      `sgender` char(255) NOT NULL,
      `sage` int(3) DEFAULT NULL,
      `saddress` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`sid`)
    ) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of student1
    -- ----------------------------
    INSERT INTO `student1` VALUES ('1', '李青', '男', '48', '德玛西亚');
    INSERT INTO `student1` VALUES ('2', '艾瑞莉娅', '女', '26', '艾欧尼亚');
    INSERT INTO `student1` VALUES ('3', '锐雯', '女', '36', '艾欧尼亚');
    INSERT INTO `student1` VALUES ('4', '凯特琳', '女', '28', '皮尔特沃夫');
    INSERT INTO `student1` VALUES ('5', '蔚', '女', '28', '皮尔特沃夫');
    INSERT INTO `student1` VALUES ('6', '金克丝', '女', '28', '皮尔特沃夫');
    INSERT INTO `student1` VALUES ('7', '普朗克', '男', '45', '比尔吉沃特');
    INSERT INTO `student1` VALUES ('8', '好运姐', '女', '36', '比尔吉沃特');
    INSERT INTO `student1` VALUES ('9', '锤石', '男', '96', '暗影岛');
    INSERT INTO `student1` VALUES ('10', '佛耶戈', '男', '52', '暗影岛');
    INSERT INTO `student1` VALUES ('11', '雷欧娜', '女', '36', '巨神峰');
    INSERT INTO `student1` VALUES ('12', '阿兹尔', '男', '108', '恕瑞玛');
    INSERT INTO `student1` VALUES ('13', '内瑟斯', '男', '118', '恕瑞玛');
    INSERT INTO `student1` VALUES ('14', '雷克顿', '男', '118', '恕瑞玛');
    INSERT INTO `student1` VALUES ('15', '艾希', '女', '38', '弗雷尔卓德');
    INSERT INTO `student1` VALUES ('16', '瑟庄妮', '女', '45', '弗雷尔卓德');
    INSERT INTO `student1` VALUES ('17', '丽桑卓', '女', '82', '弗雷尔卓德');
    INSERT INTO `student1` VALUES ('18', '亚索', '男', '35', '艾欧尼亚');
    INSERT INTO `student1` VALUES ('19', '永恩', '男', '36', '艾欧尼亚');
    INSERT INTO `student1` VALUES ('20', '德莱厄斯', '男', '36', '诺克萨斯');
    INSERT INTO `student1` VALUES ('21', '卡特琳娜', '女', '32', '诺克萨斯');
    INSERT INTO `student1` VALUES ('22', '德莱文', '男', '30', '诺克萨斯');
    INSERT INTO `student1` VALUES ('23', '斯维因', '男', '50', '诺克萨斯');
    INSERT INTO `student1` VALUES ('24', '杰斯', '男', '40', '皮尔特沃夫');
    INSERT INTO `student1` VALUES ('25', '维克托', '男', '41', '皮尔特沃夫');
    INSERT INTO `student1` VALUES ('26', '沃里克', '男', '45', '祖安');
    INSERT INTO `student1` VALUES ('27', '蒙多', '男', '40', '祖安');
    INSERT INTO `student1` VALUES ('28', '凯尔', '女', '139', '烈焰阳刚');
    INSERT INTO `student1` VALUES ('29', '莫甘娜', '女', '139', '烈焰阳刚');
    INSERT INTO `student1` VALUES ('30', '卡蜜尔', '女', '30', '皮尔特沃夫');
    INSERT INTO `student1` VALUES ('31', '菲奥娜', '女', '35', '皮尔特沃夫');
    INSERT INTO `student1` VALUES ('32', '拉克丝', '女', '30', '德玛西亚');
    INSERT INTO `student1` VALUES ('34', '乐芙兰', '女', '32', '黑色玫瑰');
    INSERT INTO `student1` VALUES ('35', '亚托克斯', '男', '108', '恕瑞玛');
    INSERT INTO `student1` VALUES ('36', '沃利贝尔', '男', '89', '无畏先锋');

SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for user
    -- ----------------------------
    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user` (
      `uid` int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(16) NOT NULL,
      `password` varchar(16) NOT NULL,
      `wrong_times` int(1) NOT NULL,
      `status` int(1) NOT NULL DEFAULT '0',
      PRIMARY KEY (`uid`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of user
    -- ----------------------------
    INSERT INTO `user` VALUES ('1', '奥特曼', '111111', '0', '0');

##### 需求分析 #####

1.  要求实现模拟用户登陆
    
    > 如果用户名不存在，则提示用户名不存在。密码错误则提示密码错误。  
    > 用户名输入正确密码输入正确，可以正常登陆系统。否则不允许登陆。  
    > 并且密码输错三次，对帐号进行锁定。再次登陆时提示已锁定。  
    > 只要未满三次错误，输入正确，则错误次数重置。
2.  模拟学生管理，应包含以下功能
    
    > 对数据表中的所有学生进行输出  
    > 可以根据关键字段模糊查询学生  
    > 根据学号，对学生信息进行修改  
    > 根据学号，对学生进行单条删除
3.  系统功能
    
    > 可以实现用户通过输入指令对所有功能进行操作

##### 代码编写 #####

目录层级如下：  
![在这里插入图片描述][da9186c4d2e7428a9a681fcea6714385.png]

**entity包**

> Student

package com.lzl.StudentManage.entity;
    
    import java.util.Date;
    
    public class Student {
        
        private Integer sid;
        private String sname;
        private String sgender;
        private Integer sage;
        private String saddress;
    
        public Student(Integer sid, String sname, String sgender, Integer sage, String saddress) {
        
            this.sid = sid;
            this.sname = sname;
            this.sgender = sgender;
            this.sage = sage;
            this.saddress = saddress;
        }
    
        public Student() {
        
        }
    
        public Integer getSid() {
        
            return sid;
        }
    
        public void setSid(Integer sid) {
        
            this.sid = sid;
        }
    
        public String getSname() {
        
            return sname;
        }
    
        public void setSname(String sname) {
        
            this.sname = sname;
        }
    
        public String getSgender() {
        
            return sgender;
        }
    
        public void setSgender(String sgender) {
        
            this.sgender = sgender;
        }
    
        public Integer getSage() {
        
            return sage;
        }
    
        public void setSage(Integer sage) {
        
            this.sage = sage;
        }
    
        public String getSaddress() {
        
            return saddress;
        }
    
        public void setSaddress(String saddress) {
        
            this.saddress = saddress;
        }
    
        @Override
        public String toString() {
        
            return "Student{" +
                    "sid=" + sid +
                    ", sname='" + sname + '\'' +
                    ", sgender='" + sgender + '\'' +
                    ", sage=" + sage +
                    ", saddress='" + saddress + '\'' +
                    '}';
        }
    }

> User

package com.lzl.StudentManage.entity;
    
    public class User {
        
        private Integer uid;
        private String username;
        private String password;
        private Integer wrong_times;
        private Integer status;
    
        public User(Integer uid, String username, String password, Integer wrong_times, Integer status) {
        
            this.uid = uid;
            this.username = username;
            this.password = password;
            this.wrong_times = wrong_times;
            this.status = status;
        }
    
        public User() {
        
        }
    
        public Integer getUid() {
        
            return uid;
        }
    
        public void setUid(Integer uid) {
        
            this.uid = uid;
        }
    
        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 Integer getWrong_times() {
        
            return wrong_times;
        }
    
        public void setWrong_times(Integer wrong_times) {
        
            this.wrong_times = wrong_times;
        }
    
        public Integer getStatus() {
        
            return status;
        }
    
        public void setStatus(Integer status) {
        
            this.status = status;
        }
    
        @Override
        public String toString() {
        
            return "User{" +
                    "uid=" + uid +
                    ", username='" + username + '\'' +
                    ", password='" + password + '\'' +
                    ", wrong_times=" + wrong_times +
                    ", status=" + status +
                    '}';
        }
    }

**util包**

> DBUtil

package com.lzl.StudentManage.util;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.ArrayList;
    
    public class DBUtil {
        
    
        private static final String driver = "com.mysql.jdbc.Driver";
        private static final String url = "jdbc:mysql://localhost:3306/qianfeng";
        private static final String user = "root";
        private static final String password = "123456";
    
        public static Connection getConnection() {
        
            Connection con = null;
            try {
        
                Class.forName(driver);
                con = DriverManager.getConnection(url, user, password);
            } catch (ClassNotFoundException e) {
        
                e.printStackTrace();
            } catch (SQLException e) {
        
                e.printStackTrace();
            }
            return con;
        }
    
        public static void closeAll(Connection conn, Statement state,ResultSet rs) {
        
            try {
        
                if (conn != null){
        
                    conn.close();
                }
                if (state != null){
        
                    state.close();
                }
                if(rs != null){
        
                    rs.close();
                }
            } catch (SQLException e) {
        
                e.printStackTrace();
            }
        }
    
    
        public static int executeUpdate(String sql,ArrayList params){
        
            //创建连接
            Connection conn = null;
            //创建平台
            PreparedStatement ps = null;
            try {
        
                conn = getConnection();
                //预编译sql语句
                ps = conn.prepareStatement(sql);
                //设置参数
                for (int i = 0; i < params.size(); i++) {
        
                    ps.setObject(i + 1, params.get(i));
                }
                //返回受影响行数
                return ps.executeUpdate();
            } catch (SQLException e) {
        
                e.printStackTrace();
            } finally {
        
                closeAll(conn, ps,null);
            }
            return -1;
        }
    
    }

> StringUtil

package com.lzl.StudentManage.util;
    
    /**
     * 处理字符串工具类
     */
    public class StringUtil {
        
        /**
         *  是null 或者 是 空字符串
         */
        public static boolean isNull(Object obj) {
        
            //将Object转为String
            String str = (String)obj;
            //返回true | false
            return (str == null) || (str.length() == 0);
        }
    
    }

**controller包**

> LoginController

package com.lzl.StudentManage.controller;
    
    
    import com.lzl.StudentManage.entity.User;
    import com.lzl.StudentManage.util.DBUtil;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Scanner;
    
    public class LoginController {
        
        //获得连接
        private  Connection conn = null;
        //预编译处理对象
        private  PreparedStatement pst = null;
        //结果集
        private ResultSet rts = null;
        //注入student控制层
        static StudentController studentController = new StudentController();
        /**
         * 登录方法
         */
        public void getLogin(){
        
            Scanner scanner = new Scanner(System.in);
            String key = "y";
            try {
        
                conn = DBUtil.getConnection();
                //预编译
                String sql = "select * from user where username = ? ";
                pst = conn.prepareStatement(sql);
                do{
        
                    System.out.println("请输入帐号：");
                    String name = scanner.nextLine();
                    pst.setString(1,name);
                    System.out.println("请输入密码");
                    String pwd = scanner.nextLine();
                    System.out.println("正在登陆");
                    for (int i = 0; i < 3; i++) {
        
                        Thread.sleep(500);
                        System.out.print(".");
                    }
                    System.out.println();
                    rts = pst.executeQuery();
                    List<User> list = new ArrayList<>();
                    while (rts.next()){
        
                        User user = new User();
                        user.setUid(rts.getInt("uid"));
                        user.setUsername(rts.getString("username"));
                        user.setPassword(rts.getString("password"));
                        user.setWrong_times(rts.getInt("wrong_times"));
                        user.setStatus(rts.getInt("status"));
                        list.add(user);
                    }
                    if (list.size()!=0){
        
                        for (User user : list) {
        
                            if(user.getStatus()!=1){
        
                                if(user.getPassword().equals(pwd)){
        
                                    System.out.println("登陆成功，欢迎回来！");
                                    String sql1 = "update user set wrong_times = 0 where username = ?";
                                    PreparedStatement pst1 = conn.prepareStatement(sql1);
                                    pst1.setString(1,name);
                                    pst1.executeUpdate();
                                    studentController.getMenu();
                                    key = "n";
                                    DBUtil.closeAll(null,pst1,rts);
                                }else {
        
                                    if (user.getWrong_times()<3){
        
                                        String sql2 = "update user set wrong_times = wrong_times + 1 where username = ?";
                                        PreparedStatement pst2 = conn.prepareStatement(sql2);
                                        pst2.setString(1,name);
                                        pst2.executeUpdate();
                                        System.out.println("密码输入错误，您已输错"+(user.getWrong_times()+1)+"次！");
                                        System.out.println("错误3次，帐号将被锁定！");
                                        System.out.println("是否重试？y/n");
                                        key = scanner.nextLine();
                                        DBUtil.closeAll(null,pst2,rts);
                                    }
                                    if(user.getWrong_times()==3){
        
                                        String sql3 = "update user set status = 1 where username = ?";
                                        PreparedStatement pst3 = conn.prepareStatement(sql3);
                                        pst3.setString(1,name);
                                        pst3.executeUpdate();
                                        System.out.println("密码错误已达3次,您的帐号疑似处于不安全状态！帐号已被锁定！！");
                                        System.out.println("请联系管理员解锁！");
                                        key = "n";
                                        DBUtil.closeAll(null,pst3,rts);
                                    }
                                }
                            }else {
        
                                System.out.println("密码错误已达3次,您的帐号疑似处于不安全状态！帐号已被锁定！！");
                                System.out.println("请联系管理员解锁！");
                                key = "n";
                            }
                        }
                    }else {
        
                        System.out.println("用户名不存在！");
                        System.out.println("是否重试！y/n");
                        key = scanner.nextLine();
                    }
                }while (key.equals("y"));
            } catch (Exception e) {
        
                e.printStackTrace();
            } finally {
        
                DBUtil.closeAll(conn,pst,rts);
            }
    
        }
    }

> StudentController

package com.lzl.StudentManage.controller;
    
    import com.lzl.StudentManage.entity.Student;
    import com.lzl.StudentManage.service.serviceimpl.StudentServiceImpl;
    
    import java.util.*;
    
    public class StudentController {
        
        static StudentServiceImpl impl = new StudentServiceImpl();
        static Scanner scanner = new Scanner(System.in);
        /**
         * 查询（条件）所有用户
         * @param student 查询的关键字
         * @return 所有符合条件的用户
         */
        private static List<Student> getAll(Student student){
        
            Map<String, Object> map = new HashMap<>();
            map.put("sid",student.getSid());
            map.put("sname",student.getSname());
            map.put("sgender",student.getSgender());
            map.put("sage",student.getSage());
            map.put("saddress",student.getSaddress());
            return impl.getAll(map);
        }
    
        /**
         *多条件查询用户
         */
        public  void searchPrintAll(){
        
            System.out.println("您可以进行关键字查询，请选择您要查询的关键字类别");
            System.out.println("1:学号，2:姓名，3:性别，4:年龄，5:家庭住址");
            Integer key = scanner.nextInt();
            Student student =  searchWords(key);
            List<Student> list = getAll(student);
            if (list.size()>0){
        
                System.out.println("查询信息如下");
                System.out.println("学号\t姓名\t\t性别 年龄 家庭住址");
                for (Student s : list) {
        
                    if (s.getSname().length()>=2){
        
                        if (s.getSage() > 99){
        
                            System.out.println(s.getSid()+"\t|"+s.getSname()+"\t|"+s.getSgender()
                                    +"\t|"+s.getSage()+"\t|"+s.getSaddress());
                        }else {
        
                            System.out.println(s.getSid()+"\t|"+s.getSname()+"\t|"+s.getSgender()
                                    +"\t|"+s.getSage()+"\t\t|"+s.getSaddress());
                        }
                    } else if(s.getSname().length()==1){
        
                        System.out.println(s.getSid()+"\t|"+s.getSname()+"\t\t|"+s.getSgender()
                                +"\t|"+s.getSage()+"\t\t|"+s.getSaddress());
                    }else {
        
                        System.out.println(s.getSid()+"\t|"+s.getSname()+"\t\t|"+s.getSgender()
                                +"\t|"+s.getSage()+"\t|"+s.getSaddress());
                    }
                }
            }else {
        
                System.out.println("没有相关数据！");
            }
        }
        /**
         * 无条件输出所有学生信息
         */
        private void printAll() {
        
            Student student = new Student();
            List<Student> list =  getAll(student);
            System.out.println("学号\t姓名\t\t性别 年龄 \t家庭住址");
            for (Student s : list) {
        
                if (s.getSname().length()>=2){
        
                    if (s.getSage() > 99){
        
                        System.out.println(s.getSid()+"\t|"+s.getSname()+"\t|"+s.getSgender()
                                +"\t|"+s.getSage()+"\t|"+s.getSaddress());
                    }else {
        
                        System.out.println(s.getSid()+"\t|"+s.getSname()+"\t|"+s.getSgender()
                                +"\t|"+s.getSage()+"\t\t|"+s.getSaddress());
                    }
                } else if(s.getSname().length()==1){
        
                    System.out.println(s.getSid()+"\t|"+s.getSname()+"\t\t|"+s.getSgender()
                            +"\t|"+s.getSage()+"\t\t|"+s.getSaddress());
                }else {
        
                    System.out.println(s.getSid()+"\t|"+s.getSname()+"\t\t|"+s.getSgender()
                            +"\t|"+s.getSage()+"\t|"+s.getSaddress());
                }
            }
        }
        /**
         * 设置多条件查询的关键字
         * @param key 查询编号
         * @return 一个用于多条件查询的学生实体
         */
        private static Student searchWords(Integer key) {
        
            Student student = new Student();
            if(key==1){
        
                System.out.println("请输入您想要查询的学号：");
                Integer sid = scanner.nextInt();
                student.setSid(sid);
            }else if (key==2){
        
                System.out.println("请输入您想要查询的姓名关键字：");
                String sname = scanner.next();
                student.setSname(sname);
            }else if (key == 3){
        
                System.out.println("请输入您想要查询的性别（男/女）：");
                String sgender = scanner.next();
                student.setSgender(sgender);
            }else if (key == 4){
        
                System.out.println("请输入您想要查询的年龄：");
                Integer sage = scanner.nextInt();
                student.setSage(sage);
            }else if (key == 5){
        
                System.out.println("请输入您想要查询的家庭住址：");
                String saddress = scanner.next();
                student.setSaddress(saddress);
            }else {
        
                System.out.println("你是来捣乱的吧！乱输入，给你所有的数据，玩去吧！");
            }
            return student;
        }
    
        /**
         * 获得功能菜单
         */
        public void getMenu() {
        
            int key = 0;
            do{
        
                System.out.println("==============================功能菜单==========================");
                System.out.println("您有以下功能：1:输出所有学生信息，2:模糊查询学生，3:新增学生，4:修改学生信息，" +
                        "5:删除学生信息，0:退出系统");
                key = scanner.nextInt();
                switch (key){
        
                    case 1:
                        printAll();
                        break;
                    case 2:
                        searchPrintAll();
                        break;
                    case 3:
                        addNew();
                        break;
                    case 4:
                        updateBySid();
                        break;
                    case 5:
                        deleteBySid();
                        break;
                    case 0:
                        break;
                    default:
                        System.out.println("你是来找茬的吧？");
                        break;
                }
            }while (key!=0);
            System.out.println("您已成功退出！");
        }
    
        /**
         * 新增方法
         */
    
        private void addNew() {
        
            System.out.println("正在进行新增操作");
            System.out.println("请输入新增学生姓名：");
            String sname = scanner.next();
            System.out.println("输入新增学生性别：");
            String sgender = scanner.next();
            System.out.println("输入新增学生年龄");
            Integer sage = scanner.nextInt();
            System.out.println("输入学生地址：");
            String saddress = scanner.next();
            Student student = new Student(null,sname,sgender,sage,saddress);
            System.out.println("新增学生信息如下：");
            System.out.println("姓名:"+sname+"|性别:"+sgender
                    +"|年龄:"+sage+"|地址:"+saddress);
            Integer key = impl.insertStudent(student);
            if (key > 0){
        
                System.out.println("新增成功！");
            }else {
        
                System.out.println("新增失败！");
            }
        }
    
        /**
         * 修改方法
         */
        private void updateBySid() {
        
            System.out.println("正在进行修改操作");
            System.out.println("请输入要修改学生的学号：");
            String sid = scanner.next();
            System.out.println("请输入新的姓名：");
            String sname = scanner.next();
            System.out.println("输入新的学生性别：");
            String sgender = scanner.next();
            System.out.println("输入新的学生年龄：");
            String sage = scanner.next();
            System.out.println("输入新的学生地址：");
            String saddress = scanner.next();
            System.out.println("修改后学生信息如下：");
            System.out.println("姓名:"+sname+"|性别:"+sgender
                    +"|年龄:"+sage+"|地址:"+saddress);
            ArrayList<String> parms = new ArrayList<>();
            parms.add(sname);
            parms.add(sgender);
            parms.add(sage);
            parms.add(saddress);
            parms.add(sid);
            //此处id要最后放入集合，因为DBUtil中字符串拼接按添加的先后顺序
            //update语句的where子句在最后，所以sid放再最后，其它字段按原顺序
            Integer key = impl.updateStudent(parms);
            if (key > 0){
        
                System.out.println("修改成功！");
            }else {
        
                System.out.println("修改失败！");
            }
        }
    
        /**
         * 删除方法
         */
        private void deleteBySid() {
        
            System.out.println("正在进行删除操作");
            System.out.println("请输入要删除的学生学号：");
            String sid = scanner.next();
            Integer key = impl.deleteStudent(sid);
            if (key > 0){
        
                System.out.println("删除成功！");
            }else {
        
                System.out.println("删除失败！");
            }
        }
    
    
    }

**service包**

> StudentService

package com.lzl.StudentManage.service;
    
    import com.lzl.StudentManage.entity.Student;
    import com.lzl.day029.User;
    
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    
    public interface StudentService {
        
        /**
         * 获得所有用户
         * @param keys 多条件查询，分页参数
         * @return 所有符合条件用户集合
         */
        List<Student> getAll(Map<String,Object> keys);
    
        /**
         * 新增学生
         * @param student 新增的学生信息
         * @return 受影响行数
         */
        Integer insertStudent(Student student);
    
        /**
         * 修改数据
         * @param params 字符串集合
         * @return 受影响行数
         */
        Integer updateStudent(ArrayList<String> params);
    
        /**
         *
         * @param sid 学生学号
         * @return 受影响行数
         */
        Integer deleteStudent(String sid);
    
    }

> StudentServiceImpl

package com.lzl.StudentManage.service.serviceimpl;
    
    import com.lzl.StudentManage.entity.Student;
    import com.lzl.StudentManage.service.StudentService;
    import com.lzl.StudentManage.util.DBUtil;
    import com.lzl.StudentManage.util.StringUtil;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    
    public class StudentServiceImpl implements StudentService {
        
        //获得连接
        private  Connection conn = null;
        //预编译处理对象
        private  PreparedStatement pst = null;
        //结果集
        private ResultSet rts = null;
        //参数集合
        private ArrayList<Object> params = null;
    
        /**
         * 获得所有用户
         *
         * @param keys 多条件查询，分页参数
         * @return 所有用户集合
         */
        @Override
        public List<Student> getAll(Map<String, Object> keys) {
        
            //构建数据存放容器
            List<Student> list = new ArrayList<>();
            //构建查询所需参数的容器
            params = new ArrayList<>();
            //初始化sql语句  设置1=1，确保语句能执行不报错
            String sql = "select * from student1 where 1 = 1";
            //拼串，将多条件查询需要的参数全部拼接过来
            //判断不为空，则进行拼接
            if(!StringUtil.isNull(keys.get("sid"))){
        
                //将sql语句进行拼接，注意and开头有一个空格
                sql+=" and sid = ?";
                //将后边要填补占位符的参数从Map（Controller传过来的参数）集合添加进参数容器
                params.add(keys.get("sid"));
            }
            if(!StringUtil.isNull(keys.get("sname"))){
        
                //将sql语句进行拼接，注意and开头有一个空格
                sql+=" and sname like ?";
                //将后边要填补占位符的参数从Map（Controller传过来的参数）集合添加进参数容器
                params.add("%"+keys.get("sname")+"%");
            }
            if(!StringUtil.isNull(keys.get("sgender"))){
        
                sql+=" and sgender = ?";
                params.add(keys.get("sgender"));
            }
            if(!StringUtil.isNull(keys.get("sage"))){
        
                sql+=" and sage = ?";
                params.add(keys.get("sage"));
            }
            if(!StringUtil.isNull(keys.get("saddress"))){
        
                sql+=" and saddress like ?";
                params.add("%"+keys.get("saddress")+"%");
            }
            sql+=" order by sid";
            try {
        
                //获取连接
                conn = DBUtil.getConnection();
                //预编译处理sql
                pst = conn.prepareStatement(sql);
                //设置参数
                for (int i = 0; i < params.size(); i++) {
        
                    pst.setObject(i+1,params.get(i));
                }
                //执行sql获得数据
                rts = pst.executeQuery();
                //处理数据
                while (rts.next()){
        
                    //建一个对象
                    Student student = new Student();
                    //将所有结果集中的值取出，赋给对象
                    student.setSid(rts.getInt("sid"));
                    student.setSname(rts.getString("sname"));
                    student.setSgender(rts.getString("sgender"));
                    student.setSage(rts.getInt("sage"));
                    student.setSaddress(rts.getString("saddress"));
                    //将对象放到容器内
                    list.add(student);
                }
            } catch (SQLException e) {
        
                e.printStackTrace();
            }finally {
        
                //关流
                DBUtil.closeAll(conn,pst,rts);
            }
            return list;
        }
    
        /**
         * 新增学生
         *
         * @param student 新增的学生信息
         * @return 受影响行数
         */
        @Override
        public Integer insertStudent(Student student) {
        
            //给一个默认返回值
            Integer key = 0;
            try {
        
                //获得连接
                conn = DBUtil.getConnection();
                //写sql语句
                String sql = "insert into student1(sname,sgender,sage,saddress) values (?,?,?,?)";
                //预编译sql
                pst = conn.prepareStatement(sql);
                //将插入的值赋给占位符
                pst.setString(1,student.getSname());
                pst.setString(2,student.getSgender());
                pst.setInt(3,student.getSage());
                pst.setString(4,student.getSaddress());
                //交给executeUpdate执行，key接收受影响行数
                key = pst.executeUpdate();
            } catch (SQLException e) {
        
                e.printStackTrace();
            }finally {
        
                //关流
                DBUtil.closeAll(conn,pst,rts);
            }
            return key;
        }
    
        /**
         *修改
         * @param params 字符串集合
         * @return 受影响行数
         */
        @Override
        public Integer updateStudent(ArrayList<String> params) {
        
            //给一个默认返回值
            Integer key = 0;
            //编写sql语句
            String sql = "update student1 set sname = ?,sgender = ?," +
                    "sage = ?,saddress = ? where sid = ?";
            //将controller传过来的字符串集合，和sql语句交给DBUtil的方法执行，用key接收受影响行数
            key = DBUtil.executeUpdate(sql,params);
            return key;
        }
    
        /**删除
         * @param sid 学生学号
         * @return 受影响行数
         */
        @Override
        public Integer deleteStudent(String sid) {
        
            Integer key = 0;
            if (!StringUtil.isNull(sid)){
        
                 String sql =  String.format("delete from student1 where sid in(%s)",sid);
                 key = DBUtil.executeUpdate(sql,params);
            }
            return key;
        }
    }

**test包**

> 测试类

package com.lzl.StudentManage.test;
    
    import com.lzl.StudentManage.controller.LoginController;
    import com.lzl.StudentManage.controller.StudentController;
    import java.util.Scanner;
    
    public class WebModel {
        
        static StudentController studentController = new StudentController();
        static LoginController loginController = new LoginController();
        static Scanner scanner = new Scanner(System.in);
        public static void main(String[] args) {
        
            System.out.println("============欢迎来到学生管理系统============");
            System.out.println("请您登陆");
            loginController.getLogin();
        }
    
    }

附上效果演示

演示CRUD

[6dab05a8f46a481ba91ccf92aa0cb67f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/cb1992f4f13f47f6a3267d9f2b2ba638.png
[3dbfe1ab7f2049859adb810aab40c670.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/dfc8659981bf43c98c581073437a8734.png
[da9186c4d2e7428a9a681fcea6714385.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/5ac6fe275cc64b4586bbe673aa0fa142.png