database.java

derby数据库源码。。。。。。。。。。。。。

/*

   Derby - Class org.apache.derbyDemo.scores.data.Database

   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

      http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.

 */

package org.apache.derbyDemo.scores.data;

import java.sql.*;

import org.apache.derby.tools.JDBCDisplayUtil;

import org.apache.derbyDemo.scores.util.*;

/**
 * <p>
 * Top level object for accessing a SQL database. Implemented against
 * embedded Derby. Could be extended for use against another server.
 * </p>
 *
 */
public  class   Database
{
    ////////////////////////////////////////////////////////
    //
    // CONSTANTS
    //
    ////////////////////////////////////////////////////////

    public  static  final   String  DATABASE_NAME = "ScoresDB";

    private static  final   String  DERBY_EMBEDDED_DRIVER =
        "org.apache.derby.jdbc.EmbeddedDriver";
    private static  final   String  DERBY_PROTOCOL = "jdbc:derby:";
    private static  final   String  CREATE_ME = ";create=true";
    
    ////////////////////////////////////////////////////////
    //
    // STATE
    //
    ////////////////////////////////////////////////////////

    private Data            _data;
    private Connection      _conn;
    
    ////////////////////////////////////////////////////////
    //
    // CONSTRUCTOR
    //
    ////////////////////////////////////////////////////////

    /**
     * <p>
     * Construct out of thin air.
     * </p>
     */
    private Database()
    {
    }

    ////////////////////////////////////////////////////////
    //
    // STATIC BEHAVIOR
    //
    ////////////////////////////////////////////////////////

    /**
     * <p>
     * Recode this method to return some subclass of Database if you
     * want to use a different data store than Derby. This method
     * creates the database if it does not already exist.
     * </p>
     */
    public  static  Database    getDatabase
        ( String serverJar, String mathJar )
        throws SQLException
    {
        Database    db = new Database();
        Logger      log = Logger.getLogger();

        db._data = new Data();

        //
        // This creates an empty database if it doesn't already exist.
        //
        Connection  conn = db.getConnection();

        //
        // If the database is empty, populate it.
        //
        if ( !db.schemaExists( conn ) )
        {
            db.createSchema( conn, serverJar, mathJar );

            // populate tables with initial slug of data
            db._data.initialize( db );

            log.logBanner( "Show schools, students, and tests..." );

            db.prettyPrintSchool( conn );
            db.prettyPrintStudent( conn );
            db.prettyPrintTest( conn );
            //db.prettyPrintQuestion( conn );
        }

        Utils.commit( conn );

        return db;
    }

    ////////////////////////////////////////////////////////
    //
    // OVERRIDABLE PUBLIC BEHAVIOR. OVERRIDE THESE METHODS
    // IF YOU WANT TO USE A DIFFERENT DATA STORE. THIS IMPLEMENTATION
    // WORKS AGAINST DERBY.
    //
    ////////////////////////////////////////////////////////

    /**
     * <p>
     * Get a connection to the database.
     * </p>
     */
    public  Connection  getConnection()
        throws SQLException
    {
        if ( _conn != null ) { return _conn; }
        
        try {
            Class.forName( DERBY_EMBEDDED_DRIVER );
        }
        catch (ClassNotFoundException e)
        {
            throw new SQLException
                ( "Could not locate " + DERBY_EMBEDDED_DRIVER );
        }

        String      connectionURL =
            DERBY_PROTOCOL + DATABASE_NAME + CREATE_ME;

        _conn = DriverManager.getConnection( connectionURL );

        _conn.setAutoCommit( false );

        return _conn;
    }

    /////////
    //
    // SCHEMA
    //
    /////////

    /**
     * <p>
     * Return true if the schema exists.
     * </p>
     */
    protected   boolean schemaExists( Connection conn )
        throws SQLException
    {
        String              heartbeat =
            "select count(*) from sys.systables where tablename = 'SCHOOL'";

        PreparedStatement   ps = Utils.prepare( conn, heartbeat );
        ResultSet           rs = ps.executeQuery();

        rs.next();
        
        int                 count = rs.getInt( 1 );

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

        return ( count > 0 );
    }

    /**
     * <p>
     * Create all schema objects.
     * </p>
     */
    protected   void    createSchema
        ( Connection conn, String serverJar, String mathJar )
        throws SQLException
    {
        Logger      log = Logger.getLogger();

        log.logBanner
            (
             "Loading jar files into database and " +
             "wiring-up the database classpath..." );

        Utils.executeCall
            (
             conn,
             "call sqlj.install_jar\n" +
             "(\n" +
             "    '" + serverJar + "',\n" +
             "    'APP.SCORES_SERVER',\n" +
             "    0\n" +
             ")\n"
             );
        Utils.executeCall
            (
             conn,
             "call sqlj.install_jar\n" +
             "(\n" +
             "    '" + mathJar +"',\n" +
             "    'APP.APACHE_COMMONS_MATH',\n" +
             "    0\n" +
             ")\n"
             );
        Utils.executeCall
            (
             conn,
             "call syscs_util.syscs_set_database_property\n" +
             "(\n" +
             "    'derby.database.classpath',\n" +
             "    'APP.SCORES_SERVER:APP.APACHE_COMMONS_MATH'\n" +
             ")\n"
             );

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

        Utils.executeDDL
            (
             conn,
             "create function formatPercent\n" +
             "(\n" +
             "    score double\n" +
             ")\n" +
             "returns varchar( 7 )\n" +
             "language java\n" +
             "parameter style java\n" +
             "no sql\n" +
             "external name 'org.apache.derbyDemo.scores.proc.Functions.formatPercent'\n"
             );
        Utils.executeDDL
            (
             conn,
             "create function weighQuestion\n" +
             "(\n" +
             "    difficulty int\n" +
             ")\n" +
             "returns double\n" +
             "language java\n" +
             "parameter style java\n" +
             "no sql\n" +
             "external name 'org.apache.derbyDemo.scores.proc.Functions.weighQuestion'\n"
             );
        Utils.executeDDL
            (
             conn,
             "create function scoreAnswer\n" +
             "(\n" +
             "    difficulty      int,\n" +
             "    numberOfChoices int,\n" +
             "    correctChoice   int,\n" +
             "    actualChoice    int\n" +
             ")\n" +
             "returns double\n" +
             "language java\n" +
             "parameter style java\n" +
             "no sql\n" +
             "external name 'org.apache.derbyDemo.scores.proc.Functions.scoreAnswer'\n"
             );
        Utils.executeDDL
            (
             conn,