import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
public class Student {
public static void main(String args[]) {
try {
Class.forName("com.mysql.jdbc.Driver"); //加载MYSQL JDBC驱动程序
// System.out.println("Success loading Mysql Driver!");
}
catch (Exception e) {
System.out.print("Error loading Mysql Driver!");
e.printStackTrace();
}
menu();
}
public static void menu() {
System.out.println("******************学生个人成绩管理系统***********");
System.out.printf("\n", null);
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.printf("\n", null);
System.out.println("************************************************");
System.out.println("请输入功能编号(1-6)：");
Scanner r0 = new Scanner(System.in);
int i = r0.nextInt();
switch(i){
case 1:
add();
try {
Thread.sleep(2000);
} catch (InterruptedException e) {
e.printStackTrace();
}
menu();
break;
case 2:
update();
try {
Thread.sleep(2000);
} catch (InterruptedException e) {
e.printStackTrace();
}
break;
case 3:
del();
try {
Thread.sleep(2000);
} catch (InterruptedException e) {
e.printStackTrace();
}
menu();
break;
case 4:
find();
try {
Thread.sleep(2000);
} catch (InterruptedException e) {
e.printStackTrace();
}
menu();
break;
case 5:
showall();
try {
Thread.sleep(2000);
} catch (InterruptedException e) {
e.printStackTrace();
}
menu();
break;
case 6: break;
default:
System.out.println("请输入数字1到6");
try {
Thread.sleep(2000);
} catch (InterruptedException e) {
e.printStackTrace();
}
break;
}
r0.close();
}
public static void add(){
Scanner sc = new Scanner(System.in);
System.out.println("请输入新的学生信息，输入格式为学号，姓名，分数。如：201220201032 xu 78.5");
String input =sc.nextLine();
String[] additem=input.split(" ");
if(additem.length==3){
try {
Connection connect = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/stumana","root","root");
Statement stmt = connect.createStatement();
int rs = stmt.executeUpdate("insert into student (id,name,grades) values( '"+additem[0]+"', '"+additem[1]+"' ,'"+additem[2]+"')");
if(rs==1)
System.out.println("添加成功！");
}
catch (Exception e) {
System.out.print("get data error!");
e.printStackTrace();
}
}
}
public static void update(){
Scanner sc = new Scanner(System.in);
System.out.println("请输入学生编号");
String id=sc.nextLine();
try {
Connection connect = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/stumana","root","root");
Statement stmt = connect.createStatement();
ResultSet rs = stmt.executeQuery("select * from student where name='"+id+"'");
if(rs!=null){
System.out.println("请输入新的学生信息，输入格式为学号，姓名，分数。如：201220201032 xu 78.5");
String input =sc.nextLine();
String[] additem=input.split(" ");
if(additem.length==3){
int rs1 = stmt.executeUpdate("update student set id= '"+additem[0]+"', name='"+additem[1]+"' ,grades='"+additem[2]+"'where id='"+id+"'");
if(rs1==1)
System.out.println("修改成功！");
}
}
free(null,stmt ,connect);
}
catch (Exception e) {
System.out.print("get data error!");
e.printStackTrace();
}
}
public static void del(){
Scanner sc = new Scanner(System.in);
System.out.println("请输入学生编号");
String id=sc.nextLine();
try {
Connection connect = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/stumana","root","root");
Statement stmt = connect.createStatement();
int rs = stmt.executeUpdate("delete from student where id='"+id+"'");
if(rs==1)
System.out.println("删除成功！");
free(null,stmt ,connect);
}
catch (Exception e) {
System.out.print("get data error!");
e.printStackTrace();
}
}
public static void find(){
Scanner sc = new Scanner(System.in);
System.out.println("请输入学生姓名");
String id=sc.nextLine();
try {
Connection connect = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/stumana","root","root");
Statement stmt = connect.createStatement();
ResultSet rs = stmt.executeQuery("select * from student where name='"+id+"'");
System.out.print("id"+" ");
System.out.print("姓名"+" ");
System.out.println("成绩");
while (rs.next()) {
System.out.print(rs.getString("id")+" ");
System.out.print(rs.getString("name")+" ");
System.out.println(rs.getFloat("grades"));
}
free(rs,stmt ,connect);
}
catch (Exception e) {
System.out.print("get data error!");
e.printStackTrace();
}
}
public static void showall(){
try {
Connection connect = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/stumana","root","root");
//连接URL为 jdbc:mysql//服务器地址/数据库名 ，后面的2个参数分别是登陆用户名和密码
// System.out.println("Success connect Mysql server!");
Statement stmt = connect.createStatement();
ResultSet rs = stmt.executeQuery("select * from student");
System.out.print("id"+" ");
System.out.print("姓名"+" ");
System.out.println("成绩");
while (rs.next()) {
System.out.print(rs.getString("id")+" ");
System.out.print(rs.getString("name")+" ");
System.out.println(rs.getFloat("grades"));
}
free(rs,stmt ,connect);
}
catch (Exception e) {
System.out.print("get data error!");
e.printStackTrace();
}
}
public static void free(ResultSet resultset,Statement st,Connection conn)
{
try{
if(resultset!=null)
resultset.close();
} catch (Exception e) {
e.printStackTrace();
}
finally
{
try
{
if(st!=null)
st.close();
} catch (Exception e) {
e.printStackTrace();
}
finally
{
if(conn!=null)
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
