Disconnect from Slick JNDI connection in Play for Scala

Question

Slick defines here how to connect to a database using JNDI:

val db = Database.forName(jndiName: String)

I use the above to connect to a database in Play for Scala, defining the JNDI connection in application.conf:

   def read (jndi: String, code: Int) = {
      val db = Database.forName(jndi)
      val records = TableQuery[TableDB]
      val action = records.filter(_.code === code).result
      val future = db.run(action.asTry)
      future.map{ 
        case Success(s) => 
          if (s.length>0)
            Some(s(0))
          else
            None
        case Failure(e) => throw new Exception ("Failure in read: " + e.getMessage)
      }
   }

Question is: how to disconnect from the JNDI resource? Simply db.close() ? Is there a way to implicitly close the connection when the read method ends?

score 1 · Accepted Answer · answered Dec 08 '17 at 18:58

1

It is not clear what make you doubt it. If you look at the source of forName, you may see that it just asks JNDI context to get object by the name you provided and then treat it as javax.sql.DataSource that can create connections. DataSource is not Closeable or anything like this so you don't have to explicitly release it. It is enough to just close the db.

As for closing when the read method ends, this is probably not what you really want because you return Future that might still be not finished and thus might need the connection to be open. So what you probably need is Future.onComplete

  def read(jndi: String, code: Int) = {
    val records = TableQuery[TableDB]
    val action = records.filter(_.code === code).result
    val db = Database.forName(jndi)
    val future = db.run(action.asTry)
    future.onComplete(_ => db.close()) // <-- added line
    future.map {
      case Success(s) =>
        if (s.length > 0)
          Some(s(0))
        else
          None
      case Failure(e) => throw new Exception("Failure in read: " + e.getMessage)
    }
  }

answered Dec 08 '17 at 18:58

SergGr

23,570
2
30
51

@ps0604, if this query is not a unique one that you do only once in a while but a typical query that can be run in parallel, why do you want to close connection after each query? It is possible to do this but sounds quite suspicious as to why. – SergGr Dec 12 '17 at 18:56
I'm getting the following error when I run two futures in parallel, each future getting the database with `Database.forName(jndi)` am I using the same JNDI connection in both futures? `Caused by: java.lang.Exception: Failure in readUserSession: HikariDataSource HikariDataSource (HikariPool-12) has been closed.` This is the Database class I'm using: `slick.jdbc.JdbcBackend.Database` – ps0604 Dec 12 '17 at 18:57
I'm fine with leaving the connection open, but don't I have to close the connection at some point? I'm accessing the database multiple times in the application, should the connections be closed only when the application shuts down? – ps0604 Dec 12 '17 at 18:59
@ps0604, Well, no, under typical usage scenarios you don't want to close connection immediately after query. On the contrary, typically you would want to use some "connection pool" to re-use few connections between many different queries over time as establishing new connection is a relatively heavy operation. And actually when application is shut down (even forcefully), any modern DB will automatically handle the error on its side of the connection to close it. In the "graceful exit" scenario (if you have one) you probably want to ensure that all queries that were in progress have finished. – SergGr Dec 12 '17 at 19:23

score 1 · Answer 2 · answered Dec 09 '17 at 14:54

You can always use play's shutdown hooks to close the db connection when you app shuts down (which is usually what you would want) and yes db.close is enough to stop the connection, your port will be freed which is all you should care about. I'm not quite sure why you would want the db to close right after the method, but there's a very easy fix to this without polluting the code, you simply inject your database into the method :

def read(db : Database, code: Int) = {
    val records = TableQuery[TableDB]
    val action = records.filter(_.code === code).result
    val future = db.run(action.asTry)
    future.map {
      case Success(s) =>
        if (s.length > 0)
          Some(s(0))
        else
          None
      case Failure(e) => throw new Exception("Failure in read: " + e.getMessage)

  }

And the caller of this method can close the respective database once you have the results you want. Much safer and easier to change :) On the other hand, you can always go with @SergGr's solution, it also works :)

Disconnect from Slick JNDI connection in Play for Scala

2 Answers2