Play in action – Connect with MySQL Database

Play Framework - Build Modern & Scalable Web Apps with Java and Scala

 

                      In this article we will discuss how to integrate “Play with MySQL“. In this article I am using Play 2.4.2 and MySQL 5.5.11. Follow the below steps to setup Play with MySQL.

Step1: As a first step create a database in MySQL by following the below SQL commands(Expecting you have installed MySQL in your machine already).

CREATE DATABASE play_mysql;
CREATE USER 'play_java'@'localhost' IDENTIFIED BY 'play_java123';
GRANT ALL PRIVILEGES ON play_mysql.* TO 'play_java'@'localhost' WITH GRANT OPTION;

Here, I have created a database called play_mysql  with username play_java and the password is play_java123

Step 2: Create a table to store the information. Here we want to store student information. So creating “Student” table.

CREATE TABLE STUDENT(USER_NAME VARCHAR(3), FIRST_NAME VARCHAR(50), LAST_NAME VARCHAR(50), AGE VARCHAR(3), PRIMARY KEY(USER_NAME));

Step 3: As we are using MySQL as the database, we need to add MySQL Java Connector as the library dependency to the project. The library dependencies will be added to “build.sbt” available under root project folder.

Play Library Dependencies

Step 4: Its time to mention the MySQL data source connection details in the “application.conf” available under <root_project_folder> –> conf.

# Database configuration
# ~~~~~
# You can declare as many datasources as you want.
# By convention, the default datasource is named `default`
#
db.default.driver=com.mysql.jdbc.Driver
db.default.url="jdbc:mysql://localhost:3306/play_mysql"
db.default.user=play_java
db.default.pass=play_java123
db.default.host=localhost
db.default.initSQL="SELECT 1"

There are other JDBC settings available. You can explore the same here.

Step 4: Now we will try to get the database connection.

Play JDBC Connection

Step 5: Its time to write the controller code and the DAO code to save the student information. Below is the sequence diagram to depict the components interaction.

Play Controller DAO Sequence Diagram

 The StudentController’s saveStudent method implementation is given below.

/**
 * This method saves the student.
 * @return - The available students in map with JSON representation
 */
 public Result saveStudent() {
    JsonNode json = request().body().asJson();
    Logger.debug(&quot;json&quot; + json.toString());
    try {
     Student.saveStudent(json);
      return ok(&quot;Student information is successfully saved&quot;);
     } catch (SQLException sqlException) {
        Logger.error(&quot;Some thing went wrong while saving the student information&quot;, sqlException);
       return status(500, &quot;Some went wrong while saving the student information&quot;);
    }
  }

The StudentDAO’s saveStudent method implementation is given below.

/**
 * This method will save the student information into &quot;STUDENT&quot; table
 * @param student to save
 * @throws SQLException 
 */
 public static void saveStudent(Student student) throws SQLException {
     Connection con = null;
     try {
        con = DB.getConnection();
        Logger.debug(&quot;The student information is getting saved&quot;);
        String insertStudentSQL = &quot;INSERT INTO STUDENT(USER_NAME, FIRST_NAME, LAST_NAME, AGE) VALUES(?,?,?,?)&quot;;
        PreparedStatement pstmt = con.prepareStatement(insertStudentSQL);
        pstmt.setString(1, student.getUserName());
        pstmt.setString(2, student.getFirstName());
        pstmt.setString(3, student.getLastName());
        pstmt.setString(4, student.getAge());
        pstmt.executeUpdate();
        Logger.debug(&quot;The student information is successfully saved&quot;);
      } finally {
        if (con != null) {
           con.close();
     }
   }
 }

The source code created as part of this article is available on github.

Play With MySQL
Advertisements

I am Siva Prasad Rao Janapati. Working as a software developer. Has hands on experience on ATG Commerce(DAS/DPS/DCS), Mozu commerce, Broadleaf Commerce, Java, JEE, Spring, Play, JPA, Hibernate, Velocity, JMS, Jboss, Weblogic,Tomcat, Jetty, Apache, Apache Solr, Spring Batch, JQuery, NodeJS, SOAP, REST, MySQL, Oracle, Mongo DB, Memcached, HazelCast, Git, SVN, CVS, Ant, Maven, Gradle, Amazon Web services, Rackspace, Quartz, JMeter, Junit, Open NLP, Facebook Graph,Twitter4J, YouTube Gdata, Bazzarvoice,Yotpo, 4-Tell, Alatest, Shopzilla, Linkshare. I have hands on experience on open sources and commercial technologies.

Tagged with: , ,
Posted in Frameworks, Play

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

DZone

DZone MVB

Java Code Geeks
Java Code Geeks
%d bloggers like this: