Derby Database Management and Programming

Tab Index

  • Intro to Derby: this page
  • Derby Project Setup: create a project, download a Derby zip, configure and test
  • Getting Started: use the Derby command line tool to create a database, a table, add rows
  • Backup and Restore a Derby database
  • Derby Java SE Program sample to manage Derby table rows: insert, delete, and select.

Links

java -jar derbyrun.jar server start

Derby Project Setup

  1. Create a NetBeans Java SE project named DerbyBasics. The project is for developing programs to manage Derby database table rows.

  2. Unzip the downloaded files, example: db-derby-10.12.1.1-bin.zip.

  3. Create a lib directory with the DerbyBasics project.
    Copy the Derby libraries into the directory.

    C:\> cd C:\2016computers\tfpWebsite\docroot\tech\booksJava\projects\DerbyBasics
    C:\...\projects\DerbyBasics> dir
     Volume in drive C is London
     Volume Serial Number is 0E60-5ADE
    
     Directory of C:\2016computers\tfpWebsite\docroot\tech\booksJava\projects\DerbyBasics
    
    09/19/2016  05:23 PM    <DIR>          .
    09/19/2016  05:23 PM    <DIR>          ..
    09/19/2016  01:33 PM    <DIR>          build
    09/19/2016  11:11 AM             3,618 build.xml
    09/19/2016  06:00 PM             1,298 derby.log
    09/19/2016  06:00 PM    <DIR>          investdb
    09/19/2016  11:12 AM    <DIR>          lib
    09/19/2016  11:11 AM                85 manifest.mf
    09/19/2016  11:11 AM    <DIR>          nbproject
    09/19/2016  11:11 AM    <DIR>          src
    09/19/2016  06:38 PM    <DIR>          test
                   3 File(s)          5,001 bytes
                   8 Dir(s)  83,664,379,904 bytes free
    
    C:\...\projects\DerbyBasics> dir lib
     Volume in drive C is London
     Volume Serial Number is 0E60-5ADE
    
     Directory of C:\2016computers\tfpWebsite\docroot\tech\booksJava\projects\DerbyBasics\lib
    
    09/19/2016  11:12 AM    <DIR>          .
    09/19/2016  11:12 AM    <DIR>          ..
    09/19/2016  10:51 AM         3,224,708 derby.jar
    09/19/2016  10:51 AM           590,087 derbyclient.jar
    09/19/2016  10:51 AM           229,665 derbytools.jar
                   3 File(s)      4,044,460 bytes
                   2 Dir(s)  83,664,379,904 bytes free
    
    C:\...\projects\DerbyBasics>
  4. Test the setup by starting the Derby command line tool.
    Then exit the tool.

    C:\> cd C:\2016computers\tfpWebsite\docroot\tech\booksJava\projects\DerbyBasics
    C:\...\projects\DerbyBasics> java -cp lib/derby.jar;lib/derbyclient.jar;lib/derbytools.jar org.apache.derby.tools.ij
    ij version 10.12
    ij> help;
    ...
    ij> exit;
    C:\...\projects\DerbyBasics>

