Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
ScalikeJdbc ConnectionPool wrapper utility
import java.sql.Connection
import com.typesafe.scalalogging.Logger
import com.company.utils.jdbc.MySQLConfig
import scalikejdbc.{ConnectionPool, ConnectionPoolSettings, DB, using}
import scala.util.control.NonFatal
object ConnectionPoolManager {
private lazy val logger: Logger = Logger[ConnectionPoolManager.type]
/**
* Checks if pool for db dbName already exists
* @param dbName Name of db
* @param poolNameOpt (Optional) Name of pool, If not provided, dbName is used
* @return
*/
def poolExists(dbName: String, poolNameOpt: Option[String] = None): Boolean = {
val poolName: String = poolNameOpt.getOrElse(dbName)
lazy val poolInitialized: Boolean = ConnectionPool.isInitialized(poolName)
lazy val poolAlive: Boolean = using(ConnectionPool.get(poolName).borrow()) { (connection: Connection) =>
if (!connection.isClosed && connection.isValid(2)) true
else {
logger.info(s"Removing dead ConnectionPool: '$poolName' for db '$dbName'")
removePool(poolName)
false
}
}
poolInitialized && poolAlive
}
/**
* Creates a new pool for db dbName with given properties,
* only if pool with given name not already present
* @param dbName Name of db
* @param poolNameOpt (Optional) Name of pool, If not provided, dbName is used
* @param timeout (Optional) Timeout (seconds) for pool connections. Default = 300s
* @param initialSize (Optional) Initial no of connections in pool. Default = 1
* @param maxSize (Optional) Maximum no of connections in pool. Default = 2
* @return
*/
def addPool(dbName: String,
poolNameOpt: Option[String] = None,
timeout: Int = 300,
initialSize: Int = 1,
maxSize: Int = 2
): Boolean = {
if (poolExists(dbName, poolNameOpt)) false
else {
val poolName: String = poolNameOpt.getOrElse(dbName)
try {
val jdbcConfigs: MySQLConfig = MySQLConfig.read(dbName)
ConnectionPool.add(
name = poolName,
url = jdbcConfigs.url,
user = jdbcConfigs.user,
password = jdbcConfigs.password,
settings = ConnectionPoolSettings(
initialSize = initialSize,
maxSize = Math.max(maxSize, initialSize),
connectionTimeoutMillis = timeout.toLong * 1000,
validationQuery = "SELECT 1",
driverName = jdbcConfigs.driver
)
)
// wait for initialization of connection pool
while (!ConnectionPool.isInitialized(poolName)) {}
logger.info(s"Added ConnectionPool '$poolName' for db '$dbName'")
true
} catch {
case NonFatal(e) => {
logger.warn(s"MySQLConfigs not found for db: '$dbName'")
false
}
}
}
}
/**
* Borrows a connection from pool for db dbName.
* If pool not present, creates it and then borrows connection.
* @param dbName Name of db
* @param poolNameOpt (Optional) Name of pool
* @return
*/
def getDB(dbName: String, poolNameOpt: Option[String] = None): DB = {
addPool(dbName, poolNameOpt)
val poolName: String = poolNameOpt.getOrElse(dbName)
DB(ConnectionPool.get(poolName).borrow())
}
/**
* Removes the pool for db dbName
* @param dbName Name of db
* @param poolNameOpt (Optional) Name of pool
* @return
*/
def removePool(dbName: String, poolNameOpt: Option[String] = None): Boolean = {
if (poolExists(dbName, poolNameOpt)) false
else {
val poolName: String = poolNameOpt.getOrElse(dbName)
ConnectionPool.close(poolName)
// wait for un-initialization of connection pool
while (ConnectionPool.isInitialized(poolName)) {}
logger.info(s"Removed ConnectionPool '$poolName' for db '$dbName'")
true
}
}
}
import ConnectionPoolManager
object ConnectionPoolUsage {
def makeQuery(dbName: String, poolNameOpt: Option[String]) = {
ConnectionPoolManager.getDB(dbName, poolNameOpt).localTx { implicit session: DBSession =>
// perform ScalikeJdbc SQL query here
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.