【数据库】学生管理系统

package com.situ.student.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;
public class JDBCUtil {
  private static ResourceBundle rb = ResourceBundle.getBundle("com.situ.student.util.JDBC");
  private static String cLassName = rb.getString("driver");
  private static String url = rb.getString("url");
  private static String user = rb.getString("user");
  private static String password = rb.getString("pass");
  // 1- 加载驱动
  static {
    try {
      Class.forName(cLassName);
    } catch (Exception e) {
      e.getCause();
    }
  }
  // 2- 获得链接
  public static Connection getConnection() throws SQLException {
    return DriverManager.getConnection(url, user, password);
  }
  public static void close(Connection conn, Statement st, ResultSet rs) {
    try {
      if (rs != null) {
        rs.close();
        rs = null;
      }
      if (st != null) {
        st.close();
        st = null;
      }
      if (conn != null) {
        conn.close();
        conn = null;
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
  public static void close(Connection conn, Statement st) {
    close(conn, st, null);
  }
  public static void main(String[] args) throws SQLException {
    // 加载驱动 + 获得链接
    Connection con = JDBCUtil.getConnection();
    System.out.println(con);
    JDBCUtil.close(con, null); // 关闭的是插入的
  }
  private JDBCUtil() {
  }
}

pass=123456
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3308/demo01?useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=Hongkong&allowPublicKeyRetrieval=true

package com.situ.student.student.model;
public class StudentModel {
  private String id;
  private String name;
  private String age;
  private String profession;
  public String getId() {
    return id;
  }
  public void setId(String id) {
    this.id = id;
  }
  public String getName() {
    return name;
  }
  public void setName(String name) {
    this.name = name;
  }
  public String getAge() {
    return age;
  }
  public void setAge(String age) {
    this.age = age;
  }
  public String getProfession() {
    return profession;
  }
  public void setProfession(String profession) {
    this.profession = profession;
  }
  @Override
  public String toString() {
    return "StudentModel [id=" + id + ", name=" + name + ", age=" + age + ", profession=" + profession + "]";
  }
}

package com.situ.student.student.dao;
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 com.situ.student.student.model.StudentModel;
import com.situ.student.util.JDBCUtil;
public class StudentDao {
  private Connection connection = null;
  private PreparedStatement ps = null;
  // 增加
  public String insert(StudentModel model) {
    String sql = "insert into student(s_id, name, age, profession) value(?,?,?,?)";
    try {
      connection = JDBCUtil.getConnection(); // 获得链接
      ps = connection.prepareStatement(sql); // 执行SQL
      ps.setString(1, model.getId()); // 填充问号
      ps.setString(2, model.getName());
      ps.setString(3, model.getAge());
      ps.setString(4, model.getProfession());
      int res = ps.executeUpdate(); // 返回改变的记录条数
      return res + "";
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      JDBCUtil.close(connection, ps);
    }
    return -1 + "";
  }
  // 删除
  public String delete(StudentModel model) {
    String sql = "delete from student where  s_id = ?";
    try {
      connection = JDBCUtil.getConnection();
      ps = connection.prepareStatement(sql);
      ps.setString(1, model.getId());
      int res = ps.executeUpdate();
      return res + "";
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtil.close(connection, ps);
    }
    return -1 + "";
  }
  // 修改
  public String update(StudentModel model) {
    String sql = "update student set profession = ? where s_id = ?";
    try {
      connection = JDBCUtil.getConnection();
      ps = connection.prepareStatement(sql);
      ps.setString(1, model.getProfession());
      ps.setString(2, model.getId());
      int res = ps.executeUpdate();
      return res + "";
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtil.close(connection, ps);
    }
    return -1 + "";
  }
  // 查询
  public List<StudentModel> select(StudentModel model) {
    StringBuffer sql = new StringBuffer("select s_id, name, age, profession from student where 1 = 1");
    List<Object> list = new ArrayList<Object>();
    list = where(sql, model);
    List<StudentModel> list2 = new ArrayList<StudentModel>();
    ResultSet set = null;
    try {
      connection = JDBCUtil.getConnection();
      ps = connection.prepareStatement(sql.toString());
      for (int i = 0; i < list.size(); i++) {
        ps.setObject(i + 1, list.get(i));
      }
      set = ps.executeQuery();
      while (set.next()) {
        StudentModel model1 = new StudentModel();
        model1.setId(set.getString("s_id"));
        model1.setName(set.getString("name"));
        model1.setAge(set.getString("age"));
        model1.setProfession(set.getString("profession"));
        list2.add(model1);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtil.close(connection, ps);
    }
    return list2;
  }
  public List<Object> where(StringBuffer sql, StudentModel model) {
    String s_id = model.getId();
    List<Object> list = new ArrayList<Object>();
    if (s_id != null && !s_id.trim().isEmpty()) {
      sql.append(" and s_id=?");
      list.add(s_id);
    }
    String name = model.getName();
    if (name != null && !name.trim().isEmpty()) {
      sql.append(" and name = ?");
      list.add(name);
    }
    String age = model.getAge();
    if (age != null && !age.trim().isEmpty()) {
      sql.append(" and age = ?");
      list.add(age);
    }
    String profession = model.getProfession();
    if (profession != null && !profession.trim().isEmpty()) {
      sql.append(" and profession = ?");
      list.add(profession);
    }
    return list;
  }
}

package com.situ.student.student.service;
import java.util.ArrayList;
import java.util.List;
import com.situ.student.student.dao.StudentDao;
import com.situ.student.student.model.StudentModel;
public class StudentService {
  private StudentDao dao = new StudentDao();
  public String insert(StudentModel model) {
    StudentModel model2 = new StudentModel();
    model2.setId(model.getId());
    List<StudentModel> list = new ArrayList<StudentModel>();
    list = dao.select(model);
    if (list == null || list.isEmpty()) {
      dao.insert(model);
      return "插入成功！";
    }
    return "已经存在！";
  }
  public String delete(StudentModel model) {
    return dao.delete(model);
  }
  public String update(StudentModel model) {
    return dao.update(model);
  }
  public List<StudentModel> selectList(StudentModel model) {
    return dao.select(model);
  }
  public StudentModel selcetModel(StudentModel model) {
    StudentModel model2 = new StudentModel();
    model2.setId(model.getId());
    List<StudentModel> list = new ArrayList<StudentModel>();
    list = dao.select(model);
    if (list == null || list.isEmpty()) {
      return null;
    }
    return list.get(0);
  }
}

package com.situ.student.student.studenttest;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
import com.situ.student.student.model.StudentModel;
import com.situ.student.student.service.StudentService;
public class StudentTset {
  private Scanner scanner = new Scanner(System.in);
  boolean p = true;
  private StudentService service = new StudentService();
  public static void main(String[] args) {
    StudentTset test = new StudentTset();
    test.start();
  }
  public void start() {
    while (p) {
      System.out.println("1. 添加学生信息");
      System.out.println("2. 按学生姓名查询学生信息");
      System.out.println("3. 修改学生的专业(转专业)");
      System.out.println("4. 按专业查询学生信息");
      System.out.println("5. 查询所有人的信息");
      System.out.println("6. 退出");
      System.out.println("请输入你要");
      String string = scanner.nextLine();
      sart_1(string);
    }
  }
  public void sart_1(String string) {
    if (string.equals("1")) {
      addStudent();
    } else if (string.equals("2")) {
      queryNameStudent();
    } else if (string.equals("3")) {
      setStudentProfessional();
    } else if (string.equals("4")) {
      queryProfessionalStudent();
    } else if (string.equals("5")) {
      querySumInformation();
    } else if (string.equals("6")) {
      p = false;
      System.out.println("已为你退出系统！");
    } else {
      System.out.println("你的输入有误！");
    }
  }
  public void addStudent() {
    StudentModel model = new StudentModel();
    System.out.println("请输入学生的编号：");
    String id = scanner.nextLine();
    System.out.println("请输入学生的名字：");
    String name = scanner.nextLine();
    System.out.println("请输入学生的年龄：");
    String age = scanner.nextLine();
    System.out.println("请输入学生的专业：");
    String profession = scanner.nextLine();
    model.setId(id);
    model.setName(name);
    model.setAge(age);
    model.setProfession(profession);
    System.out.println(service.insert(model));
  }
  // 按名字进行查询
  public void queryNameStudent() {
    System.out.println("请输入你要查询的名字：");
    String name = scanner.nextLine();
    StudentModel model = new StudentModel();
    model.setName(name);
    List<StudentModel> list = new ArrayList<StudentModel>();
    list = service.selectList(model);
    for (int i = 0; i < list.size(); i++) {
      System.out.println(list.get(i));
    }
  }
  public void querySumInformation() {
    StudentModel model = new StudentModel();
    List<StudentModel> list = new ArrayList<StudentModel>();
    list = service.selectList(model);
    for (int i = 0; i < list.size(); i++) {
      System.out.println(list.get(i));
    }
  }
  public void setStudentProfessional() {
    StudentModel model = new StudentModel();
    System.out.println("请输入你要修改的学生编号：");
    String id = scanner.nextLine();
    model.setId(id);
    System.out.println("请输入你要转的专业：");
    String profession = scanner.nextLine();
    model.setProfession(profession);
    service.update(model);
  }
  public void queryProfessionalStudent() {
    System.out.println("请输入你要查询的专业：");
    StudentModel model = new StudentModel();
    String profession = scanner.nextLine();
    model.setProfession(profession);
    List<StudentModel> list = new ArrayList<StudentModel>();
    list = service.selectList(model);
    for (int i = 0; i < list.size(); i++) {
      System.out.println(list.get(i));
    }
  }
}