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

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

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

基本准备

  1. 数据表两张分别是user表和学生表student1结构如下
    在这里插入图片描述
    分别对应，用户id，用户名，密码，错误次数，状态（是否锁定）
    在这里插入图片描述
    学生表，分别对应，学号，姓名，性别，年龄，地址
    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. 系统功能

    可以实现用户通过输入指令对所有功能进行操作

代码编写

目录层级如下：
在这里插入图片描述

entity包

Student

  1. package com.lzl.StudentManage.entity;
  3. import java.util.Date;
  5. public class Student {
  7.     private Integer sid;
  8.     private String sname;
  9.     private String sgender;
  10.     private Integer sage;
  11.     private String saddress;
  13.     public Student(Integer sid, String sname, String sgender, Integer sage, String saddress) {
  15.         this.sid = sid;
  16.         this.sname = sname;
  17.         this.sgender = sgender;
  18.         this.sage = sage;
  19.         this.saddress = saddress;
  20.     }
  22.     public Student() {
  24.     }
  26.     public Integer getSid() {
  28.         return sid;
  29.     }
  31.     public void setSid(Integer sid) {
  33.         this.sid = sid;
  34.     }
  36.     public String getSname() {
  38.         return sname;
  39.     }
  41.     public void setSname(String sname) {
  43.         this.sname = sname;
  44.     }
  46.     public String getSgender() {
  48.         return sgender;
  49.     }
  51.     public void setSgender(String sgender) {
  53.         this.sgender = sgender;
  54.     }
  56.     public Integer getSage() {
  58.         return sage;
  59.     }
  61.     public void setSage(Integer sage) {
  63.         this.sage = sage;
  64.     }
  66.     public String getSaddress() {
  68.         return saddress;
  69.     }
  71.     public void setSaddress(String saddress) {
  73.         this.saddress = saddress;
  74.     }
  76.     @Override
  77.     public String toString() {
  79.         return "Student{" +
  80.                 "sid=" + sid +
  81.                 ", sname='" + sname + '\'' +
  82.                 ", sgender='" + sgender + '\'' +
  83.                 ", sage=" + sage +
  84.                 ", saddress='" + saddress + '\'' +
  85.                 '}';
  86.     }
  87. }

User

  1. package com.lzl.StudentManage.entity;
  3. public class User {
  5.     private Integer uid;
  6.     private String username;
  7.     private String password;
  8.     private Integer wrong_times;
  9.     private Integer status;
  11.     public User(Integer uid, String username, String password, Integer wrong_times, Integer status) {
  13.         this.uid = uid;
  14.         this.username = username;
  15.         this.password = password;
  16.         this.wrong_times = wrong_times;
  17.         this.status = status;
  18.     }
  20.     public User() {
  22.     }
  24.     public Integer getUid() {
  26.         return uid;
  27.     }
  29.     public void setUid(Integer uid) {
  31.         this.uid = uid;
  32.     }
  34.     public String getUsername() {
  36.         return username;
  37.     }
  39.     public void setUsername(String username) {
  41.         this.username = username;
  42.     }
  44.     public String getPassword() {
  46.         return password;
  47.     }
  49.     public void setPassword(String password) {
  51.         this.password = password;
  52.     }
  54.     public Integer getWrong_times() {
  56.         return wrong_times;
  57.     }
  59.     public void setWrong_times(Integer wrong_times) {
  61.         this.wrong_times = wrong_times;
  62.     }
  64.     public Integer getStatus() {
  66.         return status;
  67.     }
  69.     public void setStatus(Integer status) {
  71.         this.status = status;
  72.     }
  74.     @Override
  75.     public String toString() {
  77.         return "User{" +
  78.                 "uid=" + uid +
  79.                 ", username='" + username + '\'' +
  80.                 ", password='" + password + '\'' +
  81.                 ", wrong_times=" + wrong_times +
  82.                 ", status=" + status +
  83.                 '}';
  84.     }
  85. }

util包

