How To Connect Play Application To Derby Database

Play comes with embedded H2 database which can be used for testing out database applications.  However what about testing external databases just to make sure that your application can connect to them and the code will execute properly.

Derby is a open source light weight database provide by Apache.  Derby is made in Java and supports most of the SQL statements available in any SQL database.  In the post we will see how your application can be made to connect to Derby Database.

Here are the steps.

1. Download Derby from http://db.apache.org/derby/derby_downloads.html

2. Extract the zip file into a folder.

3.  Add a environment variable - DERBY_HOME.  The value should be <installation directory of Derby>

4.  In the path variable add the following - %DERBY_HOME%/bin;%PATH% . This will add the Derby bin folder to path where all the binaries for Derby are present.

5. Start the Derby Server by going to the  <installation directory of Derby>/bin directory and issue the following command - startNetworkServer.bat  The Derby server will start and listen on the port 1527.

6. In another command window issue the command ij.  This is also present in the bin folder of Derby installation folder.

7. Issue the following command -
     connect 'jdbc:derby://localhost:1527/db;create=true';
    This will connect to db database on Derby.  In case that database is not present it will create that database.

8.  Now create the table you need in the play framework.  For example in my application I am going to need an Employee table which has auto incremented id as primary key.  Here is the script to create that table.

ij> create table employee (
  id                        bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  first_name                varchar(255),
  middle_name               varchar(255),
  last_name                 varchar(255),
  email_id                  varchar(255),
  contact_number1           varchar(255),
  contact_number2           varchar(255),
  customer_user_id          varchar(255),
  grade                     varchar(255)
);

9.  Now you need to make the changes in the Play application.

10. First we will change the application.conf and add the information as follows -

 db.default.driver=org.apache.derby.jdbc.ClientDriver
db.default.url="jdbc:derby://localhost:1527/db"

# Evolutions
# ~~~~~
# You can disable evolutions if needed
evolutionplugin=disabled

11.  Now add the Derby client jar dependency in the build.sbt

libraryDependencies ++= Seq(

  javaJdbc,

  javaEbean,

  cache,

  "org.apache.derby" % "derbyclient" % "10.8.3.0"

)    

The last line "org.apache.derby" % "derbyclient" % "10.8.3.0" is the most important line related to Derby.  It basically tells Play application to pick the jar from maven central with the following information :

GroupId   :  org.apache.derby
ArtifactId  : derbyclient
Version    : 10.8.3.0

12. If you have  Build.scala you can also add it there.  Usually if you are planning to create a war file from Play application Build.scala is required.  See how to create a Play framework war file here.

Restart the play application and it is now ready to connect to the Derby database.