testpaperdao.java

中应用程序的访问权限对Java Web Console 中应用程序的访问权限 成功登录 Web 控制台后,可能无法自动访问在该控制台中注册的所有应用程序。通常,必须安装应用程序,才能让所有的用户在控制

package edu.yinhe.mis.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;

import edu.yinhe.mis.dto.TestPaperDTO;
import edu.yinhe.mis.vo.OutlineVO;
import edu.yinhe.mis.vo.QuestionVO;
import edu.yinhe.mis.vo.TestPaperVO;
import edu.yinhe.mis.vo.TestpaperruleVO;
import edu.yinhe.system.model.BaseDAO;

/**
 * @author 王小龙
 */
public class TestPaperDAO extends BaseDAO {

	private Boolean flag;


	/**
	 * 删除指定ID的试卷
	 */
	public Object delete(Object arg0) throws SQLException {
		PreparedStatement ps = null;
		String sql = "";
		int index = 1;
		
		flag = false;
		String id =  (String) arg0;
		sql = "DELETE FROM testpaper WHERE testPaper_NO=?";
		
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(index, id);
			index = ps.executeUpdate();
		} catch (RuntimeException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		if(index!=-1){
			flag = true;
		}
		
		return flag;
	}

	public Object find() throws SQLException {

		return null;
	}

	/**
	 * 根据科目ID得到该科目下的试卷规则
	 */
	public Object find(Object arg0) throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		TestpaperruleVO vo = null;
		String sql = "";
		int index = 1;
		
		ArrayList list = new ArrayList();
		String objectID = (String) arg0;
		sql = "SELECT DISTINCT RULENAME,TESTPAPERRULE_NO FROM testpaperrule" +
				" WHERE OBJECT_NO=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(index++,objectID);
			rs = ps.executeQuery();
			
			while(rs.next()){
				vo = new TestpaperruleVO();
				vo.setRuleName(rs.getString("RULENAME"));
				vo.setTestPaperRuleNo(rs.getString("TESTPAPERRULE_NO"));
				list.add(vo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(ps!=null)ps = null;
			if(rs!=null)rs = null;
			vo = null;
			sql = null;
		}
		return list;
	}

	/**
	 * 得到大纲所有的科目列表
	 */
	public Object findAll() throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		OutlineVO vo = null;
		String sql = "";
		
