Product Catalog Application using Play and Scala Part 3 – Database setup and access

Mon, Jul 21, 2014

Play Programming #Play #scala

In this article we will be looking at how to configure database access to MySQL in Play and how to use Anorm as the database access layer. First of all, we will be using MySQL, so please make sure that you have the database server running. Run the following command to verify, I will assume that your username is “root” and password is empty.

$ mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 2
Server version: 5.5.38-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]>

If you see this message and not the “Access denied” error message, chances are you already have your database setup correctly.

It’s always a good practice to reconfigure your user access right after you installed MySQL, but this is a little bit beyond our scope here. Please do search for it, I bet there are plenty of articles/tutorial out there showing you how to set user password or grant access privileges to a particular user.

In order for the application to access database, we will need to modify few files.

At the ${project.home}/build.sbt file, we add the following lines to the end of the file, which tell the SBT that we want to import the MySQL driver for Java version 5.1.31.

libraryDependencies += "mysql" % "mysql-connector-java" % "5.1.31"

After we added the line, we run the “activator” command and in the console we run the following commands to load the database driver library into our project.

[product-catalog] $ reload
[info] Loading project definition from /home/thor/Documents/projects/product-catalog/project
[info] Set current project to product-catalog (in build file:/home/thor/Documents/projects/product-catalog/)
[product-catalog] $ update
[info] Updating {file:/home/thor/Documents/projects/product-catalog/}root...
[info] Resolving jline#jline;2.11 ...
[info] Done updating.
[success] Total time: 8 s, completed Jul 21, 2014 3:17:12 PM

Then, at ${project.home}/conf/application.conf file, you will find the commented out configuration to access database like the following

# db.default.driver=org.h2.Driver
# db.default.url="jdbc:h2:mem:play"
# db.default.user=sa
# db.default.password=""

We will change the value to access MySQL database instead.

db.default.driver=com.mysql.jdbc.Driver
db.default.url="jdbc:mysql://localhost/"
db.default.user=root
db.default.password=""

Please note that if your password is empty, you will need to put 2 double quotes instead of empty space.

After we change this, we start the server.

[product-catalog] $ run 8000

--- (Running the application from SBT, auto-reloading is enabled) ---

[info] play - Listening for HTTP on /0:0:0:0:0:0:0:0:8000

(Server started, use Ctrl+D to stop and go back to the console...)

And we open the browser and visit this url http://localhost:8000/  and we will see the following message in the console.

[info] play - database [default] connected at jdbc:mysql://localhost/
[info] play - Application started (Dev)

This means the application has successfully connected to the database.