Steps needed to use MySQL database with Play framework 2.0

Java Playframework Playframework 2.0 Ebean

Java Problem Overview

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).

Java Solutions

Solution 1 - Java

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"

Solution 2 - Java

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.

Solution 3 - Java

I am using play 2.2.0 and I just had to add the following line to build.sbt in project's root folder.

  "mysql" % "mysql-connector-java" % "5.1.27"

And play automatically downloads the driver. It seems Build.scala is not needed for this anymore. Changes to application.conf should be applied as the above commentators have mentioned.

Solution 4 - Java

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:

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"
)
```
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.
Inside your Model (/app/models/MyModel.scala) add this:
```
val connection = DB.getConnection("myotherdb")
```

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)

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()

Solution 5 - Java

Got stuck with my MySQL configuration until I found this.

Most important things taken from @biesior answer:

Add MySQL connector/J in project's dependency (which is inside /project/Build.scala)
After adding dependency, run play dependencies to resolve newly added MySQL connector/J dependency
Uncomment default ebean configuration line ebean.default="models.*"
Configure MySQL database correctly with proper character encoding db.default.driver=com.mysql.jdbc.Driver db.default.url="jdbc:mysql://www.sample.com:3306/test?characterEncoding=UTF-8" db.default.user=playuser db.default.pass=playuser

It saved my day.

Solution 6 - Java

Play 2.4.3 & MYSQL 5.7.9

I was able to get this working by piecing together bits of info from all the previous answers. So here is another one, that is hopefully more up to date or useful to those with a similar environment.

Environment Details: (this is what I am using)

Play 2.4.3 this comes with activator-1.3.7-minimal
JDK8, you should already have this as I don't think this version of play works with JDK7
MYSQL 5.7.9

appication.conf

db.default.driver=com.mysql.jdbc.Driver
db.default.url="jdbc:mysql://localhost:3306/testSchema?characterEncoding=UTF-8"
db.default.user=yourDBUserName
db.default.password=yourDBUserPass

Note:

testSchema in the URL is your database name, if you are using something like MYSQL workbench you will see this listed under the SCHEMAS section. I called mine testSchema. Others may call it something like "myDatabase"
The port should be the MYSQL port. Not your application port. I put 3306 in the example because that is usually the default for MYSQL.

build.sbt

Add this line below to your build.sbt file. This should go after the libraryDependencies ++= Seq() declaration.

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

Finally

run this command from your project root -> activator reload
restart your appplication

Solution 7 - Java

For play 2.3.1, Follow these steps.

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"
Uncomment default ebean configuration line ebean.default="models.*"
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
Most Imp. Run a reload command in the console.

Solution 8 - Java

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.

Solution 9 - Java

I had the same issue in latest play framework 2.4.x with activator 1.3.6.

Here are the steps. I Followed the steps described here https://www.playframework.com/documentation/2.4.x/JavaDatabase

Here is my application.conf

# MySQL DB Configuration
db.default.driver=com.mysql.jdbc.Driver
db.default.url="jdbc:mysql://{hostname or ipaddres}/{db name}?characterEncoding=UTF-8"
db.default.username=username  // Note that user id deprecated, instead use username. Though that is not a major issue
db.default.password="password"

# JPA Configurations
jpa.default=defaultPersistenceUnit
PlayKeys.externalizeResources = false

# JavaEbean configuration
ebean.default = ["models.*"]

Here is build.sbt

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

plugins.sbt

// The Play plugin
addSbtPlugin("com.typesafe.play" % "sbt-plugin" % "2.4.3")

// Web plugins
addSbtPlugin("com.typesafe.sbt" % "sbt-coffeescript" % "1.0.0")
addSbtPlugin("com.typesafe.sbt" % "sbt-less" % "1.0.6")
addSbtPlugin("com.typesafe.sbt" % "sbt-jshint" % "1.0.3")
addSbtPlugin("com.typesafe.sbt" % "sbt-rjs" % "1.0.7")
addSbtPlugin("com.typesafe.sbt" % "sbt-digest" % "1.1.0")
addSbtPlugin("com.typesafe.sbt" % "sbt-mocha" % "1.1.0")

// Play enhancer - this automatically generates getters/setters for public fields
// and rewrites accessors of these fields to use the getters/setters. Remove this
// plugin if you prefer not to have this feature, or disable on a per project
// basis using disablePlugins(PlayEnhancer) in your build.sbt
addSbtPlugin("com.typesafe.sbt" % "sbt-play-enhancer" % "1.1.0")

// Play Ebean support, to enable, uncomment this line, and enable in your build.sbt using
// enablePlugins(SbtEbean). Note, uncommenting this line will automatically bring in
// Play enhancer, regardless of whether the line above is commented out or not.
addSbtPlugin("com.typesafe.sbt" % "sbt-play-ebean" % "1.0.0")

Here is the important step.

> After configuring above steps, go to command line, stop your activator and run the command activator run. In my situation, I was keep getting the error unable to find mysql drivers. After running the activator run, activator would actually download the MySQL drivers and would resolve the dependencies. That is the important step that resolved my issue.

Solution 10 - Java

For me this work, add this below line into your dependencies:

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

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()

Content Type	Original Author	Original Content on Stackoverflow
Question	Veera	View Question on Stackoverflow
Solution 1 - Java	Carsten	View Answer on Stackoverflow
Solution 2 - Java	biesior	View Answer on Stackoverflow
Solution 3 - Java	jrook	View Answer on Stackoverflow
Solution 4 - Java	consuela	View Answer on Stackoverflow
Solution 5 - Java	ck1910	View Answer on Stackoverflow
Solution 6 - Java	Kris Hollenbeck	View Answer on Stackoverflow
Solution 7 - Java	working	View Answer on Stackoverflow
Solution 8 - Java	Anand Kumar	View Answer on Stackoverflow
Solution 9 - Java	Manjunath Reddy	View Answer on Stackoverflow
Solution 10 - Java	SwwapnilShirke	View Answer on Stackoverflow