The JVM Fanboy Blog 

Entries tagged [ij]

Apache Derby part 2: Getting started

by vincent


Posted on Tuesday Jul 21, 2015 at 12:07AM in Relational Database


In the first part of this series, I've introduced the Apache Derby database management system. Let's now download Derby and get it up and running by creating a small database.

I am a dedicated NetBeans IDE user (in fact, I am a registered NetBeans project contributor, thanks to my involvement with the NBPython project). I'll be using NetBeans throughout the rest of the series, but on this post I'll only be using the interactive commandline tool "ij".

Note that I assume the reader has basic skills to use Java and SQL, this is not a Java/SQL tutorial.

Downloading Derby

Go to http://db.apache.org/derby/ and choose "Download". At the time of this writing 10.11.1.1 (August 26, 2014) is the latest Official Released Release. Click on the version, then download the bin distrubution. In my case, the filename was db-derby-10.11.1.1-bin.zip.

Setting up Derby

Copy and unzip the zip file in a directory that pleases you. If you are using Windows, preferably do not put it in the Program Files directory, unless you know how to do this with administrator rights. In the examples below, I choose to unzip the ZIP file in my c:\Java directory (I am writing this on a Windows machine).

Open Command Window in Windows (Terminal in Linux) and change current directory to the unzipped directory) Derby's manual suggests setting the DERBY_HOME environmental variable is required. This is also a good moment to check if Java is installed correctly on your machine.

On my Windows machine:

cd \Java\db-derby-10.11.1.1-bin
set DERBY_HOME=\Java\db-derby-10.11.1.1-bin
java -version

if the last command produced a Java version number that is 1.7 or 1.8 or higher you'll be good to go. Otherwise you'll have to install or re-install Java on your machine.

On Linux, you'll do something like (assuming you have a "java" directory in your home directory and you unzipped the ZIP file there. Substitute your username for USER):


cd ~/java/db-derby-10.11.1.1-bin
export DERBY_HOME=/home/USER/java/db-derby-10.11.1.1-bin
java -version

If you plan to use the Derby commandline tools extensively, you'll have to add the bin directory to the path and ensure the DERBY_HOME environmental variable is set on each boot. This is out of scope for this tutorial, IMO.

Now create a directory to store the databases. I'd recommend to not store those in the db-derby-10.11.1.1-bin directory, to make upgrading your version of Derby easier.

I have created a "derby_databases" directory in my java directory.

Windows (Linux uses will know how to do this, I assume) :)
mkdir \java\derby_databases

Running ij and creating our first database in embedded mode

If we set up everything correctly, we should now be able to start "ij" the interactive Derby CLI utility.

Windows: (Linux users, start "./ij")

cd \java\db-derby-10.11.1.1-bin\bin
ij.bat

A simple "ij version 10.11" screen should appear with ij> prompt. Type "help;" here - do not forget the semicolumn ; - and press enter

ij version 10.11
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 | FUNCTIONS | SYNONYMS ] { IN schema };
                               -- lists tables, views, procedures, functions or synonyms
  SHOW INDEXES { IN schema | FROM table };
                               -- lists indexes in a schema, or for a table
  SHOW ROLES;                  -- lists all defined roles in the database, sorted
  SHOW ENABLED_ROLES;          -- lists the enabled roles for the current
                               -- connection (to see current role use
                               -- VALUES CURRENT_ROLE), sorted
  SHOW SETTABLE_ROLES;         -- lists the roles which can be set for the
                               -- current connection, sorted
  DESCRIBE name;               -- lists columns in the named table
--- cut for brevity ----

Now create our first database... Substitute the c:\java\derby_databases\ with the directory you have created to store your databases.

ij> CONNECT 'jdbc:derby:c:\java\derby_databases\derby_test;create=true';

If you somehow lost your connection while doing the exercise below, and you want to connect to your previously created database, you can simply remove the create=true part from above's string to re-open the existing database

.

Unlike some other embeddable databases, like SQLite, Derby does not store a database in one file. Rather, it creates a subdirectory with the database name and store all related files in that directory. It is very important that the files in that directory are NEVER edited. With a single edit you can completely damage your databases.

