import java.io.;
import java.sql.;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Scanner;
import java.util.ArrayList;
import java.util.List;
class Student {
private String Sno;
private String Sname;
private String Class;
private int Age;
private String Sex;
public String getSno() {
return Sno;
}
public void setSno(String sno) {
this.Sno = sno;
}
public String getSname() {
return Sname;
}
public void setSname(String sname) {
this.Sname = sname;
}
public String GetClass() {
return Class;
}
public void setClass(String classname) {
this.Class = classname;
}
public int getAge() {
return Age;
}
public void setAge(int age) {
this.Age = age;
}
public String getSex() {
return Sex;
}
public void setSex(String sex) {
this.Sex = sex;
}
}
class JDBC{
static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";//加载驱动
static final String DB_URL = "jdbc:mysql://localhost:3306/RUNOOB?useSSL=false&serverTimezone=UTC";//URL
private static final String USERNAME = "root";//用户名
private static final String PASSWORD = "mfy200314";//用户密码
private static Connection conn = null;//初始化
private static PreparedStatement pstmt = null;//创建预处理对象preparation
private static ResultSet rs = null;//ResultSet接口类似于一个临时表，用来暂时存放数据库查询操作所获得的结果集。
// ResultSet实例具有指向当前数据行的指针，
// 指针开始的位置在第一条记录的前面，通过next()方法可以将指针向下移。
// 连接数据库
public static void getConnection() {
try {
Class.forName(JDBC_DRIVER);// 加载数据库驱动类
conn = DriverManager.getConnection(DB_URL, USERNAME, PASSWORD);// 获取数据库连接对象
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 关闭数据库连接
public static void closeConnection() {
try {
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
// 插入学生信息
public static boolean insertStudent(Student student) {
boolean flag = false;
getConnection();
// SQL语句:
String sql = "insert into Student(Sno, Sname, Class, Age, Sex) values(?, ?, ?, ?, ?)";
// 创建PreparedStatement对象
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, student.getSno());
pstmt.setString(2, student.getSname());
pstmt.setString(3, student.GetClass());
pstmt.setInt(4, student.getAge());
pstmt.setString(5, student.getSex());
int result = pstmt.executeUpdate();
if (result > 0) {
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeConnection();
}
return flag;
}
// 修改学生信息
public static boolean updateStudent(Student student) {
boolean flag = false;
getConnection();
String sql = "update Student set Sname=?, Class=?, Age=?, Sex=? where Sno=?";
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, student.getSname());
pstmt.setString(2, student.GetClass());
pstmt.setInt(3, student.getAge());
pstmt.setString(4, student.getSex());
pstmt.setString(5, student.getSno());
int result = pstmt.executeUpdate();
if (result > 0) {
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeConnection();
}
return flag;
}
// 删除学生信息
public static boolean deleteStudent(String sno) {
boolean flag = false;
getConnection();
String sql = "delete from Student where Sno=?";
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, sno);
int result = pstmt.executeUpdate();
if (result > 0) {
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeConnection();
}
return flag;
}
// 按照性别查找学生信息
public static List findStudentBySex(String sex) {
List list = new ArrayList();
getConnection();
String sql = "select * from Student where Sex=?";
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, sex);
rs = pstmt.executeQuery();
while (rs.next()) {
Student student = new Student();
student.setSno(rs.getString("Sno"));
student.setSname(rs.getString("Sname"));
student.setClass(rs.getString("Class"));
student.setAge(rs.getInt("Age"));
student.setSex(rs.getString("Sex"));
list.add(student);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeConnection();
}
return list;
}
// 按照年龄段查找学生信息
public static List findStudentByAge(int minAge, int maxAge) {
List list = new ArrayList();
getConnection();
String sql = "select * from Student where Age>=? and Age<=?";
try {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, minAge);
pstmt.setInt(2, maxAge);
rs = pstmt.executeQuery();
while (rs.next()) {
Student student = new Student();
student.setSno(rs.getString("Sno"));
student.setSname(rs.getString("Sname"));
student.setClass(rs.getString("Class"));
student.setAge(rs.getInt("Age"));
student.setSex(rs.getString("Sex"));
list.add(student);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeConnection();
}
return list;
}
// 按照姓氏查找学生信息
public static List findStudentByLastName(String lastName) {
List list = new ArrayList();
getConnection();
String sql = "select * from Student where Sname like ?";
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, lastName + "%");
rs = pstmt.executeQuery();
while (rs.next()) {
Student student = new Student();
student.setSno(rs.getString("Sno"));
student.setSname(rs.getString("Sname"));
student.setClass(rs.getString("Class"));
student.setAge(rs.getInt("Age"));
student.setSex(rs.getString("Sex"));
list.add(student);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeConnection();
}
return list;
}
}
public class StudentManagementSystem {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
JDBC dao = new JDBC();
String menu = "请选择操作： " + "1. 输入学生信息 " + "2. 修改学生信息 " + "3. 删除学生信息 " + "4. 按照性别查找学生信息 " + "5. 按照年龄段查找学生信息 " + "6. 按照姓氏查找学生信息 " + "0. 退出程序 ";
int choice = -1;
while (choice != 0) {
System.out.println(menu);
choice = sc.nextInt();
switch (choice) {
case 1:
System.out.println("请输入学生信息：");
Student student = new Student();
System.out.print("学号：");
student.setSno(sc.next());
System.out.print("姓名：");
student.setSname(sc.next());
System.out.print("班级：");
student.setClass(sc.next());
System.out.print("年龄：");
student.setAge(sc.nextInt());
System.out.print("性别：");
student.setSex(sc.next());
if (JDBC.insertStudent(student)) {
System.out.println("添加学生信息成功！");
} else {
System.out.println("添加学生信息失败！");
}
break;
case 2:
System.out.println("请输入要修改的学生信息：");
Student student2 = new Student();
System.out.print("学号：");
student2.setSno(sc.next());
System.out.print("姓名：");
student2.setSname(sc.next());
System.out.print("班级：");
student2.setClass(sc.next());
System.out.print("年龄：");
student2.setAge(sc.nextInt());
System.out.print("性别：");
student2.setSex(sc.next());
if (JDBC.updateStudent(student2)) {
System.out.println("修改学生信息成功！");
} else {
System.out.println("修改学生信息失败！");
}
break;
case 3:
System.out.println("请输入要删除的学生学号：");
String sno = sc.next();
if (JDBC.deleteStudent(sno)) {
System.out.println("删除学生信息成功！");
} else {
System.out.println("删除学生信息失败！");
}
break;
case 4:
System.out.println("请输入性别：");
String sex = sc.next();
List list1 = JDBC.findStudentBySex(sex);
System.out.println("学号 姓名 班级 年龄 性别");
for (Student s : list1) {
System.out.println(s.getSno() + "\t" + s.getSname() + "\t" + s.GetClass() + "\t" + s.getAge() + "\t" + s.getSex());
}
break;
case 5:
System.out.println("请输入年龄范围（如[19,21]）：");
String range = sc.next();
int minAge = Integer.parseInt(range.substring(1, 3));
int maxAge = Integer.parseInt(range.substring(4, 6));
List list2 = JDBC.findStudentByAge(minAge, maxAge);
System.out.println("学号 姓名 班级 年龄 性别");
for (Student s : list2) {
System.out.println(s.getSno() + "\t" + s.getSname() + "\t" + s.GetClass() + "\t" + s.getAge() + "\t" + s.getSex());
}
break;
case 6:
System.out.println("请输入姓氏：");
String lastName = sc.next();
List list3 = JDBC.findStudentByLastName(lastName);
System.out.println("学号 姓名 班级 年龄 性别");
for (Student s : list3) {
System.out.println(s.getSno() + "\t" + s.getSname() + "\t" + s.GetClass() + "\t" + s.getAge() + "\t" + s.getSex());
}
break;
case 0:
System.out.println("程序已退出！");
break;
default:
System.out.println("输入有误，请重新输入！");
break;
}
}
}
