基于JavaSwing的学籍管理系统<JavaWeb简历展示>

数据库部分

主要表结构

 

管理员表信息

 

 学生表部分信息

 学生表触发器<限制性别,人数自动变化>

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;
	}
}

 结果展示

 

 

 

 

 

 

 

 已经结束嘞<图先欠着>

 

  • 0
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值