DBUtil

  1. package com.lzl.StudentManage.util;
  3. import java.sql.Connection;
  4. import java.sql.DriverManager;
  5. import java.sql.PreparedStatement;
  6. import java.sql.ResultSet;
  7. import java.sql.SQLException;
  8. import java.sql.Statement;
  9. import java.util.ArrayList;
  11. public class DBUtil {
  14.     private static final String driver = "com.mysql.jdbc.Driver";
  15.     private static final String url = "jdbc:mysql://localhost:3306/qianfeng";
  16.     private static final String user = "root";
  17.     private static final String password = "123456";
  19.     public static Connection getConnection() {
  21.         Connection con = null;
  22.         try {
  24.             Class.forName(driver);
  25.             con = DriverManager.getConnection(url, user, password);
  26.         } catch (ClassNotFoundException e) {
  28.             e.printStackTrace();
  29.         } catch (SQLException e) {
  31.             e.printStackTrace();
  32.         }
  33.         return con;
  34.     }
  36.     public static void closeAll(Connection conn, Statement state,ResultSet rs) {
  38.         try {
  40.             if (conn != null){
  42.                 conn.close();
  43.             }
  44.             if (state != null){
  46.                 state.close();
  47.             }
  48.             if(rs != null){
  50.                 rs.close();
  51.             }
  52.         } catch (SQLException e) {
  54.             e.printStackTrace();
  55.         }
  56.     }
  59.     public static int executeUpdate(String sql,ArrayList params){
  61.         //创建连接
  62.         Connection conn = null;
  63.         //创建平台
  64.         PreparedStatement ps = null;
  65.         try {
  67.             conn = getConnection();
  68.             //预编译sql语句
  69.             ps = conn.prepareStatement(sql);
  70.             //设置参数
  71.             for (int i = 0; i < params.size(); i++) {
  73.                 ps.setObject(i + 1, params.get(i));
  74.             }
  75.             //返回受影响行数
  76.             return ps.executeUpdate();
  77.         } catch (SQLException e) {
  79.             e.printStackTrace();
  80.         } finally {
  82.             closeAll(conn, ps,null);
  83.         }
  84.         return -1;
  85.     }
  87. }

StringUtil

  1. package com.lzl.StudentManage.util;
  3. /**
  4.  * 处理字符串工具类
  5.  */
  6. public class StringUtil {
  8.     /**
  9.      *  是null 或者 是 空字符串
  10.      */
  11.     public static boolean isNull(Object obj) {
  13.         //将Object转为String
  14.         String str = (String)obj;
  15.         //返回true | false
  16.         return (str == null) || (str.length() == 0);
  17.     }
  19. }

controller包

