Sunday, May 6, 2007

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:

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.ijij version 10.2ij>

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 protocolDRIVER 'class for driver'; -- loads the named classCONNECT 'url for database' [ PROTOCOL namedProtocol ] [ AS connectionName ]; -- connects to database URL -- and may assign identifierSET CONNECTION connectionName; -- switches to the specified connectionSHOW CONNECTIONS; -- lists all connectionsAUTOCOMMIT [ ON OFF ]; -- sets autocommit mode for the connectionDISCONNECT [ CURRENT connectionName ALL ]; -- drop current, named, or all connections; -- the default is CURRENT

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

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

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

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

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

ASYNC name 'SQL-J text'; -- run the command in another threadWAIT 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 cursorFIRST name; -- gets the first row from the named scroll cursorLAST name; -- gets the last row from the named scroll cursorPREVIOUS name; -- gets the previous row from the named scroll cursorABSOLUTE 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 rowBEFORE FIRST name; -- positions the named scroll cursor before the first rowGETCURRENTROWNUMBER 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 cursorLOCALIZEDDISPLAY [ ON OFF ]; -- controls locale sensitive data representation

EXIT; -- exits ijHELP; -- 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