Java Developers-Accessing an SQL Database

📌Configuring JDBC Driver Dependency

We need database drivers to establish a database connection. In Play, there's no database driver present by default, so we need to add it as an application dependency.

 

libraryDependencies ++= Seq( "mysql" % "mysql-connector-java" % "5.1.41" )

 

The above line of code will help us in adding a dependency for the MySQL5 connector.

Let us now move on to seeing how we can configure a database.

📌Database Configuration

After establishing the database connection, we configure a connection pool in the conf/application.conf file.

 

# Default database configuration
db.default.driver=org.h2.Driver
db.default.url="JDBC:h2:mem:play"

 

In the above lines of code, 'default' is the default JDBC data source. db.default.driver and db.default.URL are the configuration properties.

We can change the default name by using:

 

play.DB.default = "principal"

 

Now the database configuration would look like this:

 

db.principal.driver=org.h2.Driver
DB.principal.url="JDBC:h2:mem:play"

 

We will be sticking with 'default' for the rest of the article.

MySQL database engine connection properties

 

Let us see how we can connect the MySQL database:

 

# Connect to playdb as played
db.default.driver=com.MySQL.jdbc.Driver
db.default.url="JDBC:mysql://localhost/playdb"
db.default.username=playdb
db.default.password="DB password"

 

We can also obtain a data source reference from Java Naming and Directory Interface(JNDI). How do we access a data source? Let us discuss this now.

📌Accessing the JDBC Datasource

The Database class gives access to the default data source in the Play database packages. Let us see how we can access a database:

import javax.inject.Inject;
import javax.inject.Singleton;

import play.db.NamedDatabase;
import play.db.Database;

import java.util.concurrent.CompletableFuture;
import java.util.concurrent.CompletionStage;

@Singleton
class NotDefaultDatabase {
  private Database d;
  private DatabaseExecutionContext executionContext;

  @Inject
  public JavaNamedDatabase(
// inject "Student" database instead of "default" to access the student database instead //of the default database
      @NamedDatabase("Student") Database d, DatabaseExecutionContext executionContext) {
    this.d = d;
    this.executionContext = executionContext;
  }

  public CompletionStage<Integer> updateWhatever() {
    return CompletableFuture.supplyAsync(
        () ->
            d.withConnection(
                connection -> {
                  // do what is to be done with the database here
                  return 1;
                }),
        executionContext);
  }
}

 

Using the same way, we can also obtain a JDBC connection.

📌H2 Database

Because your evolutions are rerun from scratch when play restarts, the H2 in-memory database benefits development. You give a parameter to the database URL in your application to notify h2 that you want to imitate a specific database. 

For example to imitate a MYSQL database add the following line in conf file:
 

DB.default.url="JDBC:h2:mem:play;MODE=MYSQL"

 

The mode changes according to the database you use. If there are no connections to our in-memory database, H2 will discard it by default. To avoid this, we add DB CLOSE DELAY=-1 to the URL, for example:

 

JDBC:h2:mem:play;MODE=MYSQL;DB CLOSE DELAY=-1 

 

By entering the h2-browser into the sbt shell, we may view the contents of our database. The web browser will launch a SQL browser.

📌Evolutions

Let us discuss how do we manage, enable and synchronize evolutions. 

Managing Database Evolutions

Using Relational Database, we need a mechanism to monitor and manage the growth of your database structure. There are various circumstances when a more advanced method of tracking database schema changes is required:
 

• All members of a development team must get the updated schema.
• After deploying on a production server, there must be a reliable method for upgrading the database schema.
• You must maintain all database schemas synced if you operate across many machines.

Enable Evolutions

We need to add evolutions and JDBC into your dependencies list to enable evolutions. 

For example, in build.sbt:
 

libraryDependencies ++= Seq(evolutions, jdbc)

Evolution Scripts

Several evolution scripts are used by Play to track the evolutions of our database. The evolution scripts are written in SQL and are placed in the conf/evolutions/database named directory. conf/evolutions/default is the default path for the default database.

Each script has two parts:

• The Ups section has  the necessary modifications.
• The Downs section explains how to reverse them.

 

Consider the following initial evolution script, which bootstraps a simple application:

 

-- Student schema
--!Ups

CREATE TABLE Student (
    id int(10) NOT NULL,
    name varchar(100) NOT NULL,
    PRIMARY KEY (id)
);

--!Downs
DROP TABLE Student;

 

