java实验(3)

学生信息管理系统。
学生成绩表Student(Sno字符串,长度9, Sname字符串,长度10, Class 字符串，长度10，Age 整型，Sex字符串，长度2)
实现如下功能:
A.输入若干个学生的信息到Student表;
B.修改指定学号学生的基本信息;
C. 删除指定学号的学生基本信息;
D.按照性别找出所有相应的学生基本信息;
E.按照年龄段找出所有相应的学生基本信息，例如[19, 21]的学生;
F.按照专业查询所有学生的基本信息;
G.以上功能用菜单形式实现功能选择

package lab3;
import java.util.Scanner;
import java.sql.*;
public class Student_management_system {

	public static void main(String[]args)throws SQLException
	{
		 String No;
		 Connection conn = null;
		 Scanner cin = new Scanner(System.in);
		 try
		 {
			 Class.forName("com.mysql.jdbc.Driver");
	    	 String url = "jdbc:mysql://localhost:3306/数据库名称";
	    	 String username = "xxx";
	    	 String password = "xxxxx";
	    	 conn = DriverManager.getConnection(url, username, password); 
			 while(true)
			 {
				 show();
				 System.out.println("输入编号");
				 No = cin.nextLine();
				 while(!Check(No))
				 {
					 Waring();
					 Again();
					 No = cin.nextLine();
				 }
				 if(No.charAt(0) == '1')
				 {
					 PreparedStatement prestmt  = null;
					 Fun1();
					 String Sno,Sname,Class,Sex;
					 int Age;
					 System.out.println("请输入 学生学号");
					 Sno = cin.nextLine();
					 System.out.println("请输入 学生姓名");
					 Sname = cin.nextLine();
					 System.out.println("请输入 学生班级");
					 Class = cin.nextLine();
					 System.out.println("请输入 学生性别");
					 Sex = cin.nextLine();
					 System.out.println("请输入 学生年龄");
					 Age = cin.nextInt();
					 String sql = "INSERT INTO Student VALUES(?,?,?,?,?)";
					 prestmt = conn.prepareStatement(sql);
					 prestmt.setString(1, Sno);
					 prestmt.setString(2, Sname);
					 prestmt.setString(3, Class);
					 prestmt.setInt(4, Age);
					 prestmt.setString(5, Sex);
                     prestmt.executeUpdate();	
                     prestmt.close();
                     
				 }
				 else if(No.charAt(0) == '2')
				 {
					 PreparedStatement prestmt  = null;
					 Fun2();
					 String Sno,Sname,Class,Sex;
					 int Age;
					 System.out.println("请输入 学生学号");
					 Sno = cin.nextLine();
					 System.out.println("请输入 学生姓名");
					 Sname = cin.nextLine();
					 System.out.println("请输入 学生班级");
					 Class = cin.nextLine();
					 System.out.println("请输入 学生性别");
					 Sex = cin.nextLine(); 
					 System.out.println("请输入 学生年龄"); 
					 Age = cin.nextInt();
					 String sql = "UPDATE Student "
					 		+ "SET Sname = ?,Class = ?,Age = ?,Sex = ? "
					 		+ "WHERE Sno = ?";
					 prestmt = conn.prepareStatement(sql);
					 prestmt.setString(1, Sname);
					 prestmt.setString(2, Class);
					 prestmt.setInt(3, Age);
					 prestmt.setString(4, Sex);
					 prestmt.setString(5, Sno);
                     prestmt.executeUpdate();	
                     prestmt.close();
                     System.out.println("修改成功");
				 }
				 else  if(No.charAt(0) == '3')
				 {
					 PreparedStatement prestmt  = null;
					 Fun3();
					 String Sno;
					 System.out.println("请输入 学生学号");
					 Sno = cin.nextLine();
					 String sql = "DELETE "
					 		+ "FROM Student "
					 		+ " WHERE Sno = ?";
					 prestmt = conn.prepareStatement(sql);
					 prestmt.setString(1, Sno);
                     prestmt.executeUpdate();	
                     prestmt.close();
                     System.out.println("删除完毕");
				 }
				 else  if(No.charAt(0) == '4')
				 {
					 Fun4();
					 String Sex;
					 ResultSet rs = null;
					 PreparedStatement prestmt  = null;
					 System.out.println("请输入 性别");
					 Sex = cin.nextLine();
					 String sql = "select * from Student where Sex = ?";
					 prestmt = conn.prepareStatement(sql);
					 prestmt.setString(1, Sex);
					 rs = prestmt.executeQuery();
					 while(rs.next())
					 {
						 String Sno = rs.getString("Sno");
						 String Sname = rs.getString("Sname");
						 String Class = rs.getString("Class");
						 int Age = rs.getInt("Age");
					     System.out.println(Sno + "   " + Sname + "   " + Class + "   " + Age + "   " + Sex);
						 
					 }
					 prestmt.close();
					 rs.close();
				 }
				 else  if(No.charAt(0) == '5')
				 {
					 Fun5();
					 int Age1,Age2;
					 ResultSet rs = null;
					 PreparedStatement prestmt  = null;
					 System.out.println("输入年龄下限");
					 Age1 = cin.nextInt();
					 System.out.println("输入年龄上限");
					 Age2 = cin.nextInt();
					 String sql = "select * from Student where Age BETWEEN ? AND ?";
					 prestmt = conn.prepareStatement(sql);
					 prestmt.setInt(1, Age1);
					 prestmt.setInt(2, Age2);
					 rs = prestmt.executeQuery();
					 while(rs.next())
					 {
						 String Sno = rs.getString("Sno");
						 String Sname = rs.getString("Sname");
						 String Class = rs.getString("Class");
						 int Age = rs.getInt("Age");
						 String Sex = rs.getString("Sex");
					     System.out.println(Sno + "   " + Sname + "   " + Class + "   " + Age + "   " + Sex);
						 
					 }
					 prestmt.close();
					 rs.close();
				 }
				 else  if(No.charAt(0) == '6')
				 {
					 Fun6();
					 String Class;
					 ResultSet rs = null;
					 PreparedStatement prestmt  = null;
					 System.out.println("输入需要查询的专业名称");
					 Class = cin.nextLine();
					 Class += '%';
					 String sql = "select * from Student where Class like ?";
					 prestmt = conn.prepareStatement(sql);
					 prestmt.setString(1, Class);
					 rs = prestmt.executeQuery();
					 while(rs.next())
					 {
						 String Sno = rs.getString("Sno");
						 String Sname = rs.getString("Sname");
						 int Age = rs.getInt("Age");
						 String Sex = rs.getString("Sex");
						 Class = rs.getString("Class");
					     System.out.println(Sno + "   " + Sname + "   " + Class + "   " + Age + "   " + Sex);
						 
					 }
					 prestmt.close();
					 rs.close();
					 
				 }
				 else  if(No.charAt(0) == '7') Explain();
				 else  if(No.charAt(0) == '8')
				 {
					 Ends();
					 break;
				 }
				
				 
			 }
			 
		 }catch(Exception e)
		    {
		    	e.printStackTrace();
		    	System.out.println("操纵数据库时发生未知错误");
		    }finally
		    {
		    	if(conn!=null)conn.close();
		    	if(cin!=null)cin.close();
		    }
	}
	public static void show()
	{
		System.out.println("--------欢迎来到学生管理系统--------");
		System.out.println("-----------------------------");
		System.out.println("------------功能列表------------");
		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.println("----------7.使用说明书----------");
		System.out.println("----------8.退出程序-----------");
		System.out.println("----------请输入功能编号---------");
	}
	public static void Explain()
	{
		System.out.println("使用说明书:\n"
				+ "需要输入数字才能调用相应功能\n"
				+ "比如：\n"
				+ "请输入你想选择的功能的编号："
				+ "4"
				+ "这就是有效输入，输入不存在的编号或者非法字符系统会提醒你再次输入");
	}
	public static void Waring()
	{
		System.out.println("输入数据错误");
	}
	public static void Again()
	{
		System.out.println("请输入：");
	}
	public static void Ends()
	{
		System.out.println("欢迎再次使用，再见");
	}
	public static boolean Check(String No)
	{   
		if(No.length() != 1) return false;
		if(No.charAt(0)<'1' || No.charAt(0) > '9') return false;
		return true;
	}
	public static void Fun1()
	{
		System.out.println("------录入学生信息功能------");
		System.out.println("需要输入：学号，学生姓名，班级，年龄，性别。每位同学的信息需要输入完整");
	}
	public static void Fun2() 
	{
		System.out.println("------修改学生信息功能------");
		System.out.println("需要输入需要修改信息的同学的学号");
		System.out.println("需要注意的是，修改学生信息，可能只修改部分内容，但是仍需要将不修改的部分输入一遍");
	}
	public static void Fun3() 
	{
		System.out.println("------删除学生信息功能------");
		System.out.println("需要输入需要删除信息的同学的学号");
		
	}
	public static void Fun4() 
	{
		System.out.println("------按照性别查找学生信息功能------");
		System.out.println("输入想要查找的性别");
		
	}
    public static void Fun5()
    {
    	System.out.println("------按照年龄查找学生基本信息-------");
    	System.out.println("输入想要查找的年龄范围");
    }
    public static void Fun6()
    {
    	System.out.println("-------按照专业查找学生信息--------");
    	System.out.println("输入专业名称,因为一般班级的开头是专业名称简写，所以仅输入英文简写就可");
    }
}