Creating a Derby database and running SQL statements

Now, you will use the Derby ij tool to load the Derby database engine. You will use the Derby embedded driver to create and connect to the firstdb database. You will also use a few basic SQL statements to create and populate a table.

  1. Run the Derby ij tool.
    If you included the DERBY_HOME/bin directory in your PATH environment variable, type:
    ij
    Otherwise, you can use the java command to start the ij tool, as shown in the following table.
    Table 1. Command to start the ij tool
    Operating System Command
    UNIX (Korn Shell)
    java -jar $DERBY_HOME/lib/derbyrun.jar ij
    ij version 10.14
    Windows
    java -jar %DERBY_HOME%\lib\derbyrun.jar ij
    ij version 10.14
  2. Create the database and open a connection to the database using the embedded driver.
    CONNECT 'jdbc:derby:firstdb;create=true';
    Description of connection command:
    connect
    The ij command to establish a connection to a database. The Derby connection URL is enclosed in single quotation marks. An ij command can be in either uppercase or lowercase.
    jdbc:derby:
    The JDBC protocol specification for the Derby driver.
    firstdb
    The name of the database. The name can be any string. Because no filepath is specified, the database is created in the default working directory (DERBYTUTOR).
    ;create=true
    The Derby URL attribute that is used to create a database. Derby does not have an SQL create database command.
    ;
    The semicolon is the ij command terminator.
  3. Create a table with two columns using standard SQL.
    CREATE TABLE FIRSTTABLE
        (ID INT PRIMARY KEY,
        NAME VARCHAR(12));
    0 rows inserted/updated/deleted
  4. Insert three records.
    INSERT INTO FIRSTTABLE VALUES 
        (10,'TEN'),(20,'TWENTY'),(30,'THIRTY');
    3 rows inserted/updated/deleted
  5. Perform a simple select of all records in the table.
    SELECT * FROM FIRSTTABLE;
    ID         |NAME
    ------------------------
    10         |TEN
    20         |TWENTY
    30         |THIRTY
    
    3 rows selected
  6. Perform a qualified select of the record with column ID=20.
    SELECT * FROM FIRSTTABLE WHERE ID=20;
    ID         |NAME
    ------------------------
    20         |TWENTY
    
    1 row selected
  7. Optional: Create and populate additional tables and other schema objects.
    1. Load the SQL script ToursDB_schema.sql.
      run 'ToursDB_schema.sql';
      
      ij> ...
      CREATE TABLE AIRLINES
         (  
            AIRLINE CHAR(2) NOT NULL ,
            AIRLINE_FULL VARCHAR(24),
            BASIC_RATE DOUBLE PRECISION,
         ...
      0 rows inserted/updated/deleted
         ... Other output messages not shown ...
      
    2. Populate the tables with data by running the script loadTables.sql.
      run 'loadTables.sql';
      
      ij> run 'loadCOUNTRIES.sql';
      ij> insert into COUNTRIES values ( 'Afghanistan','AF','Asia');
      1 row inserted/updated/deleted
      ij> insert into COUNTRIES values ( 'Albania','AL','Europe');
      1 row inserted/updated/deleted
         ... Other output messages not shown ...
      
  8. Exit the ij tool.
    exit;
    You should be returned to the DERBYTUTOR directory.
  9. Browse the most important files created by this activity:
    • The derby.log file. This file is a message and error log that, under normal circumstances, contains a set of startup messages and a shutdown message.
      ----------------------------------------------------------------
      Thu Sep 13 09:52:15 EDT 2012:
      Booting Derby version The Apache Software Foundation - Apache 
        Derby - 10.10.0.0 - (1384314): 
        instance a816c00e-0139-bfe6-bff8-000000a155b8 
      on database directory C:\DERBYTUTOR\firstdb  
        with class loader sun.misc.Launcher$AppClassLoader@9931f5 
      Loaded from file:C:\db-derby-10.10.0.0-bin\lib\derby.jar
      java.vendor=Oracle Corporation
      java.runtime.version=1.7.0_07-b11
      user.dir=C:\DERBYTUTOR
      os.name=Windows XP
      os.arch=x86
      os.version=5.1
      derby.system.home=C:\DERBYTUTOR
      Database Class Loader started - derby.database.classpath=''
      ----------------------------------------------------------------
      Thu Sep 13 09:53:21 EDT 2012: Shutting down Derby engine
      ----------------------------------------------------------------
      Thu Sep 13 09:53:21 EDT 2012:
      Shutting down instance a816c00e-0139-bfe6-bff8-000000a155b8 on 
        database directory C:\DERBYTUTOR\firstdb 
        with class loader sun.misc.Launcher$AppClassLoader@9931f5 
      ----------------------------------------------------------------
    • The firstdb database directory. Within the directory are the subdirectories seg0 (containing the data files) and log (containing the transaction log files).