LoginController

  1. package com.lzl.StudentManage.controller;
  4. import com.lzl.StudentManage.entity.User;
  5. import com.lzl.StudentManage.util.DBUtil;
  7. import java.sql.Connection;
  8. import java.sql.PreparedStatement;
  9. import java.sql.ResultSet;
  10. import java.util.ArrayList;
  11. import java.util.List;
  12. import java.util.Scanner;
  14. public class LoginController {
  16.     //获得连接
  17.     private  Connection conn = null;
  18.     //预编译处理对象
  19.     private  PreparedStatement pst = null;
  20.     //结果集
  21.     private ResultSet rts = null;
  22.     //注入student控制层
  23.     static StudentController studentController = new StudentController();
  24.     /**
  25.      * 登录方法
  26.      */
  27.     public void getLogin(){
  29.         Scanner scanner = new Scanner(System.in);
  30.         String key = "y";
  31.         try {
  33.             conn = DBUtil.getConnection();
  34.             //预编译
  35.             String sql = "select * from user where username = ? ";
  36.             pst = conn.prepareStatement(sql);
  37.             do{
  39.                 System.out.println("请输入帐号：");
  40.                 String name = scanner.nextLine();
  41.                 pst.setString(1,name);
  42.                 System.out.println("请输入密码");
  43.                 String pwd = scanner.nextLine();
  44.                 System.out.println("正在登陆");
  45.                 for (int i = 0; i < 3; i++) {
  47.                     Thread.sleep(500);
  48.                     System.out.print(".");
  49.                 }
  50.                 System.out.println();
  51.                 rts = pst.executeQuery();
  52.                 List<User> list = new ArrayList<>();
  53.                 while (rts.next()){
  55.                     User user = new User();
  56.                     user.setUid(rts.getInt("uid"));
  57.                     user.setUsername(rts.getString("username"));
  58.                     user.setPassword(rts.getString("password"));
  59.                     user.setWrong_times(rts.getInt("wrong_times"));
  60.                     user.setStatus(rts.getInt("status"));
  61.                     list.add(user);
  62.                 }
  63.                 if (list.size()!=0){
  65.                     for (User user : list) {
  67.                         if(user.getStatus()!=1){
  69.                             if(user.getPassword().equals(pwd)){
  71.                                 System.out.println("登陆成功，欢迎回来！");
  72.                                 String sql1 = "update user set wrong_times = 0 where username = ?";
  73.                                 PreparedStatement pst1 = conn.prepareStatement(sql1);
  74.                                 pst1.setString(1,name);
  75.                                 pst1.executeUpdate();
  76.                                 studentController.getMenu();
  77.                                 key = "n";
  78.                                 DBUtil.closeAll(null,pst1,rts);
  79.                             }else {
  81.                                 if (user.getWrong_times()<3){
  83.                                     String sql2 = "update user set wrong_times = wrong_times + 1 where username = ?";
  84.                                     PreparedStatement pst2 = conn.prepareStatement(sql2);
  85.                                     pst2.setString(1,name);
  86.                                     pst2.executeUpdate();
  87.                                     System.out.println("密码输入错误，您已输错"+(user.getWrong_times()+1)+"次！");
  88.                                     System.out.println("错误3次，帐号将被锁定！");
  89.                                     System.out.println("是否重试？y/n");
  90.                                     key = scanner.nextLine();
  91.                                     DBUtil.closeAll(null,pst2,rts);
  92.                                 }
  93.                                 if(user.getWrong_times()==3){
  95.                                     String sql3 = "update user set status = 1 where username = ?";
  96.                                     PreparedStatement pst3 = conn.prepareStatement(sql3);
  97.                                     pst3.setString(1,name);
  98.                                     pst3.executeUpdate();
  99.                                     System.out.println("密码错误已达3次,您的帐号疑似处于不安全状态！帐号已被锁定！！");
  100.                                     System.out.println("请联系管理员解锁！");
  101.                                     key = "n";
  102.                                     DBUtil.closeAll(null,pst3,rts);
  103.                                 }
  104.                             }
  105.                         }else {
  107.                             System.out.println("密码错误已达3次,您的帐号疑似处于不安全状态！帐号已被锁定！！");
  108.                             System.out.println("请联系管理员解锁！");
  109.                             key = "n";
  110.                         }
  111.                     }
  112.                 }else {
  114.                     System.out.println("用户名不存在！");
  115.                     System.out.println("是否重试！y/n");
  116.                     key = scanner.nextLine();
  117.                 }
  118.             }while (key.equals("y"));
  119.         } catch (Exception e) {
  121.             e.printStackTrace();
  122.         } finally {
  124.             DBUtil.closeAll(conn,pst,rts);
  125.         }
  127.     }
  128. }

