Accessing an SQL Database

Configuring The JDBC Driver Dependency

Database drivers are not provided by Play. As a result, in order to deploy in production, you must include your database driver as an application requirement. 

For example, if we use MySQL5, we must add the following requirement for the connector:

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

Database Configuration

There is a need to configure the connection pool in the conf/application.conf file. The default JDBC data source is called default by convention. It’s corresponding configuration properties are db.default.driver and db.default.url.

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

 

If anything is not configured correctly, we will be told straight in our browser:

We can change the default name by setting play.db.default, for example:

play.db.default = "primary"

db.primary.driver=org.h2.Driver
db.primary.url="jdbc:h2:mem:play"

How To Configure Several Data Sources

If we want to configure several data sources then:

# Orders database
db.orders.driver=org.h2.Driver
db.orders.url="jdbc:h2:mem:orders"


# Customers database
db.customers.driver=org.h2.Driver
db.customers.url="jdbc:h2:mem:customers"

H2 Database Engine Connection Properties

Here, in memory database following will be there.

# Default-database configuration using H2 database engine in in-memory mode
db.default.driver=org.h2.Driver
db.default.url="jdbc:h2:mem:play"

 

In file database.

# Default-database configuration using H2 database engine in  persistent mode
db.default.driver=org.h2.Driver
db.default.url="jdbc:h2:/path/to/db-file"

SQLite Database Engine Connection Properties

Following is the configuration using SQLite database engine

# Default database configuration using SQLite database engine
db.default.driver=org.sqlite.JDBC
db.default.url="jdbc:sqlite:/path/to/db-file"

PostgreSQL Database Engine Connection Properties

Following is the configuration using the PostgreSQL database engine

# Default database configuration using PostgreSQL database engine
db.default.driver=org.postgresql.Driver
db.default.url="jdbc:postgresql://database.example.com/playdb"

MySQL Database Engine Connection Properties

Following is the configuration using the MySQL database engine

# Default database configuration using MySQL database engine
# Connect to playdb as playdbuser
db.default.driver=com.mysql.jdbc.Driver
db.default.url="jdbc:mysql://localhost/playdb"
db.default.username=playdbuser
db.default.password="a strong password"

Exposing The Datasource Through JNDI

Some libraries anticipate getting the JNDI Datasource reference. By including the following settings in conf/application.conf, we may expose any Play controlled datasource using JDNI:

db.default.driver=org.h2.Driver
db.default.url="jdbc:h2:mem:play"
db.default.jndiName=DefaultDS

How To Configure SQL Log Statement

Not all connection pools provide an option to log SQL statements right out of the box. HikariCP advises using your database vendor's log capacity in each individual case. HikariCP documentation.

In order to ensure consistent SQL log statement support for supported pools, Play uses jdbcdslog-exp. By utilising the logSql property, the database may configure the SQL log statement:

# Default database configuration using PostgreSQL database engine
db.default.driver=org.postgresql.Driver
db.default.url="jdbc:postgresql://database.example.com/playdb"
db.default.logSql=true

 

Then we configure jdbcdslog-exp. Basically, we need to set the root logger to INFO and then choose the jdbcdslog-exp logs that we want to use (connections, statements and result sets). Here is an illustration of how to setup the logs using logback.xml:
 

<configuration>


  <conversionRule conversionWord="coloredLevel" converterClass="play.api.libs.logback.ColoredLevel" />


  <appender name="FILE" class="ch.qos.logback.core.FileAppender">
     <file>${application.home:-.}/logs/application.log</file>
     <encoder>
       <pattern>%date [%level] from %logger in %thread - %message%n%xException</pattern>
     </encoder>
  </appender>


  <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
    <encoder>
      <pattern>%coloredLevel %logger{15} - %message%n%xException{10}</pattern>
    </encoder>
  </appender>


  <appender name="ASYNCFILE" class="ch.qos.logback.classic.AsyncAppender">
    <appender-ref ref="FILE" />
  </appender>


  <appender name="ASYNCSTDOUT" class="ch.qos.logback.classic.AsyncAppender">
    <appender-ref ref="STDOUT" />
  </appender>


  <logger name="play" level="INFO" />


  <logger name="org.jdbcdslog.ConnectionLogger" level="OFF"  /> <!-- Won' log connections -->
  <logger name="org.jdbcdslog.StatementLogger"  level="INFO" /> <!-- Will log all statements -->
  <logger name="org.jdbcdslog.ResultSetLogger"  level="OFF"  /> <!-- Won' log result sets -->


  <root level="WARN">
    <appender-ref ref="ASYNCFILE" />
    <appender-ref ref="ASYNCSTDOUT" />
  </root>


  <shutdownHook class="ch.qos.logback.core.hook.DelayingShutdownHook"/> 
  
