Data Base systems are intended to organize, store, and retrieve large amounts of data easily. Whether it is a collections of files, has as SQL language interface, key-value pairs, clustered, or a single file, it should make our lives easier.
Coupled with our Web Server from my last article, we will have the foundation of dynamic shared information over the network of interconnected hosts; our be-loved Web.
Keeping with the theme of portability and Java, JavaDB is a good choice. We want a SQL interface and JDBC because they are well known and the DB engine can be switched out later if desired without core code changes. Produced by the Apache foundation under the name Derby, the latest release can be found here:
Apache Derby, an Apache DB subproject, is an open source relational database implemented entirely in Java and available under the Apache License, Version 2.0. Some key advantages include:
* Derby has a small footprint — about 2.6 megabytes for the base engine and embedded JDBC driver.
* Derby is based on the Java, JDBC, and SQL standards.
* Derby provides an embedded JDBC driver that lets you embed Derby in any Java-based solution.
* Derby also supports the more familiar client/server mode with the Derby Network Client JDBC driver and Derby Network Server.
* Derby is easy to install, deploy, and use.
Download it here: Derby Download
With our database comes the responsibility of a rational file system structure for our code base deployment. I chose the name trutree as my project name, so let us start simple:
Create a workspace:
$ mkdir -p ~/Documents/workspace/trutree $ cd ~/Documents/workspace/trutree
Copy the derby library:
$ mkdir lib $ cd lib $ tar xzvf ~/Downloads/db-derby-10.7.1.1-bin.tar.gz $ ls KEYS bin javadoc LICENSE demo lib NOTICE docs test RELEASE-NOTES.html index.html
Create an environment setting file (notice the difference between Linux and Mac OS X ‘Darwin’):
$ cd ~/Documents/workspace/trutree $ vi derby.env ~ if [[ $(uname) == "Linux" ]]; then export DERBY_HOME=/home/don/workspace/trutree/lib/db-derby-10.7.1.1-bin export TRUTREE_HOME=/home/don/workspace/trutree elif [[ $(uname) == "Darwin" ]]; then export DERBY_HOME=/Users/don/Documents/workspace/trutree/lib/db-derby-10.7.1.1-bin export TRUTREE_HOME=/Users/don/Documents/workspace/trutree fi export PATH=$PATH:$DERBY_HOME/bin export CLASSPATH=$DERBY_HOME/lib/derby.jar:$DERBY_HOME/lib/derbyclient.jar:$TRUTREE_HOME/bin:. ~
Now create a Java environment file (I am using Java 1.6):
$ vi java.env ~ if [[ $(uname) == "Linux" ]]; then export JAVA_HOME=/usr elif [[ $(uname) == "Darwin" ]]; then export JAVA_HOME=/System/Library/Frameworks/JavaVM.framework/Versions/1.6.0/Home fi ~
Finally create a script to start and stop the Derby database server (we will run it in client/server mode to enable multi-user/program access, through port 1527):
$ vi derby.sh ~ . derby.env . java.env export PATH=$PATH:$DERBY_HOME/bin export CLASSPATH=$DERBY_HOME/lib/derby.jar:$DERBY_HOME/lib/derbyclient.jar:. if [[ $1 = 'start' ]] then $JAVA_HOME/bin/java -jar $DERBY_HOME/lib/derbyrun.jar server start fi if [[ $1 = 'stop' ]] then $JAVA_HOME/bin/java -jar $DERBY_HOME/lib/derbyrun.jar server shutdown fi ~
Verify Derby
Run the sysinfo command, as shown below, to output Derby system information:
java org.apache.derby.tools.sysinfo
Successful output will look something like this:
$ . java.env
$ . derby.env
$ java org.apache.derby.tools.sysinfo
------------------ Java Information ------------------
Java Version: 1.6.0_24
Java Vendor: Apple Inc.
Java home: /System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home
Java classpath: /Users/don/Documents/workspace/trutree/lib/db-derby-10.7.1.1-bin/lib/derby.jar:/Users/don/Documents/workspace/trutree/lib/db-derby-10.7.1.1-bin/lib/derbyclient.jar:/Users/don/Documents/workspace/trutree/bin:.
OS name: Mac OS X
OS architecture: x86_64
OS version: 10.5.8
Java user name: don
Java user home: /Users/don
Java user dir: /Users/don/Documents/workspace/trutree
java.specification.name: Java Platform API Specification
java.specification.version: 1.6
java.runtime.version: 1.6.0_24-b07-334-9M3326
--------- Derby Information --------
JRE - JDBC: Java SE 6 - JDBC 4.0
[/Users/don/Documents/workspace/trutree/lib/db-derby-10.7.1.1-bin/lib/derby.jar] 10.7.1.1 - (1040133)
[/Users/don/Documents/workspace/trutree/lib/db-derby-10.7.1.1-bin/lib/derbyclient.jar] 10.7.1.1 - (1040133)
------------------------------------------------------
----------------- Locale Information -----------------
Current Locale : [English/United States [en_US]]
Found support for locale: [cs]
version: 10.7.1.1 - (1040133)
Now we start the server in a new command window (Use /Applications/Utilities/Terminal on Mac, or Accessories > Terminal on Linux). Make sure you cd to the correct directory first!
$ cd ~/Documents/workspace/trutree $ derby.sh start Sun Apr 10 14:58:29 EDT 2011 : Security manager installed using the Basic server security policy. Sun Apr 10 14:58:30 EDT 2011 : Apache Derby Network Server - 10.7.1.1 - (1040133) started and ready to accept connections on port 1527
Then we create the database using Derby’s command line tool in another Terminal window, ij:
$ cd ~/Documents/workspace/trutree $ ij ij version 10.7 ij> connect 'jdbc:derby://localhost:1527/trutree;user=app;create=true'; ij> exit;
Check for your database files (notice there are in the directory where you started the DB server, i.e.: ~/Documents/workspace/trutree/trutree):
$ ls -l trutree total 8 drwxrwxrwx 3 don don 102 Dec 22 05:29 jar drwxrwxrwx 5 don don 170 Mar 26 18:25 log drwxrwxrwx 84 don don 2856 Dec 22 05:29 seg0 -rwxrwxrwx 1 don don 870 Nov 6 18:01 service.properties
Good deal! Now we have a database server and a new database.
To browse around the new database I recommend SQL Squirrel.
Download and install the package for your system, it supports Linux, Mac OS X, and Windows.
Configure the driver for Derby, then connect to your new database:
Alias: trutree Driver: Apache Derby Client URL: jdbc:derby://localhost:1527/trutree User: app Password: app
If all is good, here is what you will see:
trutree database initial connection using SQL Squirrel
Don Cohoon 
You must be logged in to post a comment.