dataoperate.java

一个学生选课系统

package com.student.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;
import com.student.DB.DbCont;
import com.student.po.Course;
import com.student.po.Student;
import com.student.po.Teacher;

public class DataOperate {
	public static void main(String args[])
	{
		String id="40";
		String name="学生";
		String sex="男";
		int age=20;
		String dept="计算机";
		String pass="11111";
		for(int i=140;i<1000;i++)
		{
			insertStuInfo(id+i,name+i, age,sex,dept,pass);
		}
	}
	//退选课程
	public static void exitCourse(ArrayList cid,String usr)
	{
		java.sql.Connection conn = null;
		java.sql.PreparedStatement pstmt = null;
		java.sql.ResultSet rs = null;
		
		conn = DbCont.getConn();
		
		for (int i = 0; i < cid.size(); i++) {
			String Cid = (String) cid.get(i);

			String sql1 = "delete  from study where Sid=? and Cid=?";
			String log="delete  from study where Sid="+usr+" and Cid="+Cid;
			try {
				BackLog.bckLog(log);
				pstmt = conn.prepareStatement(sql1);
				pstmt.setString(1, usr);
				pstmt.setString(2, Cid);
				pstmt.executeUpdate();
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	// 检查学生用户
	public static boolean studentCheck(String usr, String password) {

		// 连接数据库
		java.sql.Connection conn = null;
		java.sql.PreparedStatement pstmt = null;
		java.sql.ResultSet rs = null;

		try {
			conn = DbCont.getConn();
			String sql = "select * from student where Sid=? and Spass=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, usr);
			pstmt.setString(2, password);

			rs = pstmt.executeQuery();

			if (rs.next()) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	// 检查教师用户
	public static int teacherCheck(String usr, String password) {
		int root = -1;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		// 连接数据库
		Connection conn = (Connection) DbCont.getConn();
		// 使用sql语句操纵数据库，在数据库中查找是否有符合条件的记录
		try {
			String sql = "select * from teacher where Tid=? and Tpass=?";
			stmt = (PreparedStatement) conn.prepareStatement(sql);
			stmt.setString(1, usr);
			stmt.setString(2, password);
			rs = (ResultSet) stmt.executeQuery();
			if (rs.next())
				root = rs.getInt("root");
			else
				root = -1;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return root;
	}

	// 获取某个学生信息
	public static Student personInfo(String usr) {
		Student stu = new Student();
		Statement stmt;
		ResultSet rs;
		// 连接数据库
		Connection conn = (Connection) DbCont.getConn();
		// 使用sql语句操纵数据库，在数据库中查找是否有符合条件的记录
		try {
			String sql1 = "select Sname,Sage,Ssex,Sdept from student where Sid='"
					+ usr + "'";
			stmt = (Statement) conn.createStatement();
			rs = (ResultSet) stmt.executeQuery(sql1);
			// 获取学生基本信息
			if (rs.next()) {
				stu.setId(usr);
				stu.setName(rs.getString("Sname"));
				stu.setAge(rs.getInt("Sage"));
				stu.setSex(rs.getString("Ssex"));
				stu.setSdept(rs.getString("Sdept"));
			}
			// 获取学生的课程和成绩信息
			ArrayList score = new ArrayList();
			String sql2 = "select study.Cid,Cname,Cdept,Score from course,study where course.Cid=study.Cid and study.Sid='"
					+ usr + "'";
			rs = (ResultSet) stmt.executeQuery(sql2);
			while (rs.next()) {
				Course course = new Course();
				course.setId(rs.getString("Cid"));
				course.setName(rs.getString("Cname"));
				course.setDept(rs.getString("Cdept"));
				course.setScore(rs.getInt("Score"));
				score.add(course);
			}
			stu.setScore(score);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return stu;
	}

	// 获取某个教师信息
	public static Teacher getTeacherInfo(String Tid) {
		Teacher teacher = new Teacher();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			String sql = "select * from teacher where Tid=?";
			conn = (Connection) DbCont.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, Tid);
			rs = (ResultSet) pstmt.executeQuery();
			if (rs.next()) {
				teacher.setId(Tid);
				teacher.setName(rs.getString("Tname"));
				teacher.setRoot(rs.getInt("root"));
			} else
				return null;
		} 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 teacher;
	}
	//修改某个教师信息
	public static boolean alterTeacherInfo(String tid,String name,int root)
	{
		boolean bool=false;
		Connection conn=null;
		PreparedStatement pstmt=null;
		
		String sql="update teacher set Tname=?,root=? where Tid=?";
		String log="update teacher set Tname="+name+",root="+root+" where Tid="+tid;
		try {
			BackLog.bckLog(log);
			conn=(Connection)DbCont.getConn();
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setInt(2, root);
			pstmt.setString(3, tid);
			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();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return bool;
	}
	//删除某个教师信息
	public static boolean deleteTeacher(String tid,int root)
	{
		boolean bool =false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		System.out.println("qqqqqqqqqqqqq"+root);
		System.out.println("qqqqqqqqqqqqq"+tid);
		//System.out.println(rt);
		String sql = "select * from teacher where Tid=?";
		try {
			conn = (Connection) DbCont.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tid);
			rs = (ResultSet) pstmt.executeQuery();
			if (rs.next()) {
				int rt=rs.getInt("root");
				System.out.println("qqqqqqqqqqqqq"+root);
				System.out.println(rt);
				if(root>rt)
				{
					String sql1="delete from teacher where Tid=?";
					String  log="delete from teacher where Tid="+tid;
					BackLog.bckLog(log);
					pstmt = conn.prepareStatement(sql1);
					pstmt.setString(1, tid);
					pstmt.executeUpdate();
					bool=true;
				}
				else return bool;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return bool;		
	}
	// 获取全校学生信息
	public static ArrayList getAllStudentInfo() {
		ArrayList students = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			String sql = "select * from student";
			conn = (Connection) DbCont.getConn();
			pstmt = conn.prepareStatement(sql);
			rs = (ResultSet) pstmt.executeQuery();
			while (rs.next()) {
				Student student = new Student();
				student.setId(rs.getString("Sid"));
				student.setName(rs.getString("Sname"));
				student.setAge(rs.getInt("Sage"));
				student.setSex(rs.getString("Ssex"));
				student.setSdept(rs.getString("Sdept"));
				students.add(student);
			}
		} 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 students;
	}
	//获取全校教师信息
	public static ArrayList getAllTeacher()
	{
		ArrayList teachers=new ArrayList();
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		String sql="select * from teacher";
		
		try {
			conn=(Connection) DbCont.getConn();
			pstmt=conn.prepareStatement(sql);
			rs=(ResultSet) pstmt.executeQuery();
			
			while(rs.next())
			{
				Teacher tec=new Teacher();
				tec.setId(rs.getString("Tid"));
				tec.setName(rs.getString("Tname"));
				tec.setRoot(rs.getInt("root"));
				teachers.add(tec);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return teachers;
	}
	// 获取全校课程信息
	public static ArrayList getAllCourse() {
		java.sql.Connection conn = null;
		java.sql.PreparedStatement pstmt = null;
		java.sql.ResultSet rs = null;
		ArrayList courses = new ArrayList();

		try {
			String sql = "select * from course";
			conn = DbCont.getConn();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				Course course = new Course();
				course.setId(rs.getString("Cid"));
				course.setName(rs.getString("Cname"));
				course.setDept(rs.getString("Cdept"));
				course.setTeacher(rs.getString("teacher"));
				courses.add(course);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return courses;
	}

	// 往学习表中插入学生选课信息
	public static void insertCourse(ArrayList cid, String usr) {
		java.sql.Connection conn = null;
		java.sql.PreparedStatement pstmt = null;
		java.sql.ResultSet rs = null;
		System.out.println(usr);
		try {
			for (int i = 0; i < cid.size(); i++) {
				conn = DbCont.getConn();
				String Cid = (String) cid.get(i);

				String sql1 = "select * from study where Sid=? and Cid=?";
				pstmt = conn.prepareStatement(sql1);
				pstmt.setString(1, usr);
				pstmt.setString(2, Cid);
				rs = pstmt.executeQuery();
				// System.out.println("sddsadassas"+usr);