Steps needed to use MySQL database with Play framework 2.0

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

Solution 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"

Solution 2

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

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

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

Solution 5

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.

View more solutions

Comments

[Veera]

thanks. Once that is done, what would be the configuration changes should I make in application.conf file? (db.default.driver, db.default.url, etc)

[biesior]

@Carsten, giving url without quotes will fail

[Kinjal Dixit]

play framework 2.1.1 here . after updating the build.sbt, you should give the command 'update' in the play terminal

[Jack Slingerland]

This just saved me. Using Play 2.10.3 and this was the correct way to do it.

[Lavixu]

Can you please update the documentation too here github.com/playframework/playframework/blob/2.2.x/documentat‌​ion/… so that everyone can benefit? Thanks!

[Adrian Scott]

Nowadays, it should be added into the build.sbt at the root-level of the project, e.g.: libraryDependencies ++= Seq( javaJdbc, javaEbean, "mysql" % "mysql-connector-java" % "5.1.28", cache )

[johanandren]

When you get it working you should also read the thread pool section of the docs and update your config accordingly since jdbc is a blocking API. playframework.com/documentation/2.2.x/ThreadPools

[Sparko]

After including the above settings, running 'dependencies' as opposed to 'update' (mentioned by @KinjalDixit) in the play console was required.

[ivvi]

Other things to consider is to make sure that MySQL is not limited to socket only connections (Mac/Linux) and that localhost may need to be replaced with 127.0.0.1. In specifict terms, using MariaDB (an Oracle-free drop in replacement for MySQL) from MacPorts I had to comment out skip-networking in my.cnf and use the IP address instead of localhost to have Play successfully connect.

[BenMorganIO]

Why did you add jdbc to the beginning of the db url?

[biesior]

@BenMorganIO cause we need to use JDBC driver, such a syntax, nothing more

[Dao Lam]

Thank you!!! For people who need detailed instructions like me, you basically go to build.sbt and add that line to libraryDependencies ++= Seq(jdbc,anorm,cache,"mysql" % "mysql-connector-java" % "5.1.27")

[Nick]

This works all perfectly fine for me locally. But when I create a dist package, upload the package to a Ubuntu server and try to start the application I get java.sql.SQLException: No suitable driver found for mysql://....

[working]

try putting your mysql driver in classpath.

[Nick]

I've added it to libraryDependencies in my build.sbt (which made it work locally) and on the server I've installed it with sudo apt-get install mysql-client; sudo apt-get install libmysql-java put export CLASSPATH=/usr/share/java/mysql-connector-java.jar and also added it to /etc/environment (as described on help.ubuntu.com/community/JDBCAndMySQL). It still doesn't work.

[lomse]

Your example is very useful. How would it look like for Play Java?

[3xCh1_23]

For people like me, do not forget to stop the ./activator, and then run it again :)

[Michael Lafayette]

I'm confused. What is the purpose of "jdbc:mysql:" in the URL? Is "jdbc:mysql" the name of the database?

[bharal]

should possibly be updated with the new build.sbt command ,or a version outlining?