Accessing the DB using Java Database Connectivity (JDBC) is common in applications using relational DBs. Play provides a plugin to manage the JDBC connection pool. The plugin internally uses BoneCP (http://jolbox.com/), a fast Java Database Connection pool (JDBC pool) library.
Note
To use the plugin, a dependency in the build file should be added:
val appDependencies = Seq(jdbc)
The plugin supports H2, SQLite, PostgreSQL, MySQL, and SQL. Play is bundled with an H2 database driver, but to use any of the other databases we should add a dependency on its corresponding driver:
val appDependencies = Seq( jdbc, "mysql" % "mysql-connector-java" % "5.1.18",...)
The plugin exposes the following methods:
getConnection: It accepts the name of the database it should get the connection for and whether any statement executed using this connection should commit automatically or not. If a name is not provided, it fetches the connection for database with the default name.withConnection: It accepts a block of code that should be executed using a JDBC connection. Once the block is executed, the connection is released. Alternatively, it accepts the name of the database.withTransaction: It accepts a block of code that should be executed using a JDBC transaction. Once the block is executed, the connection and all its created statements are released.
How does the plugin know the details of the database? The details of the database can be set in conf/application.
conf:
db.default.driver=com.mysql.jdbc.Driver db.default.url="jdbc:mysql://localhost:3306/app" db.default.user="changeme" db.default.password="changeme"
The first part, db, is a set of properties, which are used by the DBPlugin. The second part is the name of the database, default in the example, and the last part is the name of the property.
For MySQL and PostgreSQL, we could include the user and password in the URL:
db.default.url="mysql://user:password@localhost:3306/app" db.default.url="postgres://user:password@localhost:5432/app"
For additional JDBC configurations, refer to https://www.playframework.com/documentation/2.3.x/SettingsJDBC.
Now that we've enabled and configured the the JDBC plugin, we can connect to a SQL-like database and execute queries:
def fetchDBUser = Action {
var result = "DB User:"
val conn = DB.getConnection()
try{
val rs = conn.createStatement().executeQuery("SELECT USER()")
while (rs.next()) {
result += rs.getString(1)
}
} finally {
conn.close()
}
Ok(result)
}Alternatively, we can use the DB.withConnection helper, which manages the DB connection:
def fetchDBUser = Action {
var result = "DB User:"
DB.withConnection { conn =>
val rs = conn.createStatement().executeQuery("SELECT USER()")
while (rs.next()) {
result += rs.getString(1)
}
}
Ok(result)
}