Getting Started

  1. Unzip the downloaded files.
    Create a NetBeans project for developing Java SE programs to test the database.

  2. Copy the Derby library files into the NetBeans project directory.

    C:\> cd C:\2016computers\tfpWebsite\docroot\tech\booksJava\projects\DerbyBasics
    C:\...\projects\DerbyBasics> dir
     Volume in drive C is London
     Volume Serial Number is 0E60-5ADE
    
     Directory of C:\2016computers\tfpWebsite\docroot\tech\booksJava\projects\DerbyBasics
    
    09/19/2016  11:11 AM    <DIR>          .
    09/19/2016  11:11 AM    <DIR>          ..
    09/19/2016  11:11 AM             3,618 build.xml
    09/19/2016  11:12 AM    <DIR>          lib
    09/19/2016  11:11 AM                85 manifest.mf
    09/19/2016  11:11 AM    <DIR>          nbproject
    09/19/2016  11:11 AM    <DIR>          src
                   2 File(s)          3,703 bytes
                   5 Dir(s)  87,085,346,816 bytes free
    
    C:\...\projects\DerbyBasics>dir lib
     Volume in drive C is London
     Volume Serial Number is 0E60-5ADE
    
     Directory of C:\2016computers\tfpWebsite\docroot\tech\booksJava\projects\DerbyBasics\lib
    
    09/19/2016  11:12 AM    <DIR>          .
    09/19/2016  11:12 AM    <DIR>          ..
    09/19/2016  10:51 AM         3,224,708 derby.jar
    09/19/2016  10:51 AM           590,087 derbyclient.jar
    09/19/2016  10:51 AM           229,665 derbytools.jar
                   3 File(s)      4,044,460 bytes
                   2 Dir(s)  87,085,346,816 bytes free
                                                
  3. Start the Derby command line tool. These include the required Java libraries.

    C:\...\projects\DerbyBasics> java -cp lib/derby.jar;lib/derbyclient.jar;lib/derbytools.jar org.apache.derby.tools.ij
    ij version 10.12
    ij>                                            
  4. Create a database.
    Note, user and password are optional.
    Connect to the database, create a table, insert rows, and select the rows.

    ij> connect 'jdbc:derby:investdb;user=tfp;password=tigers;create=true';
    ij> create table derbyDB(num int, addr varchar(40));
    0 rows inserted/updated/deleted
    ij> insert into derbyDB values (1956,'Webster St.');
    1 row inserted/updated/deleted
    ij> insert into derbyDB values (1910,'Union St.');
    1 row inserted/updated/deleted
    ij> select * from derbyDb;
    NUM        |ADDR
    ----------------------------------------------------
    1956       |Webster St.
    1910       |Union St.
    
    2 rows selected
    ij>
  5. Run an SQL file.

    Click here to view the SQL file.
    Click here to hide the SQL file.
    ij> run 'test/investdbTest.sql';
    ij> -- Clear out the old table, if they existed at all.
    DROP TABLE League;
    ERROR 42Y55: 'DROP TABLE' cannot be performed on 'LEAGUE' because it does not exist.
    ij> -- This table represents the League objects
    --
    CREATE TABLE League (
    -- PRIMARY KEY --
       LID             INTEGER        PRIMARY KEY,   -- League's object ID
    -- DATA FIELDS --
       theyear         INTEGER        NOT NULL,      -- year of league
       season          VARCHAR(10)    NOT NULL,      -- season: Spring/Summer/Fall
       title           VARCHAR(30)    NOT NULL       -- the title of the league
    );
    0 rows inserted/updated/deleted
    ij> -- Create the initial set of leagues.
    INSERT INTO League (LID, theyear, season, title)
      VALUES (1, 2003, 'Spring', 'Soccer League (Spring ''03)');
    1 row inserted/updated/deleted
    ij> INSERT INTO League (LID, theyear, season, title)
      VALUES (2, 2003, 'Summer', 'Summer Soccer Fest 2003');
    1 row inserted/updated/deleted
    ij> INSERT INTO League (LID, theyear, season, title)
      VALUES (3, 2003, 'Fall', 'Fall Soccer League (2003)');
    1 row inserted/updated/deleted
    ij> INSERT INTO League (LID, theyear, season, title)
      VALUES (4, 2004, 'Spring', 'Soccer League (Spring ''04)');
    1 row inserted/updated/deleted
    ij> INSERT INTO League (LID, theyear, season, title)
      VALUES (5, 2004, 'Summer', 'The Summer of Soccer Love 2004');
    1 row inserted/updated/deleted
    ij> INSERT INTO League (LID, theyear, season, title)
      VALUES (6, 2004, 'Fall', 'Fall Soccer League (2004)');
    1 row inserted/updated/deleted
    ij> SELECT * FROM league;
    LID        |THEYEAR    |SEASON    |TITLE
    -----------------------------------------------------------------
    1          |2003       |Spring    |Soccer League (Spring '03)
    2          |2003       |Summer    |Summer Soccer Fest 2003
    3          |2003       |Fall      |Fall Soccer League (2003)
    4          |2004       |Spring    |Soccer League (Spring '04)
    5          |2004       |Summer    |The Summer of Soccer Love 2004
    6          |2004       |Fall      |Fall Soccer League (2004)
    
    6 rows selected
    ij> COMMIT;
    ij>
  6. Exit the Derby command line tool.

    ij> disconnect;
    ij> exit;
    C:\...\projects\DerbyBasics>

Using a Derby Database

  1. Confirm there is data by selecting data using Derby command line tool.
    Then exit the tool.

    C:\> cd C:\2016computers\tfpWebsite\docroot\tech\booksJava\projects\DerbyBasics
    C:\...\projects\DerbyBasics> java -cp lib/derby.jar;lib/derbyclient.jar;lib/derbytools.jar org.apache.derby.tools.ij
    ij version 10.12
    ij> connect 'jdbc:derby:investdb;user=tfp;password=tigers;';
    ij> show table;
    TABLE_SCHEM         |TABLE_NAME                    |REMARKS
    ------------------------------------------------------------------------
    SYS                 |SYSUSERS                      |
    ...
    TFP                 |LEAGUE                        |
    
    24 rows selected
    ij> SELECT * FROM league;
    LID        |THEYEAR    |SEASON    |TITLE
    -----------------------------------------------------------------
    1          |2003       |Spring    |Soccer League (Spring '03)
    2          |2003       |Summer    |Summer Soccer Fest 2003
    3          |2003       |Fall      |Fall Soccer League (2003)
    4          |2004       |Spring    |Soccer League (Spring '04)
    5          |2004       |Summer    |The Summer of Soccer Love 2004
    6          |2004       |Fall      |Fall Soccer League (2004)
    
    6 rows selected
    ij> describe league;
    COLUMN_NAME         |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
    ------------------------------------------------------------------------------
    LID                 |INTEGER  |0   |10  |10    |NULL      |NULL      |NO
    THEYEAR             |INTEGER  |0   |10  |10    |NULL      |NULL      |NO
    SEASON              |VARCHAR  |NULL|NULL|10    |NULL      |20        |NO
    TITLE               |VARCHAR  |NULL|NULL|30    |NULL      |60        |NO
    
    ij> describe SYS.SYSTABLES;
    COLUMN_NAME         |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
    ------------------------------------------------------------------------------
    TABLEID             |CHAR     |NULL|NULL|36    |NULL      |72        |NO
    TABLENAME           |VARCHAR  |NULL|NULL|128   |NULL      |256       |NO
    TABLETYPE           |CHAR     |NULL|NULL|1     |NULL      |2         |NO
    SCHEMAID            |CHAR     |NULL|NULL|36    |NULL      |72        |NO
    LOCKGRANULARITY     |CHAR     |NULL|NULL|1     |NULL      |2         |NO
    
    5 rows selected
    ij> CAST(TABLENAME AS VARCHAR(30))
    
    ij> select CAST(TABLENAME AS VARCHAR(30)) from SYS.SYSTABLES where TABLETYPE='T';
    TABLENAME
    ------------------------------
    LEAGUE
    
    1 rows selected
    ij> abc
    
    ij>
  2. Exit the Derby command line tool.

    ij> disconnect;
    ij> exit;
    C:\...\projects\DerbyBasics>

Backup and Restore

  1. Confirm there is data by selecting data using Derby command line tool.
    Then exit the tool.

    C:\> cd C:\2016computers\tfpWebsite\docroot\tech\booksJava\projects\DerbyBasics
    C:\...\projects\DerbyBasics> java -cp lib/derby.jar;lib/derbyclient.jar;lib/derbytools.jar org.apache.derby.tools.ij
    ij version 10.12
    ij> connect 'jdbc:derby:investdb;user=tfp;password=tigers;';
    ij> select * from league;
    LID        |THEYEAR    |SEASON    |TITLE
    -----------------------------------------------------------------
    1          |2003       |Spring    |Soccer League (Spring '03)
    2          |2003       |Summer    |Summer Soccer Fest 2003
    3          |2003       |Fall      |Fall Soccer League (2003)
    4          |2004       |Spring    |Soccer League (Spring '04)
    5          |2004       |Summer    |The Summer of Soccer Love 2004
    6          |2004       |Fall      |Fall Soccer League (2004)
    
    6 rows selected
    ij> exit;
    C:\...\projects\DerbyBasics>
  2. Backup the database by making a copy of the database directory.
    To restore the database, copy it back of the database directory.

    C:\...\projects\DerbyBasics> dir investdb
     Volume in drive C is London
     Volume Serial Number is 0E60-5ADE
    
     Directory of C:\2016computers\tfpWebsite\docroot\tech\booksJava\projects\DerbyBasics\investdb
    
    09/20/2016  12:14 AM    <DIR>          .
    09/20/2016  12:14 AM    <DIR>          ..
    09/19/2016  05:23 PM    <DIR>          log
    09/19/2016  05:21 PM               608 README_DO_NOT_TOUCH_FILES.txt
    09/19/2016  05:23 PM    <DIR>          seg0
    09/19/2016  05:21 PM               982 service.properties
                   2 File(s)          1,590 bytes
                   4 Dir(s)  83,663,134,720 bytes free
    
    C:\...\projects\DerbyBasics> xcopy C:\2016computers\tfpWebsite\docroot\tech\booksJava\projects\DerbyBasics\investdb c:\work2016\databaseBackup\investdb /s /i
    ...
    C:\...\projects\DerbyBasics> dir \work2016\databaseBackup\investdb
     Volume in drive C is London
     Volume Serial Number is 0E60-5ADE
    
     Directory of C:\work2016\databaseBackup\investdb
    
    09/19/2016  05:22 PM    <DIR>          .
    09/19/2016  05:22 PM    <DIR>          ..
    09/19/2016  05:22 PM    <DIR>          log
    09/19/2016  05:21 PM               608 README_DO_NOT_TOUCH_FILES.txt
    09/19/2016  05:22 PM    <DIR>          seg0
    09/19/2016  05:21 PM               982 service.properties
                   2 File(s)          1,590 bytes
                   4 Dir(s)  83,663,691,776 bytes free
    C:\...\projects\DerbyBasics>
  3. Drop the database by deleting the recently created Derby database directory.

    C:\...\projects\DerbyBasics> rd investdb /s /q
    
    C:\...\projects\DerbyBasics>
  4. Confirm the database is gone.

    C:\> cd C:\2016computers\tfpWebsite\docroot\tech\booksJava\projects\DerbyBasics
    C:\...\projects\DerbyBasics> java -cp lib/derby.jar;lib/derbyclient.jar;lib/derbytools.jar org.apache.derby.tools.ij
    ij version 10.12
    ij> connect 'jdbc:derby:investdb;user=tfp;password=tigers;';
    ERROR XJ004: Database 'investdb' not found.
    ij> exit;
    C:\...\projects\DerbyBasics>
  5. Restore the database by copying it back to the original database directory.

    C:\...\projects\DerbyBasics> xcopy c:\work2016\databaseBackup\investdb C:\2016computers\tfpWebsite\docroot\tech\booksJava\projects\DerbyBasics\investdb /s /i
    ...
    
    C:\...\projects\DerbyBasics>
  6. Confirm the database was restored.

    C:\> cd C:\2016computers\tfpWebsite\docroot\tech\booksJava\projects\DerbyBasics
    C:\...\projects\DerbyBasics> java -cp lib/derby.jar;lib/derbyclient.jar;lib/derbytools.jar org.apache.derby.tools.ij
    ij version 10.12
    ij> connect 'jdbc:derby:investdb;user=tfp;password=tigers;';
    ij> select * from league;
    LID        |THEYEAR    |SEASON    |TITLE
    -----------------------------------------------------------------
    1          |2003       |Spring    |Soccer League (Spring '03)
    2          |2003       |Summer    |Summer Soccer Fest 2003
    3          |2003       |Fall      |Fall Soccer League (2003)
    4          |2004       |Spring    |Soccer League (Spring '04)
    5          |2004       |Summer    |The Summer of Soccer Love 2004
    6          |2004       |Fall      |Fall Soccer League (2004)
    
    6 rows selected
    ij> exit;
    C:\...\projects\DerbyBasics>

Derby Java SE Program Sample

Sample Java program file.

Click here to view the Java program file.
Click here to hide the Java program file.

Other

  1. abc