学生信息管理系统

学生信息管理系统

刚学完mysql和jdbc，写了一个简单的系统实践一下

1. 主要实现的功能和界面
   
   
   
   其他界面和学生登陆的界面就不展示了，功能主要是对学生信息以及分数增删查改。

2. 主要代码
   –数据库工具类，用于数据库连接和关闭

public class Dbutil {
	private String url="jdbc:mysql://localhost:3306/information";
	private String username="root";
	private String password="toor";
	private String jdbcname="com.mysql.jdbc.Driver";
	/**
	 * 获取数据库连接
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public Connection getcon() throws ClassNotFoundException, SQLException {
		Class.forName(jdbcname);
		Connection con=DriverManager.getConnection(url, username, password);
		return con;
	}
	/**
	 * 关闭数据库连接
	 * @param con
	 * @throws SQLException
	 */
	public void conclose(Connection con) throws SQLException {
		if(con!=null)
		con.close();
	}

–增删改查的实现方法

public class Studentdao {
	//查询
	public ResultSet list(Connection con,Student student) throws Exception{
//		StringBuffer sb=new StringBuffer("select *from student");
//		if(Stringutil.isNotEmpty(student.getName())) {
//			System.out.println(student.getName());
//			sb.append(" and name like'"+student.getName()+"'");
//		}
//		PreparedStatement ps=con.prepareStatement(sb.toString().replaceFirst("and", "where"));
		PreparedStatement ps=con.prepareStatement("select *from student where name like'%"+student.getName()+"%'");
		return ps.executeQuery();
	}
	//添加
		public int add(Connection con,Student student) throws Exception {
			String sql="insert into student(num,name,sex,age,grade,id) values(?,?,?,?,?,?)";
			PreparedStatement pr = con.prepareStatement(sql);
			pr.setString(1, student.getNum());
			pr.setString(2,student.getName());
			pr.setString(3, student.getSex());
			pr.setInt(4, student.getAge());
			pr.setString(5, student.getGrade());
			pr.setString(6, student.getId());
			return pr.executeUpdate();
		}
	//删除
		public int delete(Connection con,Student student) throws Exception {
			String sql="delete from student where name=?";
			PreparedStatement pr=con.prepareStatement(sql);
			pr.setString(1,student.getName());
			return pr.executeUpdate();
		}
	//修改
		public int update(Connection con,Student student,String num) throws Exception {
			String sql="update student set num=?,name=?,sex=?,age=?,grade=?,id=? where num=?";
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setString(1, student.getNum());
			ps.setString(2, student.getName());
			ps.setString(3, student.getSex());
			ps.setInt(4, student.getAge());
			ps.setString(5, student.getGrade());
			ps.setString(6, student.getId());
			ps.setString(7, num);
			return ps.executeUpdate();
		}
	//录入或修改成绩
		public int enter(Connection con,Student student) throws Exception{
			String sql="update student set math=?,english=?,physical=? where num=?";
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setInt(1, student.getMath());
			ps.setInt(2, student.getEnglish());
			ps.setInt(3, student.getPhysical());
			ps.setString(4, student.getNum());
			return ps.executeUpdate();
		}
	//按学号查询
		public ResultSet list1(Connection con,Student student) throws Exception{
			PreparedStatement ps=con.prepareStatement("select *from student where num ="+student.getNum());
			return ps.executeQuery();
		}
	//排序
		public ResultSet sort(Connection con,Student student) throws Exception{
			PreparedStatement ps=con.prepareStatement("select *from student where grade like'%"+student.getGrade()+"%' order by math+physical+math desc");
			return ps.executeQuery();
		}

–具体实现(以录入学生信息为例)

	private void restoreAction() {
		String num=numtxt.getText();
		String name=nametxt.getText();
		String sex="";
		if(man.isSelected()) {
			sex="男";
		}else if(female.isSelected()) {
			sex="女";
		}
		int age=Integer.parseInt(agetxt.getText());
		String grade=(String)gradetxt.getSelectedItem();
		String id=idtxt.getText();
		if(Stringutil.isEmpty(num)) {
			JOptionPane.showMessageDialog(null, "学号不能为空！");
			return;
		}
		if(Stringutil.isEmpty(name)) {
			JOptionPane.showMessageDialog(null, "姓名不能为空！");
			return;
		}
		if(sex=="") {
			JOptionPane.showMessageDialog(null, "请选择性别！");
			return;
		}
		if(Stringutil.isEmpty(agetxt.getText())) {
			JOptionPane.showMessageDialog(null, "年龄不能为空！");
			return;
		}
		if(Stringutil.isEmpty(id)) {
			JOptionPane.showMessageDialog(null, "身份证号不能为空！");
			return;
		}
		Student op=new Student(num,name,sex,age,grade,id);
		Connection con=null;
		try {
			con=dbutil.getcon();
			int a=studentdao.add(con, op);
			if(a==1) {
				JOptionPane.showMessageDialog(null, "录入成功！");
				//清空
				resetAction();
			}else {
				JOptionPane.showMessageDialog(null, "该同学已存在！");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				dbutil.conclose(con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

–具体实现(搜索显示学生信息)

private void inittable(Student op) {
		DefaultTableModel  t= (DefaultTableModel)table.getModel();
		t.setRowCount(0);
		String name=nametxt.getText();
		op.setName(name);
		Connection con =null;
		try {
			con =dbutil.getcon();
			ResultSet set=studentdao.list(con, op);
			while(set.next()) {
				Vector v=new Vector();
				v.add(set.getString("num"));
				v.add(set.getString("name"));
				v.add(set.getString("sex"));
				v.add(set.getInt("age"));
				v.add(set.getString("grade"));
				v.add(set.getString("id"));
				t.addRow(v);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
				dbutil.conclose(con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

-其他功能在代码实现上和上面类似

3. 遇到的问题
   在学生成绩排名查询页面，原本是想实现对每个单科成绩以及总成绩分别进行排名并显示，由于刚接触mysql，最终只实现了对总排名进行显示。

点击查看：学生信息管理系统源码