The Ups and Downs sections are separated by a regular, single-line SQL remark in your script that contains either !Ups or !Downs.

Evolutions Configuration

Evolutions can be set per data source or globally. Keys should be prefixed with play.evolutions for global configuration. Keys should be prefixed with play.evolutions.db per data source settings.

There are multiple configuration modes like enabled, schema, autocommit, etc.

For example, to enable enabled for all evolutions, we will set Play.evolutions.enabled=true in an application.conf or in a system property.

Synchronizing Concurrent Changes

There may be a time when two users work on the same database and want to synchronize their content. It can be done as follows:

Consider a user A creates a new table by creating the following evolution script :
 

-- Add Student
-- !Ups

CREATE TABLE Student (
    id int(10) NOT NULL,
    name varchar(100) NOT NULL,
    PRIMARY KEY (id)
);

-- !Downs

DROP TABLE Student;

 

Now suppose another user, B wants to alter another table Faculty. B will also create an evolution script for the same:

 

-- Update Faculty
-- !Ups
ALTER TABLE Faculty ADD salary INT;
-- !Downs
ALTER TABLE Faculty DROP salary;

 

The merge can be done as follows:
 

-- Add Student and update Faculty

-- !Ups
ALTER TABLE Faculty ADD salary INT;
CREATE TABLE Student (
    id int(10) NOT NULL,
    name varchar(100) NOT NULL,
    PRIMARY KEY (id)
);

-- !Downs
ALTER TABLE Faculty DROP salary;
DROP TABLE Student;

Now let us see how we can use JPA to access our database.

📌Using JPA to access the database

Adding Dependencies to  the Project

We must first notify Play that our project relies on javaJpa, which will offer JDBC and JPA API requirements.

There’s no already existing JPA implementation in play. We may use any existing implementation like Hibernate. To use Hibernate, we add the following dependency:

 

libraryDependencies ++= Seq(
  javaJpa,
  "org.hibernate" % "hibernate-core" % "5.4.32.Final"
)

Creating a Persistence Unit

Following that, we must construct a valid persistence.xml JPA configuration file. This file must be placed in the conf/META-INF directory.

Here is an example Hibernate setup 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="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>
        </properties>
    </persistence-unit>

</persistence>

 

We must specify the persistent unit your JPA provider should utilize. This is the jpa.default value in your conf/application.conf file.

 

jpa.default=defaultPersistenceUnit

 

To deploy play with JPA, we will add the following to build.sbt file:

 

PlayKeys.externalizeResourcesExcludes += base directory.value / "conf" / "META-INF" / "persistence.xml"

 

Using an API which is provided by play we can easily work with Transactions and Entity Manager. This API is described by the class play.DB.jpa.JPAApi, which may be injected into other objects as seen below:

 

import play.DB.jpa.JPAApi;

import javax.inject.*;
import javax.persistence.*;
import java.util.concurrent.*;

@Singleton
public class JPARepository {
  private JPAApi jpaApi;
  private DatabaseExecutionContext executionContext;

  @Inject
  public JPARepository(JPAApi API, DatabaseExecutionContext executionContext) {
    this.jpaApi = API;
    this.executionContext = executionContext;
  }
}

 

When using JPA, we should always utilize a specific execution context to guarantee that Play's rendering thread pool is 100% focused on rendering pages and entirely using cores. We may create a custom execution context devoted to servicing JDBC operations by using Play's CustomExecutionContext class.

Frequently asked questions?

What is Play?

Play is a developer-friendly framework that helps us to make web applications using Java and Scala.

What does the Ups and Downs section used for in evolution scripts?

The Ups section has  the necessary modifications and the Downs section explains how to reverse them.

Does play's built-in JDBC module automatically prevents dropping your in-memory database?

Yes, the built-in JDBC Module in Lay will automatically add DB CLOSE DELAY=-1 to prevent dropping off an in-memory database.

What are some options for configuration in evolution?

Schema, enable, autocommit, useLocks, and autoApply are some options for configuration in evolution.

Conclusion

This article taught us how to access SQL databases using JAVA in Play. We saw how we can configure JDBC connections and how to configure the JDBC driver. We did see what evolution is. 

Learn DSA and web technologies on our website. Study core subjects like DBMS, Software engineering, and computer networks. Do not stop here! You can also consider our paid courses such as DSA in Java to give your career an edge over others!.

Happy Learning!