实验内容
学生信息管理系统
学生成绩表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的内容这里就不写了哈