package com.qlx.Service;
import com.qlx.DAO.JDDCUtil;
import com.qlx.pojo.Student;
import com.qlx.ui.View;
import java.lang.reflect.Array;
import java.sql.*;
import java.util.*;
public class StudentService {
private static Connection connection;
static {
connection = JDDCUtil.getConnection();
}
//查询学生
public static ArrayList selectAll() throws SQLException {
String sql = "select * from studentsys";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
ArrayList<Student> studentlist = new ArrayList<>();
while (resultSet.next()) {
int studentno = resultSet.getInt("studentno");
String name = resultSet.getString("name");
String sex = resultSet.getString("sex");
String bornday = resultSet.getString("bornday");
String adress = resultSet.getString("adress");
Student student = new Student(name, studentno, sex, bornday, adress);
studentlist.add(student);
}
preparedStatement.close();
return studentlist;
}
//删除学生
public static boolean delect(int no) throws SQLException {
String sql = "delete from studentsys where studentno = ?";
PreparedStatement preparedStatement = null;
try {
preparedStatement = connection.prepareStatement(sql);
} catch (SQLException e) {
throw new RuntimeException(e);
}
preparedStatement.setInt(1, no);
preparedStatement.executeUpdate();
preparedStatement.close();
return true;
}
//增加学生
public static void add(Student student) throws SQLException {
String sql = "insert into studentsys values(?,?,?,?,?)";
PreparedStatement preparedStatement = null;
try {
preparedStatement = connection.prepareStatement(sql);
} catch (SQLException e) {
throw new RuntimeException(e);
}
int studentno = student.getStudentno();
String bornday = student.getBornday();
String sex = student.getSex();
String name = student.getName();
String adress = student.getAdress();
preparedStatement.setInt(1, studentno);
preparedStatement.setString(2, name);
preparedStatement.setString(3, sex);
preparedStatement.setString(4, bornday);
preparedStatement.setString(5, adress);
preparedStatement.executeUpdate();
preparedStatement.close();
}
//修改学生
public static void revise() throws SQLException {
Scanner scanner = new Scanner(System.in);
String nameSql = "update studentsys set " + "`name`" + "= ? where studentno = ?";
String sexSql = "update studentsys set " + "`sex`" + "= ? where studentno = ?";
String bornDaySql = "update studentsys set " + "`bornday`" + "= ? where studentno = ?";
String addressSql = "update studentsys set " + "`adress`" + "= ? where studentno = ?";
String sql1 = "select * from studentsys where studentno = ?";
PreparedStatement preparedStatement = null;
PreparedStatement preparedStatement1 = null;
try {
preparedStatement1 = connection.prepareStatement(sql1);
} catch (SQLException e) {
throw new RuntimeException(e);
}
System.out.println("请输入要修改的学生学号");
int no = scanner.nextInt();
preparedStatement1.setInt(1, no);
ResultSet resultSet = preparedStatement1.executeQuery();
if (resultSet.next()) {
while (true) {
System.out.println("1.姓名, 2.性别 ,3.出生日期 ,4.地址 ,5.结束返回主菜单");
int x = scanner.nextInt();
switch (x) {
case 1:
preparedStatement = connection.prepareStatement(nameSql);
preparedStatement.setInt(2, no);
System.out.println("亲输入新的姓名");
String name = scanner.next();
preparedStatement.setString(1, name);
break;
case 2:
preparedStatement = connection.prepareStatement(sexSql);
preparedStatement.setInt(2, no);
System.out.println("亲输入新的性别");
String sex = scanner.next();
preparedStatement.setString(1, sex);
break;
case 3:
preparedStatement = connection.prepareStatement(bornDaySql);
preparedStatement.setInt(2, no);
System.out.println("亲输入新的出生日期");
String bornday = scanner.next();
preparedStatement.setString(1, bornday);
break;
case 4:
preparedStatement = connection.prepareStatement(addressSql);
preparedStatement.setInt(2, no);
System.out.println("亲输入新的地址");
String adress = scanner.next();
preparedStatement.setString(1, adress);
break;
case 5:
new View();
break;
}
preparedStatement.executeUpdate();
preparedStatement.close();
}
} else {
System.out.println("可能输入了错误的学号,请重新输入");
revise();
}
}
}