Apache Derby Tutorial in english

-- This is technical content and the intended audience are programmers --
-- This tutorial has been translated from german to english, so please excuse any translation errors --

I have been working on a small project, which uses the Wikipedia as information source and though the result will only be a small spot on a map there happens a lot of memory intense string loading, searching and manipulation. I was very busy until a heap overflow spoiled my party...

So I checked my options and writing a database adaptor was not my first choice. But I picked a solution that promised less effort: JPA (Java Persistence API)
The basic concept is to match Java classes on database tables. You only have to care for the mapping configuration - which class is stored in what table and which instance variable is stored in what field - the rest is done by the persistency layer.

There is already a JPA-Tutorial mit Beispiel-Dateien for Netbeans. My approach is the same but will use Eclipse. So I will show how to get the code from the above mentioned tutorial running.

The storage job will be done by Derby, from the Apache DB Projekt which is maintained by the Apache Software Foundation. The beauty of Derby is its light-weight concept. The whole database software takes only 2 MB on the disk and maintenance and installation are very easy to manage.

Here comes a little ToDo overview:

  1. Download the files
  2. Install the Eclipse plugins
  3. Setup of the Eclipse projekts

Enough said, let's roll...


Download the files

First of all you should download the following zip file: JPA_Apache_Derby.zip (6,2 MB). It contains 2 Eclipse plugins and the Eclipse sample program. The plugins care for the Derby integration into Eclipse. So save the file in the Eclipse workspace and unpack the contained folder.


Installation of the Eclipse plugins

After that install the plugins. So unpack in the "JPA Apache Derby" folder the included derby_core_plugin_10.3.1.zip and as well the derby_ui_plugin_1.1.1.zip. Afterwards you can copy the extracted "plugins" folder into the Eclipse main directory. After that unpack the Eclipse project from the "JPA_Eclipse.zip". That creates the folder "JPA_Eclipse".
This or similar is how your folder should look like:


Installation of the Eclipse projekts

In the next step open Eclipse. There must not appear any error messages. At the first glimpse you will not see anything of the new plugins. But the essence is that you can add with a right-click a "Apache Derby Nature" to an Eclipse project.

Afterwards import the projekt "JPA_Eclipse", which is contained in the "JPA Apache Derby" folder.

After a right-click in the package explorer choose "Import" and then "Existing projekt into Workspace"


Hooray! Now we are close to start the real work.


Start Derby

The imported project already has the above mentioned "Apache Derby Nature". So after right clicking the project you will see a menu item "Apache Derby". Choose the option "Start Derby Network Server".

This is acknowledged with the following message:

And the console reports:

DRDA_SecurityInstalled.I
Apache Derby Network Server 10.3.1.4 - (561794) was started and is ready since 2007-11-22 21:25:03.773 GMT to accept connections at Port 1527.


Creation of a Derby database and tables

In the project you will find in the folder "sql" the files "createDatabase.ij" and "tables_derby.sql". The best is to open the files with a right-click by choosing the text editor. They contain the commands we will need in the following steps.

With another right click on the project you start the interactive SQL-Console ("Apache Derby" -> "ij (Interactive SQL)").

First of all paste the first line of the file "createDatabase.ij" in the ij:

connect 'jdbc:derby://localhost:1527/testDB;create=true;user=APP;password=APP';


By doing this you connect to the database "testDB" or create it if it does not exist yet.
You should not change the username or the password here.

After that paste the content of the file "tables_derby.sql" line by line into the console. Do not paste it all in one step because ij is a little bit fragile and later you would get an error message that the table "ORDER_TABLE" could not be found.

I)

drop table ORDER_TABLE;

II)

drop table CUSTOMER;

III)

create table CUSTOMER (
ID NUMERIC(10) PRIMARY KEY,
NAME VARCHAR(256)
);

IV)

