Using MySQL DB

This section will describe how to use MySQL from your Play app.

It assumes you already have a running projects use H2 DB.

Install MySQL

1. Download MySQL https://dev.mysql.com/downloads/mysql/ (download the DMG file)
2. Install the file and follow the instructions NOTE! Write down the random root password shown
3. Open System Preferences, there should be new option "MySQL" that lets you start and stop the DB
4. Download Sequel Pro (drag to Application folder)
5. Open the Sequel Pro and connect to the database

Select Socket tab
Name: localhost
Username: root 
Passwork: THE TEMP PASSWORD

Press connect

6. You need to change the temporary password do something permanent (have not tried this in Sequel Pro). DO NOT FORGET IT
7. Create a database, e.g. "library"

You can continue creating the Book table and columns manually using Sequel Pro or we can let our PlayApp create the schema automatically when we start our server.

Configure MySQL in Play App

1. Update settings in "application.config"

# Default database configuration using MySQL database engine
default.driver=com.mysql.jdbc.Driver
default.url="jdbc:mysql://localhost/library"
default.username=root
default.password="YOUR DB ROOT PASSWORD"

NOTE! You need to remove the H2 configuration.

2. Update your persistence.xml file

<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"
             version="2.1">

    <persistence-unit name="defaultPersistenceUnit" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
        <non-jta-data-source>DefaultDS</non-jta-data-source>
        <properties>
            <property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
        </properties>
    </persistence-unit>

</persistence>

This will create your schema from your Entity annotations the first time the Play server is started.

10. Add MySQL JDBC drive as dependency to your project. Update build.sbt

libraryDependencies ++= Seq(
  javaJdbc,
  cache,
  javaWs,
  javaJpa,
  "org.hibernate" % "hibernate-entitymanager" % "5.1.0.Final",
  "mysql" % "mysql-connector-java" % "5.1.36"
)

11. Compile your app activator compile
12. Start your app activator run
13. Look in the logs for any errors
14. Make any request to your app (to compile the app)
15. Look in Sequel Pro and you library BD, you should see two new tables (Books an hibernate_sequence)
16. Stop the server
17. Update your persistence.xml file to NOT drop the tables as startup each time
18. Start your server again
19. Create some books and use Sequel Pro to inspect the data