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() + ' ' + s.getSname() + ' ' + s.GetClass() + ' ' + s.getAge() + ' ' + 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() + ' ' + s.getSname() + ' ' + s.GetClass() + ' ' + s.getAge() + ' ' + 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() + ' ' + s.getSname() + ' ' + s.GetClass() + ' ' + s.getAge() + ' ' + s.getSex());
}
break;
case 0:
System.out.println('程序已退出！');
break;
default:
System.out.println('输入有误，请重新输入！');
break;
}
}
}