create table ORDER_TABLE (
ORDER_ID NUMERIC PRIMARY KEY,
SHIPPING_ADDRESS VARCHAR(500),
CUSTOMER_ID NUMERIC(10),
FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (ID)
);


Save the table definitions

That is the point where you say good-bye to the ij with the command:

disconnect ALL;

Now stop Derby by right-clicking the project and choose -> "Apache Derby" -> "Stop Derby Network Server"

The console should look like this:

DRDA_SecurityInstalled.I
Apache Derby Network Server 10.3.1.4 - (561794) was started and is ready since 2007-11-22 21:25:03.773 GMT to accept connections at Port 1527.
Apache Derby Network Server 10.3.1.4 - (561794) was shut down on 2007-11-22 21:50:43.998 GMT.

The ij process does not quit itself and blocks all incoming connection to the database, so change to the debug view and quit the ij process manually.


Derby restart

Now we are really close to heaven. Just start Apache Derby one more time (Right-click project -> "Apache Derby" -> "Start Derby Network Server").


Start of the JPA test program

>ou can start the test program by right-clicking the file "Client.java" in the folder "Source/client" and choose "Debug As" -> "Java Application".

The test program creates the following output. During the execution the respective data is saved and before the termination deleted from the database. So you will not see anything in the database without breakpoints in the right places.

Output:

