Play framework with MySQL database Part 3

4 min readJun 30, 2021

Alright guys. So this the final part. And let’s directly jump into the task. So in the previous part we have seen that how to specify dependencies and how to deal with the views as well.

Now let’s see how to do MySQL injection to our HomeContoller.scala

First we must import db API to the HomeContoller and specify that injection as a parameter within the HomeContoller class parameter list.

Adding the API and specifying it withing the HomeController class parameter list

As you can see there are few functions index(), explore(), tutorial() added default to manage the transition between index, explore and tutorial views. But in our case since we are not going to use those I’m just gonna remove those because I need my script clean and readable.

Cool. So now we are going to make function to execute query in MySQL to get data to our HomeController. Then we are going to pass those values to hour home view.

/** establishing the db connection in order to execute queries */
val connection = db.getConnection()
val statement = connection.createStatement
val query = "SELECT * FROM tutorial"
val resultset = statement.executeQuery(query)

Lets quickly look at what is the idea of above mentioned syntaxes. First I just established the db connection using getConnection() function. Then I created a statement using that connection. Then I declare a query string. (Note that in this case I use val as the the type of the query since I’m not going to change it at the execution time, but if you are going to use more queries then make sure you specified the type as var). Finally I executed the query and stored the results on resultset value.

Now here is the thing. resultset is not a single value. It store the entire query result that generated after executing it. So now we need to filter out curresponding data for our work. So if you remember our table “tutorial” has two columns named “IndexNo”, “Name”. So I’m going to get each data from resultSet and store those values in two separate arrays named “indexNoArray”, “nameArray”.

var i = 0
var indexNoArray = new Array[Int](5)
var nameArray = new Array[String](5)

while(resultset.next()){
  indexNoArray(i) = resultset.getInt("IndexNo")
  nameArray(i) = resultset.getString("Name")
  i = i + 1
}

Above mentioned loop is pretty much straight forward. It just going for each positions in resultset and getting data associated with labels and store those in corresponding array positions.

So we are almost done. Now we have to do is passing those stored data within the arrays to the home view. Since this is just and example I’m going to pass each data one by one (which is not quite good) but of course when you are dealing with massive data collection it is necessary to use loop to pass data.

So go to the home function and pass those as parameters.

/**This is a main home function that passes the data associated within the arrays to the home.scala.html*/
def home = Action {
  Ok(views.html.home(
    indexNoArray(0),indexNoArray(1),indexNoArray(2),indexNoArray(3),
    nameArray(0),nameArray(1),nameArray(2),nameArray(3)
  ))
}

But still our view not ready to get those data. Therefore let’s declare variables within our home view to get these passed data. Go to home.scala.html and add those variables.

adding variables to get passed data from the HomeController

@(id1:Int, id2:Int, id3:Int, id4:Int, name1:String, name2:String, name3:String, name4:String)

Now the final step. Add these variables in corresponding position in the html code. Then it will show the actual data instead of the dummy data.

Now all done. Go to the browser and reload the page. You will see the table with data added in our database table. Oh forgot to mention that remove the unnecessary routes from the routes as well. Otherwise it will throw a compilation error. And remove the unnecessary views from the view folder.

Now refresh the page you will see the content.

Amazing. So we have successfully retrieved data from MySQL database and displayed the data.

Thank you folks. So it is up to play with the play framework. And I myself also new to this framework. So yeah give a clap for my stories if you get something. And don’t forget to leave a comment as well. See you soon.

From : Dhanushka sandakelum. B.Sc. in Computer science. University of Colombo School of Computing.

Play framework with MySQL database Part 3

Written by Dhanushka sandakelum