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.