data.java

jsp级联下拉菜单

            throws SQLException
        {
            Connection          conn = database.getConnection();
            int                 param = 1;

            PreparedStatement   ps = Utils.prepare
                (
                 conn,
                 "insert into Question\n" +
                 "( testID, questionName, difficulty, " +
                 "numberOfChoices, correctChoice )\n" +
                 "values ( ?, ?, ?, ?, ? )"
                 );

            ps.setInt( param++, _test.getPrimaryKey( database ) );
            ps.setString( param++, _questionName );
            ps.setInt( param++, _difficulty );
            ps.setInt( param++, _numberOfChoices );
            ps.setInt( param++, _correctChoice );

            ps.execute();
            Utils.close( ps );
        }

        protected   PreparedStatement   getKeyFinder( Database database )
            throws SQLException
        {
            Connection          conn = database.getConnection();
            PreparedStatement   ps = Utils.prepare
                (
                 conn,
                 "select questionID from Question where questionName = ?"
                 );

            ps.setString( 1, _questionName );

            return ps;
        }

    }

    public  static  class   QuestionTaking  extends KeyedObject
    {
        private TestTaking  _testTaking;
        private Question    _question;
        private int         _actualChoice;

        public  QuestionTaking
            (
             TestTaking testTaking,
             Question   question,
             int        actualChoice
             )
        {
            _testTaking = testTaking;
            _question = question;
            _actualChoice = actualChoice;
        }

        public  TestTaking  getTestTaking() { return _testTaking; }
        public  Question    getQuestion() { return _question; }
        public  int         getActualChoice() { return _actualChoice; }

        protected   void                createMinion( Database database )
            throws SQLException
        {
            Connection          conn = database.getConnection();
            int                 param = 1;

            PreparedStatement   ps = Utils.prepare
                (
                 conn,
                 "insert into QuestionTaking\n" +
                 "( questionID, takingID, actualChoice ) values ( ?, ?, ? )"
                 );

            ps.setInt( param++, _question.getPrimaryKey( database ) );
            ps.setInt( param++, _testTaking.getPrimaryKey( database ) );
            ps.setInt( param++, _actualChoice );

            ps.execute();
            Utils.close( ps );
        }

        protected   PreparedStatement   getKeyFinder( Database database )
            throws SQLException
        {
            // no primary key for this table!
            throw notImplemented();
        }
    }

    ////////////////////////////////////////////////////////
    //
    // CONSTRUCTOR
    //
    ////////////////////////////////////////////////////////

    /**
     * <p>
     * Create an object to populate the tables for the Scores application.
     * </p>
     */
    public  Data()
    {
    }
    
    ////////////////////////////////////////////////////////
    //
    // PUBLIC BEHAVIOR
    //
    ////////////////////////////////////////////////////////

    /**
     * <p>
     * Initialize the Scores application with data.
     * </p>
     */
    public  void    initialize( Database database )
        throws SQLException
    {
        initialize( database, SCHOOLS );
        initialize( database, STUDENTS );
        initialize( database, TESTS );
        initializeQuestions( database );
    }

    /**
     * <p>
     * Make the students take their tests.
     * </p>
     */
    public  void    takeTests( Database database )
        throws SQLException
    {
        int                 testCount = TESTS.length;
        int                 takings = 0;

        for ( int i = 0; i < testCount; i++ )
        {
            takings = takeTest( database, TESTS[ i ], takings );
        }
    }

    /**
     * <p>
     * Make all eligible students take the test.
     * Grammar school tests are taken
     * by all students. High school tests are only taken by high school
     * students. Returns the number of test takings so far (this is used
     * to throttle diagnostic noise.
     * </p>
     */
    public  int takeTest( Database database, Test test, int takings )
        throws SQLException
    {
        Logger      log = Logger.getLogger();
        boolean     loggingEnabled = log.isLoggingEnabled();
        int         studentCount = STUDENTS.length;
        double      highSchoolScore = test.getHighSchoolScore();
        double      grammarSchoolScore =
            highSchoolScore - GRAMMAR_SCHOOL_PENALTY;
        double      fluctuation;
        double      targetScore;

        for ( int i = 0; i < studentCount; i++ )
        {
            Student     student = STUDENTS[ i ];
            boolean     grammarSchoolStudent =
                student.getSchool().isGrammarSchool();

            if ( grammarSchoolStudent && (!test.isGrammarSchoolTest()) )
            { continue; }

            if ( grammarSchoolStudent )
            { targetScore = grammarSchoolScore; }
            else
            { targetScore = highSchoolScore; }

            fluctuation = (((double) i) * FLUCTATION_MULTIPLIER) * (-1.0);
            targetScore += fluctuation;

            try {
                //
                // Only print out the first test taking.
                // They all look alike.
                //
                if ( takings > 0 ) { log.enableLogging( false ); }

                takeTest( database, student, test, targetScore );
                takings++;
            }
            finally
            {
                log.enableLogging( loggingEnabled );
            }
        }

        return takings;
    }

    /**
     * <p>
     * Take a test.
     * </p>
     */
    public  void    takeTest
        (
         Database   database,
         Student    student,
         Test       test,
         double     initialTargetScore
         )
        throws SQLException
    {
        Logger      log = Logger.getLogger();
        boolean     loggingEnabled = log.isLoggingEnabled();
        TestTaking  testTaking;
        
        // don't print out the chatter from creating these rows.
        try {
            log.enableLogging( false );
            
            testTaking = takeTestingMinion
                ( database, student, test, initialTargetScore );
        }
        finally
        {
            log.enableLogging( loggingEnabled );
        }

        // I want to see this, though
        finishTest( database, testTaking );
    }

    /**
     * <p>
     * The guts of test taking, removed here
     * to a separate method so that we can turn off logging.
     * </p>
     */
    private TestTaking  takeTestingMinion
        (
         Database   database,
         Student    student,
         Test       test,
         double     initialTargetScore
         )
        throws SQLException
    {
        TestTaking          testTaking = new TestTaking( student, test );
        Connection          conn = database.getConnection();
        double              targetScore = makeTargetScore
            ( database, student, test, initialTargetScore );

        //
        // Initiate the test.
        //
        testTaking.create( database );

        //
        // Now get the questions which have to be answered.
        //

        PreparedStatement   ps = Utils.prepare
            (
             conn,
             "select questionName, difficulty, numberOfChoices, " +
             "correctChoice from Question\n" +
             "where testID = ?"
             );
        ps.setInt( 1, test.getPrimaryKey( database ) );

        ResultSet           rs = ps.executeQuery();
        
        double              weightSoFar = 0.0;
        double              answersSoFar = 0.0;
        double              scoreSoFar = 0.0;

        while( rs.next() )
        {
            int             column = 1;
            Question        question = new Question
                (
                 rs.getString( column++ ),
                 rs.getInt( column++ ),
                 rs.getInt( column++ ),
                 rs.getInt( column++ ),
                 test
                 );
            int             correctChoice = question.getCorrectChoice();
            int             difficulty = question.getDifficulty();
            int             actualChoice;

            //
            // Answer the question correctly if we haven't achieved our
            // target score.
            //
            if ( scoreSoFar <= targetScore )
            { actualChoice = correctChoice; }
            else
            { actualChoice = correctChoice - 1; }

            weightSoFar += Utils.weighQuestion( difficulty );
            answersSoFar += Utils.scoreAnswer
                (
                 difficulty,
                 question.getNumberOfChoices(),
                 correctChoice,
                 actualChoice
                 );
            scoreSoFar = Utils.finishScore( weightSoFar, answersSoFar );

            QuestionTaking  questionTaking =
                new QuestionTaking( testTaking, question, actualChoice );

            questionTaking.create( database );
        }

        return testTaking;
    }

    /**
     * <p>
     * The student's grade should improve with each taking.
     * </p>
     */
    private double  makeTargetScore
        (
         Database   database,
         Student    student,
         Test       test,
         double     initialTargetScore
         )
        throws SQLException
    {
        int                 param = 1;
        PreparedStatement   ps = Utils.prepare
            (
             database.getConnection(),
             "select count(*) from TestTaking\n" +
             "where studentID = ? and testID = ?"
             );

        ps.setInt( param++, student.getPrimaryKey( database ) );
        ps.setInt( param++, test.getPrimaryKey( database ) );
        
        int     takingCount = Utils.getScalarValue( ps );

        return
            (((double) takingCount) * IMPROVEMENT_PER_TAKING) +
            initialTargetScore;
    }

    /**
     * <p>
     * Finish taking a Test. This involves updating the datestamp and
     * triggering Test scoring.
     * </p>
     */
    private void    finishTest( Database database, TestTaking testTaking )
        throws SQLException
    {
        Connection          conn = database.getConnection();
        int                 param = 1;
        java.sql.Date       date =
            new java.sql.Date( System.currentTimeMillis() );
        PreparedStatement   ps = Utils.prepare
            (
             conn,
             "update TestTaking set takingDate = ? where takingID = ?\n"
             );

        ps.setDate( param++, date );
        ps.setInt( param++, testTaking.getPrimaryKey( database ) );

        ps.execute();

        Utils.close( ps );
    }

    /**
     * <p>
     * Report which students in the given school incorrectly answered the
     * given question.
     * </p>
     */
    public void    reportWhoNeedsImprovement
        ( Database database, School school, String questionName )
        throws SQLException
    {
        Connection          conn = database.getConnection();
        int                 param = 1;
        PreparedStatement   ps = Utils.prepare
            (
             conn,
             "select st.lastName, st.firstName\n" +
             "from School sc, Student st,\n" +
             "     LastTaking lt, QuestionTaking qt,\n" +
             "     Question q\n" +
             "where\n" +
             "q.questionName = ?\n" +
             "and sc.schoolID = ?\n" +
             "\n" +
             "and q.questionID = qt.questionID\n" +
             "and sc.schoolID = st.schoolID\n" +
             "\n" +
             "and lt.testID = q.testID\n" +
             "and lt.studentID = st.studentID\n" +
             "and lt.takingID = qt.takingID\n" +
             "\n" +
             "and scoreAnswer"+
             "( q.difficulty, q.numberOfChoices, " +
             "q.correctChoice, qt.actualChoice )\n" +
             "  < weighQuestion( q.difficulty )\n" +
             "\n" +
             "order by st.lastName, st.firstName\n"
             );

        ps.setString( param++, questionName );
        ps.setInt( param++, school.getPrimaryKey( database ) );

        ResultSet   rs = ps.executeQuery();

        Database.prettyPrint( conn, rs );

        Utils.close( rs );
        Utils.close( ps );
    }

    ////////////////////////////////////////////////////////
    //
    // MINIONS
    //
    ////////////////////////////////////////////////////////
    
    /**
     * <p>
     * Create a bunch of questions from the template questions.
     * </p>
     */
    private void    initializeQuestions( Database database )
        throws SQLException
    {
        int     count = QUESTIONS.length;

        for ( int i = 0; i < count; i++ )
        {
            Question    template = QUESTIONS[ i ];

            for ( int j = 0; j < QUESTIONS_PER_TEMPLATE; j++ )
            {
                Question    clone = new Question
                    (
                     template.getQuestionName() + '_' + j,
                     template.getDifficulty(),
                     template.getNumberOfChoices(),
                     template.getCorrectChoice(),
                     template.getTest()
                     );

                clone.create( database );
            }
        }
    }

    /**
     * <p>
     * Populate a table with its objects.
     * </p>
     */
    private void    initialize( Database database, KeyedObject[] objects )
        throws SQLException
    {
        int     count = objects.length;

        for ( int i = 0; i < count; i++ )
        { objects[ i ].create( database ); }
    }

}