Steps needed to use MySQL database with Play frame

Question

I'm new to Play framework. I'm trying to configure MySQL database as a datasource to be used with Play Ebeans.

Could you some one please explain the steps that are needed to configure MySQL with Play 2.0 framework (like, downloading drivers, adding dependency etc).

Answer 1

Look at this page from Play's documentation. It says:

Other than for the h2 in-memory database, useful mostly in development mode, Play 2.0 does not provide any database drivers. Consequently, to deploy in production you will have to add your database driver as an application dependency.

For example, if you use MySQL5, you need to add a dependency for the connector:

val appDependencies = Seq(
    // Add your project dependencies here,
    ...
    "mysql" % "mysql-connector-java" % "5.1.18"
    ...
)

SBT will download the driver for you. You should also check out the section on managing dependencies.

To connect to MySQL, you will also need to change some settings in your application.conf:

db.default.driver=com.mysql.jdbc.Driver
db.default.url="mysql://root:secret@localhost/myDatabase"

Answer 2

Most of the methods of accessing a mysql database that I've come across do not explain how to establish a connection and retrieve data from within the Model. In my application, I am using both mongoDB and an external mysql database. So here's how I did (the mysql side of) things:

1. For Play 2.3.3, in the build.sbt file add the mysql specific line in the libraryDependencies:

   libraryDependencies ++= Seq(
       "mysql" % "mysql-connector-java" % "5.1.27"
   )
   

2. In the /conf/application.conf file add this:

   db.myotherdb.driver = com.mysql.jdbc.Driver
   db.myotherdb.url = "jdbc:mysql://xxx.xxx.xxx.xxx/NameOfOtherDB?characterEncoding=UTF-8"
   db.myotherdb.user = MyOtherDbUSername
   db.myotherdb.password = MyOtherDbPass
   

   You can replace "myotherdb" by "default" in case you want to use the default database or with any other name that you want to use. Replace "xxx.xxx.xxx.xxx" with the IP address of the server where your database is located (in case of an external database) or localhost (or 127.0.0.1) for local database. Replace "NameOfOtherDB" with the name of the database that you want to use, the "MyOtherDbUSername" with your database username and "MyOtherDbPass" with your database password.

3. Inside your Model (/app/models/MyModel.scala) add this:

   val connection = DB.getConnection("myotherdb")
   

4. Create the statement, the query and execute it:

   val statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)
   val query = "SELECT * FROM myTableName"
   val resultset = statement.executeQuery(query)
   

5. Then you can continue with whatever you want to do with the retrieved data. For example:

   while (resultset.next()) {
       resultset.getString("columnName")
   }
   

   Where "columnName" is the name of the DB table column/field that you want to retrieve.

Last but not least, I would like to note that you might want to close the connection by calling close()

Answer 3

For me this work ,Add this below line into your Dependencies

**"mysql" % "mysql-connector-java" % "5.1.36"**


So , here is the code

      import java.sql.Connection

      val driver = "com.mysql.jdbc.Driver"
      val url = "jdbc:mysql://localhost/world"
      val username = "root"
      val password = "root"
      var connection: Connection = null

  try {         // make the connection
                Class.forName(driver)
                connection = DriverManager.getConnection(url, username, password)

                // create the statement, and run the select query
                val statement = connection.createStatement()
                val resultSet = statement.executeQuery("SELECT id , name FROM bar")

                val sql: SqlQuery = SQL("select * from products order by name asc")

                while (resultSet.next()) {
                  val id = resultSet.getString("id")
                  val name = resultSet.getString("name")
                  println(id, name)
                }
              } catch {
                case e: Exception => println("exception caught: " + e);
              }
              connection.close()

Answer 4

For play 2.3.1, Follow these steps.

1) Add MySQL connector/J in project's dependency (which is inside /project/build.sbt)

libraryDependencies ++= Seq( javaJdbc, javaEbean, "mysql" % "mysql-connector-java" % "5.1.29"

2) Uncomment default ebean configuration line ebean.default="models.*"

3) Configure MySQL database correctly with proper character encoding

db.default.driver=com.mysql.jdbc.Driver    //this is com. and not org.
db.default.url="jdbc:mysql://127.0.0.1/test?characterEncoding=UTF-8"
db.default.user=playuser
db.default.pass=playuser

4) Most Imp. Run a reload command in the console.

Answer 5

As Carsten wrote it can be fetched from documentation, however here's a summary:

make sure you have the dependency configured in /project/Build.scala

val appDependencies = Seq(
    // Add your project dependencies here,
    "mysql" % "mysql-connector-java" % "5.1.18"
)

Add a proper config of the DB (replace default H2 config) in /conf/application.conf:

(don't remove encoding from URL):

db.default.driver=com.mysql.jdbc.Driver
db.default.url="jdbc:mysql://localhost/your_db_name?characterEncoding=UTF-8"
db.default.user=your_login
db.default.password=your_pass

in the same file find and make sure this line is NOT commented:

ebean.default="models.*"

That's all, restart your app (or run in dev mode), then it will create a DDL and ask you to apply it.

Answer 6

For play java project Using SBT

Change the libraryDependency to llok like this in "build.sbt"

libraryDependencies ++= Seq(
  javaJdbc,
  javaEbean,
  cache,
  javaWs,
  "mysql" % "mysql-connector-java" % "5.1.27"
)

Run your project using "activator run"

Play will down required jdbc connector.