Let's find out what schema's are created by default and which tables are added to the default "APP" schema.

ij> show schemas;
TABLE_SCHEM
------------------------------
APP
NULLID
SQLJ
SYS
SYSCAT
SYSCS_DIAG
SYSCS_UTIL
SYSFUN
SYSIBM
SYSPROC
SYSSTAT

11 rows selected

ij> show tables in APP;
TABLE_SCHEM         |TABLE_NAME                    |REMARKS
------------------------------------------------------------------------

0 rows selected

The schemas starting with SYS* are for internal use. The "APP" schema is the one we will be using for our first table. As you can see, Derby did not create any table in this schema. Let's create an extremely simplistic database to store your DVD/Bluray collection.

ij> set schema "APP";
0 rows inserted/updated/deleted

ij> CREATE TABLE media (id INT NOT NULL GENERATED ALWAYS AS IDENTITY, name VARCHAR(32), CONSTRAINT media_pk PRIMARY KEY (id));
0 rows inserted/updated/deleted

ij> CREATE TABLE movie (
ID INT NOT NULL GENERATED ALWAYS AS IDENTITY, 
name VARCHAR(255) NOT NULL,  
media INT, 
CONSTRAINT movie_pk PRIMARY KEY (id), 
CONSTRAINT movie_media_fk FOREIGN KEY (media) REFERENCES media ON DELETE SET NULL ON UPDATE RESTRICT);
0 rows inserted/updated/deleted

ij> SHOW TABLES IN APP;
TABLE_SCHEM         |TABLE_NAME                    |REMARKS
------------------------------------------------------------------------
APP                 |MEDIA                         |
APP                 |MOVIE                         |
2 rows selected

Note that although we used names in lower case, all names have been converted to UPPERCASE. This happened because we did not put names between quotes. The SQL standard dictates that unquoted names are always converted to uppercase, but many modern database relaxes this rule a bit (or can be configured that way). In Derby this is, as far as I am aware, not configurable. If you do not want table, column, schema, constraint etc. names to be stored in uppercase, then you will always have to put each and every name between two double quotes ("media" or "Media"). Beware that using unquoted names will not work in that case anymore, so Derby is case sensitive only when quotes are used, in all other cases it will convert the name to uppercase.

Finally let's add some records to our database.

ij> INSERT INTO media (name) VALUES ('DVD');
1 row inserted/updated/deleted
ij> INSERT INTO media (name) VALUES ('Bluray');
1 row inserted/updated/deleted
ij> SELECT * FROM media;
ID         |NAME
--------------------------------------------
1          |DVD
2          |Bluray


 ij> INSERT INTO movie (name, media) VALUES ('Sharknado', 2);
1 row inserted/updated/deleted
ij> INSERT INTO movie (name, media) VALUES ('Kung Fury', 1);
1 row inserted/updated/deleted
ij> INSERT INTO movie (name, media) VALUES ('Mega Shark vs Giant Octopus', 1);
1 row inserted/updated/deleted
ij> SELECT * FROM movie;
ID         |NAME
                                                            |MEDIA
--------------------------------------------------------------------------------------------------------------------------------------------------------
1          |Sharknado
                                                            |2
2          |Kung Pow!
                                                            |1
3          |Mega Shark vs Giant Octopus
                                                            |1

3 rows selected

ij> SELECT MOVIE.id, MOVIE.name, MEDIA.name AS "media" FROM MOVIE INNER JOIN MEDIA ON MOVIE.MEDIA=MEDIA.ID ORDER BY MOVIE.NAME;
ID         |NAME
                                                            |media

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------
2          |Kung Pow!
                                                            |DVD

3          |Mega Shark vs Giant Octopus
                                                            |DVD

1          |Sharknado
                                                            |Bluray


3 rows selected

ij>quit;

Now note that in the last query output, the third header column name is displayed in lowercase , because the alias name "media" was put between double quotes.

To be continued

Now that we have Derby up and running, let's continue with the fun stuff: configuring NetBeans and creating our first stored procedure in Java.