在 Play 框架2.0中使用 MySQL 数据库所需的步骤

我对 Play 框架是个新手,我正在尝试将 MySQL 数据库配置为一个可以与 Play Ebeans 一起使用的数据源。

你能解释一下使用 Play 2.0框架配置 MySQL 所需的步骤吗(比如,下载驱动程序,添加依赖项等)。

77986 次浏览

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"

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.

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.

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.

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.

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

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.

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.

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

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