Play in action – Connect with MySQL Database

                      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 set up 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 information. Here we want to store student information. So creating a “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.

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.

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 interaction of the components.

 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("json" + json.toString());
    try {
     Student.saveStudent(json);
      return ok("Student information is successfully saved");
     } catch (SQLException sqlException) {
        Logger.error("Some thing went wrong while saving the student information", sqlException);
       return status(500, "Some went wrong while saving the student information");
    }
  }

StudentDAO’s saveStudent method implementation is given below.

/**
 * This method will save the student information into "STUDENT" table
 * @param student to save
 * @throws SQLException
 */
 public static void saveStudent(Student student) throws SQLException {
     Connection con = null;
     try {
        con = DB.getConnection();
        Logger.debug("The student information is getting saved");
        String insertStudentSQL = "INSERT INTO STUDENT(USER_NAME, FIRST_NAME, LAST_NAME, AGE) VALUES(?,?,?,?)";
        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("The student information is successfully saved");
      } finally {
        if (con != null) {
           con.close();
     }
   }
 }

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

About Siva Prasad Rao Janapati

Siva Janapati is an Architect with experience in building Cloud Native Microservices architectures, Reactive Systems, Large scale distributed systems, and Serverless Systems. Siva has hands-on in architecture, design, and implementation of scalable systems using Cloud, Java, Go lang, Apache Kafka, Apache Solr, Spring, Spring Boot, Lightbend reactive tech stack, APIGEE edge & on-premise and other open-source, proprietary technologies. Expertise working with and building RESTful, GraphQL APIs. He has successfully delivered multiple applications in retail, telco, and financial services domains. He manages the GitHub(https://github.com/2013techsmarts) where he put the source code of his work related to his blog posts.