StudentController

  1. package com.lzl.StudentManage.controller;
  3. import com.lzl.StudentManage.entity.Student;
  4. import com.lzl.StudentManage.service.serviceimpl.StudentServiceImpl;
  6. import java.util.*;
  8. public class StudentController {
  10.     static StudentServiceImpl impl = new StudentServiceImpl();
  11.     static Scanner scanner = new Scanner(System.in);
  12.     /**
  13.      * 查询（条件）所有用户
  14.      * @param student 查询的关键字
  15.      * @return 所有符合条件的用户
  16.      */
  17.     private static List<Student> getAll(Student student){
  19.         Map<String, Object> map = new HashMap<>();
  20.         map.put("sid",student.getSid());
  21.         map.put("sname",student.getSname());
  22.         map.put("sgender",student.getSgender());
  23.         map.put("sage",student.getSage());
  24.         map.put("saddress",student.getSaddress());
  25.         return impl.getAll(map);
  26.     }
  28.     /**
  29.      *多条件查询用户
  30.      */
  31.     public  void searchPrintAll(){
  33.         System.out.println("您可以进行关键字查询，请选择您要查询的关键字类别");
  34.         System.out.println("1:学号，2:姓名，3:性别，4:年龄，5:家庭住址");
  35.         Integer key = scanner.nextInt();
  36.         Student student =  searchWords(key);
  37.         List<Student> list = getAll(student);
  38.         if (list.size()>0){
  40.             System.out.println("查询信息如下");
  41.             System.out.println("学号\t姓名\t\t性别 年龄 家庭住址");
  42.             for (Student s : list) {
  44.                 if (s.getSname().length()>=2){
  46.                     if (s.getSage() > 99){
  48.                         System.out.println(s.getSid()+"\t|"+s.getSname()+"\t|"+s.getSgender()
  49.                                 +"\t|"+s.getSage()+"\t|"+s.getSaddress());
  50.                     }else {
  52.                         System.out.println(s.getSid()+"\t|"+s.getSname()+"\t|"+s.getSgender()
  53.                                 +"\t|"+s.getSage()+"\t\t|"+s.getSaddress());
  54.                     }
  55.                 } else if(s.getSname().length()==1){
  57.                     System.out.println(s.getSid()+"\t|"+s.getSname()+"\t\t|"+s.getSgender()
  58.                             +"\t|"+s.getSage()+"\t\t|"+s.getSaddress());
  59.                 }else {
  61.                     System.out.println(s.getSid()+"\t|"+s.getSname()+"\t\t|"+s.getSgender()
  62.                             +"\t|"+s.getSage()+"\t|"+s.getSaddress());
  63.                 }
  64.             }
  65.         }else {
  67.             System.out.println("没有相关数据！");
  68.         }
  69.     }
  70.     /**
  71.      * 无条件输出所有学生信息
  72.      */
  73.     private void printAll() {
  75.         Student student = new Student();
  76.         List<Student> list =  getAll(student);
  77.         System.out.println("学号\t姓名\t\t性别 年龄 \t家庭住址");
  78.         for (Student s : list) {
  80.             if (s.getSname().length()>=2){
  82.                 if (s.getSage() > 99){
  84.                     System.out.println(s.getSid()+"\t|"+s.getSname()+"\t|"+s.getSgender()
  85.                             +"\t|"+s.getSage()+"\t|"+s.getSaddress());
  86.                 }else {
  88.                     System.out.println(s.getSid()+"\t|"+s.getSname()+"\t|"+s.getSgender()
  89.                             +"\t|"+s.getSage()+"\t\t|"+s.getSaddress());
  90.                 }
  91.             } else if(s.getSname().length()==1){
  93.                 System.out.println(s.getSid()+"\t|"+s.getSname()+"\t\t|"+s.getSgender()
  94.                         +"\t|"+s.getSage()+"\t\t|"+s.getSaddress());
  95.             }else {
  97.                 System.out.println(s.getSid()+"\t|"+s.getSname()+"\t\t|"+s.getSgender()
  98.                         +"\t|"+s.getSage()+"\t|"+s.getSaddress());
  99.             }
  100.         }
  101.     }
  102.     /**
  103.      * 设置多条件查询的关键字
  104.      * @param key 查询编号
  105.      * @return 一个用于多条件查询的学生实体
  106.      */
  107.     private static Student searchWords(Integer key) {
  109.         Student student = new Student();
  110.         if(key==1){
  112.             System.out.println("请输入您想要查询的学号：");
  113.             Integer sid = scanner.nextInt();
  114.             student.setSid(sid);
  115.         }else if (key==2){
  117.             System.out.println("请输入您想要查询的姓名关键字：");
  118.             String sname = scanner.next();
  119.             student.setSname(sname);
  120.         }else if (key == 3){
  122.             System.out.println("请输入您想要查询的性别（男/女）：");
  123.             String sgender = scanner.next();
  124.             student.setSgender(sgender);
  125.         }else if (key == 4){
  127.             System.out.println("请输入您想要查询的年龄：");
  128.             Integer sage = scanner.nextInt();
  129.             student.setSage(sage);
  130.         }else if (key == 5){
  132.             System.out.println("请输入您想要查询的家庭住址：");
  133.             String saddress = scanner.next();
  134.             student.setSaddress(saddress);
  135.         }else {
  137.             System.out.println("你是来捣乱的吧！乱输入，给你所有的数据，玩去吧！");
  138.         }
  139.         return student;
  140.     }
  142.     /**
  143.      * 获得功能菜单
  144.      */
  145.     public void getMenu() {
  147.         int key = 0;
  148.         do{
  150.             System.out.println("==============================功能菜单==========================");
  151.             System.out.println("您有以下功能：1:输出所有学生信息，2:模糊查询学生，3:新增学生，4:修改学生信息，" +
  152.                     "5:删除学生信息，0:退出系统");
  153.             key = scanner.nextInt();
  154.             switch (key){
  156.                 case 1:
  157.                     printAll();
  158.                     break;
  159.                 case 2:
  160.                     searchPrintAll();
  161.                     break;
  162.                 case 3:
  163.                     addNew();
  164.                     break;
  165.                 case 4:
  166.                     updateBySid();
  167.                     break;
  168.                 case 5:
  169.                     deleteBySid();
  170.                     break;
  171.                 case 0:
  172.                     break;
  173.                 default:
  174.                     System.out.println("你是来找茬的吧？");
  175.                     break;
  176.             }
  177.         }while (key!=0);
  178.         System.out.println("您已成功退出！");
  179.     }
  181.     /**
  182.      * 新增方法
  183.      */
  185.     private void addNew() {
  187.         System.out.println("正在进行新增操作");
  188.         System.out.println("请输入新增学生姓名：");
  189.         String sname = scanner.next();
  190.         System.out.println("输入新增学生性别：");
  191.         String sgender = scanner.next();
  192.         System.out.println("输入新增学生年龄");
  193.         Integer sage = scanner.nextInt();
  194.         System.out.println("输入学生地址：");
  195.         String saddress = scanner.next();
  196.         Student student = new Student(null,sname,sgender,sage,saddress);
  197.         System.out.println("新增学生信息如下：");
  198.         System.out.println("姓名:"+sname+"|性别:"+sgender
  199.                 +"|年龄:"+sage+"|地址:"+saddress);
  200.         Integer key = impl.insertStudent(student);
  201.         if (key > 0){
  203.             System.out.println("新增成功！");
  204.         }else {
  206.             System.out.println("新增失败！");
  207.         }
  208.     }
  210.     /**
  211.      * 修改方法
  212.      */
  213.     private void updateBySid() {
  215.         System.out.println("正在进行修改操作");
  216.         System.out.println("请输入要修改学生的学号：");
  217.         String sid = scanner.next();
  218.         System.out.println("请输入新的姓名：");
  219.         String sname = scanner.next();
  220.         System.out.println("输入新的学生性别：");
  221.         String sgender = scanner.next();
  222.         System.out.println("输入新的学生年龄：");
  223.         String sage = scanner.next();
  224.         System.out.println("输入新的学生地址：");
  225.         String saddress = scanner.next();
  226.         System.out.println("修改后学生信息如下：");
  227.         System.out.println("姓名:"+sname+"|性别:"+sgender
  228.                 +"|年龄:"+sage+"|地址:"+saddress);
  229.         ArrayList<String> parms = new ArrayList<>();
  230.         parms.add(sname);
  231.         parms.add(sgender);
  232.         parms.add(sage);
  233.         parms.add(saddress);
  234.         parms.add(sid);
  235.         //此处id要最后放入集合，因为DBUtil中字符串拼接按添加的先后顺序
  236.         //update语句的where子句在最后，所以sid放再最后，其它字段按原顺序
  237.         Integer key = impl.updateStudent(parms);
  238.         if (key > 0){
  240.             System.out.println("修改成功！");
  241.         }else {
  243.             System.out.println("修改失败！");
  244.         }
  245.     }
  247.     /**
  248.      * 删除方法
  249.      */
  250.     private void deleteBySid() {
  252.         System.out.println("正在进行删除操作");
  253.         System.out.println("请输入要删除的学生学号：");
  254.         String sid = scanner.next();
  255.         Integer key = impl.deleteStudent(sid);
  256.         if (key > 0){
  258.             System.out.println("删除成功！");
  259.         }else {
  261.             System.out.println("删除失败！");
  262.         }
  263.     }
  266. }

