dataoperate.java

一个学生选课系统

				if (rs.next()) {
					System.out.println(rs.getString("Cid"));
					continue;
				} else {
					String sql2 = "insert into study values (?,?,null)";
					String log="insert into study values ("+usr+","+Cid+",null)";
					BackLog.bckLog(log);
					conn = DbCont.getConn();
					pstmt = conn.prepareStatement(sql2);
					pstmt.setString(1, usr);
					pstmt.setString(2, Cid);
					int result = pstmt.executeUpdate();
					System.out.println(Cid);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {

				e.printStackTrace();
			}
		}
	}

	// 删除某个学生及其相关信息
	public static boolean deletePersonInfo(String usr) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean bool = false;
		try {
			conn = (Connection) DbCont.getConn();
			// 检测是否存在改用户
			String sql1 = "select * from student where Sid=?";
			pstmt = (PreparedStatement) conn.prepareStatement(sql1);
			pstmt.setString(1, usr);

			rs = (ResultSet) pstmt.executeQuery();
			if (rs.next())// 如果存在
			{
				// 检测study是否有其相关信息
				String sql2 = "select * from study where Sid=?";
				pstmt = (PreparedStatement) conn.prepareStatement(sql2);
				pstmt.setString(1, usr);

				rs = (ResultSet) pstmt.executeQuery();
				if (rs.next()) {// 如果有删除
					String sql3 = "delete from study where Sid=?";
					String log="delete from study where Sid="+usr;
					BackLog.bckLog(log);
					pstmt = (PreparedStatement) conn.prepareStatement(sql3);
					pstmt.setString(1, usr);
					pstmt.executeUpdate();

				}
				// 删除student表中的基本信息
				String sql4 = "delete from student where Sid=?";
				String log="delete from student where Sid="+usr;
				BackLog.bckLog(log);
				pstmt = (PreparedStatement) conn.prepareStatement(sql4);
				pstmt.setString(1, usr);
				pstmt.executeUpdate();
				bool = true;
			} else
				bool = false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (conn != null)
				conn = null;
			if (pstmt != null)
				pstmt = null;
			if (rs != null)
				rs = null;
		}
		return bool;
	}

	// 插入一条新的学生记录
	public static boolean insertStuInfo(String sid, String name, int age,
			String sex, String dept,String password) {
		boolean bool = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		conn = (Connection) DbCont.getConn();

		try {
			String sql1 = "select * from student where Sid=?";
			pstmt = conn.prepareStatement(sql1);
			pstmt.setString(1, sid);
			rs = (ResultSet) pstmt.executeQuery();

			if (rs.next())
				bool = false;
			else {
				String sql2 = "insert into student values(?,?,?,?,?,?)";
				String log="insert into student values("+sid+","+name+","+age+","+sex+","+dept+","+password+")";
				BackLog.bckLog(log);
				pstmt = conn.prepareStatement(sql2);
				pstmt.setString(1, sid);
				pstmt.setString(2, name);
				pstmt.setInt(3, age);
				pstmt.setString(4, sex);
				pstmt.setString(5, dept);
				pstmt.setString(6, password);
				pstmt.executeUpdate();
				bool = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return bool;
	}

	// 修改学生资料
	public static boolean alterStuInfo(String sid, String name, int age,
			String sex, String dept) {
		boolean bool = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		conn = (Connection) DbCont.getConn();

		try {

			String sql2 = "update student set Sname=?,Sage=?,Ssex=?,Sdept=?,Spass=? where Sid=?";
			//String log="update student set Sname='"+name+"',Sage="+age+",Ssex='"+sex+"',Sdept='"+dept+"',Spass='111111' where Sid="+sid;
			//BackLog.bckLog(log);
			pstmt = conn.prepareStatement(sql2);
			pstmt.setString(6, sid);
			pstmt.setString(1, name);
			pstmt.setInt(2, age);
			pstmt.setString(3, sex);
			pstmt.setString(4, dept);
			pstmt.setString(5, "111111");
			pstmt.executeUpdate();
			bool = true;

	 	} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return bool;
	}
	//删除一门课程信息
	public static boolean deleteCourse(String cid) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean bool = false;
		try {
			conn = (Connection) DbCont.getConn();
			// 检测是否存在改用户
			String sql1 = "select * from course where Cid=?";
			pstmt = (PreparedStatement) conn.prepareStatement(sql1);
			pstmt.setString(1, cid);

			rs = (ResultSet) pstmt.executeQuery();
			if (rs.next())// 如果存在
			{
				// 检测study是否有其相关信息
				String sql2 = "select * from study where Cid=?";
				pstmt = (PreparedStatement) conn.prepareStatement(sql2);
				pstmt.setString(1, cid);

				rs = (ResultSet) pstmt.executeQuery();
				if (rs.next()) {// 如果有删除
					String sql3 = "delete from study where Cid=?";
					String log="delete from study where Cid='"+cid+"'";
					BackLog.bckLog(log);
					pstmt = (PreparedStatement) conn.prepareStatement(sql3);
					pstmt.setString(1, cid);
					pstmt.executeUpdate();

				}
				// 删除student表中的基本信息
				String sql4 = "delete from course where Cid=?";
				String log="delete from course where Cid='"+cid+"'";
				BackLog.bckLog(log);
				pstmt = (PreparedStatement) conn.prepareStatement(sql4);
				pstmt.setString(1, cid);
				pstmt.executeUpdate();
				bool = true;
			} else
				bool = false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (conn != null)
				conn = null;
			if (pstmt != null)
				pstmt = null;
			if (rs != null)
				rs = null;
		}
		return bool;
	}
	//添加一门新课程
	public static boolean addCourse(String cid, String cname,
			String cdept, String cteacher) {
		boolean bool = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		conn = (Connection) DbCont.getConn();

		try {
			String sql1 = "select * from course where Cid=?";
			pstmt = conn.prepareStatement(sql1);
			pstmt.setString(1, cid);
			rs = (ResultSet) pstmt.executeQuery();

			if (rs.next())
				bool = false;
			else {
				String sql2 = "insert into course values(?,?,?,?)";
				String log="insert into course values('"+cid+"','"+cname+"','"+cdept+"','"+cteacher+"')";
				BackLog.bckLog(log);
				pstmt = conn.prepareStatement(sql2);
				pstmt.setString(1, cid);
				pstmt.setString(2, cname);
				pstmt.setString(3, cdept);
				pstmt.setString(4, cteacher);

				pstmt.executeUpdate();
				bool = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return bool;
	}
	//获取课程信息
	public static Course courseInfo(String cid) {
		Course course = new Course();
		Statement stmt;
		ResultSet rs;
		// 连接数据库
		Connection conn = (Connection) DbCont.getConn();
		// 使用sql语句操纵数据库，在数据库中查找是否有符合条件的记录
		try {
			String sql1 = "select * from course where Cid='"+ cid + "'";
			stmt = (Statement) conn.createStatement();
			rs = (ResultSet) stmt.executeQuery(sql1);
			// 获取学生基本信息
			if (rs.next()) {
				course.setId(rs.getString("Cid"));
				course.setName(rs.getString("Cname"));
				course.setTeacher(rs.getString("teacher"));
				course.setDept(rs.getString("Cdept"));
			}
			// 获取学生的课程和成绩信息
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return course;
	}
	//修改课程信息
	public static boolean alterCourseInfo(String cid, String cname,
			String cdept, String teacher) {
		boolean bool = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		conn = (Connection) DbCont.getConn();

		try {

			String sql2 = "update course set Cname=?,Cdept=?,teacher=? where Cid=?";
			String log="update course set Cname='"+cname+"',Cdept='"+cdept+"',teacher='"+teacher+"' where Cid='"+cid+"'";
			BackLog.bckLog(log);
			pstmt = conn.prepareStatement(sql2);
			pstmt.setString(4, cid);
			pstmt.setString(1, cname);
			pstmt.setString(2, cdept);
			pstmt.setString(3, teacher);
			pstmt.executeUpdate();
			bool = true;

	 	} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return bool;
	}
	//插入一跳教师信息
	public static boolean insertTeacherInfo(String tid, String tname,String password, int root) 
	{
		boolean bool = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		conn = (Connection) DbCont.getConn();

		try {
			String sql1 = "select * from teacher where Tid=?";
			pstmt = conn.prepareStatement(sql1);
			pstmt.setString(1, tid);
			rs = (ResultSet) pstmt.executeQuery();

			if (rs.next())
				bool = false;
			else {
				String sql2 = "insert into teacher values(?,?,?,?)";
				String log="insert into teacher values('"+tid+"','"+tname+"','"+password+"','"+root+"')";
				BackLog.bckLog(log);
				pstmt = conn.prepareStatement(sql2);
				pstmt.setString(1, tid);
				pstmt.setString(2, tname);
				pstmt.setString(3, password);
				pstmt.setInt(4, root);
				pstmt.executeUpdate();
				bool = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return bool;
	}
}