Java实验3

实验内容

学生信息管理系统

学生成绩表Student(Sno 字符串，长度9, Sname 字符串，长度10, Class 字符串，长度10, Age 整型, Sex 字符串，长度2)

实现如下功能：

A．输入若干个学生的信息到Student表；

B．修改指定学号学生的基本信息；

C．删除指定学号的学生基本信息；

D．按照性别找出所有相应的学生基本信息；

E．按照年龄段找出所有相应的学生基本信息，例如[19，21]的学生；

F．按照姓氏查询所有学生的基本信息；

G．以上功能用菜单形式实现功能选择。

实验环境

eclipse+MySQL8.0版本+MySQL的JDBC驱动

实验结果

数据库中的内容

Sno、Sname、Class、Sex是varchar类型，Age是int类型

源代码

Student.java

package lab3;
import java.util.Scanner;
import java.sql.*;
public class Student {
	public static void main(String[]args)throws SQLException
	{
		 String No;
		 Connection conn = null;
		 Scanner cin = new Scanner(System.in);
		 try
		 {
			 Class.forName("com.mysql.cj.jdbc.Driver");
	    	 String url = "jdbc:mysql://localhost:3306/?user=root";//换成自己的mysql用户名
	    	 String username = "root";	//换成自己的mysql用户名，应该也是root
	    	 String password = "123456";//换成自己的mysql密码
	    	 conn = DriverManager.getConnection(url, username, password);
			 while(true)
			 {
				 show();
				 No = cin.nextLine();
				 while(!Check(No))
				 {
					 System.out.println("输入数据错误");
					 System.out.println("请输入：");
					 No = cin.nextLine();
				 }
				 if(No.charAt(0) == '1')
				 {
					 PreparedStatement prestmt  = null;
					 System.out.println("------录入学生信息功能------");
					 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();	//sql语句中stusys.student要换成自己的数据库名.表名
					 String sql = "INSERT INTO stusys.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;
					 System.out.println("------修改学生信息功能------");
					 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 stusys.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;
					 System.out.println("------删除学生信息功能------");
					 String Sno;
					 System.out.println("请输入 学生学号");
					 Sno = cin.nextLine();
					 String sql = "DELETE "
					 		+ "FROM stusys.student "
					 		+ " WHERE Sno = ?";
					 prestmt = conn.prepareStatement(sql);
					 prestmt.setString(1, Sno);
                     prestmt.executeUpdate();	
                     prestmt.close();
                     System.out.println("删除成功");
				 }
				 else  if(No.charAt(0) == '4')
				 {
					 System.out.println("------按照性别查找学生信息功能------");
					 String Sex;
					 ResultSet rs = null;
					 PreparedStatement prestmt  = null;
					 System.out.println("请输入 性别");
					 Sex = cin.nextLine();
					 String sql = "select * from stusys.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')
				 {
					 System.out.println("------按照年龄查找学生基本信息-------");
					 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 stusys.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')
				 {
					 System.out.println("-------按照姓氏查找学生信息--------");
					 String Xing;
					 ResultSet rs = null;
					 PreparedStatement prestmt  = null;
					 System.out.println("输入需要查询的姓氏");
					 Xing = cin.nextLine();
					 Xing += '%';
					 String sql = "select * from stusys.student where Sname like ?";
					 prestmt = conn.prepareStatement(sql);
					 prestmt.setString(1, Xing);
					 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");
						 String Class = rs.getString("Class");
					     System.out.println(Sno + "   " + Sname + "   " + Class + "   " + Age + "   " + Sex);
					 }
					 prestmt.close();
					 rs.close();
				 }
				 else  if(No.charAt(0) == '7')
				 {
					 System.out.println("欢迎再次使用，再见");
					 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("请输入功能编号");
	}
	public static boolean Check(String No)
	{   
		if(No.length() != 1)
			return false;
		if(No.charAt(0)<'1' || No.charAt(0) > '8')
			return false;
		return true;
	}
}

MySQL数据库大家应该都会用吧，MySQL的内容这里就不写了哈