cexam.java

jsp+servlet+javabean实现的学校教务管理系统 (1)系统测试环境: Windows xp sp2 + myEclipse5.5 + Tomcat6.0 + SQLServer2

package com.sevenEleven.javaBean.exam;

import java.util.ArrayList;
import java.util.List;


import com.sevenEleven.Beans.CchooseTestBean;
import com.sevenEleven.Beans.CtextTestBean;
import com.sevenEleven.javaBean.CDBConnection;

/**
 * @author Tim 
 * 关于老师对试题表的操作Bean
 */

public class Cexam extends CDBConnection {
	@SuppressWarnings("unchecked")
	/**
	 * @author Tim Something about exam find the teacher who came into this
	 *         system and his's course and make view in the page of
	 *         teacher\exam_addTest.jsp
	 */
	private String id;

	private String countOfChoose1;

	private String countOfChoose2;

	private String countOfText;

	public List findLesson1(String teacherNumber) { // 由老师找必修课
		if (!checkParameter(teacherNumber)) {
			teacherNumber = "null";
		}
		try {
			sql = "select distinct  a.T_SKILL  \"course\" from teacher a, lesson1 b where a.T_SKILL = b.L1_Name and a.T_number ="
					+ " '" + teacherNumber + "' ";
			if (con == null) {
				DBConn(); // 连接
			}
			rs = executeQuery(sql);
			List list = new ArrayList();
			for (int i = 0; rs.next(); i++) {
				// rs.getCursorName().length();
				list.add(i, rs.getString("course"));
			}
			this.dropConnection();
			return list;

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	/*
	 * @author Tim Something about exam find the teacher who came into this
	 * system and his's course and make view in the page of
	 * teacher\exam_addTest.jsp
	 */
	@SuppressWarnings("unchecked")
	public List findLesson2(String teacherNumber) { // 由老师找选修课
		if (!checkParameter(teacherNumber)) {
			teacherNumber = "null";
		}
		try {
			// Clesson1Bean lesson2 = null;
			sql = "select distinct  a.T_SKILL  \"course\" from teacher a, lesson2 b where a.T_SKILL = b.L2_Name and a.T_number ="
					+ " '" + teacherNumber + "' ";
			if (con == null) {
				DBConn(); // 连接
			}
			rs = executeQuery(sql);
			List list = new ArrayList();
			for (int i = 0; rs.next(); i++) {
				// rs.getCursorName().length();
				list.add(i, rs.getString("course"));
			}
			this.dropConnection();
			return list;

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	/*
	 * @author tim insert infomation of one test to the Table "Choose1"
	 * 
	 */
	public boolean insertIntoChoose1Table(CchooseTestBean test) {
		try {
			sql = "insert into choose1 values ("// + test.getCh_id() + ", "
					+ test.getL_id() + ", " + " '" + test.getCh_question()
					+ "' , '" + test.getKeya() + "' , '" + test.getKeyb()
					+ "' , '" + test.getKeyc() + "' , '" + test.getKeyd()
					+ "' , '" + test.getAnswer() + "' , " + test.getType()
					+ ") ";

			if (con == null) {
				DBConn(); // 连接
			}
			if (this.executeUpdate(sql) != 0) {
				executeUpdate("commit");
				return true;
			}

		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return false;
	}

	/*
	 * @author tim update infomation of one test to the Table "Choose1"
	 * 
	 */
	public boolean updateChoose1Table(CchooseTestBean choose) {
		try {

			sql = "update choose1 set CH_QUESTION= '" + choose.getCh_question()
					+ "' ," + "KEYA= '" + choose.getKeya() + "' ," + "KEYB= '"
					+ choose.getKeyb() + "' ," + "KEYC= '" + choose.getKeyc()
					+ "' ," + "KEYD= '" + choose.getKeyd() + "' ,"
					+ "CH_ANSWER= '" + choose.getAnswer() + "' "
					+ " WHERE CH_ID=" + choose.getCh_id();
			if (con == null) {
				DBConn(); // 连接
			}
			if (this.executeUpdate(sql) != 0) {
				executeUpdate("commit");
				return true;
			}

		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return false;
	}

	/*
	 * @author tim insert infomation of one test to the Table "Choose2" by the
	 * attribute of courseType
	 */
	public boolean insertIntoChoose2Table(CchooseTestBean test) {
		try {
			sql = "insert into choose2 values (" //+ test.getCh_id() + ", "
					+ test.getL_id() + ", " + " '" + test.getCh_question()
					+ "' , '" + test.getKeya() + "' , '" + test.getKeyb()
					+ "' , '" + test.getKeyc() + "' , '" + test.getKeyd()
					+ "' , '" + test.getAnswer() + "' , " + test.getType()
					+ ") ";

			if (con == null) {
				DBConn(); // 连接
			}
			if (this.executeUpdate(sql) != 0) {
				executeUpdate("commit");
				return true;
			}
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	/*
	 * @author tim update infomation of one test to the Table "Choose2" by the
	 * attribute of courseType
	 */
	public boolean updateChoose2Table(CchooseTestBean choose) {
		try {
			sql = "update choose2 set CH_QUESTION= '" + choose.getCh_question()
					+ "' ," + "KEYA= '" + choose.getKeya() + "' ," + "KEYB= '"
					+ choose.getKeyb() + "' ," + "KEYC= '" + choose.getKeyc()
					+ "' ," + "KEYD= '" + choose.getKeyd() + "' ,"
					+ "CH_ANSWER= '" + choose.getAnswer() + "' "
					+ " WHERE CH_ID=" + choose.getCh_id();

			if (con == null) {
				DBConn(); // 连接
			}
			if (this.executeUpdate(sql) != 0) {
				executeUpdate("commit");
				return true;
			}
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * @author tim insert infomation of one test to the Table "text1" by the
	 *         attribute of courseType
	 */
	public boolean insertIntoText1Table(CtextTestBean text) {
		try {
			sql = "insert into text1 values ( " //+ text.getText_id() + ", "
					+ text.getL_id() + ", '" + text.getText_question()
					+ "' , '" + text.getText_answer() + "') ";

			if (con == null) {
				DBConn(); // 连接
			}
			if (this.executeUpdate(sql) != 0) {
				executeUpdate("commit");
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return false;
	}

	/*
	 * @author tim update infomation of one test to the Table "text1" by the
	 * attribute of courseType
	 */
	public boolean updateText1Table(CtextTestBean text) {
		try {
			sql = "update text1 set TEXT_QUESTION= '" + text.getText_question()
					+ "' ," + "TEXT_ANSWER= '" + text.getText_answer() + "' "
					+ " WHERE TEXT_ID=" + text.getText_id();

			if (con == null) {
				DBConn(); // 连接
			}
			if (this.executeUpdate(sql) != 0) {
				executeUpdate("commit");
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return false;
	}

	/*
	 * @author tim insert infomation of one test to the Table "text2" by the
	 * attribute of courseType
	 */
	public boolean insertIntoText2Table(CtextTestBean text) {
		try {
			sql = "insert into text2 values ( "// + text.getText_id() + ", "
					+ text.getL_id() + ", '" + text.getText_question()
					+ "' , '" + text.getText_answer() + "') ";

			if (con == null) {
				DBConn(); // 连接
			}
			if (this.executeUpdate(sql) != 0) {
				executeUpdate("commit");
				return true;
			}
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	/*
	 * @author tim update infomation of one test to the Table "text2" by the
	 * attribute of courseType
	 */
	public boolean updateText2Table(CtextTestBean text) {
		try {
			sql = "update text2 set TEXT_QUESTION= '" + text.getText_question()
					+ "' ," + "TEXT_ANSWER= '" + text.getText_answer() + "' "
					+ " WHERE TEXT_ID=" + text.getText_id();

			if (con == null) {
				DBConn(); // 连接
			}
			if (this.executeUpdate(sql) != 0) {
				executeUpdate("commit");
				return true;
			}
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	/*
	 * @author tim delete a item of this table
	 * 
	 */
	public boolean deleteItemOfChoose1Table(String id) {
		try {
			sql = "delete Choose1 where CH_ID =" + id;

			if (con == null) {
				DBConn(); // 连接
			}
			if (this.executeUpdate(sql) != 0) {
				executeUpdate("commit");
				return true;
			}

		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return false;
	}

	/*
	 * @author tim delete a item of this table
	 * 
	 */
	public boolean deleteItemOfChoose2Table(String id) {
		try {
			sql = "delete Choose2 where CH_ID =" + id;

			if (con == null) {
				DBConn(); // 连接
			}
			if (this.executeUpdate(sql) != 0) {
				executeUpdate("commit");
				return true;
			}

		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return false;
	}

	/*
	 * @author tim delete a item of this table
	 * 
	 */
	public boolean deleteItemOfText1Table(String id) {
		try {
			sql = "delete text1 where TEXT_ID =" + id;

			if (con == null) {
				DBConn(); // 连接
			}
			if (this.executeUpdate(sql) != 0) {
				executeUpdate("commit");
				return true;
			}

		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return false;
	}

	/*
	 * @author tim delete a item of this table
	 * 
	 */
	public boolean deleteItemOfText2Table(String id) {
		try {
			sql = "delete text2 where TEXT_ID =" + id;

			if (con == null) {
				DBConn(); // 连接
			}
			if (this.executeUpdate(sql) != 0) {
				executeUpdate("commit");
				return true;
			}

		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return false;
	}

	/*
	 * @author Tim 由课程名得到对应的课程ID
	 * 
	 */
	// 由lesson1 得到它的id
	public int getLesson1Id(String course) {
		try {
			int id = 0;
			sql = "select distinct L1_ID  from lesson1 where L1_NAME = " + " '"
					+ course + "' ";

			if (con == null) {
				DBConn(); // 连接
			}
			rs = this.executeQuery(sql);

			for (int i = 0; rs.next(); i++) {
				id = rs.getInt("L1_ID");
			}
			return id;
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}

	}

	/*
	 * @author Tim 由课程名得到对应的课程ID
	 * 
	 */
	// 由lesson2 得到它的id
	public int getLesson2Id(String course) {
		try {
			int id = 0;
			sql = "select distinct L2_ID  from lesson2 where L2_NAME = " + " '"
					+ course + "' ";

			if (con == null) {
				DBConn(); // 连接
			}
			rs = this.executeQuery(sql);

			for (int i = 0; rs.next(); i++) {
				id = rs.getInt("L2_ID");
			}
			return id;
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}

	}
  
	// 过滤非法字符
	public boolean checkParameter(String para) {
		int flag = 0;
		flag += para.indexOf("'") + 1;
		flag += para.indexOf(";") + 1;
		flag += para.indexOf("=") + 1;
		flag += para.indexOf("|") + 1;
		flag += para.indexOf("<") + 1;
		flag += para.indexOf(">") + 1;
		if (flag != 0) {
			System.out.println("提交了非法字符!!!");
			return false;
		}
		return true;
	}
	/*
	 * @author Tim 得到表Choose1的记录集条数,
	 *  @return 记录集的条数
	 * 
	 */
	public int getLengthOfChoose1Table(){
		int length = 0;
		try{
			sql = "select count(*) from Choose1";
		
		if (con == null) {
			DBConn(); // 连接
		}
		rs = executeQuery(sql);
		while(rs.next()){
		length = rs.getInt(1);
		}
		this.dropConnection();