Working with Apache Derby

Apache Derby is helpful for quick prototyping of web applications without needing to work with bulkier RDBMSs. The following instructions show how to install Apache Derby and create a sample Department/Employee database. Other important topics such as authentication and running Derby in network server mode are also covered.

Derby download and installation:

1. Download the latest official release, and extract the zipfile into a new directory.
2. Make sure your JAVA_HOME environment variable is set, and add {derby home}/bin to your PATH environment variable. More detailed information is available but note the DERBY_HOME variable it mentions is not strictly necessary.
3. Run sysinfo at a command prompt from a directory outside your Derby installation directory to make sure the database and PATH variables are working correctly.

Creation of database instance and browsing of system tables:

1. Open ij from a command prompt and create a sample database, recommended in a folder different from DERBY_HOME:

   connect 'jdbc:derby:/home/myname/derbyDBs/EMPLDB;create=true';

   or, for Windows:

   connect 'jdbc:derby:C:\home\myname\derbyDBs\EMPLDB;create=true';

   Either will connect to the "EMPLDB" database in the specified directory, creating the database first if necessary. Also note, from the above example:

   • It is recommended to use an all-uppercase database name to avoid case sensitivity problems in DB connections.
   • The Derby team recommends keeping all of your databases under one directory, here, /home/myname/derbyDBs.
2. Next, you can browse the system tables by issuing commands such as "SELECT * from SYS.SYSTABLES;". Note the schema for the system tables is SYS (and needs to be explicitly specified here) and the default for your application tables is APP.
3. To leave ij, just type "quit;". To drop the database, just use operating system commands to delete the EMPLDB directory folder.

Creation of Database Objects:

Within Derby you can create schemas, tables, views, indexes, and Java-defined stored procedures and functions. The purpose of this section is to create a SQL script which will be run by ij to create the new database's objects.

1. For creating tables, review the data types available, and also consider if any special constructs will be needed for each column:
   • constraints (NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK)
   • WITH DEFAULT values for inserts
   • GENERATED AS IDENTITY for automatic sequence generation
2. Creation of the other objects listed are defined within the Derby reference manual.
3. For a sample SQL script showing table and view creation, copy the following script into a file called create-database.sql:

   SET SCHEMA APP;
   DROP VIEW EmplByDeptView;
   DROP TABLE Employee;
   DROP TABLE Department;
   
   CREATE TABLE DEPARTMENT (
     Dept_Id   SMALLINT NOT NULL PRIMARY KEY,
     Name      VARCHAR(20) NOT NULL,
     Location  VARCHAR(20) NOT NULL
   );
    
   CREATE TABLE EMPLOYEE (
     Empl_Id    SMALLINT NOT NULL PRIMARY KEY
                GENERATED ALWAYS AS IDENTITY,
     Last_Name  VARCHAR(20) NOT NULL,
     First_Name VARCHAR(20) NOT NULL,
     Gender     CHAR(1) NOT NULL 
                CONSTRAINT GENDER_CONSTRAINT 
                CHECK (Gender IN ('M', 'F')),
     Dept_Id    SMALLINT NOT NULL REFERENCES DEPARTMENT(Dept_Id),
     Hiredate   DATE NOT NULL, 
     Salary     DECIMAL(8,2),
     UNIQUE (Last_Name, First_Name)
   );
   
   CREATE VIEW EmplByDeptView AS (
     SELECT Empl.Empl_ID, Empl.Last_Name, Empl.First_Name,
   	Dept.Name as "Dept Name", Dept.Location, Empl.Hiredate, 
   	Empl.Salary
       FROM Employee Empl, Department Dept
     WHERE
       Empl.Dept_Id = Dept.Dept_Id
   );
   

4. Run the script within ij to create the objects:

   ij> connect 'jdbc:derby:c:\derbyDBs\EMPLDB';
   ij> run 'create-database.sql';
   

   The Derby tools guide shows multiple ways to run scripts within ij.
5. Next, to load some sample data into the two tables, copy the following data into a sample-data.sql script and run within ij as before:

   delete from employee;
   delete from department;
   
   INSERT INTO DEPARTMENT (Dept_Id, Name, Location) 
   VALUES
   (1, 'Accounting', 'Chicago'),
   (2, 'Sales', 'Boston'),
   (3, 'Training', 'New York'),
   (4, 'Research', 'Atlanta');
   
   INSERT INTO EMPLOYEE (Last_Name, First_Name, Gender, Dept_id, 
       Hiredate, Salary)
   VALUES
   ('SMITH', 'BOB', 'M', 1, '2002-10-15', 50000.00),
   ('JONES', 'TED', 'M', 2, '1998-04-05', 60000.00),
   ('MILLER', 'SALLY', 'F', 3, '2004-01-12', 70000.00),
   ('WILSON', 'MARK', 'M', 1, '1997-05-27', 80000.00);
   
   commit;
   

   The above data can be queried within ij using standard SQL SELECT syntax.

Additional ij Notes:

1. If you wish to use ij (or any other query tool) to connect to a database that another JVM (such as a webapp or standalone application) is presently accessing you'll need to run Derby in network mode.
2. The disconnect command is used to terminate either certain or all connections you presently have open within ij.
3. ij has autocommit set to on by default. This can be changed via the autocommit command. This list shows other properties that can be set within ij.
4. The show command can be used to view listings of already created database objects such as tables and views.
5. The DERBY equivalent of the Oracle DUAL table is sysibm.sysdummy1. For example, to output current system time or use other built-in functions, the command would be:

   select current_timestamp from sysibm.sysdummy1;

