databasesqlcommand.java

简单的在线考试系统 提供添加修改 考试等操作

package olts;

import java.sql.*;

public class DatabaseSQLCommand implements TestCommand {
	/**this class is the implemention of interface TestCommand that
	 * perform database access operation with preparedStatements
	 */
	//reference to database connection
	private Connection connection;
	private String questionContent;
	private String questionType;
	private String questionAnswer;
	private String questionScore;
		
	//reference to prepared statement for locating entry
	private PreparedStatement sqlQuestionFound;
	private PreparedStatement sqlInsert;
	private PreparedStatement sqlUpdate;
	private PreparedStatement sqlDelete;
	private PreparedStatement sqlQuestionType;
	private PreparedStatement sqlQuestionQuery;

	
	/*private PreparedStatement sqlTruefalseQuestionFound;
	private PreparedStatement sqlTruefalseInsert;
	private PreparedStatement sqlTruefalseUpdate;
	private PreparedStatement sqlTruefalseDelete;
	private PreparedStatement sqlTruefalseQuery;
	
	private PreparedStatement sqlBlankQuestionFound;
	private PreparedStatement sqlBlankInsert;
	private PreparedStatement sqlBlankUpdate;
	private PreparedStatement sqlBlankDelete;
	private PreparedStatement sqlBlankQuery;*/
	
	
	public DatabaseSQLCommand() throws Exception{
		connect();
		//locate question
		/*sqlQuestionFound=connection.prepareStatement("SELECT id,content,option1," +
				"option2,option3,option4,score,difficulty,timeLimited" +
				"FROM SingleChoiceItem");*/
		sqlQuestionQuery=connection.prepareStatement("SELECT * FROM SingleChoiceItem WHERE id=?");
		sqlQuestionFound=connection.prepareStatement("SELECT * FROM SingleChoiceItem WHERE id=?");
		//sqlQuery=connection.prepareStatement("SELECT*FROM SingleChoiceItem ORDER BY id");
		sqlInsert=connection.prepareStatement("INSERT INTO SingleChoiceItem " +
				"VALUES(?,?,?,?,?,?,?)");
		sqlDelete=connection.prepareStatement("DELETE FROM SingleChoiceItem WHERE id=?");
		sqlUpdate=connection.prepareStatement("UPDATE SingleChoiceItem" +
				"SET content=?,answer=?,score=?,difficulty=?,timeLimited=?" +
				"WHERE id=?");
		sqlQuestionType=connection.prepareStatement("SELECT type FROM SingleChoiceItem " +
				"WHERE id=?");
		//sqlQuestionSort=connection.prepareStatement("SELECT questionType FROM SingleChoiceItem" +
				//"WHERE id=?");
		//sqlMark=sqlQuestionQuery=connection.prepareStatement("SELECT * FROM SingleChoiceItem WHERE id=?");
		
		/*
		sqlTruefalseQuestionFound=connection.prepareStatement("SELECT id FROM TruefalseItem");
		sqlTruefalseQuery=connection.prepareStatement("SELECT*FROM TruefalseItem ORDER BY id");
		sqlTruefalseInsert=connection.prepareStatement("INSERT INTO TruefalseItem " +
				"VALUES(?,?,?,?,?,?,?,?,?,?)");
		sqlTruefalseDelete=connection.prepareStatement("DELETE FROM TruefalseItem WHERE id=?");
		sqlTruefalseUpdate=connection.prepareStatement("UPDATE TruefalseItem " +
				"SET option1,option2,option3,option4,score,difficulty," +
				"answer,timeLimited WHERE id=?");
		
		sqlBlankQuestionFound=connection.prepareStatement("SELECT id FROM BlankItem");
		sqlBlankQuery=connection.prepareStatement("SELECT*FROM BlankItem ORDER BY id");
		sqlBlankInsert=connection.prepareStatement("INSERT INTO BlankItem " +
				"VALUES(?,?,?,?,?,?,?,?,?,?)");
		sqlBlankDelete=connection.prepareStatement("DELETE FROM BlankItem WHERE id=?");
		sqlBlankUpdate=connection.prepareStatement("UPDATE BlankItem " +
				"SET option1,option2,option3,option4,score,difficulty," +
				"answer,timeLimited WHERE id=?");	*/
	}
	private void connect()throws Exception{
		/*obtain a connection to question database,method may throw 
		 * testexctption or SQLException
		 */
		String driver="jdbc:odbc:question";
		String url="sun.jdbc.odbc.JdbcOdbcDriver";
		Class.forName(url);
		connection=DriverManager.getConnection(driver, "wee", "wee");
		connection.setAutoCommit(false);
	}
	public boolean singleQuestionFound(String num) throws TestException {
		// TODO Auto-generated method stub		
		try{
			int n=0;
			n=Integer.parseInt(num);
			sqlQuestionFound.setInt(1, n);
			ResultSet questionFound=sqlQuestionFound.executeQuery();
			while(questionFound.next()){	
				if(num.equals(questionFound.getString(1))){
					return true;					
					}
				}
			return false;
			}
			catch(SQLException sqle){
				throw new TestException(sqle);
				}
	}
	public boolean QuestionFound(int num) throws TestException {
		//lacate question in the database	
		try{
			sqlQuestionFound.setInt(1, num);
			ResultSet questionFound=sqlQuestionFound.executeQuery();
			while(questionFound.next()){	
				if(num==Integer.parseInt(questionFound.getString(1))){
					return true;					
					}
				}
			return false;
			}
			catch(SQLException sqle){
				throw new TestException(sqle);
				}
	}
	/*public boolean truefalseQuestionFound(String num) throws TestException {
		// TODO Auto-generated method stub		
		try{
			ResultSet questionFound=sqlTruefalseQuestionFound.executeQuery();
			while(questionFound.next()){				
				if(num==questionFound.getString(1)){
					return true;					
					}
				}
			return true;
			}
			catch(SQLException sqle){
				throw new TestException(sqle);
				}
	}
	public boolean blankQuestionFound(String num) throws TestException {
		// TODO Auto-generated method stub		
		try{
			ResultSet questionFound=sqlBlankQuestionFound.executeQuery();
			while(questionFound.next()){				
				if(num==questionFound.getString(1)){
					return true;					
					}
				}
			return true;
			}
			catch(SQLException sqle){
				throw new TestException(sqle);
				}
	}*/
	public String queryQuestion(int id) throws SQLException{
		//return the specify question content in the database
		sqlQuestionQuery.setInt(1,id);
		ResultSet resultQuery=sqlQuestionQuery.executeQuery();
		if(resultQuery.next()){		
			questionContent=resultQuery.getString(2);
		}
		return questionContent;
	}
	public String queryType(int id) throws SQLException{
		//return the specify question type in the database
		sqlQuestionQuery.setInt(1,id);
		ResultSet resultQuery=sqlQuestionQuery.executeQuery();
		if(resultQuery.next()){		
			questionType=resultQuery.getString(7);
		}
		return questionType;
	}
	public String queryAnswer(int id) throws SQLException{
		//return the specify question answer in the database
		sqlQuestionQuery.setInt(1,id);
		ResultSet resultQuery=sqlQuestionQuery.executeQuery();
		if(resultQuery.next()){		
			questionAnswer=resultQuery.getString(3);
		}
		return questionAnswer;
	}
	public String queryScore(int id) throws SQLException{
		//return the specify question score in the database
		sqlQuestionQuery.setInt(1,id);
		ResultSet resultQuery=sqlQuestionQuery.executeQuery();
		if(resultQuery.next()){		
			questionScore=resultQuery.getString(4);
		}
		return questionScore;
	}
	public void deleteQusetion(String num) throws TestException{
		// delete the specify question in the database
		try {
			connection.setAutoCommit(false);
			int i=Integer.parseInt(num);
			sqlDelete.setInt(1,i);
			sqlDelete.executeUpdate();
			connection.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public boolean insertQuestion(TestQuestionEntry question) throws TestException {
		// insert a question into the database
		try {
			int result;			
			sqlInsert.setString(1, question.getID());
			sqlInsert.setString(2, question.getContent());
			//sqlInsert.setString(3, question.getOption1());
			//sqlInsert.setString(4, question.getOption2());
			//sqlInsert.setString(5, question.getOption3());
			//sqlInsert.setString(6, question.getOption4());
			sqlInsert.setString(3, question.getAnswer());
			sqlInsert.setString(4, question.getScore());
			sqlInsert.setString(5,question.getDifficulty());
			sqlInsert.setString(6, question.getTimelimited());
			sqlInsert.setString(7,question.getType());
			result=sqlInsert.executeUpdate();
			if(result==0){
				connection.rollback();
				return false;
			}
			connection.commit();
						
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return true;
	}
	public boolean saveQuestion(TestQuestionEntry question) throws TestException {
		// save the question
		try{
		ResultSet questionFound=sqlQuestionFound.executeQuery();
		while(questionFound.next()){				
			if(question.getID()==questionFound.getString(1)){
				break;
				//return false;					
				}
			}
		return true;
		}
		catch(SQLException sqle){
			throw new TestException(sqle);
			}
		}		
	public TestQuestionEntry searchQusetion(String id) {
		// serch question in the database  and return 
		//an TestQuestionEntry type value
		try{
			TestQuestionEntry question=new TestQuestionEntry();
			connection.setAutoCommit(false);		
			int num=Integer.parseInt(id);
			sqlQuestionQuery.setInt(1, num);
			ResultSet result=sqlQuestionQuery.executeQuery();

			//create new question	
			if(result.next())
			{
				String content=result.getString(2);
				question.setID(result.getString(1));			
				question.setContent(content);	
				question.setAnswer(result.getString(3));			
				question.setScore(result.getString(4));			
				question.setDifficulty(result.getString(5));
				question.setTimelimited(result.getString(6));
			}
			return question;
		}
		catch(SQLException sqle){
			return null;
		}
	}
	public String questionChoice(int num){
		//return the question type but it provide an int paramenter
		String type=null;
		ResultSet questionType;
		try {
			questionType = sqlQuestionType.executeQuery();
			if(questionType.next())type=questionType.getString(2);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return type;	
	}
	public void sqlUpdate(TestQuestionEntry question){
			try {
				sqlUpdate.setString(1, question.getContent());
				sqlUpdate.setString(2, question.getAnswer());
				sqlUpdate.setString(3, question.getScore());
				sqlUpdate.setString(4, question.getDifficulty());
				sqlUpdate.setString(5, question.getTimelimited());
				int num=0;
				num=Integer.parseInt(question.getID());
				sqlUpdate.setInt(6, num);
				int result=sqlUpdate.executeUpdate();
				if(result==0){
				 connection.rollback();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}	
	}
	public int lengthCount(){
		//count the database current length
		int length=0;
		try {
			//ResultSet countLength=sqlLength.executeQuery();
			ResultSet rs = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE).executeQuery("select * from  SingleChoiceItem" );
			rs.last();
			length=rs.getRow();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return length;	
	}
	public void close(){
		//close the sql command
		try{
			sqlInsert.close();
			sqlDelete.close();
			sqlUpdate.close();
		}
		catch(SQLException sqlException){
			sqlException.printStackTrace();
		}
	}
	protected void finalize(){
		close();
	}

	
   
}