Java 6 Feature : Working with Derby Database

The Java 5 and Java 6 releases have introduced some significant changes. While Java 5 introduces new features (e.g. Generics, Variable arguments, Enhanced for loop, Boxing/unboxing, Type-safe enumerations, Static import, Metadata), Java 6 installation includes a lightweight database known as Derby.
Derby is actually an Apache Database project. Derby is a transactional, relational database and provides a small footprint on disk. When you install Java 6, core libraries, example programs, and a sample database gets automatically installed.
Derby has a command-line tool called ij. This tool provides a way to connect to and manipulate Derby databases.

To connect to Derby is quite easy. You need to have following jar files in the classpath to enable you to access Derby:

D:\tools\jdk1.6.0_01\db\lib\derby.jar
D:\tools\jdk1.6.0_01\db\lib\derbytools.jar

The derby.jar has JDBC drivers, while derbytools.jar contains the ij tool.

Once you have configured the classpath for Derby, open the command prompt, and start ij tool:

C:\Documents and Settings\Administrator>java org.apache.derby.tools.ij
ij version 10.2
ij>

You are now connected to Derby db.
Connect to the supplied example database (toursdb):

ij> connect 'jdbc:derby:Absolute_Path_to_the_connecting_database';
ij>

You can create a new database from the ij tool.

connect 'jdbc:derby:DB4MyApplication;create=true';

A semicolon (;) at the end of each command is also required.
You can get a list of Derby commands by issuing help command.

ij> help;

Supported commands include:

PROTOCOL 'JDBC protocol' [ AS ident ];
                             -- sets a default or named protocol
DRIVER 'class for driver';   -- loads the named class
CONNECT 'url for database' [ PROTOCOL namedProtocol ] [ AS connectionName ];
                             -- connects to database URL
                             -- and may assign identifier
SET CONNECTION connectionName; -- switches to the specified connection
SHOW CONNECTIONS;            -- lists all connections
AUTOCOMMIT [ ON  OFF ];     -- sets autocommit mode for the connection
DISCONNECT [ CURRENT  connectionName  ALL ];
                             -- drop current, named, or all connections;
                             -- the default is CURRENT

SHOW SCHEMAS;                -- lists all schemas in the current database
SHOW [ TABLES  VIEWS  PROCEDURES  SYNONYMS ] { IN schema };
                             -- lists tables, views, procedures or synonyms
SHOW INDEXES { IN schema  FROM table };
                             -- lists indexes in a schema, or for a table
DESCRIBE name;               -- lists columns in the named table

COMMIT;                      -- commits the current transaction
ROLLBACK;                    -- rolls back the current transaction

PREPARE name AS 'SQL-J text'; -- prepares the SQL-J text
EXECUTE { name  'SQL-J text' } [ USING { name  'SQL-J text' } ] ;
                             -- executes the statement with parameter
                             -- values from the USING result set row
REMOVE name;                 -- removes the named previously prepared statement

RUN 'filename';              -- run commands from the named file

ELAPSEDTIME [ ON  OFF ];    -- sets elapsed time mode for ij
MAXIMUMDISPLAYWIDTH integerValue;
                             -- sets the maximum display width for
                             -- each column to integerValue

ASYNC name 'SQL-J text';     -- run the command in another thread
WAIT FOR name;               -- wait for result of ASYNC'd command

GET [SCROLL INSENSITIVE] CURSOR name AS 'SQL-J query';
                             -- gets a cursor (JDBC result set) on the query
                             -- SCROLL cursors are only available
                             -- in JDBC 2.0 and higher.
                             -- (Cursor scroll type is ignored in JDBC 1.X.)
NEXT name;                   -- gets the next row from the named cursor
FIRST name;                  -- gets the first row from the named scroll cursor
LAST name;                   -- gets the last row from the named scroll cursor
PREVIOUS name;               -- gets the previous row from the named scroll cursor
ABSOLUTE integer name;       -- positions the named scroll cursor at the absolute row number
                             -- (A negative number denotes position from the last row.)
RELATIVE integer name;       -- positions the named scroll cursor relative to the current row
                             -- (integer is number of rows)
AFTER LAST name;             -- positions the named scroll cursor after the last row
BEFORE FIRST name;           -- positions the named scroll cursor before the first row
GETCURRENTROWNUMBER name;    -- returns the row number for the current position of the named scroll cursor
                             -- (0 is returned when the cursor is not positioned on a row.)
CLOSE name;                  -- closes the named cursor
LOCALIZEDDISPLAY [ ON  OFF ];
                             -- controls locale sensitive data representation

EXIT;                        -- exits ij
HELP;                        -- shows this message

Any unrecognized commands are treated as potential SQL-J commands and executed directly.

So, now you have a lightweight DB installed alongwith the JDK. You can use this database for Proof-of-Concepts (POC), sample applications, demos among other things. To connect to Derby from Java, use the supplied driver

org.apache.derby.jdbc.EmbeddedDriver

. Connect to DB as shown earlier.