Connection Pool

Configuration

Borrowing Connections

Simply just call #borrow method.

import scalikejdbc._
// default
val conn: java.sql.Connection = ConnectionPool.borrow()
// named
val conn: java.sql.Connection = ConnectionPool("named").borrow()

Be careful. The connection object should be released by yourself.

Basically using loan pattern is recommended to avoid human errors.

using(ConnectionPool.borrow()) { conn =>
  // do something
}

ScalikeJDBC wraps a java.sql.Connection object as a scalikejdbc.DB object.

using(DB(ConnectionPool.borrow())) { db =>
  // ...
}

DB object can provide DBSession for each operation.

using(DB(ConnectionPool.borrow())) { db =>
  db.readOnly { implicit session =>
    // ...
  }
}

Right, above code is too verbose! Using DB object make it much simpler.

You can simplify the same things by using DB or NamedDB objects and it’s the common usage of ScalikeJDBC.

// default
DB readOnly { implicit session =>
  // ...
}
// named
NamedDB("named") readOnly { implicit session =>
  // ...
}

Reusing same DB instance several times

By default, scalikejdbc.DB or scalikejdbc.NamedDB instance always closes its connection (= releases and returns it to connection pools).

using(connectionPool.borrow()) { (conn: java.sql.Connection) => 
  val db: DB = DB(conn)

  db.localTx { implicit session =>
    sql"update something set name = ${name} where id = ${id}".update.apply()
  } // localTx or other APIs always close the connection to avoid connection leaks

  // the Connection already has been closed here!
  // java.sql.SQLException will be thrown!
  db.localTx { implicit session =>
    // ....
  }
}

When you prefer reusing the same Connection without releasing and returning to connection pools, use DB#autoClose(Boolean).

using(connectionPool.borrow()) { (conn: java.sql.Connection) => 
  val db: DB = DB(conn)

  // set as auto-close disabled
  db.autoClose(false)

  db.localTx { implicit session =>
    sql"update something set name = ${name} where id = ${id}".update.apply()
  } // localTx won't close the current Connection

  // this block also works fine!
  db.localTx { implicit session =>
    // ....
  }
}

Thread-local Connection Pattern

You can share DB connections as thread-local values. The connection should be released by yourself.

def init() = {
  val newDB = ThreadLocalDB.create(conn)
  newDB.begin()
}
// after that..
def action() = {
  val db = ThreadLocalDB.load()
}
def finalize() = {
  try { ThreadLocalDB.load().close() } catch { case e => }
}

Replacing ConnectionPool on Runtime

You can replace ConnectionPool settings safely on runtime.

The old pool won’t be abandoned until all the borrowed connections are closed.

def doSomething = {
  ConnectionPool.singleton("jdbc:h2:mem:db1", "user", "pass")
  DB localTx { implicit s =>
    // long transaction...

    // overwrite singleton CP
    ConnectionPool.singleton("jdbc:h2:mem:db2", "user", "pass")

    // db1 connection pool is still available until this trancation is committed.
    // Newly borrowed connections will access db2.
  }
}

Using Another ConnectionPool Implementation

If you want to use another one which is not Commons DBCP as the connection provider, You can also specify your own ConnectionPoolFactory as follows:

/**
 * c3p0 Connection Pool Factory
 */
object C3P0ConnectionPoolFactory extends ConnectionPoolFactory {
  override def apply(url: String, user: String, password: String,
    settings: ConnectionPoolSettings = ConnectionPoolSettings()) = {
    new C3P0ConnectionPool(url, user, password, settings)
  }
}

/**
 * c3p0 Connection Pool
 */
class C3P0ConnectionPool(
  override val url: String,
  override val user: String,
  password: String,
  override val settings: ConnectionPoolSettings = ConnectionPoolSettings())
  extends ConnectionPool(url, user, password, settings) {

  import com.mchange.v2.c3p0._
  private[this] val _dataSource = new ComboPooledDataSource
  _dataSource.setJdbcUrl(url)
  _dataSource.setUser(user)
  _dataSource.setPassword(password)
  _dataSource.setInitialPoolSize(settings.initialSize)
  _dataSource.setMaxPoolSize(settings.maxSize);
  _dataSource.setCheckoutTimeout(settings.connectionTimeoutMillis.toInt);

  override def dataSource: DataSource = _dataSource
  override def borrow(): Connection = dataSource.getConnection()
  override def numActive: Int = _dataSource.getNumBusyConnections(user, password)
  override def numIdle: Int = _dataSource.getNumIdleConnections(user, password)
  override def maxActive: Int = _dataSource.getMaxPoolSize
  override def maxIdle: Int = _dataSource.getMaxPoolSize
  override def close(): Unit = _dataSource.close()
}

implicit val factory = C3P0ConnectionPoolFactory
ConnectionPool.add("xxxx", url, user, password)

Switching ConnectionPool Implementation by configuration

When ConnectionPoolFactory implementation already exists, it’s possible to specify it by configuration. By default, commons-dbcp ½ and bonecp are already prepared. When you’d like to add another ConnectionPoolFactory, call repository’s add method like this:

scalikejdbc.ConnectionPoolFactoryRepository.add("name", YourConnectionPoolFactory)

Default: commons-dbcp2

https://commons.apache.org/proper/commons-dbcp/

https://search.maven.org/search?q=g:org.apache.commons%20AND%20a:commons-dbcp2

ConnectionPool.singleton(url, user, password, 
  ConnectionPoolSettings(connectionPoolFactoryName = "commons-dbcp2"))

commons-dbcp 1.x

Previously, commons-dbcp 1.4 was the default connection pool for ScalikeJDBC. We don’t recomment using the older one now, but if you need to choose 1.4 instead for some reason, specifying commons-dbcp works.

ConnectionPool.singleton(url, user, password, 
  ConnectionPoolSettings(connectionPoolFactoryName = "commons-dbcp"))

commons-dbcp dependency should be added by yourself.

libraryDependencies += "commons-dbcp" % "commons-dbcp" % "1.4"

BoneCP

http://jolbox.com/

ConnectionPool.singleton(url, user, password, 
  ConnectionPoolSettings(connectionPoolFactoryName = "bonecp"))

bonecp dependency should be added by yourself.

libraryDependencies += "com.jolbox" % "bonecp" % "0.8.0.RELEASE"

HikariCP

https://github.com/brettwooldridge/HikariCP

HikariCP expects dataSourceClassName. So we recommend using DataSourceConnectionPool.

val dataSource: DataSource = {
  val ds = new HikariDataSource()
  ds.setDataSourceClassName(dataSourceClassName)
  ds.addDataSourceProperty("url", url)
  ds.addDataSourceProperty("user", user)
  ds.addDataSourceProperty("password", password)
  ds
}
ConnectionPool.singleton(new DataSourceConnectionPool(dataSource))

HikariCP dependency should be added by yourself.

libraryDependencies += "com.zaxxer" % "HikariCP" % "3.+"

If this webpage has a typo or something wrong, Please report or fix it. How?