程序地带

Java+MySQL实现简单学生信息管理系统(控制台交互)


本次主要介绍使用Java语言实现学生信息管理系统,采用控制台输入输出,完成主要的增删改查功能,使用的开发工具主要有eclipse、navicat、mysql数据库。


以下是界面展示: 在这里插入图片描述在这里插入图片描述


简介: 创建一个zq.stu的package和一个lib包,导入MySQL的JDBC驱动jar包到lib,Build Path > Add to Build Path DBUtils类:数据库连接与关闭 View: 负责菜单的显示和处理用户操作 Main: 主函数 及用户注册、登录的实现 Register: 注册信息类 RegDao:注册信息与数据库的交互 Student: 实体对象,封装学生基本信息 StuCtrl: 学生信息控制 StuDao: 学生信息与数据库的交互 StuMark: 学生成绩信息类 StuMarkCtrl: 学生成绩控制 StuMarkDao: 学生成绩信息与数据库的交互


项目结构: 项目结构


流程图: 在这里插入图片描述


数据库部分

在这里插入图片描述 stu为数据库的名字.


需要在stu数据库内建三张数据库表(register,student,mark)


register建表语句如下:


create table register(
identity varchar(12),
user varchar(12),
password varchar(12),
id int(11) auto_increment primary key);

student建表语句如下:


create table student(
sid int(12) auto_increment primary key,
name varchar(10),
age char(4),
address varchar(12),
tel varchar(20)
) ;

mark建表语句如下:


create table mark(
sid int(11) auto_increment,
name varchar(10),
clbum int(11),
cScore double,
javaScore double,
pythonScore double,
aveScore double,
primary key(sid)
);
代码部分:

数据库连接:DBUtils.java


