基于JDBC的学生管理系统
基于JDBC的学生管理系统
使用JDBC实现对MySQL数据库中学生信息的增删改查
主类代码,展示功能区
public class Test {
public static void main(String[] args) {
boolean flag = true;
while (flag) {
services();
Scanner input = new Scanner(System.in);
System.out.print("Please input your select:");
int select = input.nextInt();
FactoryInfo fi = new FactoryInfo(select);
if (select != 0) {
flag = true;
} else {
flag = false;
}
}
System.out.println("bye!");
}
public static void services() {
System.out.println();
System.out.println("welcome to use student manage system!");
System.out.println("This system provides the following services:");
System.out.println("1.add student information.");
System.out.println("2.delete student information.");
System.out.println("3.modify student information.");
System.out.println("4.query student information.");
System.out.println("5.show student information.");
System.out.println("0.Exit the system.");
System.out.println();
}
}
Factory类，建立MySQL连接工厂
public class FactoryInfo {
int select;
public FactoryInfo() {
}
public FactoryInfo(int select) {
this.select = select;
ConnMysql connMysql = new ConnMysql();
connMysql.conn(select);
}
}
MySQL连接类，实现对MySQL的连接
1.加载驱动
2.获取功能选择
3.实例化对象
public class ConnMysql {
static final String mysql_url = "jdbc:mysql://localhost:3306/dsj?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
static final String USER = "root";
static final String PASSWD = "123456";
void conn(int select) {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection(mysql_url, USER, PASSWD);
Statement state = conn.createStatement(); // 获取驱动
if (select == 1) {
AddInfo addInfo = new AddInfo(state);
} else if (select == 2) {
DeleteInfo deleteInfo = new DeleteInfo(state);
} else if (select == 3) {
ModifyInfo modifyInfo = new ModifyInfo(state);
} else if (select == 4) {
QueryInfo queryInfo = new QueryInfo(state);
} else if (select == 5) {
ShowInfo showInfo = new ShowInfo(state);
} else if (select == 0) {
System.out.println("Exit successful");
}
conn.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
增加信息类，向MySQL数据库中增加学生信息
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class AddInfo {
Scanner input = new Scanner(System.in);
String name;
int age;
String sex;
int height;
int weight;
String sql;
Boolean flag = true;
public AddInfo(Statement state) throws SQLException {
while (flag) {
System.out.print("Please input name:");
name = input.next();
System.out.print("Please input age:");
age = input.nextInt();
System.out.print("Please input sex:");
sex = input.next();
System.out.print("Please input height:");
height = input.nextInt();
System.out.print("Please input weight:");
weight = input.nextInt();
sql = "insert into test1 values (" + """ + name + """ + "," + age + "," + """ + sex + """ + "," + height + "," + weight + ");";
if (state.executeUpdate(sql) >= 1) {
System.out.print("添加成功,是否继续:1.继续 2.退出:");
int result = input.nextInt();
if (result == 1) {
flag = true;
} else {
flag = false;
}
}
}
}
}
删除信息类，删除MySQL数据库中对应学生的信息
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class DeleteInfo {
public DeleteInfo(Statement state) throws SQLException {
boolean flag = true;
while (flag) {
Scanner input = new Scanner(System.in);
String name;
String sql;
System.out.print("please input student name:");
name = input.next();
sql = "delete from test1 where 姓名="+"""+name+""";
if (state.executeUpdate(sql) >= 1) {
System.out.print("删除成功,是否继续: 1.继续 2.退出:");
int select = input.nextInt();
flag = select == 1;
}
}
}
}
修改信息类，更新MySQL数据库中学生信息
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class ModifyInfo {
String sql;
public ModifyInfo(Statement state) throws SQLException {
boolean flag = true;
Scanner input = new Scanner(System.in);
while (flag) {
System.out.print("请输入您要修改学生的名字:");
String name = input.next();
System.out.print("您要修改此学生的哪一条数据:");
String result = input.next();
if (result.equals("姓名") || result.equals("性别")) {
System.out.print("请输入新的数据:");
String new_data = input.next();
if (result.equals("姓名")) {
this.sql = "update test1 set 姓名=" + """ + new_data + """ + " where 姓名=" +""" + name + "";";
}else {
this.sql = "update test1 set 性别=" + """ + new_data + """ + " where 姓名=" + """ + name + "";";
}
}else if (result.equals("年龄") || result.equals("身高") || result.equals("体重")) {
System.out.print("请输入新的数据:");
int new_data = input.nextInt();
if (result.equals("年龄")) {
this.sql = "update test1 set 年龄=" + """ + new_data + """ + " where 姓名=" + """ + name + "";";
}else if (result.equals("身高")) {
this.sql = "update test1 set 身高="+ """ + new_data + """ + " where 姓名=" + """ + name + "";";
}else {
this.sql = "update test1 set 体重=" + """ + new_data + """ + " where 姓名=" + """ + name + "";";
}
}
if (state.executeUpdate(this.sql) >= 1) {
System.out.print("修改成功,是否继续:1.继续 2.退出:");
int select = input.nextInt();
flag = select == 1;
}
}
}
}
查询信息类，查询对应学生的信息
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class QueryInfo {
public QueryInfo(Statement state) throws SQLException {
boolean flag = true;
while (flag) {
Scanner input = new Scanner(System.in);
String name;
String sql;
System.out.print("please input student name:");
name = input.next();
sql = "select * from test1 where 姓名="+"""+name+""";
ResultSet rs = state.executeQuery(sql);
while (rs.next()) {
int age = rs.getInt("年龄");
String sex = rs.getString("性别");
int height = rs.getInt("身高");
int weight = rs.getInt("体重");
System.out.println("name:" + name + ",age:" + age + ",sex:" + sex + ",height:" + height + ",weight:" + weight);
System.out.println();
}
System.out.print("是否继续查询: 1.继续 2.退出:");
int select = input.nextInt();
flag = select == 1;
}
}
}
展示全部学生信息类
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class ShowInfo {
public ShowInfo(Statement state) throws SQLException {
String sql = "select * from test1";
ResultSet rs = state.executeQuery(sql);
boolean flag = true;
while (rs.next()) {
String name = rs.getString("姓名");
int age = rs.getInt("年龄");
String sex = rs.getString("性别");
int height = rs.getInt("身高");
int weight = rs.getInt("体重");
System.out.println("name:" + name + ",age:" + age + ",sex:" + sex + ",height:" + height + ",weight:" + weight);
System.out.println();
}
Scanner sc = new Scanner(System.in);
System.out.print("请输入exit退出:");
String answer = sc.next();
if (!answer.equals("exit")) {
System.out.println("输入有误,强行退出!");
}
flag = false;
}
}