service包

StudentService

  1. package com.lzl.StudentManage.service;
  3. import com.lzl.StudentManage.entity.Student;
  4. import com.lzl.day029.User;
  6. import java.util.ArrayList;
  7. import java.util.List;
  8. import java.util.Map;
  10. public interface StudentService {
  12.     /**
  13.      * 获得所有用户
  14.      * @param keys 多条件查询，分页参数
  15.      * @return 所有符合条件用户集合
  16.      */
  17.     List<Student> getAll(Map<String,Object> keys);
  19.     /**
  20.      * 新增学生
  21.      * @param student 新增的学生信息
  22.      * @return 受影响行数
  23.      */
  24.     Integer insertStudent(Student student);
  26.     /**
  27.      * 修改数据
  28.      * @param params 字符串集合
  29.      * @return 受影响行数
  30.      */
  31.     Integer updateStudent(ArrayList<String> params);
  33.     /**
  34.      *
  35.      * @param sid 学生学号
  36.      * @return 受影响行数
  37.      */
  38.     Integer deleteStudent(String sid);
  40. }

StudentServiceImpl

  1. package com.lzl.StudentManage.service.serviceimpl;
  3. import com.lzl.StudentManage.entity.Student;
  4. import com.lzl.StudentManage.service.StudentService;
  5. import com.lzl.StudentManage.util.DBUtil;
  6. import com.lzl.StudentManage.util.StringUtil;
  7. import java.sql.Connection;
  8. import java.sql.PreparedStatement;
  9. import java.sql.ResultSet;
  11. import java.sql.SQLException;
  12. import java.util.ArrayList;
  13. import java.util.List;
  14. import java.util.Map;
  16. public class StudentServiceImpl implements StudentService {
  18.     //获得连接
  19.     private  Connection conn = null;
  20.     //预编译处理对象
  21.     private  PreparedStatement pst = null;
  22.     //结果集
  23.     private ResultSet rts = null;
  24.     //参数集合
  25.     private ArrayList<Object> params = null;
  27.     /**
  28.      * 获得所有用户
  29.      *
  30.      * @param keys 多条件查询，分页参数
  31.      * @return 所有用户集合
  32.      */
  33.     @Override
  34.     public List<Student> getAll(Map<String, Object> keys) {
  36.         //构建数据存放容器
  37.         List<Student> list = new ArrayList<>();
  38.         //构建查询所需参数的容器
  39.         params = new ArrayList<>();
  40.         //初始化sql语句  设置1=1，确保语句能执行不报错
  41.         String sql = "select * from student1 where 1 = 1";
  42.         //拼串，将多条件查询需要的参数全部拼接过来
  43.         //判断不为空，则进行拼接
  44.         if(!StringUtil.isNull(keys.get("sid"))){
  46.             //将sql语句进行拼接，注意and开头有一个空格
  47.             sql+=" and sid = ?";
  48.             //将后边要填补占位符的参数从Map（Controller传过来的参数）集合添加进参数容器
  49.             params.add(keys.get("sid"));
  50.         }
  51.         if(!StringUtil.isNull(keys.get("sname"))){
  53.             //将sql语句进行拼接，注意and开头有一个空格
  54.             sql+=" and sname like ?";
  55.             //将后边要填补占位符的参数从Map（Controller传过来的参数）集合添加进参数容器
  56.             params.add("%"+keys.get("sname")+"%");
  57.         }
  58.         if(!StringUtil.isNull(keys.get("sgender"))){
  60.             sql+=" and sgender = ?";
  61.             params.add(keys.get("sgender"));
  62.         }
  63.         if(!StringUtil.isNull(keys.get("sage"))){
  65.             sql+=" and sage = ?";
  66.             params.add(keys.get("sage"));
  67.         }
  68.         if(!StringUtil.isNull(keys.get("saddress"))){
  70.             sql+=" and saddress like ?";
  71.             params.add("%"+keys.get("saddress")+"%");
  72.         }
  73.         sql+=" order by sid";
  74.         try {
  76.             //获取连接
  77.             conn = DBUtil.getConnection();
  78.             //预编译处理sql
  79.             pst = conn.prepareStatement(sql);
  80.             //设置参数
  81.             for (int i = 0; i < params.size(); i++) {
  83.                 pst.setObject(i+1,params.get(i));
  84.             }
  85.             //执行sql获得数据
  86.             rts = pst.executeQuery();
  87.             //处理数据
  88.             while (rts.next()){
  90.                 //建一个对象
  91.                 Student student = new Student();
  92.                 //将所有结果集中的值取出，赋给对象
  93.                 student.setSid(rts.getInt("sid"));
  94.                 student.setSname(rts.getString("sname"));
  95.                 student.setSgender(rts.getString("sgender"));
  96.                 student.setSage(rts.getInt("sage"));
  97.                 student.setSaddress(rts.getString("saddress"));
  98.                 //将对象放到容器内
  99.                 list.add(student);
  100.             }
  101.         } catch (SQLException e) {
  103.             e.printStackTrace();
  104.         }finally {
  106.             //关流
  107.             DBUtil.closeAll(conn,pst,rts);
  108.         }
  109.         return list;
  110.     }
  112.     /**
  113.      * 新增学生
  114.      *
  115.      * @param student 新增的学生信息
  116.      * @return 受影响行数
  117.      */
  118.     @Override
  119.     public Integer insertStudent(Student student) {
  121.         //给一个默认返回值
  122.         Integer key = 0;
  123.         try {
  125.             //获得连接
  126.             conn = DBUtil.getConnection();
  127.             //写sql语句
  128.             String sql = "insert into student1(sname,sgender,sage,saddress) values (?,?,?,?)";
  129.             //预编译sql
  130.             pst = conn.prepareStatement(sql);
  131.             //将插入的值赋给占位符
  132.             pst.setString(1,student.getSname());
  133.             pst.setString(2,student.getSgender());
  134.             pst.setInt(3,student.getSage());
  135.             pst.setString(4,student.getSaddress());
  136.             //交给executeUpdate执行，key接收受影响行数
  137.             key = pst.executeUpdate();
  138.         } catch (SQLException e) {
  140.             e.printStackTrace();
  141.         }finally {
  143.             //关流
  144.             DBUtil.closeAll(conn,pst,rts);
  145.         }
  146.         return key;
  147.     }
  149.     /**
  150.      *修改
  151.      * @param params 字符串集合
  152.      * @return 受影响行数
  153.      */
  154.     @Override
  155.     public Integer updateStudent(ArrayList<String> params) {
  157.         //给一个默认返回值
  158.         Integer key = 0;
  159.         //编写sql语句
  160.         String sql = "update student1 set sname = ?,sgender = ?," +
  161.                 "sage = ?,saddress = ? where sid = ?";
  162.         //将controller传过来的字符串集合，和sql语句交给DBUtil的方法执行，用key接收受影响行数
  163.         key = DBUtil.executeUpdate(sql,params);
  164.         return key;
  165.     }
  167.     /**删除
  168.      * @param sid 学生学号
  169.      * @return 受影响行数
  170.      */
  171.     @Override
  172.     public Integer deleteStudent(String sid) {
  174.         Integer key = 0;
  175.         if (!StringUtil.isNull(sid)){
  177.              String sql =  String.format("delete from student1 where sid in(%s)",sid);
  178.              key = DBUtil.executeUpdate(sql,params);
  179.         }
  180.         return key;
  181.     }
  182. }

test包

测试类

  1. package com.lzl.StudentManage.test;
  3. import com.lzl.StudentManage.controller.LoginController;
  4. import com.lzl.StudentManage.controller.StudentController;
  5. import java.util.Scanner;
  7. public class WebModel {
  9.     static StudentController studentController = new StudentController();
  10.     static LoginController loginController = new LoginController();
  11.     static Scanner scanner = new Scanner(System.in);
  12.     public static void main(String[] args) {
  14.         System.out.println("============欢迎来到学生管理系统============");
  15.         System.out.println("请您登陆");
  16.         loginController.getLogin();
  17.     }
  19. }

附上效果演示

演示CRUD

发表评论

表情：
评论列表 （有 0 条评论，109人围观）

还没有评论，来说两句吧...

相关阅读

    相关 用户登陆注册【JDBC版】

    前言 > 在讲解Web开发模式的时候，曾经写过XML版的用户登陆注册案例！现在在原有的项目上，使用数据库版来完成用户的登陆注册！如果不了解的朋友，可以看看我Web开发模式

    Bertha 。Bertha 。/ 0 赞/ 525 阅读