6. In SELECT statements, ij truncates columns to 128 characters by default. If you need to view strings longer than that, use the MaximumDisplayWidth command similar to as follows:

   ij> MaximumDisplayWidth 32762;
   

ij is of course a bare-bones command-line tool for working with databases. For a convenient graphical tool for making SQL queries, the Derby site provides a good tutorial on using Derby with Squirrel SQL.

Activating User Authentication and Authorization:

By default Derby does not authenticate; anything given for username and password (or none at all) is acceptable for it. For example, either of the two commands below will connect to the EMPLDB:

connect 'jdbc:derby:/home/myname/derbyDBs/EMPLDB;user=XXX;password=YYY';
connect 'jdbc:derby:c:\sampleDir\EMPLDB';

User authentication is normally handled either of two ways: via system-level users (used for all Derby databases) or database-level only users. (LDAP is also a possibility.)

To activate user authentication at the system level, follow these steps:

1. Create a derby.properties file in the base /home/myname/derbyDBs directory. In this file, set the requireAuthentication field to true, and add the username and password of each user as follows:

   derby.connection.requireAuthentication=true
   derby.user.username1=password1
   derby.user.username2=password2
   ...
   

2. Start ij, either from the /home/myname/derbyDBs directory directly (where it reads the property file automatically) or using the -p (property file) flag to point to the file created above:

   C:\ij -p  /home/myname/derbyDBs/derby.properties

   Warning: If you start Derby outside of the /home/myname/derbyDBs directory, or without specifying the property file, authentication will be deactivated.

To activate user authentication at the database level, follow these steps:

1. Within ij, create a database user and password by issuing the following command:

   CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.username1', 'password1');

2. Activate User authentication by issuing the following command:

   CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.connection.requireAuthentication', 'true');

For either authentication method, when you issue a connect command, you will now be required to supply correct user and password fields as shown here:

connect 'jdbc:derby:c:\derbyDBs\EMPLDB;user=username1;password=password1';

Also note you will now need to specify the (default) APP schema in your SQL statements when referencing database objects made in that schema: SELECT * FROM APP.XXXX; etc. Or you can switch the schema for the whole session over to APP by issuing a "SET SCHEMA APP" statement within ij.

Apache Derby in Network Server Mode:

The standard embedded mode for Derby, while it allows for multiple connections, requires that they be within the same JVM. This can be demonstrated by opening up ij in two different console windows and attempting to connect to the same database in embedded mode. The second console window will report an error similar to ERROR XSDB6: Another instance of Derby may have already booted the database /media/work1/EMPLDB.

For connections to the same Derby database across multiple JVM's (for example, from a web app and from a query tool), the Derby Network Server is needed. To run Derby in Network Server mode, assuming you have already added [DERBYINSTALLDIR]\bin to your path, all you need to do is enter the following command:

gmazza@gmazza-work:~$ startNetworkServer

(More detailed instructions here.) Next, from the client side, modify your connection string from the Derby embedded format of

connect 'jdbc:derby:/media/work1/EMPLDB;user=glen;password=password123';

to the Derby client format of

connect 'jdbc:derby://localhost:1527//media/work1/EMPLDB;user=glen;password=password123';

Where username and password fields can be omitted if authentication has not been activated, as described earlier.

Using the Derby Network server may require adding additional jars to your classpath, all of which should be available from your {DERBY_HOME}/lib folder. To access a Network running Derby instance from your Java code, you'll need to use a different JDBC driver, derbyclient.jar's org.apache.derby.jdbc.ClientDriver, available in Mavenized applications by adding thederbyclient dependency into your pom.xml.

Once done, you can open multiple ij console windows and connect to the same database. The Derby documentation provides more information about connection parameters available when connecting to the database in Network mode.

Apache Derby within WAR Files:

For distributing demonstration Java-based web applications that need to access a sample database, a Derby database instance can be directly included within the WAR file using theclasspath: sub-subprotocol. This will remove the need for anyone installing the demo to separately install and configure a Derby database instance. However, such "classpath" databases are unfortunately read-only, which may not work for your situation. Configuring a JNDI Resource within your servlet container (or optionally, within the META-INF/context.xml file within the WAR), will allow you write access to your Derby database.

For read-only within-the-WAR-file deployment, place derby.jar in the lib directory of your war file and the database instance in the "classes" folder under "WEB-INF". For Mavenized webapps, that would translate to adding the Derby dependency to your pom.xml and the database instance to the src/main/resources folder. Next, be sure to add the classpathsubprotocol to your JDBC connection string when opening connections from your Java code: jdbc:derby:classpath:/media/work1/EMPLDB, for example.

Activating Apache Derby logging:

While Derby is running it will write to a derby.log file located in the directory where Derby was activated. SQL statements are not logged by default, to do so create a derby.propertiesfile in the same directory with the derby.language.logStatementText property set. (Be aware that SQL parameters containing potentially sensitive information will get written out to that file.) For SQL statement tuning use derby.language.logQueryPlan.