The next step in Play Framework discovery is the work with database data. Thanks to it, we can use centralized and dynamically managed data.
At the first part of this article we'll discover how to configure database connection. This will be also the opportunity to see how to manage dependencies in pom-like files. At the last part we'll show how to send selecting queries in the database through Play-native JPA mechanism.
Databases in Play Framework
When you install new Play Framework application, you can see that some of database connection elements are already defined in conf/application.conf file. They're only commented:
# db.default.driver=org.h2.Driver # db.default.url="jdbc:h2:mem:play" # db.default.user=sa # db.default.password=""
This commented configuration gives an idea about the right way of configuring the database connection. default key means that the configuration is applied to default database. A default database is the database returned by play.db.DB's getConnection() method. They're also a second method with the same name but with the different signature. This method takes one String parameter which means the name of database which connection we want to retrieve (for example: getConnection("crm") will return the connection defined by db.crm prefix).
Another interesting database feature in Play Framework is the automation of updates. It can be made thanks to evolution scripts which are no more, no less, than simple SQL files named by incremented counter. Ie. the first database evolution should be called 1.sql, the second 2.sql, the third 3.sql and so on. The scripts should be composed by two elements: !Ups and !Downs. The first defines the changes to apply while the second one the way of revert introduced changes (for example: deleting of created table). The scripts need to be placed in conf/evolutions/${databaseName}. For our default database, we'll place these scripts in conf/evolutions/default. In dev mode, the evolutions are applied manually (by clicking "Apply Evolutions" in Play console). In production environment, they need to be enabled with the attribute applyEvolutions.${databaseName}=true set either on configuraton file or as a system property (prefixed with -D).
Play supports JPA and simply by adding new dependency with JPA implementation (for example Hibernate), you can work with. In our example, we'll use Hibernate to this job. Let's see all of these things on real example. We'll start by creating the database with following statements:
CREATE DATABASE IF NOT EXISTS play_store CHARACTER SET utf8 COLLATE utf8_general_ci; USE 'play_store'; CREATE TABLE IF NOT EXISTS categories ( id INT(3) UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE IF NOT EXISTS products ( id INT(5) UNSIGNED NOT NULL AUTO_INCREMENT, categories_id INT(3) UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL, description TEXT NOT NULL, price DOUBLE(7, 2) NOT NULL, PRIMARY KEY (id) );
After executing this script, we need to modify the connection to the database in conf/application.conf file to following:
db.default.driver=com.mysql.jdbc.Driver db.default.url="mysql://root:root@localhost/play_store" db.default.user="root" db.default.password="root" db.default.jndiName=DefaultDS jpa.default=defaultPersistenceUnit
Actually the database connection won't work because the application doesn't know used driver and JPA provider (Hibernate). To enable them, we must add new dependencies in /build.sbt file:
libraryDependencies ++= Seq( // some of dependencies are defined by default, we insert two new dependencies directly after "org.hibernate" % "hibernate-entitymanager" % "3.6.9.Final", "mysql" % "mysql-connector-java" % "5.1.31" )
Note that you can't use any Hibernate's version. In our case, if we tried to add the 4.3.5 version (the current stable version), we could get some exceptions on runtime. One possible exception is:
play.api.UnexpectedException: Unexpected exception[NoSuchMethodError: javax.persistence.Table.indexes()[Ljavax/persistence/Index;] at play.core.ReloadableApplication$$anonfun$get$1$$anonfun$apply$1$$anonfun$1.apply(ApplicationProvider.scala:155) ~[play_2.11-2.3.1.jar:2.3.1] at play.core.ReloadableApplication$$anonfun$get$1$$anonfun$apply$1$$anonfun$1.apply(ApplicationProvider.scala:115) ~[play_2.11-2.3.1.jar:2.3.1] at scala.Option.map(Option.scala:145) ~[scala-library-2.11.1.jar:na] at play.core.ReloadableApplication$$anonfun$get$1$$anonfun$apply$1.apply(ApplicationProvider.scala:115) ~[play_2.11-2.3.1.jar:2.3.1] at play.core.ReloadableApplication$$anonfun$get$1$$anonfun$apply$1.apply(ApplicationProvider.scala:113) ~[play_2.11-2.3.1.jar:2.3.1] Caused by: java.lang.NoSuchMethodError: javax.persistence.Table.indexes()[Ljavax/persistence/Index; at org.hibernate.cfg.annotations.EntityBinder.processComplementaryTableDefinitions(EntityBinder.java:936) ~[hibernate-core-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.cfg.AnnotationBinder.bindClass(AnnotationBinder.java:824) ~[hibernate-core-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.cfg.Configuration$MetadataSourceQueue.processAnnotatedClassesQueue(Configuration.java:3788) ~[hibernate-core-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.cfg.Configuration$MetadataSourceQueue.processMetadata(Configuration.java:3742) ~[hibernate-core-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.cfg.Configuration.secondPassCompile(Configuration.java:1410) ~[hibernate-core-4.3.5.Final.jar:4.3.5.Final]
The setup is done. However, Play doesn't know the JPA configuration. We need add persistence.xml file to conf/META-INF directory:
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
version="2.0">
<persistence-unit name="defaultPersistenceUnit" transaction-type="RESOURCE_LOCAL">
<provider>org.hibernate.ejb.HibernatePersistence
<non-jta-data-source>DefaultDS
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" />
<property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver" />
</properties>
</persistence-unit>
</persistence>
Querying database in Play Framework
The configuration is not the only thing to do before working with database data. We must also define JPA entities which will match Java objects its database representation. They're both a simple POJO composed by getters and setters. You can see here the shortest one, Category which matches categories table:
show Category.java source codeLet's try to define a service layer which will group some of database actions. In this example we'll write a simple "find by id" method:
package services;
import models.Category;
import play.db.jpa.JPA;
public class CategoryService {
public Category getById(int id) {
return JPA.em().find(Category.class, id);
}
}
If you put this code in your IDE, you'll see that it doesn't recognize play.db.jpa package. It's normal because the build.sbt doesn't contain the dependency to it. We need to add this dependency before continue. In additionally, we must remove the dependency to Ebean which is the default ORM layer. So, the new version of dependencies in build.sbt looks now like:
libraryDependencies ++= Seq( javaJdbc, cache, javaWs, javaJpa, "org.hibernate" % "hibernate-entitymanager" % "3.6.9.Final", "mysql" % "mysql-connector-java" % "5.1.31" )
Now we can come back to our service and try to explain the method used to select a row from database. Two classes useful to work with persistence in Play Framework are located in
public EntityManager em(String key) {
EntityManagerFactory emf = emfs.get(key);
if(emf == null) {
return null;
}
return emf.createEntityManager();
}
This method is invoked after by JPA's method with the same name (em). Unlike JPAPlugin's one, em(String key) in JPA is static. If they're no entity managers found, a RuntimeException is thrown. Finally, the transactions are handled by play.db.jpa.Transactional annotation.
So, our service method can remain unchanged. Because the services are stateless, we can use only one instance of each service in whole application. It's why we'll create a singleton for them:
public enum ServicesInstances {
CATEGORY_SERVICE(new CategoryService());
private Object service;
private ServicesInstances(Object service) {
this.service = service;
}
public Object getService() {
return this.service;
}
}
To test if these two classes work well we must add some sample data into database. Following SQL code will make that:
USE 'play_store'; INSERT INTO categories VALUES (1, "food"), (2, "drinks"), (3, "candy"), (4, "pasta"), (5, "fish"), (6, "meat"); INSERT INTO products VALUES (1, 1, "bread", "You must eat it every morning !", 1.99), (2, 1, "butter", "You can''t eat our bread without this famous butter !", 5.99), (3, 2, "water", "This mineral water will add you a lot of energy", 0.99);
We can test if it works by adding following code to our's Application controller index method and watching Play's logs:
@Transactional(readOnly=true)
public static Result index() {
CategoryService categoryService = (CategoryService) ServicesInstances.CATEGORY_SERVICE.getService();
Category category = categoryService.getById(1);
Logger.debug("Found category: "+category);
return ok(index.render("Your new application is ready."));
}
After running http://localhost:9000, you should see following entries in the logs:
[info] play - datasource [mysql://root:root@localhost/play_store] bound to JNDI as DefaultDS
[info] play - database [default] connected at jdbc:mysql://localhost/play_store?useUnicode=yes&characterEncoding=UTF-8&connectionCollation=utf8_general_ci
[info] play - Application started (Dev)
[debug] application - Found category: Category {id: 1, name: food}
This article introduces us how to simply implement Hibernate in Play Framework. At the begin we created the base of database structure. After we modified Play's dependencies by adding Hibernate's one. At the last part we made a simple reading method which illustrated how to to use EntityManager in JPA's layer of Play Framework.