		ArrayList list = new ArrayList();
		sql = "SELECT OBJECT_NO,OBJECT_NAME FROM outline";
		
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				vo = new OutlineVO();
				vo.setObjectNo(rs.getString("OBJECT_NO"));
				vo.setObjectName(rs.getString("OBJECT_NAME"));
				list.add(vo);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			if(ps!=null)ps = null;
			if(rs!=null)rs = null;
			vo = null;
			sql = null;
		}
		
		return list;
	}

	/**
	 * 查询所有试卷及根据条件查询试卷
	 */
	public Object findAll(Object arg0) throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		TestPaperVO vo = null;
		String sql ="";
		Integer count;
		TestPaperDTO dto =(TestPaperDTO) arg0;
		Object obj[]=new Object[2];
		ArrayList list=new ArrayList();
		
		sql="SELECT DISTINCT t.TESTPAPER_NO,t.TESTPAPER_NAME,o.OBJECT_NAME FROM testpaper t," +
				"outline o WHERE t.OBJECT_NAME=o.OBJECT_NAME";
		StringBuffer strbf = new StringBuffer(sql);
		
		try {
			count = this.getCount(dto);
			if(dto.getTestPaper_NO()!=null&&!"".equals(dto.getTestPaper_NO())){
				strbf.append(" AND t.TESTPAPER_NO='"+dto.getTestPaper_NO()+"'");
			}
			if(dto.getTestPaper_Name()!=null&&!"".equals(dto.getTestPaper_Name())){
				strbf.append(" AND t.TESTPAPER_NAME='"+dto.getTestPaper_Name()+"'");
			}
			if(dto.getObject_Name()!=null&&!"".equals(dto.getObject_Name())){
				strbf.append(" AND t.OBJECT_NAME='"+dto.getObject_Name()+"'");
			}
			if(dto.getCurrentPage()!=null&&!"".equals(dto.getCurrentPage())&&dto.getPageSize()!=null&&!"".equals(dto.getPageSize())){
				int i = (Integer.parseInt(dto.getCurrentPage())-1)*Integer.parseInt(dto.getPageSize());
				strbf.append(" LIMIT "+i+","+dto.getPageSize());
			}
			
			sql = strbf.toString();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				vo = new TestPaperVO();
				vo.setTestPaper_NO(rs.getString("TESTPAPER_NO"));
				vo.setTestPaper_Name(rs.getString("TESTPAPER_NAME"));
				vo.setObject_Name(rs.getString("OBJECT_NAME"));
				list.add(vo);
			}
			obj[0] = count;
			obj[1] = list;
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			if(ps!=null)ps = null;
			if(rs!=null)rs = null;
			sql =null;
			dto=null;
		}
			
	
		return obj;
	}

	/**
	 * 根据试卷ID得到试卷试题
	 */
	public Object findById(Object arg0) throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		QuestionVO vo = null;
		String sql = "";
		int index = 1;
		int count = 0;
		Object obj[] = new Object[2];
		ArrayList list = new ArrayList();
		TestPaperDTO dto =  (TestPaperDTO) arg0;
		
		sql = "SELECT q.QUESTION_ID,q.QUESTION_NAME,q.OBJECT_NAME FROM testpaper t,question q WHERE" +
				" t.QUESTION_ID=q.QUESTION_ID AND t.testPaper_NO=? LIMIT ?,?";
		
		try {
			count = (Integer) this.getMaxId(dto);
			
			ps = conn.prepareStatement(sql);
			ps.setString(index++, dto.getTestPaper_NO());
			ps.setInt(index++, (Integer.parseInt(dto.getCurrentPage())-1)*Integer.parseInt(dto.getPageSize()));
			ps.setInt(index++, Integer.parseInt(dto.getPageSize()));
			rs = ps.executeQuery();
			while(rs.next()){
				vo = new QuestionVO();
				vo.setQuestionID(rs.getInt("QUESTION_ID"));
				vo.setQuestionName(rs.getString("QUESTION_NAME"));
				vo.setObjectName(rs.getString("OBJECT_NAME"));
				list.add(vo);
			}
			
			obj[0] = count;
			obj[1] = list;
		} catch (RuntimeException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(ps!=null)ps = null;
			if(rs!=null)rs = null;
			sql = null;
			vo = null;
		}
		return obj;
	}

	/**
	 * 根据试卷规则ID得到具体的试卷规则 
	 */
	public Object findByObject(Object arg0) throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		TestpaperruleVO vo = null;
		String sql = "";
		int index = 1;
		ArrayList list = new ArrayList();
		TestPaperDTO dto = (TestPaperDTO) arg0;
		String ruleID = dto.getTestPaperRule_NO();
		sql = "SELECT TESTPAPERRULE_NO,QUESTIONTYPE_ID,RULENAME,OBJECT_NAME,QUESTIONAMOUNT," +
				"QUESTION_VALUE,QUESTIONDIFFICULTSCALE,SCORESCALE FROM testpaperrule" +
				" WHERE TESTPAPERRULE_NO=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(index++,ruleID);
			rs = ps.executeQuery();
			while(rs.next()){
				vo = new TestpaperruleVO();
				vo.setQuestionTypeID(rs.getInt("QUESTIONTYPE_ID"));
				vo.setObjectName(rs.getString("OBJECT_NAME"));
				vo.setQuestionAmount(rs.getInt("QUESTIONAMOUNT"));
				vo.setQuestionDifficultScale(rs.getString("QUESTIONDIFFICULTSCALE"));
				vo.setQuestionValue(rs.getInt("QUESTION_VALUE"));
				vo.setRuleName(rs.getString("RULENAME"));
				vo.setScoreScale(rs.getString("SCORESCALE"));
				list.add(vo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(ps!=null)ps = null;
			if(rs!=null)rs = null;
			sql = null;
		}
		return list;
	}

	/**
	 * 查询试卷的总条数
	 */
	public int getCount(Object arg0) throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		TestPaperVO vo = null;
		String sql ="";
		Integer count = 0;
		TestPaperDTO dto =(TestPaperDTO) arg0;
		sql="SELECT count(DISTINCT TESTPAPER_NO)result FROM testpaper  WHERE 1=1";
		StringBuffer strbf = new StringBuffer(sql);
		
		try {
			if(dto.getTestPaper_NO()!=null&&!"".equals(dto.getTestPaper_NO())){
				strbf.append(" AND TESTPAPER_NO='"+dto.getTestPaper_NO()+"'");
			}
			if(dto.getTestPaper_Name()!=null&&!"".equals(dto.getTestPaper_Name())){
				strbf.append(" AND TESTPAPER_NAME='"+dto.getTestPaper_Name()+"'");
			}
			if(dto.getObject_Name()!=null&&!"".equals(dto.getObject_Name())){
				strbf.append(" AND OBJECT_NAME='"+dto.getObject_Name()+"'");
			}
//			if(dto.getCurrentPage()!=null&&!"".equals(dto.getCurrentPage())&&dto.getPageSize()!=null&&!"".equals(dto.getPageSize())){
//				int i = (Integer.parseInt(dto.getCurrentPage())-1)*Integer.parseInt(dto.getPageSize());
//				strbf.append(" LIMIT "+i+","+dto.getPageSize());
//			}
			
			sql = strbf.toString();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				count = new Integer(rs.getInt("result"));
			}
		
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			if(ps!=null)ps = null;
			if(rs!=null)rs = null;
			sql =null;
			dto=null;
		}
			
	
		return count;
	}

	/**
	 * 返回一份试卷中的试题数量 
	 */
	public Object getMaxId(Object arg0) throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql ="";
		Integer count = 0;
		int index = 1;
		TestPaperDTO dto =(TestPaperDTO) arg0;
		sql="SELECT count(*)result FROM testpaper  WHERE TESTPAPER_NO=?";
		
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(index++, dto.getTestPaper_NO());
			rs = ps.executeQuery();
			while(rs.next()){
				count = new Integer(rs.getInt("result"));
			}
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(ps!=null)ps = null;
			if(rs!=null)rs = null;
			sql =null;
			dto=null;
		}
		return count;
	}

	/**
	 * 根据规则产生试卷并向试卷中插入试题
	 */
	public Object insert(Object arg0) throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql ="";
		int i = 0;
		int j = 1;
		TestPaperDTO dto = null;
		ArrayList qlist = null;
		QuestionVO vo = null;
		flag = false;
		Object obj[] = (Object[]) arg0;
		dto = (TestPaperDTO) obj[0];
		qlist = (ArrayList) obj[1];
		
		try {
			for(Iterator it=qlist.iterator();it.hasNext();){
				j++;
				vo = (QuestionVO) it.next();
				sql = "INSERT INTO testpaper(TESTPAPER_NO,TESTPAPER_NAME,QUESTION_ID,QUESTIONTYPE_ID,OBJECT_NAME,TESTPAPERRULE_NO,OBJECT_NO) " +
						"VALUE(?,?,?,?,?,?,?)";
				ps = conn.prepareStatement(sql);
				ps.setString(1, dto.getTestPaper_NO());
				ps.setString(2, dto.getTestPaper_Name());
				ps.setInt(3, vo.getQuestionID());
				ps.setInt(4, vo.getQuestionTypeID());
				ps.setString(5, vo.getObjectName());
				ps.setString(6, dto.getTestPaperRule_NO());
				ps.setString(7, dto.getObject_NO());
				i = ps.executeUpdate();
				if(i>=0){
					flag = true;
				}else{
					System.out.println("第"+j+"次向试卷中插入试题失败!!!");
				}
			}
		} catch (RuntimeException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(ps!=null)ps = null;
			if(rs!=null)rs = null;
			sql =null;
			dto = null;
			vo = null;
			qlist = null;
		}

		return flag;
	}

	/**
	 * 批量删除
	 */
	public Object update(Object arg0) throws SQLException {
		PreparedStatement ps = null;
		String sql = "";
		flag = false;
		int n;
		String strs[] = null;
		String str = (String) arg0;
		strs = str.split("-");
		sql = "DELETE FROM testpaper WHERE testPaper_NO=?";
		try {
			for(int i=0;i<strs.length;i++){
				ps = conn.prepareStatement(sql);
				ps.setString(1, strs[i]);
				n = ps.executeUpdate();
				if(n!=-1){
					flag = true;
				}
			}
		} catch (RuntimeException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(ps!=null)ps = null;
			sql =null;
			strs = null;
			str = null;
		}
		return flag;
	}
}