</configuration>

Now, it’s time to learn about the accessing of the JDBC datasource.

Accessing The JDBC Datasource

Access to the default datasource is provided by Play database packages, principally through the Database class.

import javax.inject.*;


import play.db.*;


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


@Singleton
class JavaApplicationDatabase {


  private Database db;
  private DatabaseExecutionContext executionContext;


  @Inject
  public JavaApplicationDatabase(Database db, DatabaseExecutionContext context) {
    this.db = db;
    this.executionContext = executionContext;
  }


  public CompletionStage<Integer> updateSomething() {
    return CompletableFuture.supplyAsync(
        () -> {
          return db.withConnection(
              connection -> {
                // do whatever we need with the db connection
                return 1;
              });
        },
        executionContext);
  }
}

If the database is not default.

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 JavaNamedDatabase {
  private Database db;
  private DatabaseExecutionContext executionContext;


  @Inject
  public JavaNamedDatabase(
      // inject "orders" database instead of "default"
      @NamedDatabase("orders") Database db, DatabaseExecutionContext executionContext) {
    this.db = db;
    this.executionContext = executionContext;
  }


  public CompletionStage<Integer> updateSomething() {
    return CompletableFuture.supplyAsync(
        () ->
            db.withConnection(
                connection -> {
                  // do whatever we need with the db connection
                  return 1;
                }),
        executionContext);
  }
}

When using withConnection in either scenario, the connection will be immediately terminated at the conclusion of the block.

Now, this was about accessing what about obtaining a connection. Okay, let’s see that also.

Obtaining A JDBC Connection

JDBC connection can be retrieved in the same way:

import java.sql.Connection;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.CompletionStage;
import javax.inject.Inject;


import play.db.Database;


class JavaJdbcConnection {
  private Database db;
  private DatabaseExecutionContext executionContext;


  @Inject
  public JavaJdbcConnection(Database db, DatabaseExecutionContext executionContext) {
    this.db = db;
    this.executionContext = executionContext;
  }


  public CompletionStage<Void> updateSomething() {
    return CompletableFuture.runAsync(
        () -> {
          // get jdbc connection
          Connection connection = db.getConnection();


          // do whatever we need with the db connection
          return;
        },
        executionContext);
  }
}

 

It is essential to remember that connections created as a consequence are not immediately destroyed at the conclusion of the request cycle. This means that in order for them to be instantly returned to the pool, you must use their close() function somewhere in your code.

Configuring The Connection Pool

HikariCP is the default database connection pool solution used by Play out of the box. Additionally, we may utilise our own pool by using the fully qualified class name of play.api.db.ConnectionPool:

play.db.pool=your.own.ConnectionPool

Frequently Asked Questions

Define the Play framework.

Play is a very important framework in today’s world. Based on a stateless, lightweight, web-friendly architecture. Play is built on Akka, and it has minimal resource consumption. It is a developer-friendly framework and very versatile. Most of the Java libraries are also supported in Play.

Define JDBC.

JDBC is the abbreviation for Java Database Connectivity. It specifies how a client can access a database. It is a Java-based data access method used for connecting Java databases.

Define JNDI.

JNDI is the abbreviation for Java Naming and Directory Interface API. In the JNDI directory, we actually store a JDBC DataSource, so we simply use JDBC to obtain a connection via JNDI lookup.

Which is the default database connection used by Play?

HikariCP is the default database connection pool solution used by Play.

What is the use of withConnection?

When accessing the database when we use withConnection in either scenario, the connection will be immediately terminated at the conclusion of the block.

Conclusion

In this article, we extensively discussed accessing an SQL database Play. We started with the introduction of the Play, JDBC and JDNI. Then we saw the default database configuration. Then we learned about the engine connection of the H2 database. Then we saw accessing and obtaining a connection with JDBC. In the end, we saw the frequently asked questions. 

Now you must be curious after knowing about web2py and how simple it is to learn. You can visit Basics of Python with Data Structures and Algorithms and Free Python Foundation with DS and Algo and start your journey.

Nevertheless, you may consider our paid courses to give your career an edge over others!

Happy Learning Ninja! 🥷