数据库部分
表
主要表结构
管理员表信息
学生表部分信息
学生表触发器<限制性别,人数自动变化>
Java部分
程序表
主要核心部分代码位于Dao.impl包中
*这里只展示StudentDaoImpl实现类的代码*
public class StudentDaoImpl implements StudentDao{
/**
* 根据关键字查找,默认是显示全部学生信息
*/
@Override
public List<Vector> SelectAllStudent(Student stu_search, int search_loop) {
List<Vector> list = new ArrayList<Vector>();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtilsPro.GetConnection();
StringBuilder sql = new StringBuilder("select s_id, s_name,s_sex,s_age,class.class_id,dept.dept_name,department.dpt_name,s_address,s_beizhu from student,class,"
+ "dept,department where class.class_id = student.class_id and dept.dept_id = student.dept_id and department.dpt_id = student.dpt_id");//未拼接前的sql语句,默认查找所有学生信息
if(search_loop == 0) {
//下拉框选择学号
if(!StringUtils.isEmpty(stu_search.getS_id())) {
sql.append(" and s_id like '%" + stu_search.getS_id() + "%' ");
}
}
else if(search_loop == 1) {
if(!StringUtils.isEmpty(stu_search.getS_name())) {
//查找搜索的关键字和姓名类似的信息,append()字符串连接,StringBuilder的方法
sql.append(" and s_name like '%" + stu_search.getS_name() + "%' ");
}
}
else if(search_loop == 2) {
if(!StringUtils.isEmpty(stu_search.getS_sex())) {
sql.append(" and s_sex like '%" + stu_search.getS_sex() + "%' ");
}
}
else if(search_loop == 3) {
String str = Integer.toString(stu_search.getS_age());
if(!StringUtils.isEmpty(str)) {
sql.append(" and s_age = " + stu_search.getS_age() );
}
}
else if(search_loop == 4) {
if(!StringUtils.isEmpty(stu_search.getS_class())) {
sql.append(" and class.class_name like '%" + stu_search.getS_class() + "%' ");
}
}
else if(search_loop == 5) {
if(!StringUtils.isEmpty(stu_search.getS_dept())) {
sql.append(" and dept.dept_name like '%" + stu_search.getS_dept() + "%' ");
}
}
else if(search_loop == 6) {
if(!StringUtils.isEmpty(stu_search.getS_dpt_name())) {
sql.append(" and department.dpt_name like '%" + stu_search.getS_dpt_name() + "%' ");
}
}
else if(search_loop == 7) {
if(!StringUtils.isEmpty(stu_search.getS_address())) {
sql.append(" and s_address like '%" + stu_search.getS_address() + "%' ");
}
}
ps = conn.prepareStatement(sql.toString());//将对象sql转为字符串
rs = ps.executeQuery();
while(rs.next()) {
Vector v = new Vector();//动态数组的顺序容器
v.add(rs.getString(1));//学号
v.add(rs.getString(2));//姓名
v.add(rs.getString(3));//性别
v.add(rs.getInt(4));//年龄
v.add(rs.getString(5));//班级号
v.add(rs.getString(6));//专业
v.add(rs.getString(7));//院系
v.add(rs.getString(8));//家庭住址
v.add(rs.getString(9));//备注
list.add(v);//将每次存的v数组存到list数组中以便返回
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtilsPro.CloseCP(conn, ps);//关闭资源
JDBCUtilsPro.CloseResultSet(rs);;//关闭资源
}
return list;//将查找的全部信息返回
}
/**
* 删除信息
*/
@Override
public int DeleteStudent(String id) {
int a = 0;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtilsPro.GetConnection();
ps = conn.prepareStatement("delete from student where s_id = ?");
ps.setString(1, id);
a = ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtilsPro.CloseCP(conn, ps);//关闭资源
}
return a;
}
/**
* 修改信息
*/
@Override
public int UpdataStudent(Student stu) {
int i = 0;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtilsPro.GetConnection();
ps = conn.prepareStatement("update student set s_name=?,s_sex=?,s_age=?,class_id=?,dept_id=?,dpt_id=?,s_address=?,s_beizhu=? where s_id = ?");
ps.setString(1, stu.getS_name());
ps.setString(2, stu.getS_sex());
ps.setInt(3, stu.getS_age());
ps.setString(4, stu.getS_class());
ps.setString(5, stu.getS_dept());
ps.setString(6, stu.getS_dpt_name());
ps.setString(7, stu.getS_address());
ps.setString(8, stu.getS_beizhu());
ps.setString(9, stu.getS_id());
i = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtilsPro.CloseCP(conn, ps);//关闭资源
}
return i;
}
/**
*添加信息
*/
@Override
public int AddStudent(Student stu) {
int i = 0;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtilsPro.GetConnection();
ps = conn.prepareStatement("insert into student(s_id,s_name,s_sex,s_age,class_id,dept_id,dpt_id,s_address,s_beizhu) values(?,?,?,?,?,?,?,?,?)");
ps.setString(1, stu.getS_id());
ps.setString(2, stu.getS_name());
ps.setString(3, stu.getS_sex());
ps.setInt(4, stu.getS_age());
ps.setString(5, stu.getS_class());
ps.setString(6, stu.getS_dept());
ps.setString(7, stu.getS_dpt_name());
ps.setString(8, stu.getS_address());
ps.setString(9, stu.getS_beizhu());
i = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtilsPro.CloseCP(conn, ps);//关闭资源
}
return i;
}
/**
* 根据关键字查找,默认是显示全部成绩信息
*/
@Override
public List<Vector> SelectAllGrade(Student stu_search, int search_loop) {
List<Vector> list = new ArrayList<Vector>();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtilsPro.GetConnection();
StringBuilder sql = new StringBuilder("select student.s_id, s_name,department.dpt_name,dept.dept_name,student.class_id,object.c_name,choose.s_grade,"
+ "((choose.s_grade-60)/10.0+1.0) from student,choose,dept,department,object "
+ "where dept.dept_id = student.dept_id and department.dpt_id = student.dpt_id and student.s_id = choose.s_id and choose.c_id = object.c_id");//未拼接前的sql语句,默认查找所有学生信息
if(search_loop == 0) {
//下拉框选择学号
if(!StringUtils.isEmpty(stu_search.getS_id())) {
sql.append(" and student.s_id like '%" + stu_search.getS_id() + "%' ");
}
}
else if(search_loop == 1) {
if(!StringUtils.isEmpty(stu_search.getS_name())) {
//查找搜索的关键字和姓名类似的信息,append()字符串连接,StringBuilder的方法
sql.append(" and student.s_name like '%" + stu_search.getS_name() + "%' ");
}
}
else if(search_loop == 2) {
if(!StringUtils.isEmpty(stu_search.getS_dpt_name())) {
sql.append(" and department.dpt_name like '%" + stu_search.getS_dpt_name() + "%' ");
}
}
else if(search_loop == 3) {
if(!StringUtils.isEmpty(stu_search.getS_dept())) {
sql.append(" and dept.dept_name like '%" + stu_search.getS_dept() + "%' ");
}
}
else if(search_loop == 4) {
if(!StringUtils.isEmpty(stu_search.getS_class())) {
sql.append(" and student.class_id like '%" + stu_search.getS_class() + "%' ");
}
}
else if(search_loop == 5) {
if(!StringUtils.isEmpty(stu_search.getC_name())) {
sql.append(" and object.c_name like '%" + stu_search.getC_name() + "%' ");
}
}
else if(search_loop == 6) {
String str = Double.toString(stu_search.getS_grade());
if(!StringUtils.isEmpty(str)) {
sql.append(" and choose.s_grade = " + stu_search.getS_grade() );
}
}
ps = conn.prepareStatement(sql.toString());//将对象sql转为字符串
rs = ps.executeQuery();
while(rs.next()) {
Vector v = new Vector();//动态数组的顺序容器
v.add(rs.getString(1));//学号
v.add(rs.getString(2));//姓名
v.add(rs.getString(3));//院系
v.add(rs.getString(4));//专业
v.add(rs.getString(5));//班级号
v.add(rs.getString(6));//课程名
v.add(rs.getDouble(7));//成绩
v.add(rs.getDouble(8));//绩点
list.add(v);//将每次存的v数组存到list数组中以便返回
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtilsPro.CloseCP(conn, ps);//关闭资源
JDBCUtilsPro.CloseResultSet(rs);;//关闭资源
}
return list;//将查找的全部信息返回
}
/*
* 修改成绩
*/
@Override
public int UpdataGrade(Student stu) {
int i = 0;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtilsPro.GetConnection();
ps = conn.prepareStatement("update choose set s_grade=? where s_id = ? and c_id = ?");
ps.setDouble(1, stu.getS_grade());
ps.setString(2, stu.getS_id());
ps.setString(3, stu.getC_id());
i = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtilsPro.CloseCP(conn, ps);//关闭资源
}
return i;
}
/*
* 删除成绩
*/
@Override
public int DeleteGrade(String s_id, String c_name) {
int a = 0;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtilsPro.GetConnection();
ps = conn.prepareStatement("delete from choose where s_id = ? and c_id in (select c_id from object where c_name=?);");
ps.setString(1, s_id);
ps.setString(2, c_name);
a = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtilsPro.CloseCP(conn, ps);//关闭资源
}
return a;
}
/**
*添加成绩信息
*/
@Override
public int AddGrade(Student stu) {
int i = 0;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtilsPro.GetConnection();
ps = conn.prepareStatement("insert into choose(s_id,c_id,s_grade) values(?,?,?)");
ps.setString(1, stu.getS_id());
ps.setString(2, stu.getC_id());
ps.setDouble(3, stu.getS_grade());
i = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtilsPro.CloseCP(conn, ps);//关闭资源
}
return i;
}
/*
* 统计男女人数
*/
@Override
public List<Vector> SelectAllNum(Student stu, int tongji_loop) {
List<Vector> list = new ArrayList<Vector>();
Connection conn = null;
PreparedStatement ps = null;
ResultSet res = null;
String sql = "";
try
{
conn = JDBCUtilsPro.GetConnection();
if(tongji_loop == 0) {
//统计全体学生
sql = "call tongji_num()";
}
else if(tongji_loop == 1) {
//统计院系学生
sql = "call tongji_dpt()";
}
else if(tongji_loop == 2){
//统计专业学生
sql = "call tongji_dept()";
}
else if(tongji_loop == 3){
//统计班级学生
sql = "call tongji_class()";
}
ps = conn.prepareStatement(sql);
res = ps.executeQuery();
// 循环遍历结果集
while(res.next()) {
Vector v = new Vector();//动态数组的顺序容器
v.add(res.getString(1));//统计类型
v.add(res.getInt(2));//总人数
v.add(res.getInt(3));//男生人数
v.add(res.getInt(4));//女生人数
list.add(v);//将每次存的v数组存到list数组中以便返回
}
} catch (SQLException sqle){
sqle.printStackTrace();
} finally{
JDBCUtilsPro.CloseCP(conn, ps);//关闭资源
JDBCUtilsPro.CloseResultSet(res);//关闭资源
}
return list;//将查找的全部信息返回
}
/*
* 统计成绩
*/
@Override
public List<Vector> TongjiGrade(Student stu_grade, int grade_loop) {
List<Vector> list = new ArrayList<Vector>();
Connection conn = null;
PreparedStatement ps = null;
ResultSet res = null;
String sql = "";
try
{
conn = JDBCUtilsPro.GetConnection();
if(grade_loop == 0) {
//统计全体成绩
sql = "call all_grade()";
}
else if(grade_loop == 1) {
//统计院系成绩
sql = "call dpt_grade()";
}
else if(grade_loop == 2) {
//统计专业成绩
sql = "call dept_grade()";
}
else if(grade_loop == 3) {
//统计班级成绩
sql = "call class_grade()";
}
ps = conn.prepareStatement(sql);
res = ps.executeQuery();
// 循环遍历结果集
while(res.next()) {
Vector v = new Vector();//动态数组的顺序容器
v.add(res.getString(1));//统计类型
v.add(res.getString(2));//科目
v.add(res.getDouble(3));//平均成绩
v.add(res.getDouble(4));//最高成绩
v.add(res.getDouble(5));//最低成绩
list.add(v);//将每次存的v数组存到list数组中以便返回
}
} catch (SQLException sqle){
sqle.printStackTrace();
} finally{
JDBCUtilsPro.CloseCP(conn, ps);//关闭资源
JDBCUtilsPro.CloseResultSet(res);//关闭资源
}
return list;//将查找的全部信息返回
}
@Override
public List<Vector> LookReward(Student stu, int look_loop) {
List<Vector> list = new ArrayList<Vector>();
Connection conn = null;
PreparedStatement ps = null;
ResultSet res = null;
String sql = "";
try
{
conn = JDBCUtilsPro.GetConnection();
if(look_loop == 0) {
//统计全体成绩
sql = "select * from reward";
}
else if(look_loop == 1) {
//统计院系成绩
sql = "select * from reward where r_loop = 1";
}
else if(look_loop == 2) {
//统计专业成绩
sql = "select * from reward where r_loop = 0";
}
ps = conn.prepareStatement(sql);
res = ps.executeQuery();
// 循环遍历结果集
while(res.next()) {
Vector v = new Vector();//动态数组的顺序容器
v.add(res.getInt(1));//奖惩编号
v.add(res.getString(2));//学生学号
v.add(res.getInt(3));//奖惩类型
v.add(res.getString(4));//奖惩名称
v.add(res.getString(5));//奖惩理由
list.add(v);//将每次存的v数组存到list数组中以便返回
}
} catch (SQLException sqle){
sqle.printStackTrace();
} finally{
JDBCUtilsPro.CloseCP(conn, ps);//关闭资源
JDBCUtilsPro.CloseResultSet(res);//关闭资源
}
return list;//将查找的全部信息返回
}
@Override
public int AddReward(Student stu) {
int i = 0;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtilsPro.GetConnection();
ps = conn.prepareStatement("insert into reward(s_id,r_loop,r_name,r_case) values(?,?,?,?)");
ps.setString(1, stu.getS_id());
ps.setInt(2, stu.getR_flag());
ps.setString(3, stu.getR_name());
ps.setString(4, stu.getR_case());
i = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtilsPro.CloseCP(conn, ps);//关闭资源
}
return i;
}
/*
* 修改奖惩信息
*/
@Override
public int UpdataReward(Student stu) {
int i = 0;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtilsPro.GetConnection();
ps = conn.prepareStatement("update reward set s_id=?,r_loop=?,r_name=?,r_case=? where r_id = ?");
ps.setString(1, stu.getS_id());
ps.setInt(2, stu.getR_flag());
ps.setString(3, stu.getR_name());
ps.setString(4, stu.getR_case());
ps.setInt(5, stu.getR_id());
i = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtilsPro.CloseCP(conn, ps);
}
return i;
}
/*
* 删除奖惩信息
*/
@Override
public int DeleteReward(String r_id) {
int a = 0;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtilsPro.GetConnection();
ps = conn.prepareStatement("delete from reward where r_id = ?");
ps.setString(1, r_id);
a = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtilsPro.CloseCP(conn, ps);
}
return a;
}
}
结果展示
已经结束嘞<图先欠着>