简易考生管理系统

主程序

class Main

public class Main {

public static void main(String[] args) {

System.out.println("考生信息管理系统");

System.out.println("1 添加学生 2 删除学生 3 更改学校资料 4 显示所有学生 5 获取所有学生总成绩 6 查询高于平均分的所有学生 7 退出");

boolean valve = true;

while (valve) {

System.out.println("请输入 指令");

int instruction = TheexamineeUtil.Keyinteger();

switch (instruction) {

case 1:

StudentDAO.addStudent();

break;

case 2:

System.out.println("请输入需要删除的学习考号");

TheexamineeUtil.getInstance().deleteStudentById(TheexamineeUtil.Keyinteger());

break;

case 3:

StudentDAO.updataStudent();

break;

case 4:

StudentDAO.showStudent();

break;

case 5:

System.out.println("学生总成绩:" + TheexamineeUtil.getInstance().getSum());

break;

case 6:

TheexamineeUtil.getInstance().getAllStudent();

break;

case 7:

valve = false;

break;

}

}

}

}

学生类 Student

public class Student {

private int number;

private String name;

private int grade;

public int getNumber() {

return number;

}

public void setNumber(int number) {

this.number = number;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public int getGrade() {

return grade;

}

public void setGrade(int grade) {

this.grade = grade;

}

@Override

public String toString() {

return name+"\t"+number+"\t"+grade;

}

}

工具类  TheexamineeUtil

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


import cn.yzk.mysql.CourseDB;


public class TheexamineeUtil {
private static TheexamineeUtil theexamineeUtil = null;


public static TheexamineeUtil getInstance() {
if (theexamineeUtil == null) {
theexamineeUtil = new TheexamineeUtil();
}
return theexamineeUtil;
}


CourseDB courseDB = new CourseDB();


public void AddStudnet(Student stundet) {
Connection conn = courseDB.getConnection();
String sql = "insert into tab_ks (number,name,grade)" + "values(?,?,?)";
try {
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setInt(1, stundet.getNumber());
preparedStatement.setString(2, stundet.getName());
preparedStatement.setInt(3, stundet.getGrade());
preparedStatement.execute();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}


public List<Student> printMessageAllStudent() {
// System.out.println("编号" + "\t" + "姓名" + "\t" + "成绩" + "\t");
List<Student> studnetlist = new ArrayList<Student>();
String sql = "SELECT * FROM tab_ks";
Connection conn = courseDB.getConnection();
Statement statement = null;
try {
statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery(sql);


while (resultSet.next()) {
Student student = new Student();
student.setNumber(resultSet.getInt(2));
student.setName(resultSet.getString(3));
student.setGrade(resultSet.getInt(4));
studnetlist.add(student);
// int id=resultSet.getInt(1);
// int number = resultSet.getInt(2);
// String name = resultSet.getString(3);
// int grade = resultSet.getInt(4);
// System.out.println(number + "\t" + name + "\t" + grade +
// "\t");


}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
statement.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return studnetlist;
}


public void updateStudentByName(Student student, String name) {
Connection conn = courseDB.getConnection();
String sql = "UPDATE tab_ks set number=?,name=?,grade=? where name=?";
PreparedStatement preparedStatement = null;
try {
preparedStatement = conn.prepareStatement(sql);
preparedStatement.setInt(1, student.getNumber());
preparedStatement.setString(2, student.getName());
preparedStatement.setInt(3, student.getGrade());
preparedStatement.setString(4, name);
preparedStatement.execute();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}


public void deleteStudentById(int number) {
String sql = "DELETE FROM tab_ks WHERE number=?";
Connection conn = courseDB.getConnection();
PreparedStatement preparedStatement = null;
try {
preparedStatement = conn.prepareStatement(sql);
preparedStatement.setInt(1, number);
preparedStatement.execute();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}


public int getSum() {
Connection conn = courseDB.getConnection();
String sql = "select SUM(grade) from tab_ks;";
Statement statement = null;
ResultSet resultSet = null;
int sum = 0;
try {
statement = conn.createStatement();
resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
sum = resultSet.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
return sum;
}


// 方法 List<Student> getAllStudent()
// 功能：查询高于平均分的所有学生
@SuppressWarnings("unused")
public List<Student> getAllStudent() {
Connection conn = courseDB.getConnection();
String sql = "select name from tab_ks where grade>(select AVG(grade) from tab_ks);";
Statement statement = null;
ResultSet resultSet = null;
try {
statement = conn.createStatement();
resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
Student student = new Student();
System.out.println(resultSet.getString(1) + "  成绩高于平均分");


}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}


public static int Keyinteger() {
InputStreamReader isr = new InputStreamReader(System.in);
BufferedReader br = new BufferedReader(isr);
int i = 0;
try {
String a = br.readLine();
i = Integer.parseInt(a);
} catch (IOException e) {
System.out.println("你输入的不是数字");
} catch (NumberFormatException e) {
System.out.println("你输入的不是数字");
}
return i;
}


public static String KeyString() {
InputStreamReader isr = new InputStreamReader(System.in);
BufferedReader br = new BufferedReader(isr);
String string = null;
try {
string = br.readLine();
} catch (IOException e) {
System.out.println("输入字符错误");
}
return string;
}


public boolean selectStudent(String newStudent) {
boolean judge = false;
String sql = "SELECT name FROM tab_ks";
Connection conn = courseDB.getConnection();
Statement statement = null;
try {
statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
String name = resultSet.getString(1);
if (name.equals(newStudent)) {
judge = true;
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
statement.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return judge;
}


public static void main(String[] args) {


}
}

JDBCDrive 类

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class CourseDB {
private static final String URL = "jdbc:mysql://localhost:3306/HomeWork";
private static final String USER = "root";
private static final String PASSWORD = "123";
@SuppressWarnings("unused")
private static Connection conn = null;
@SuppressWarnings("unused")
private static ResultSet resultSet = null;
@SuppressWarnings("unused")
private static Statement statement = null;


public CourseDB() {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}


//  建立数据库连接
public Connection getConnection() {
Connection conn = null;
try {
conn = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}


public void createCourseTable() {
// String sql = "create table course2(" + "id int(10) not null," + "course_name varchar(10) not null,"
// + "course_number int(10) not null," + "course_description varchar(100) not null,"
// + "course_time int(10) default 100)";
// conn = getConnection();
//
// try {
// statement = conn.createStatement();
// statement.execute(sql);
// } catch (SQLException e) {
// e.printStackTrace();
// }


}


public void addCourse(int id, String name, int number, String description, int time) {
// String sql = "insert into course2 (id,course_name,course_number,course_description,course_time)"
// + "values(?,?,?,?,?)";
// conn = getConnection();
// try {
// PreparedStatement preparedStatement = conn.prepareStatement(sql);
// preparedStatement.setInt(1, id);
// preparedStatement.setString(2, name);
// preparedStatement.setInt(3, number);
// preparedStatement.setString(4, description);
// preparedStatement.setInt(5, time);
// preparedStatement.execute();
// } catch (SQLException e) {
// e.printStackTrace();
// } finally {
// try {
// conn.close();
// } catch (SQLException e) {
// e.printStackTrace();
// }
// }
}


// public void deleteCourse(int id) {
// String sql = "delete from course2 where id=?;";
// conn = getConnection();
// try {
// PreparedStatement preparedStatement = conn.prepareStatement(sql);
// preparedStatement.setInt(1, id);
// preparedStatement.execute();
// } catch (SQLException e) {
// e.printStackTrace();
// } finally {
// try {
// conn.close();
// } catch (SQLException e) {
// e.printStackTrace();
// }
// }
//
// }


public void updateCourse_name(String Course_name, String NewewCourse_name) {
// String sql = "update course2 set course_name=? where course_name=?";
// conn = getConnection();
// PreparedStatement preparedStatement = null;
// try {
// preparedStatement = conn.prepareStatement(sql);
// preparedStatement.setString(1, NewewCourse_name);
// preparedStatement.setString(2, Course_name);
// preparedStatement.execute();
// } catch (SQLException e1) {
// e1.printStackTrace();
// }
// try {
// conn.close();
// } catch (SQLException e) {
// e.printStackTrace();
// }


}


public void selectCourse() {
// String sql = "select * from course2";
// conn = getConnection();
// Statement statement = null;
// try {
// statement = conn.createStatement();
// resultSet = statement.executeQuery(sql);
// System.out.println("id" + "\t" + "课程名字" + "\t" + "课程编号" + "\t" + "课程介绍" + "\t" + "课程时长");
// while (resultSet.next()) {
// int id = resultSet.getInt(1);
// String name = resultSet.getString(2);
// int number = resultSet.getShort(3);
// String descript = resultSet.getString(4);
// int time = resultSet.getInt(5);
// System.out.println(id + "\t" + name + "\t" + number + "\t" + descript + "\t" + time);
// }
// } catch (SQLException e) {
// e.printStackTrace();
// } finally {
// try {
// statement.close();
// conn.close();
// } catch (SQLException e) {
// e.printStackTrace();
// }
// }


}


}