[TopLink Config]: 2007.11.22 10:45:21.982--ServerSession(13037557)--The alias name for the entity class [class entity.Customer] is being defaulted to: Customer.
[TopLink Config]: 2007.11.22 10:45:22.008--ServerSession(13037557)--The table name for entity [class entity.Customer] is being defaulted to: CUSTOMER.
[TopLink Config]: 2007.11.22 10:45:22.023--ServerSession(13037557)--The column name for element [public java.lang.String entity.Customer.getName()] is being defaulted to: NAME.
[TopLink Config]: 2007.11.22 10:45:22.039--ServerSession(13037557)--The column name for element [public int entity.Customer.getId()] is being defaulted to: ID.
[TopLink Config]: 2007.11.22 10:45:22.057--ServerSession(13037557)--The alias name for the entity class [class entity.Order] is being defaulted to: Order.
[TopLink Config]: 2007.11.22 10:45:22.159--ServerSession(13037557)--The target entity (reference) class for the one to many mapping element [public java.util.Collection entity.Customer.getOrders()] is being defaulted to: class entity.Order.
[TopLink Config]: 2007.11.22 10:45:22.163--ServerSession(13037557)--The target entity (reference) class for the many to one mapping element [public entity.Customer entity.Order.getCustomer()] is being defaulted to: class entity.Customer.
[TopLink Config]: 2007.11.22 10:45:22.172--ServerSession(13037557)--The primary key column name for the mapping element [public entity.Customer entity.Order.getCustomer()] is being defaulted to: ID.
[TopLink Info]: 2007.11.22 10:45:22.643--ServerSession(13037557)--TopLink, version: Oracle TopLink Essentials - 2006.8 (Build 060830)
[TopLink Config]: 2007.11.22 10:45:23.548--ServerSession(13037557)--Connection(5535614)--connecting(DatabaseLogin(
platform=>JavaDBPlatform
user name=> "APP"
datasource URL=> "jdbc:derby://localhost:1527/testDB"
))
[TopLink Config]: 2007.11.22 10:45:23.555--ServerSession(13037557)--Connection(24298619)--Connected: jdbc:derby://localhost:1527/testDB
User: APP
Database: Apache Derby Version: 10.3.1.4 - (561794)
Driver: Apache Derby Network Client JDBC Driver Version: 10.3.1.4 - (561794)
[TopLink Config]: 2007.11.22 10:45:23.556--ServerSession(13037557)--Connection(29460424)--connecting(DatabaseLogin(
platform=>JavaDBPlatform
user name=> "APP"
datasource URL=> "jdbc:derby://localhost:1527/testDB"
))
[TopLink Config]: 2007.11.22 10:45:23.563--ServerSession(13037557)--Connection(2707298)--Connected: jdbc:derby://localhost:1527/testDB
User: APP
Database: Apache Derby Version: 10.3.1.4 - (561794)
Driver: Apache Derby Network Client JDBC Driver Version: 10.3.1.4 - (561794)
[TopLink Config]: 2007.11.22 10:45:23.563--ServerSession(13037557)--Connection(1752967)--connecting(DatabaseLogin(
platform=>JavaDBPlatform
user name=> "APP"
datasource URL=> "jdbc:derby://localhost:1527/testDB"
))
[TopLink Config]: 2007.11.22 10:45:23.571--ServerSession(13037557)--Connection(1331389)--Connected: jdbc:derby://localhost:1527/testDB
User: APP
Database: Apache Derby Version: 10.3.1.4 - (561794)
Driver: Apache Derby Network Client JDBC Driver Version: 10.3.1.4 - (561794)
[TopLink Config]: 2007.11.22 10:45:23.571--ServerSession(13037557)--Connection(29408998)--connecting(DatabaseLogin(
platform=>JavaDBPlatform
user name=> "APP"
datasource URL=> "jdbc:derby://localhost:1527/testDB"
))
[TopLink Config]: 2007.11.22 10:45:23.575--ServerSession(13037557)--Connection(20259911)--Connected: jdbc:derby://localhost:1527/testDB
User: APP
Database: Apache Derby Version: 10.3.1.4 - (561794)
Driver: Apache Derby Network Client JDBC Driver Version: 10.3.1.4 - (561794)
[TopLink Config]: 2007.11.22 10:45:23.575--ServerSession(13037557)--Connection(31517012)--connecting(DatabaseLogin(
platform=>JavaDBPlatform
user name=> "APP"
datasource URL=> "jdbc:derby://localhost:1527/testDB"
))
[TopLink Config]: 2007.11.22 10:45:23.579--ServerSession(13037557)--Connection(16164994)--Connected: jdbc:derby://localhost:1527/testDB
User: APP
Database: Apache Derby Version: 10.3.1.4 - (561794)
Driver: Apache Derby Network Client JDBC Driver Version: 10.3.1.4 - (561794)
[TopLink Config]: 2007.11.22 10:45:23.579--ServerSession(13037557)--Connection(28378327)--connecting(DatabaseLogin(
platform=>JavaDBPlatform
user name=> "APP"
datasource URL=> "jdbc:derby://localhost:1527/testDB"
))
[TopLink Config]: 2007.11.22 10:45:23.586--ServerSession(13037557)--Connection(11850652)--Connected: jdbc:derby://localhost:1527/testDB
User: APP
Database: Apache Derby Version: 10.3.1.4 - (561794)
Driver: Apache Derby Network Client JDBC Driver Version: 10.3.1.4 - (561794)
[TopLink Config]: 2007.11.22 10:45:23.586--ServerSession(13037557)--Connection(28365628)--connecting(DatabaseLogin(
platform=>JavaDBPlatform
user name=> "APP"
datasource URL=> "jdbc:derby://localhost:1527/testDB"
))
[TopLink Config]: 2007.11.22 10:45:23.590--ServerSession(13037557)--Connection(33310350)--Connected: jdbc:derby://localhost:1527/testDB
User: APP
Database: Apache Derby Version: 10.3.1.4 - (561794)
Driver: Apache Derby Network Client JDBC Driver Version: 10.3.1.4 - (561794)
[TopLink Info]: 2007.11.22 10:45:23.639--ServerSession(13037557)--file:/C:/Users/Stefan%20Linke/Desktop/Apache%20Derby/JPA_Eclipse/bin-pu1 login successful
Inserting Customer and Orders... OK
Verifying that all are inserted... OK
Removing all... OK
Verifying that all are removed... OK

That's it...