import java.sql.DriverManager;
import com.mysql.jdbc.Connection;
public class DBUtils {
final static String DRIVER = "com.mysql.jdbc.Driver";
final static String URL = "jdbc:mysql://localhost:3306/stu";
final static String USER = "root";
final static String PASSWORD = "123456";
public static Connection getConnection() {
try {
Class.forName(DRIVER);
Connection connection = (Connection) DriverManager.getConnection(URL, USER, PASSWORD);
// System.out.println("连接成功");
return connection;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
public static void closeConnection(Connection con) {
try {
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}

Main.java :主函数


import java.util.List;
import java.util.Scanner;
public class Main {
//登录
public String Login() {
RegDao reg = new RegDao();
Scanner sc = new Scanner(System.in);
System.out.println("====登录====");
System.out.println("请输入用户名:");
String user = sc.nextLine();
if(user.equals(reg.FindByName(user))) {
System.out.println("请输入密码:");
String passwd = sc.nextLine();
if(passwd.equals(reg.FindByUserPwd(user, passwd))) {
System.out.println("登录成功!");
return user;
}else
return null;
}else
return null;
}
//用户注册
public boolean register(String str) {
Scanner s = new Scanner(System.in);
RegDao dao1 = new RegDao();
String identity, user, password;
System.out.println("====注册====");
//System.out.print("身份:");
identity = str;
System.out.print("用户名:");
user = s.next();
System.out.print("密码:");
password = s.next();
Register reg = new Register(identity, user, password);
boolean ok = dao1.add(reg);
if (ok) {
System.out.println("注册成功!");
return true;
} else {
System.out.println("注册失败!");
return false;
}
}
//主函数
public static void main(String[] args) {
View v = new View ();
v.ChooseMenu();
}
}

View.java


import java.util.List;
import java.util.Scanner;
public class View {
Main m = new Main();
StuCtrl s = new StuCtrl();
StuMarkCtrl sm = new StuMarkCtrl();
//管理界面,选择权限,进入相应界面
public void ChooseMenu() {
boolean flag = true;
while(flag) {
System.out.println("************************************************************************************************");
System.out.println("--------------欢迎进入学生信息管理系统-------------");
System.out.println("1.教师入口");
System.out.println("2.学生入口");
System.out.println("3.退出系统");
System.out.print("请选择:");
Scanner sc = new Scanner(System.in);
int ch = sc.nextInt();
String user;
if(ch == 1) {//教师入口
System.out.println("登录还是注册?");
System.out.println("1.登录 2.注册");
int cho = sc.nextInt();
if(cho==1) {
//教师登录
if((user=m.Login())!=null) {
Teachermenu(user);
}
else {
System.out.println("出错了!");
}
}
else if(cho == 2) {
//教师注册
String identity = "教师";
if(m.register(identity)) {
if((user=m.Login())!=null) {
Teachermenu(user);
}
else {
System.out.println("出错了!");
}
}
else {
System.out.println("注册失败,返回主界面");
ChooseMenu();
}
}
else {
System.out.println("输入有误,重新进入系统!");
ChooseMenu();
}
}
else if(ch == 2) {//学生入口
System.out.println("登录还是注册?");
System.out.println("1.登录 2.注册");
int cho = sc.nextInt();
if(cho==1) {
//学生登录
if((user=m.Login())!=null) {
StudentMenu(user);
}
else {
System.out.println("出错了!");
}
}
else if(cho == 2) {
//学生注册
String identity = "学生";
if(m.register(identity)) {
if((user=m.Login())!=null) {
StudentMenu(user);
}
else {
System.out.println("出错了!");
}
}
else
System.out.println("注册失败,返回主界面");
ChooseMenu();
}
else {
System.out.println("输入有误,重新进入系统!");
ChooseMenu();
}
}
else if(ch == 3) { //退出系统
System.out.print("您选择了退出系统,确定要退出吗?(Y/N)");
Scanner scan = new Scanner(System.in);
String scanExit = scan.next().toUpperCase();
if (scanExit.equals("Y")) {
System.out.println("您已成功退出系统,欢迎您再次使用!");
System.exit(0);
}
}
else
System.out.println("选择有误,请重新输入:");
}
}
//学生界面
public void StudentMenu(String user) {
int choose;
boolean flag = true;
while(flag) {
System.out.println("************************************************************************************************");
System.out.println("--------------欢迎* "+user+" *进入学生界面-------------");
System.out.println("1.按学号查询成绩信息");
System.out.println("2.查询所有学生成绩信息");
System.out.println("3.退出该界面");
System.out.print("请选择(1-3):");
Scanner scanner = new Scanner(System.in);
choose = scanner.nextInt();
System.out.println("************************************************************************************************");
switch (choose) {
case 1:
// 菜单选择1,按学号查询个人成绩信息
sm.queryStuMark();
break;
case 2:
//菜单选择2,查询所有学生成绩信息
sm.queryAllStuMark();
break;
case 3:
// 菜单选择3,退出该界面
System.out.print("您选择了退出学生界面,确定要退出吗?(Y/N)");
Scanner scan = new Scanner(System.in);
String scanExit = scan.next().toUpperCase();
if (scanExit.equals("Y")) {
System.out.println("您已成功退出学生界面!");
flag = false;
}
break;
default:
System.out.println("输入有误!请重新输入!");
break;
}
}
}
//教师界面
public void Teachermenu(String user) {
// 1.打印并输出菜单
// 2.switch菜单选择
int choose;
do {
System.out.println("************************************************************************************************");
System.out.println("--------------欢迎* "+user+" *进入教师管理界面-------------");
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.print("请选择(1-6):");
Scanner scanner = new Scanner(System.in);
choose = scanner.nextInt();
System.out.println("************************************************************************************************");
switch (choose) {
case 1:
// 菜单选择1,新增学生基本信息与成绩
boolean flag=true;
while (flag){
String sid= s.addStu();
if(sid!=null) {
sm.addStuMark(sid);}
System.out.print("是否继续添加(Y/N):");
Scanner scanner2 = new Scanner(System.in);
String str = scanner2.next().toUpperCase();
if(str.equals("Y")) {
flag = true;
}else
flag = false;
}
break;
case 2:
// 菜单选择2,修改学生基本信息与成绩
s.updateStu();
sm.updateStuMark();
break;
case 3:
// 菜单选择3,删除学生信息与成绩
s.delStu();
sm.delStuMark();
break;
case 4:
//菜单选择4,仅查询学生成绩
sm.queryAllStuMark();
break;
case 5:
// 菜单选择5,查询所有学生的信息与成绩
s.queryAllStu();
break;
case 6: // 菜单选择6,退出该系统
System.out.print("您选择了退出教师界面,确定要退出吗?(Y/N)");
Scanner scan = new Scanner(System.in);
String scanExit = scan.next().toUpperCase();
if (scanExit.equals("Y")) {
System.out.println("您已成功退出教师界面!");
}
break;
default:
break;
}
} while (choose != 6);
}
}

Student.java


public class Student {
private String stuId;
private String name;
private String age;
private String address;
private String tel;
private String clbum;
private String cScore;
private String javaScore;
private String pythonScore;
private String aveScore;
public Student(String stuId, String name, String age, String address, String tel) {
super();
this.stuId = stuId;
this.name = name;
this.age = age;
this.address = address;
this.tel = tel;
}
public Student() {
super();
}
public Student(String stuId, String name, String age, String address, String tel, String clbum, String cScore,
String javaScore, String pythonScore, String aveScore) {
super();
this.stuId = stuId;
this.name = name;
this.age = age;
this.address = address;
this.tel = tel;
this.clbum = clbum;
this.cScore = cScore;
this.javaScore = javaScore;
this.pythonScore = pythonScore;
this.aveScore = aveScore;
}
public String getStuId() {
return stuId;
}
public void setStuId(String stuId) {
this.stuId = stuId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
public String getClbum() {
return clbum;
}
public void setClbum(String clbum) {
this.clbum = clbum;
}
public String getcScore() {
return cScore;
}
public void setcScore(String cScore) {
this.cScore = cScore;
}
public String getJavaScore() {
return javaScore;
}
public void setJavaScore(String javaScore) {
this.javaScore = javaScore;
}
public String getPythonScore() {
return pythonScore;
}
public void setPythonScore(String pythonScore) {
this.pythonScore = pythonScore;
}
public String getAveScore() {
return aveScore;
}
public void setAveScore(String aveScore) {
this.aveScore = aveScore;
}
}

StuCtrl.java


import java.util.List;
import java.util.Scanner;
public class StuCtrl {
//新增学生信息
public String addStu() {
Scanner s = new Scanner(System.in);
StuDao stuDao = new StuDao();
String stuId, name, age, address, tel;
System.out.println("====新增学生====");
System.out.print("学号:");
stuId = s.next();
System.out.print("姓名:");
name = s.next();
System.out.print("年龄:");
age = s.next();
System.out.print("住址:");
address = s.next();
System.out.print("联系电话:");
tel = s.next();
Student stu = new Student(stuId, name, age, address, tel);
boolean ok = stuDao.add(stu);
if (ok) {
System.out.println("保存成功!");
return stuId;
} else {
System.out.println("保存失败!");
}
return null;
}
// 删除学生信息
public void delStu() {
Scanner s = new Scanner(System.in);
String id;
System.out.println("====删除学生====");
System.out.print("请输入要删除的学生学号:");
id = s.next();
System.out.println("该学生的信息如下:");
StuDao delDao = new StuDao();
System.out.println("学生学号:" + delDao.FindById(id).getStuId());
System.out.println("学生姓名:" + delDao.FindById(id).getName());
System.out.println("学生年龄:" + delDao.FindById(id).getAge());
System.out.println("学生住址:" + delDao.FindById(id).getAddress());
System.out.println("学生电话:" + delDao.FindById(id).getTel());
System.out.print("是否真的删除(Y/N):");
Scanner scanner3 = new Scanner(System.in);
String x = scanner3.next().toUpperCase();
if (x.equals("Y")) {
boolean ok = delDao.delete(id);
if (ok) {
System.out.println("删除成功!");
} else {
System.out.println("删除失败!");
}
}
}
// 修改学生信息
public void updateStu() {
Scanner s = new Scanner(System.in);
String sid;
System.out.println("====修改学生====");
System.out.print("请输入要修改的学生学号:");
sid = s.next();
System.out.println("该学生的信息如下:");
StuDao dao = new StuDao();
System.out.println("学生学号:" + dao.FindById(sid).getStuId());
System.out.println("学生姓名:" + dao.FindById(sid).getName());
System.out.println("学生年龄:" + dao.FindById(sid).getAge());
System.out.println("学生住址:" + dao.FindById(sid).getAddress());
System.out.println("学生电话:" + dao.FindById(sid).getTel());
System.out.println("请输入新的学生信息:");
Scanner StudentUp = new Scanner(System.in);
String name, age, address, tel;
System.out.print("学生姓名:");
name = StudentUp.next();
System.out.print("学生年龄:");
age = StudentUp.next();
System.out.print("学生住址:");
address = StudentUp.next();
System.out.print("学生电话:");
tel = StudentUp.next();
Student stu = new Student(sid, name, age, address, tel);
boolean ok = dao.update(stu, sid);
if (ok) {
System.out.println("保存成功!");
} else {
System.out.println("保存失败!");
}
}
// 查询所有学生信息与成绩
public void queryAllStu() {
System.out.println("====查询所有学生信息与成绩====");
System.out.println("学生的信息如下:");
System.out.println("学号 姓名 年龄 住址 电话 班级 C语言成绩 Java成绩 Python成绩 平均分");
StuDao dao = new StuDao();
List<Student> list = dao.list();
for (Student stuList : list) { // 循环打印出查询结果
System.out.println(stuList.getStuId() + " " + stuList.getName() + " " + stuList.getAge() + " "
+ stuList.getAddress() + " " + stuList.getTel() + " " + stuList.getClbum() + " " + stuList.getcScore()
+ " " + stuList.getJavaScore() + " " + stuList.getPythonScore() + " " + stuList.getAveScore());
}
}
//查询学生成绩信息(单个)
public void queryStu() {
Scanner s = new Scanner(System.in);
String id;
System.out.println("====查询成绩====");
System.out.print("请输入要查询的学生学号:");
id = s.next();
System.out.println("您的信息如下:");
StuDao dao = new StuDao();
System.out.println("学生学号:" + dao.FindById(id).getStuId());
System.out.println("学生姓名:" + dao.FindById(id).getName());
System.out.println("学生年龄:" + dao.FindById(id).getAge());
System.out.println("学生住址:" + dao.FindById(id).getAddress());
System.out.println("学生电话:" + dao.FindById(id).getTel());
}
}

StuDao.java


import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.sql.PreparedStatement;
import com.mysql.jdbc.Connection;
public class StuDao {
Connection con = null;
// 添加学生信息
public boolean add(Student stu) {
String sql = "insert into student(sid,name,age,address,tel) values(?,?,?,?,?)";
try {
con = DBUtils.getConnection();
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, stu.getStuId());
pstmt.setString(2, stu.getName());
pstmt.setString(3, stu.getAge());
pstmt.setString(4, stu.getAddress());
pstmt.setString(5, stu.getTel());
pstmt.execute();
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
DBUtils.closeConnection(con);
}
return true;
}
// 查看学生列表(所有)
public List<Student> list() {
List<Student> list = new ArrayList<Student>();
try {
con = DBUtils.getConnection();
String sql = "select * from student s,mark m where s.sid=m.sid";
PreparedStatement pst = con.prepareStatement(sql);
ResultSet rs = pst.executeQuery();
while (rs.next()){
Student stu = new Student();
stu.setStuId(rs.getString("sid"));
stu.setName(rs.getString("name"));
stu.setAge(rs.getString("age"));
stu.setAddress(rs.getString("address"));
stu.setTel(rs.getString("tel"));
stu.setClbum(rs.getString("clbum"));
String cS = rs.getString("cScore");
stu.setcScore(cS);
String jS = rs.getString("javaScore");
stu.setJavaScore(jS);
String pS = rs.getString("pythonScore");
stu.setPythonScore(pS);
double sum = Double.parseDouble(cS)+Double.parseDouble(jS)+Double.parseDouble(pS);
java.text.DecimalFormat df = new java.text.DecimalFormat("#.00");
String ave = df.format(sum/3);
stu.setAveScore(ave);
list.add(stu);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.closeConnection(con);
}
return list;
}
//根据ID获取学生信息
public Student FindById(String id) {
try {
con = DBUtils.getConnection();
String sql = "select * from student where sid=?";
PreparedStatement pst = con.prepareStatement(sql);
pst.setString(1, id);
ResultSet rs = pst.executeQuery();
if (rs.next()) {
Student stu = new Student();
stu.setStuId(rs.getString("sid"));
stu.setName(rs.getString("name"));
stu.setAge(rs.getString("age"));
stu.setAddress(rs.getString("address"));
stu.setTel(rs.getString("tel"));
return stu;
} else {
return null;
}
} catch (Exception e) {
e.printStackTrace();
return null;
} finally {
DBUtils.closeConnection(con);
}
}
//修改学生信息
public boolean update(Student stu, String id) {
try {
con = DBUtils.getConnection();
String sql = "update student set name=?,age=?,address=?,tel=? where sid=?";
PreparedStatement pst = con.prepareStatement(sql);
pst.setString(1, stu.getName());
pst.setString(2, stu.getAge());
pst.setString(3, stu.getAddress());
pst.setString(4, stu.getTel());
pst.setString(5, id);
pst.execute();
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
DBUtils.closeConnection(con);
}
}
//删除学生信息
public boolean delete(String sid) {
try {
con = DBUtils.getConnection();
String sql = "delete from student where sid=?";
PreparedStatement pst = con.prepareStatement(sql);
pst.setString(1, sid);
pst.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
DBUtils.closeConnection(con);
}
return true;
}
}

StuMark.java


public class StuMark {
private String stuId;
private String name;
private String clbum;
private String cScore;
private String javaScore;
private String pythonScore;
private String aveScore;
private String rank;
public String getRank() {
return rank;
}
public void setRank(String rank) {
this.rank = rank;
}
public String getId() {
return stuId;
}
public void setId(String id) {
this.stuId = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getClbum() {
return clbum;
}
public void setClbum(String clbum) {
this.clbum = clbum;
}
public String getcScore() {
return cScore;
}
public void setcScore(String cScore) {
this.cScore = cScore;
}
public String getJavaScore() {
return javaScore;
}
public void setJavaScore(String javaScore) {
this.javaScore = javaScore;
}
public String getPythonScore() {
return pythonScore;
}
public void setPythonScore(String pythonScore) {
this.pythonScore = pythonScore;
}
public String getAveScore() {
return aveScore;
}
public void setAveScore(String aveScore) {
this.aveScore = aveScore;
}
// 无参构造函数
public StuMark() {
super();
}
public StuMark(String id, String name, String clbum, String cScore, String javaScore, String pythonScore) {
super();
this.stuId = id;
this.name = name;
this.clbum = clbum;
this.cScore = cScore;
this.javaScore = javaScore;
this.pythonScore = pythonScore;
}
public StuMark(String id, String name, String clbum, String cScore, String javaScore, String pythonScore, String aveScore) {
super();
this.stuId = id;
this.name = name;
this.clbum = clbum;
this.cScore = cScore;
this.javaScore = javaScore;
this.pythonScore = pythonScore;
this.aveScore = aveScore;
}
public StuMark(String id, String name, String clbum, String cScore, String javaScore, String pythonScore, String aveScore,String rank) {
super();
this.stuId = id;
this.name = name;
this.clbum = clbum;
this.cScore = cScore;
this.javaScore = javaScore;
this.pythonScore = pythonScore;
this.aveScore = aveScore;
this.rank = rank;
}
}

StuMarkCtrl.java


import java.util.List;
import java.util.Scanner;
public class StuMarkCtrl {
//新增学生成绩信息
public void addStuMark(String sid) {
Scanner s = new Scanner(System.in);
StuMarkDao dao = new StuMarkDao();
String name, clbum, cScore, javaScore, pythonScore,aveScore;
System.out.println("====新增学生成绩====");
System.out.print("姓名:");
name = s.next();
System.out.print("班级:");
clbum = s.next();
System.out.print("C语言成绩:");
cScore = s.next();
System.out.print("Java成绩:");
javaScore = s.next();
System.out.print("Python成绩:");
pythonScore = s.next();
double sum = Double.parseDouble(cScore)+Double.parseDouble(javaScore)+Double.parseDouble(pythonScore);
aveScore = String.valueOf(sum/3);
StuMark stu = new StuMark(sid, name, clbum, cScore, javaScore, pythonScore, aveScore);
boolean ok = dao.add(stu);
if (ok) {
System.out.println("保存成功!");
} else {
System.out.println("保存失败!");
}
}
// 删除学生成绩信息
public void delStuMark() {
Scanner s = new Scanner(System.in);
String id;
System.out.println("====删除学生成绩====");
System.out.print("请输入要删除的学生学号:");
id = s.next();
System.out.println("该学生的信息如下:");
StuMarkDao dao = new StuMarkDao();
System.out.println("学生学号:" + dao.FindById(id).getId());
System.out.println("学生姓名:" + dao.FindById(id).getName());
System.out.println("学生班级:" + dao.FindById(id).getClbum());
System.out.println("学生C语言成绩:" + dao.FindById(id).getcScore());
System.out.println("学生Java成绩:" + dao.FindById(id).getJavaScore());
System.out.println("学生Python成绩:" + dao.FindById(id).getPythonScore());
System.out.println("学生平均分:" + dao.FindById(id).getAveScore());
System.out.print("是否真的删除(Y/N):");
Scanner scanner3 = new Scanner(System.in);
String x = scanner3.next().toUpperCase();
if (x.equals("Y")) {
boolean ok = dao.delete(id);
if (ok) {
System.out.println("删除成功!");
} else {
System.out.println("删除失败!");
}
}
}
// 修改学生成绩信息
public void updateStuMark() {
Scanner s = new Scanner(System.in);
System.out.println("====修改学生成绩====");
System.out.print("请输入要修改的学生学号:");
String id = s.next();
System.out.println("该学生的信息如下:");
StuMarkDao dao = new StuMarkDao();
System.out.println("学生学号:" + dao.FindById(id).getId());
System.out.println("学生姓名:" + dao.FindById(id).getName());
System.out.println("学生班级:" + dao.FindById(id).getClbum());
System.out.println("学生C语言成绩:" + dao.FindById(id).getcScore());
System.out.println("学生Java成绩:" + dao.FindById(id).getJavaScore());
System.out.println("学生Python成绩:" + dao.FindById(id).getPythonScore());
System.out.println("学生平均分:" + dao.FindById(id).getAveScore());
System.out.println("请输入新的学生信息:");
Scanner StudentUp = new Scanner(System.in);
String name, clbum, cScore, javaScore, pythonScore, aveScore;
System.out.print("学生姓名:");
name = StudentUp.next();
System.out.print("学生班级:");
clbum = StudentUp.next();
System.out.print("C语言成绩:");
cScore = StudentUp.next();
System.out.print("Java成绩:");
javaScore = StudentUp.next();
System.out.print("Python成绩:");
pythonScore = StudentUp.next();
double sum = Double.parseDouble(cScore)+Double.parseDouble(javaScore)+Double.parseDouble(pythonScore);
java.text.DecimalFormat df = new java.text.DecimalFormat("#.00");
aveScore = df.format(sum/3);
StuMark stu = new StuMark(id, name, clbum, cScore, javaScore, pythonScore, aveScore);
boolean ok = dao.update(stu, id);
if (ok) {
System.out.println("保存成功!");
} else {
System.out.println("保存失败!");
}
}
// 查询所有学生成绩信息
public void queryAllStuMark() {
System.out.println("====查询所有学生成绩====");
System.out.println("学生的成绩信息如下:");
System.out.println("学号 姓名 班级 C语言 Java Python 平均分 排名");
StuMarkDao dao = new StuMarkDao();
List<StuMark> list = dao.list();
for (StuMark stuList : list) {
System.out.println(stuList.getId() + " " + stuList.getName() + " " + stuList.getClbum() + " "
+ stuList.getcScore() + " " + stuList.getJavaScore() + " " + stuList.getPythonScore() + " "
+ stuList.getAveScore() + " " +stuList.getRank());
}
}
//查询学生成绩信息(单个)
public void queryStuMark() {
Scanner s = new Scanner(System.in);
String id;
System.out.println("====查询成绩====");
System.out.print("请输入要查询的学生学号:");
id = s.next();
System.out.println("查询到学号为" +id+ " 的信息如下:");
StuMarkDao dao = new StuMarkDao();
System.out.println("学生学号:" + dao.FindById(id).getId());
System.out.println("学生姓名:" + dao.FindById(id).getName());
System.out.println("学生班级:" + dao.FindById(id).getClbum());
System.out.println("学生C语言成绩:" + dao.FindById(id).getcScore());
System.out.println("学生Java成绩:" + dao.FindById(id).getJavaScore());
System.out.println("学生Python成绩:" + dao.FindById(id).getPythonScore());
System.out.println("学生平均分:" + dao.FindById(id).getAveScore());
}
}

StuMarkDao.java


import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.sql.PreparedStatement;
import com.mysql.jdbc.Connection;
public class StuMarkDao {
Connection con = null;
// 添加学生成绩信息
public boolean add(StuMark stu) {
String sql = "insert into mark(sid,name,clbum,cScore,javaScore,pythonScore,aveScore) values(?,?,?,?,?,?,?)";
try {
con = DBUtils.getConnection();
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, stu.getId());
pstmt.setString(2, stu.getName());
pstmt.setString(3, stu.getClbum());
pstmt.setString(4, stu.getcScore());
pstmt.setString(5, stu.getJavaScore());
pstmt.setString(6, stu.getPythonScore());
pstmt.setString(7, stu.getAveScore());
pstmt.execute();
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
DBUtils.closeConnection(con);
}
return true;
}
// 查看学生成绩列表(所有)
public List<StuMark> list() {
List<StuMark> list = new ArrayList <StuMark>();
try {
con = DBUtils.getConnection();
String sql = "select *,(@rowNum:=@rowNum+1) rank from mark,(select (@rowNum:=0)) b order by mark.aveScore desc;";
PreparedStatement pst = con.prepareStatement(sql);
ResultSet rs = pst.executeQuery();
while (rs.next()){
StuMark stu = new StuMark();
stu.setId(rs.getString("sid"));
stu.setName(rs.getString("name"));
stu.setClbum(rs.getString("clbum"));
String cS = rs.getString("cScore");
stu.setcScore(cS);
String jS = rs.getString("javaScore");
stu.setJavaScore(jS);
String pS = rs.getString("pythonScore");
stu.setPythonScore(pS);
stu.setRank(rs.getString("rank"));
double sum = Double.parseDouble(cS)+Double.parseDouble(jS)+Double.parseDouble(pS);
java.text.DecimalFormat df = new java.text.DecimalFormat("#.00");
String ave = df.format(sum/3);
stu.setAveScore(ave);
list.add(stu);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.closeConnection(con);
}
return list;
}
//根据ID获取学生信息
public StuMark FindById(String sid) {
try {
con = DBUtils.getConnection();
String sql = "select * from mark where sid=?";
PreparedStatement pst = con.prepareStatement(sql);
pst.setString(1, sid);
ResultSet rs = pst.executeQuery();
if (rs.next()) {
StuMark stu = new StuMark();
stu.setId(rs.getString("sid"));
stu.setName(rs.getString("name"));
stu.setClbum(rs.getString("clbum"));
String cS = rs.getString("cScore");
stu.setcScore(cS);
String jS = rs.getString("javaScore");
stu.setJavaScore(jS);
String pS = rs.getString("pythonScore");
stu.setPythonScore(pS);
double sum = Double.parseDouble(cS)+Double.parseDouble(jS)+Double.parseDouble(pS);
java.text.DecimalFormat df = new java.text.DecimalFormat("#.00");
String ave = df.format(sum/3);
stu.setAveScore(ave);
return stu;
} else {
return null;
}
} catch (Exception e) {
e.printStackTrace();
return null;
} finally {
DBUtils.closeConnection(con);
}
}
//修改学生信息
public boolean update(StuMark stu, String sid) {
try {
con = DBUtils.getConnection();
String sql = "update mark set name=?,clbum=?,cScore=?,javaScore=?,pythonScore=?,aveScore=? where sid=?";
PreparedStatement pst = con.prepareStatement(sql);
pst.setString(1, stu.getName());
pst.setString(2, stu.getClbum());
pst.setString(3, stu.getcScore());
pst.setString(4, stu.getJavaScore());
pst.setString(5, stu.getPythonScore());
pst.setString(6, stu.getAveScore());
pst.setString(7, sid);
pst.execute();
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
DBUtils.closeConnection(con);
}
}
//删除学生信息
public boolean delete(String sid) {
try {
con = DBUtils.getConnection();
String sql = "delete from mark where sid=?";
PreparedStatement pst = con.prepareStatement(sql);
pst.setString(1, sid);
pst.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
DBUtils.closeConnection(con);
}
return true;
}
}

Register.java


public class Register {
String user;
String password;
String identity;
public String getUser() {
return user;
}
public void setUser(String user) {
this.user = user;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getIdentity() {
return identity;
}
public void setIdentity(String identity) {
this.identity = identity;
}
public Register() {
super();
}
public Register(String identity, String user, String password) {
super();
this.user = user;
this.password = password;
this.identity = identity;
}
}

RegDao.java


import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.sql.PreparedStatement;
import com.mysql.jdbc.Connection;
public class RegDao {
Connection con = null;
// 添加用户
public boolean add(Register reg) {
String sql = "insert into register(identity,user,password) values(?,?,?)";
try {
con = DBUtils.getConnection();
PreparedStatement pstmt = con.prepareStatement(sql);
// System.out.println(con);
pstmt = con.prepareStatement(sql);
pstmt.setString(1, reg.getIdentity());
pstmt.setString(2, reg.getUser());
pstmt.setString(3, reg.getPassword());
pstmt.execute();
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
DBUtils.closeConnection(con);
}
return true;
}
// 查看用户
public List<Register> list() {
List<Register> list = new ArrayList<Register>();
try {
con = DBUtils.getConnection();
String sql = "select * from register";
PreparedStatement pst = con.prepareStatement(sql);
ResultSet rs = pst.executeQuery();
while (rs.next()){
Register reg = new Register();
reg.setIdentity(rs.getString("identity"));
reg.setUser(rs.getString("user"));
reg.setPassword(rs.getString("password"));
list.add(reg);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.closeConnection(con);
}
return list;
}
//获取用户名字
public String FindByName(String user) {
try {
con = DBUtils.getConnection();
String sql = "select * from register where user=?";
PreparedStatement pst = con.prepareStatement(sql);
pst.setString(1, user);
ResultSet rs = pst.executeQuery();
if(rs.next()) {
return rs.getString("user");
}else
return null;
} catch (Exception e) {
e.printStackTrace();
return null;
} finally {
DBUtils.closeConnection(con);
}
}
//获取用户名及密码
public String FindByUserPwd(String user,String passwd) {
try {
con = DBUtils.getConnection();
String sql = "select * from register where user=? and password=?";
PreparedStatement pst = con.prepareStatement(sql);
pst.setString(1, user);
pst.setString(2, passwd);
ResultSet rs = pst.executeQuery();
if (rs.next()) {
String pw = rs.getString("password");
return pw;
}
} catch (Exception e) {
e.printStackTrace();
return null;
} finally {
DBUtils.closeConnection(con);
}
return null;
}
}

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/weixin_44264223/article/details/112689391

随机推荐

Matlab之netCDF格式文件读取方法

Matlab之netCDF格式文件读取方法

Matlab之netCDF格式文件读取方法目录一、netCDF格式文件1.简介2.NetCDF的数据结构二、Matlab读取netCDF使用到的函数1.ncdisp()2....

珞瑜· 阅读(996)

iOS LeetCode☞有效的括号

给定一个只包括‘(’,’)’,’{’,’}’,’[’,’]’的字符串,判断字符串是否有效。有效字符串需满足ÿ...

荒唐的天梯 阅读(400)

Ubuntu安装教程、换源及NVIDIA驱动

本人是个小白,在网上搜加上自己琢磨,安装了很多次Ubuntu操作系统,踩过一系列坑,总结了一点点经验,分享给小白们,...

BouKove 阅读(345)

c++动态内存理解

C++程序中的内存分为两个部分:栈:在函数内部声明的所有变量都将占用栈内存。栈,就是那些由编译器在需要的时候分配,在不需要的时候...

Jack Ju 阅读(575)

消息队列

目录1.为什么要用消息队列(应用场景)?2.各种消息队列产品的比较3.消息队列的优势和缺点4.如何保证消息队列的高可用?5.如何保证消息不丢失&...

mlm5678 阅读(273)