database.java

jsp级联下拉菜单

             "create function computeAge\n" +
             "(\n" +
             "    birthday date\n" +
             ")\n" +
             "returns int\n" +
             "language java\n" +
             "parameter style java\n" +
             "no sql\n" +
             "external name 'org.apache.derbyDemo.scores.proc.Functions.computeAge'\n"
             );
        Utils.executeDDL
            (
             conn,
             "create function getMedianTestScore\n" +
             "(\n" +
             "    testID int\n" +
             ")\n" +
             "returns double\n" +
             "language java\n" +
             "parameter style java\n" +
             "reads sql data\n" +
             "external name " +
             "'org.apache.derbyDemo.scores.proc.Functions.getMedianTestScore'\n"
             );
        Utils.executeDDL
            (
             conn,
             "create function vetChoice\n" +
             "(\n" +
             "    actualChoice int,\n" +
             "    questionID   int\n" +
             ")\n" +
             "returns int\n" +
             "language java\n" +
             "parameter style java\n" +
             "reads sql data\n" +
             "external name 'org.apache.derbyDemo.scores.proc.Functions.vetChoice'\n"
             );

        log.logBanner
            ( "Creating procedures..." );

        Utils.executeDDL
            (
             conn,
             "create procedure ScoreTestTaking\n" +
             "( in takingID int )\n" +
             "language java\n" +
             "parameter style java\n" +
             "modifies sql data\n" +
             "external name 'org.apache.derbyDemo.scores.proc.Procedures.ScoreTestTaking'\n"
             );

        log.logBanner
            (
             "Creating tables. Note the function " +
             "in the check constraint on QuestionTaking..." );

        Utils.executeDDL
            (
             conn,
             "create table School\n" +
             "(\n" +
             " schoolID   int primary key generated always as identity,\n" +
             " schoolName varchar( 20 ) not null,\n" +
             "\n" +
             " unique( schoolName )\n" +
             ")\n"
             );
        Utils.executeDDL
            (
             conn,
             "create table Student\n" +
             "(\n" +
             " studentID int primary key generated always as identity,\n" +
             " schoolID  int not null references School( schoolID ),\n" +
             " lastName  varchar( 10 ) not null,\n" +
             " firstName varchar( 10 ) not null,\n" +
             " birthday  date not null,\n" +
             "\n" +
             " unique( lastName, firstName )\n" +
             ")\n"
             );
        Utils.executeDDL
            (
             conn,
             "create table Test\n" +
             "(\n" +
             " testID   int primary key generated always as identity,\n" +
             " testName varchar( 20 ) not null,\n" +
             "\n" +
             " unique( testName )\n" +
             ")\n"
             );
        Utils.executeDDL
            (
             conn,
             "create table TestTaking\n" +
             "(\n" +
             " takingID   int primary key generated always as identity,\n" +
             " studentID  int not null references Student( studentID ),\n" +
             " testID     int not null references Test( testID ),\n" +
             " takingDate date,\n" +
             " score      double not null\n" +
             ")\n"
             );
        Utils.executeDDL
            (
             conn,
             "create table Question\n" +
             "(\n" +
             " questionID      int primary key " +
             "   generated always as identity,\n" +
             " testID          int not null references Test( testID ),\n" +
             " questionName    varchar( 10 ) not null unique,\n" +
             " difficulty      int not null,\n" +
             " numberOfChoices int not null,\n" +
             " correctChoice   int not null,\n" +
             "\n" +
             " check ( ( correctChoice > -1 ) " +
             " and ( correctChoice < numberOfChoices ) )\n" +
             ")\n"
             );
        Utils.executeDDL
            (
             conn,
             "create table QuestionTaking\n" +
             "(\n" +
             " questionID   int not null" +
             "  references Question( questionID ),\n" +
             " takingID     int not null" +
             "  references TestTaking( takingID ),\n" +
             " actualChoice int not null,\n" +
             "\n" +
             " unique( questionID, takingID ),\n" +
             " check ( vetChoice( actualChoice, questionID ) > 0 )\n" +
             ")\n"
             );

        log.logBanner( "Creating views..." );

        Utils.executeDDL
            (
             conn,
             "create view LastTaking\n" +
             "(\n" +
             "    takingID,\n" +
             "    studentID,\n" +
             "    testID\n" +
             ")\n" +
             "as select max( takingID ), studentID, testID\n" +
             "from TestTaking\n" +
             "group by studentID, testID\n"
             );

        log.logBanner
            (
             "Creating triggers. Note that the trigger " +
             "invokes a procedure..."
             );

        Utils.executeDDL
            (
             conn,
             "create trigger ScoreTestWhenDone\n" +
             "after update of takingDate\n" +
             "on TestTaking\n" +
             "referencing new as testTakingRow\n" +
             "for each row mode db2sql\n" +
             "call ScoreTestTaking( testTakingRow.takingID )\n"
             );
    }

    
    ////////////////////////////////////////////////////////
    //
    // PRETTY PRINITING
    //
    ////////////////////////////////////////////////////////

    /** <p>Pretty print the School table.</p> */
    public  void    prettyPrintSchool( Connection conn )
        throws SQLException
    {
        prettyPrint( conn, "select * from School order by schoolName\n" );
    }

    /** <p>Pretty print the Student table.</p> */
    public  void    prettyPrintStudent( Connection conn )
        throws SQLException
    {
        prettyPrint
            (
             conn,
             "select st.studentID, sc.schoolName," +
             " st.lastName, st.firstName, st.birthday\n" +
             "from Student st, School sc\n" +
             "where st.schoolID = sc.schoolID\n" +
             "order by st.lastName, st.firstName\n"
             );
    }

    /** <p>Pretty print the Test table.</p> */
    public  void    prettyPrintTest( Connection conn )
        throws SQLException
    {
        prettyPrint( conn, "select * from Test order by testName\n" );
    }

    /** <p>Pretty print the Question table.</p> */
    public  void    prettyPrintQuestion( Connection conn )
        throws SQLException
    {
        prettyPrint
            (
             conn,
             "select * from Question order by testID, questionID\n"
             );
    }

    /** <p>Pretty print the QuestionTaking table.</p> */
    public  void    prettyPrintQuestionTaking( Connection conn )
        throws SQLException
    {
        prettyPrint
            (
             conn,
             "select * from QuestionTaking order by takingID, questionID\n"
             );
    }

    /** <p>Pretty print the TestTaking table.</p> */
    public  void    prettyPrintTestTaking( Connection conn )
        throws SQLException
    {
        prettyPrint( conn, "select * from TestTaking order by takingID\n" );
    }

    /**
     * <p>
     * Pretty print the results of a query, given its text.
     * </p>
     */
    public  static  void    prettyPrint( Connection conn, String text )
        throws SQLException
    {
        PreparedStatement   ps = Utils.prepare( conn, text );
        ResultSet           rs = ps.executeQuery();

        prettyPrint( conn, rs );

        Utils.close( rs );
        Utils.close( ps );
    }
    
    /**
     * <p>
     * Print a ResultSet, using Derby's pretty-printing tool.
     * </p>
     */
    public  static  void    prettyPrint( Connection conn, ResultSet rs )
        throws SQLException
    {
        Logger      log = Logger.getLogger();

        JDBCDisplayUtil.DisplayResults
            ( log.getPrintStream(), rs, conn );

        log.log( "\n" );
    }

    ////////////////////////////////////////////////////////
    //
    // OTHER MINIONS
    //
    ////////////////////////////////////////////////////////

    /**
     * <p>
     * Get the Data populating tool.
     * </p>
     */
    public  Data    getData() { return _data; }
    
}