SDP（2）：ScalikeJDBC-Connection Pool Configuration

scalikeJDBC可以通过配置文件来设置连接池及全局系统参数。对配置文件的解析是通过TypesafeConfig工具库实现的。默认加载classpath下的application.conf,application.json和application.properties文件。作为尝试，我们可以在resource/application.conf文件里进行h2和mysql数据库的JDBC驱动参数定义：

# JDBC settings

db {

  h2 {

    driver="org.h2.Driver"

    url="jdbc:h2:tcp://localhost/~/slickdemo"

    user=""

    password=""

    poolInitialSize=

    poolMaxSize=

    poolConnectionTimeoutMillis=

    poolValidationQuery="select 1 as one"

    poolFactoryName="commons-dbcp"

  }

}

db.mysql.driver="com.mysql.jdbc.Driver"

db.mysql.url="jdbc:mysql://localhost:3306/testdb"

db.mysql.user="root"

db.mysql.password=""

db.mysql.poolInitialSize=

db.mysql.poolMaxSize=

db.mysql.poolConnectionTimeoutMillis=

db.mysql.poolValidationQuery="select 1 as one"

db.mysql.poolFactoryName="commons-dbcp"

# scallikejdbc Global settings

scalikejdbc.global.loggingSQLAndTime.enabled=true

scalikejdbc.global.loggingSQLAndTime.logLevel=info

scalikejdbc.global.loggingSQLAndTime.warningEnabled=true

scalikejdbc.global.loggingSQLAndTime.warningThresholdMillis=

scalikejdbc.global.loggingSQLAndTime.warningLogLevel=warn

scalikejdbc.global.loggingSQLAndTime.singleLineMode=false

scalikejdbc.global.loggingSQLAndTime.printUnprocessedStackTrace=false

scalikejdbc.global.loggingSQLAndTime.stackTraceDepth=

上面h2和mysql设置采用了不同的格式。scalikeJDBC是在trait DBs中的setup(dbname)来进行dbname数据库连接池的设定的：

/**

 * DB configurator

 */

trait DBs { self: TypesafeConfigReader with TypesafeConfig with EnvPrefix =>

  def setup(dbName: Symbol = ConnectionPool.DEFAULT_NAME): Unit = {

    val JDBCSettings(url, user, password, driver) = readJDBCSettings(dbName)

    val cpSettings = readConnectionPoolSettings(dbName)

    if (driver != null && driver.trim.nonEmpty) {

      Class.forName(driver)

    }

    ConnectionPool.add(dbName, url, user, password, cpSettings)

  }

  def setupAll(): Unit = {

    loadGlobalSettings()

    dbNames.foreach { dbName => setup(Symbol(dbName)) }

  }

  def close(dbName: Symbol = ConnectionPool.DEFAULT_NAME): Unit = {

    ConnectionPool.close(dbName)

  }

  def closeAll(): Unit = {

    ConnectionPool.closeAll

  }

}

/**

 * Default DB setup executor

 */

object DBs extends DBs

  with TypesafeConfigReader

  with StandardTypesafeConfig

  with NoEnvPrefix

可以看到：setup(dbname)进行了dbname设置操作包括Class.forName(driver),ConnectionPool.add(dbname...)。我们首先试试使用h2数据库进行一些操作：

import scalikejdbc._

import scalikejdbc.config._

import org.joda.time._

import scala.util._   //Try

import scalikejdbc.TxBoundary.Try._

object JDBCConfig extends App{

  // DBs.setup/DBs.setupAll loads specified JDBC driver classes.

  // DBs.setupAll()

   DBs.setup('h2)

   DBs.setup('mysql)

  // Unlike DBs.setupAll(), DBs.setup() doesn't load configurations under global settings automatically

  DBs.loadGlobalSettings()

  val dbname = 'h2

  //clear table object

  try {

    sql"""

        drop table members

          """.execute().apply()(NamedAutoSession(dbname))

  }

  catch {

    case _: Throwable =>

  }

也可以用DBs.setupAll()来设定配置文件中的所有数据库设置。setupAll()还运行了loadGlobalSettings()。下面我们再进行实际的数据操作：

 //construct SQL object

  val createSQL: SQL[Nothing,NoExtractor] =SQL("""

    create table members (

      id bigint primary key auto_increment,

      name varchar() not null,

      description varchar(),

      birthday date,

      created_at timestamp not null

    )""")

  //run this SQL

  createSQL.execute().apply()(NamedAutoSession(dbname))   //autoCommit

  //data model

  case class Member(

                     id: Long,

                     name: String,

                     description: Option[String] = None,

                     birthday: Option[LocalDate] = None,

                     createdAt: DateTime)

  def create(name: String, birthday: Option[LocalDate], remarks: Option[String])(implicit session: DBSession): Member = {

    val insertSQL: SQL[Nothing,NoExtractor]  =

      sql"""insert into members (name, birthday, description, created_at)

           values (${name}, ${birthday}, ${remarks}, ${DateTime.now})"""

    val id: Long = insertSQL.updateAndReturnGeneratedKey.apply()

    Member(id, name, remarks, birthday,DateTime.now)

  }

  val users = List(

    ("John",new LocalDate("2008-03-01"),"youngest user"),

    ("Susan",new LocalDate("2000-11-03"),"middle aged user"),

    ("Peter",new LocalDate("1983-01-21"),"oldest user"),

  )

  val result: Try[List[Member]] =

    NamedDB(dbname) localTx { implicit session =>

      Try {

        val members: List[Member] = users.map { person =>

          create(person._1, Some(person._2), Some(person._3))

        }

        members

      }

    }

  result match {

    case Success(mlist) => println(s"batch added members: $mlist")

    case Failure(err) => println(s"${err.getMessage}")

  }

  //data row converter

  val toMember = (rs: WrappedResultSet) => Member(

    id = rs.long("id"),

    name = rs.string("name"),

    description = rs.stringOpt("description"),

    birthday = rs.jodaLocalDateOpt("birthday"),

    createdAt = rs.jodaDateTime("created_at")

  )

  val selectSQL: SQL[Member,HasExtractor] = sql"""select * from members""".map(toMember)

  val members: List[Member] = NamedDB(dbname) readOnly { implicit session =>

    selectSQL.list.apply()

  }

  println(s"all members: $members")

  NamedDB('h2mem).close()

注意在过程中我们使用Named???（???)来指定目标数据库连接connection。在上面的配置文件中有一项属性poolFactoryName，它指定了具体使用的数据库连接池工具。scalikeJDBC提供了commons-dbcp,commons-dbcp2,bonecp如下：

poolFactoryName="commons-dbcp"

poolFactoryName="commons=dbcp2"

poolFactoryName="bonecp"

如果配置文件中不提供poolFactoryName的设置，默认为commons-dbcp。翻查了一下，上面这几个连接池管理工具都很陈旧了。想到slick用的是HikariCP，上网看了看2018年还进行了最近更新。下面我们就为scalikeJDBC增加HikariCP连接池管理工具支持。首先，我们需要用TypesafeConfig解析HikariCP配置后构建HikariConfig对象，然后用它来构建HikariDataSource。

下面是配置文件解析代码：

package configdbs

import scala.collection.mutable

import scala.concurrent.duration.Duration

import scala.language.implicitConversions

import com.typesafe.config._

import java.util.concurrent.TimeUnit

import java.util.Properties

import scalikejdbc.config._

import com.typesafe.config.Config

import com.zaxxer.hikari._

import scalikejdbc.ConnectionPoolFactoryRepository

/** Extension methods to make Typesafe Config easier to use */

class ConfigExtensionMethods(val c: Config) extends AnyVal {

  import scala.collection.JavaConverters._

  def getBooleanOr(path: String, default: => Boolean = false) = if(c.hasPath(path)) c.getBoolean(path) else default

  def getIntOr(path: String, default: => Int = ) = if(c.hasPath(path)) c.getInt(path) else default

  def getStringOr(path: String, default: => String = null) = if(c.hasPath(path)) c.getString(path) else default

  def getConfigOr(path: String, default: => Config = ConfigFactory.empty()) = if(c.hasPath(path)) c.getConfig(path) else default

  def getMillisecondsOr(path: String, default: => Long = 0L) = if(c.hasPath(path)) c.getDuration(path, TimeUnit.MILLISECONDS) else default

  def getDurationOr(path: String, default: => Duration = Duration.Zero) =

    if(c.hasPath(path)) Duration(c.getDuration(path, TimeUnit.MILLISECONDS), TimeUnit.MILLISECONDS) else default

  def getPropertiesOr(path: String, default: => Properties = null): Properties =

    if(c.hasPath(path)) new ConfigExtensionMethods(c.getConfig(path)).toProperties else default

  def toProperties: Properties = {

    def toProps(m: mutable.Map[String, ConfigValue]): Properties = {

      val props = new Properties(null)

      m.foreach { case (k, cv) =>

        val v =

          if(cv.valueType() == ConfigValueType.OBJECT) toProps(cv.asInstanceOf[ConfigObject].asScala)

          else if(cv.unwrapped eq null) null

          else cv.unwrapped.toString

        if(v ne null) props.put(k, v)

      }

      props

    }

    toProps(c.root.asScala)

  }

  def getBooleanOpt(path: String): Option[Boolean] = if(c.hasPath(path)) Some(c.getBoolean(path)) else None

  def getIntOpt(path: String): Option[Int] = if(c.hasPath(path)) Some(c.getInt(path)) else None

  def getStringOpt(path: String) = Option(getStringOr(path))

  def getPropertiesOpt(path: String) = Option(getPropertiesOr(path))

}

object ConfigExtensionMethods {

  @inline implicit def configExtensionMethods(c: Config): ConfigExtensionMethods = new ConfigExtensionMethods(c)

}

trait HikariConfigReader extends TypesafeConfigReader {

  self: TypesafeConfig =>      // with TypesafeConfigReader => //NoEnvPrefix =>

  import ConfigExtensionMethods.configExtensionMethods

  def getFactoryName(dbName: Symbol): String = {

    val c: Config = config.getConfig(envPrefix + "db." + dbName.name)

    c.getStringOr("poolFactoryName", ConnectionPoolFactoryRepository.COMMONS_DBCP)

  }

  def hikariCPConfig(dbName: Symbol): HikariConfig = {

    val hconf = new HikariConfig()

    val c: Config = config.getConfig(envPrefix + "db." + dbName.name)

    // Connection settings

    if (c.hasPath("dataSourceClass")) {

      hconf.setDataSourceClassName(c.getString("dataSourceClass"))

    } else {

      Option(c.getStringOr("driverClassName", c.getStringOr("driver"))).map(hconf.setDriverClassName _)

    }

    hconf.setJdbcUrl(c.getStringOr("url", null))

    c.getStringOpt("user").foreach(hconf.setUsername)

    c.getStringOpt("password").foreach(hconf.setPassword)

    c.getPropertiesOpt("properties").foreach(hconf.setDataSourceProperties)

    // Pool configuration

    hconf.setConnectionTimeout(c.getMillisecondsOr("connectionTimeout", ))

    hconf.setValidationTimeout(c.getMillisecondsOr("validationTimeout", ))

    hconf.setIdleTimeout(c.getMillisecondsOr("idleTimeout", ))

    hconf.setMaxLifetime(c.getMillisecondsOr("maxLifetime", ))

    hconf.setLeakDetectionThreshold(c.getMillisecondsOr("leakDetectionThreshold", ))

    hconf.setInitializationFailFast(c.getBooleanOr("initializationFailFast", false))

    c.getStringOpt("connectionTestQuery").foreach(hconf.setConnectionTestQuery)

    c.getStringOpt("connectionInitSql").foreach(hconf.setConnectionInitSql)

    val numThreads = c.getIntOr("numThreads", )

    hconf.setMaximumPoolSize(c.getIntOr("maxConnections", numThreads * ))

    hconf.setMinimumIdle(c.getIntOr("minConnections", numThreads))

    hconf.setPoolName(c.getStringOr("poolName", dbName.name))

    hconf.setRegisterMbeans(c.getBooleanOr("registerMbeans", false))

    // Equivalent of ConnectionPreparer

    hconf.setReadOnly(c.getBooleanOr("readOnly", false))

    c.getStringOpt("isolation").map("TRANSACTION_" + _).foreach(hconf.setTransactionIsolation)

    hconf.setCatalog(c.getStringOr("catalog", null))

    hconf

  }

}

hikariCPConfig函数返回了hconf。下面我们还需要修改DBs.setup调用HikariConfigReader里的函数来构建HikariDataSource已经相关的配置参数：

import scalikejdbc._

trait ConfigDBs {

    self: TypesafeConfigReader with TypesafeConfig with HikariConfigReader =>

  def setup(dbName: Symbol = ConnectionPool.DEFAULT_NAME): Unit = {

    getFactoryName(dbName) match {

      case "hikaricp" => {

        val hconf = hikariCPConfig(dbName)

        val hikariCPSource = new HikariDataSource(hconf)

        if (hconf.getDriverClassName != null && hconf.getDriverClassName.trim.nonEmpty) {

          Class.forName(hconf.getDriverClassName)

        }

        ConnectionPool.add(dbName, new DataSourceConnectionPool(hikariCPSource))

      }

      case _ => {

        val JDBCSettings(url, user, password, driver) = readJDBCSettings(dbName)

        val cpSettings = readConnectionPoolSettings(dbName)

        if (driver != null && driver.trim.nonEmpty) {

          Class.forName(driver)

        }

        ConnectionPool.add(dbName, url, user, password, cpSettings)

      }

    }

  }

  def setupAll(): Unit = {

    loadGlobalSettings()

    dbNames.foreach { dbName => setup(Symbol(dbName)) }

  }

  def close(dbName: Symbol = ConnectionPool.DEFAULT_NAME): Unit = {

    ConnectionPool.close(dbName)

  }

  def closeAll(): Unit = {

    ConnectionPool.closeAll

  }

}

object ConfigDBs extends ConfigDBs

 with TypesafeConfigReader

  with StandardTypesafeConfig

  with HikariConfigReader

case class ConfigDBsWithEnv(envValue: String) extends ConfigDBs

  with TypesafeConfigReader

  with StandardTypesafeConfig

  with HikariConfigReader

  with EnvPrefix {

  override val env = Option(envValue)

}

增加了ConfigDBs对象来替代原来的DBs对象。在ConfigDBs.setup(dbname)实现了HikariCP的调用和配置。ConfigDBsWithEnv可以支持在配置文件中外包一层路径：

dev {

  db {

    h2 {

      driver = "org.h2.Driver"

      url = "jdbc:h2:tcp://localhost/~/slickdemo"

      user = ""

      password = ""

      poolFactoryName = "hikaricp"

      numThreads =

      maxConnections =

      minConnections =

      keepAliveConnection = true

    }

    mysql {

      driver = "com.mysql.jdbc.Driver"

      url = "jdbc:mysql://localhost:3306/testdb"

      user = "root"

      password = ""

      poolInitialSize =

      poolMaxSize =

      poolConnectionTimeoutMillis =

      poolValidationQuery = "select 1 as one"

      poolFactoryName = "bonecp"

    }

  }

  # scallikejdbc Global settings

  scalikejdbc.global.loggingSQLAndTime.enabled = true

  scalikejdbc.global.loggingSQLAndTime.logLevel = info

  scalikejdbc.global.loggingSQLAndTime.warningEnabled = true

  scalikejdbc.global.loggingSQLAndTime.warningThresholdMillis =

  scalikejdbc.global.loggingSQLAndTime.warningLogLevel = warn

  scalikejdbc.global.loggingSQLAndTime.singleLineMode = false

  scalikejdbc.global.loggingSQLAndTime.printUnprocessedStackTrace = false

  scalikejdbc.global.loggingSQLAndTime.stackTraceDepth =

}

好了，下面是增加了HikariCP的测试代码：

import configdbs._

import scalikejdbc._

import org.joda.time._

import scala.util._   //Try

import scalikejdbc.TxBoundary.Try._

object ConfigureDBs extends App {

  ConfigDBsWithEnv("dev").setupAll()

  val dbname = 'h2

  //clear table object

  try {

    sql"""

        drop table members

          """.execute().apply()(NamedAutoSession(dbname))

  }

  catch {

    case _: Throwable =>

  }

  //construct SQL object

  val createSQL: SQL[Nothing,NoExtractor] =SQL("""

    create table members (

      id bigint primary key auto_increment,

      name varchar() not null,

      description varchar(),

      birthday date,

      created_at timestamp not null

    )""")

  //run this SQL

  createSQL.execute().apply()(NamedAutoSession(dbname))   //autoCommit

  //data model

  case class Member(

                     id: Long,

                     name: String,

                     description: Option[String] = None,

                     birthday: Option[LocalDate] = None,

                     createdAt: DateTime)

  def create(name: String, birthday: Option[LocalDate], remarks: Option[String])(implicit session: DBSession): Member = {

    val insertSQL: SQL[Nothing,NoExtractor]  =

      sql"""insert into members (name, birthday, description, created_at)

           values (${name}, ${birthday}, ${remarks}, ${DateTime.now})"""

    val id: Long = insertSQL.updateAndReturnGeneratedKey.apply()

    Member(id, name, remarks, birthday,DateTime.now)

  }

  val users = List(

    ("John",new LocalDate("2008-03-01"),"youngest user"),

    ("Susan",new LocalDate("2000-11-03"),"middle aged user"),

    ("Peter",new LocalDate("1983-01-21"),"oldest user"),

  )

  val result: Try[List[Member]] =

    NamedDB(dbname) localTx { implicit session =>

      Try {

        val members: List[Member] = users.map { person =>

          create(person._1, Some(person._2), Some(person._3))

        }

        members

      }

    }

  result match {

    case Success(mlist) => println(s"batch added members: $mlist")

    case Failure(err) => println(s"${err.getMessage}")

  }

  //data row converter

  val toMember = (rs: WrappedResultSet) => Member(

    id = rs.long("id"),

    name = rs.string("name"),

    description = rs.stringOpt("description"),

    birthday = rs.jodaLocalDateOpt("birthday"),

    createdAt = rs.jodaDateTime("created_at")

  )

  val selectSQL: SQL[Member,HasExtractor] = sql"""select * from members""".map(toMember)

  val members: List[Member] = NamedDB(dbname) readOnly { implicit session =>

    selectSQL.list.apply()

  }

  println(s"all members: $members")

  NamedDB('h2mem).close()

}

运行正常！

下面是本次讨论的示范源代码：

build.sbt

name := "learn-scalikeJDBC"

version := "0.1"

scalaVersion := "2.12.4"

// Scala 2.10, 2.11, 2.12

libraryDependencies ++= Seq(

  "org.scalikejdbc" %% "scalikejdbc"       % "3.1.0",

  "org.scalikejdbc" %% "scalikejdbc-test"   % "3.1.0"   % "test",

  "org.scalikejdbc" %% "scalikejdbc-config"  % "3.1.0",

  "com.h2database"  %  "h2"                % "1.4.196",

  "mysql" % "mysql-connector-java" % "6.0.6",

  "org.postgresql" % "postgresql" % "9.4-1205-jdbc42",

  "commons-dbcp" % "commons-dbcp" % "1.4",

  "org.apache.tomcat" % "tomcat-jdbc" % "9.0.2",

  "com.zaxxer" % "HikariCP" % "2.7.4",

  "com.jolbox" % "bonecp" % "0.8.0.RELEASE",

  "ch.qos.logback"  %  "logback-classic"   % "1.2.3"

)

resource/application.conf

# JDBC settings

test {

  db {

    h2 {

      driver = "org.h2.Driver"

      url = "jdbc:h2:tcp://localhost/~/slickdemo"

      user = ""

      password = ""

      poolInitialSize =

      poolMaxSize =

      poolConnectionTimeoutMillis =

      poolValidationQuery = "select 1 as one"

      poolFactoryName = "commons-dbcp2"

    }

  }

  db.mysql.driver = "com.mysql.jdbc.Driver"

  db.mysql.url = "jdbc:mysql://localhost:3306/testdb"

  db.mysql.user = "root"

  db.mysql.password = ""

  db.mysql.poolInitialSize =

  db.mysql.poolMaxSize =

  db.mysql.poolConnectionTimeoutMillis =

  db.mysql.poolValidationQuery = "select 1 as one"

  db.mysql.poolFactoryName = "bonecp"

  # scallikejdbc Global settings

  scalikejdbc.global.loggingSQLAndTime.enabled = true

  scalikejdbc.global.loggingSQLAndTime.logLevel = info

  scalikejdbc.global.loggingSQLAndTime.warningEnabled = true

  scalikejdbc.global.loggingSQLAndTime.warningThresholdMillis =

  scalikejdbc.global.loggingSQLAndTime.warningLogLevel = warn

  scalikejdbc.global.loggingSQLAndTime.singleLineMode = false

  scalikejdbc.global.loggingSQLAndTime.printUnprocessedStackTrace = false

  scalikejdbc.global.loggingSQLAndTime.stackTraceDepth =

}

dev {

  db {

    h2 {

      driver = "org.h2.Driver"

      url = "jdbc:h2:tcp://localhost/~/slickdemo"

      user = ""

      password = ""

      poolFactoryName = "hikaricp"

      numThreads =

      maxConnections =

      minConnections =

      keepAliveConnection = true

    }

    mysql {

      driver = "com.mysql.jdbc.Driver"

      url = "jdbc:mysql://localhost:3306/testdb"

      user = "root"

      password = ""

      poolInitialSize =

      poolMaxSize =

      poolConnectionTimeoutMillis =

      poolValidationQuery = "select 1 as one"

      poolFactoryName = "bonecp"

    }

  }

  # scallikejdbc Global settings

  scalikejdbc.global.loggingSQLAndTime.enabled = true

  scalikejdbc.global.loggingSQLAndTime.logLevel = info

  scalikejdbc.global.loggingSQLAndTime.warningEnabled = true

  scalikejdbc.global.loggingSQLAndTime.warningThresholdMillis =

  scalikejdbc.global.loggingSQLAndTime.warningLogLevel = warn

  scalikejdbc.global.loggingSQLAndTime.singleLineMode = false

  scalikejdbc.global.loggingSQLAndTime.printUnprocessedStackTrace = false

  scalikejdbc.global.loggingSQLAndTime.stackTraceDepth =

}

HikariConfig.scala

package configdbs

import scala.collection.mutable

import scala.concurrent.duration.Duration

import scala.language.implicitConversions

import com.typesafe.config._

import java.util.concurrent.TimeUnit

import java.util.Properties

import scalikejdbc.config._

import com.typesafe.config.Config

import com.zaxxer.hikari._

import scalikejdbc.ConnectionPoolFactoryRepository

/** Extension methods to make Typesafe Config easier to use */

class ConfigExtensionMethods(val c: Config) extends AnyVal {

  import scala.collection.JavaConverters._

  def getBooleanOr(path: String, default: => Boolean = false) = if(c.hasPath(path)) c.getBoolean(path) else default

  def getIntOr(path: String, default: => Int = ) = if(c.hasPath(path)) c.getInt(path) else default

  def getStringOr(path: String, default: => String = null) = if(c.hasPath(path)) c.getString(path) else default

  def getConfigOr(path: String, default: => Config = ConfigFactory.empty()) = if(c.hasPath(path)) c.getConfig(path) else default

  def getMillisecondsOr(path: String, default: => Long = 0L) = if(c.hasPath(path)) c.getDuration(path, TimeUnit.MILLISECONDS) else default

  def getDurationOr(path: String, default: => Duration = Duration.Zero) =

    if(c.hasPath(path)) Duration(c.getDuration(path, TimeUnit.MILLISECONDS), TimeUnit.MILLISECONDS) else default

  def getPropertiesOr(path: String, default: => Properties = null): Properties =

    if(c.hasPath(path)) new ConfigExtensionMethods(c.getConfig(path)).toProperties else default

  def toProperties: Properties = {

    def toProps(m: mutable.Map[String, ConfigValue]): Properties = {

      val props = new Properties(null)

      m.foreach { case (k, cv) =>

        val v =

          if(cv.valueType() == ConfigValueType.OBJECT) toProps(cv.asInstanceOf[ConfigObject].asScala)

          else if(cv.unwrapped eq null) null

          else cv.unwrapped.toString

        if(v ne null) props.put(k, v)

      }

      props

    }

    toProps(c.root.asScala)

  }

  def getBooleanOpt(path: String): Option[Boolean] = if(c.hasPath(path)) Some(c.getBoolean(path)) else None

  def getIntOpt(path: String): Option[Int] = if(c.hasPath(path)) Some(c.getInt(path)) else None

  def getStringOpt(path: String) = Option(getStringOr(path))

  def getPropertiesOpt(path: String) = Option(getPropertiesOr(path))

}

object ConfigExtensionMethods {

  @inline implicit def configExtensionMethods(c: Config): ConfigExtensionMethods = new ConfigExtensionMethods(c)

}

trait HikariConfigReader extends TypesafeConfigReader {

  self: TypesafeConfig =>      // with TypesafeConfigReader => //NoEnvPrefix =>

  import ConfigExtensionMethods.configExtensionMethods

  def getFactoryName(dbName: Symbol): String = {

    val c: Config = config.getConfig(envPrefix + "db." + dbName.name)

    c.getStringOr("poolFactoryName", ConnectionPoolFactoryRepository.COMMONS_DBCP)

  }

  def hikariCPConfig(dbName: Symbol): HikariConfig = {

    val hconf = new HikariConfig()

    val c: Config = config.getConfig(envPrefix + "db." + dbName.name)

    // Connection settings

    if (c.hasPath("dataSourceClass")) {

      hconf.setDataSourceClassName(c.getString("dataSourceClass"))

    } else {

      Option(c.getStringOr("driverClassName", c.getStringOr("driver"))).map(hconf.setDriverClassName _)

    }

    hconf.setJdbcUrl(c.getStringOr("url", null))

    c.getStringOpt("user").foreach(hconf.setUsername)

    c.getStringOpt("password").foreach(hconf.setPassword)

    c.getPropertiesOpt("properties").foreach(hconf.setDataSourceProperties)

    // Pool configuration

    hconf.setConnectionTimeout(c.getMillisecondsOr("connectionTimeout", ))

    hconf.setValidationTimeout(c.getMillisecondsOr("validationTimeout", ))

    hconf.setIdleTimeout(c.getMillisecondsOr("idleTimeout", ))

    hconf.setMaxLifetime(c.getMillisecondsOr("maxLifetime", ))

    hconf.setLeakDetectionThreshold(c.getMillisecondsOr("leakDetectionThreshold", ))

    hconf.setInitializationFailFast(c.getBooleanOr("initializationFailFast", false))

    c.getStringOpt("connectionTestQuery").foreach(hconf.setConnectionTestQuery)

    c.getStringOpt("connectionInitSql").foreach(hconf.setConnectionInitSql)

    val numThreads = c.getIntOr("numThreads", )

    hconf.setMaximumPoolSize(c.getIntOr("maxConnections", numThreads * ))

    hconf.setMinimumIdle(c.getIntOr("minConnections", numThreads))

    hconf.setPoolName(c.getStringOr("poolName", dbName.name))

    hconf.setRegisterMbeans(c.getBooleanOr("registerMbeans", false))

    // Equivalent of ConnectionPreparer

    hconf.setReadOnly(c.getBooleanOr("readOnly", false))

    c.getStringOpt("isolation").map("TRANSACTION_" + _).foreach(hconf.setTransactionIsolation)

    hconf.setCatalog(c.getStringOr("catalog", null))

    hconf

  }

}

import scalikejdbc._

trait ConfigDBs {

    self: TypesafeConfigReader with TypesafeConfig with HikariConfigReader =>

  def setup(dbName: Symbol = ConnectionPool.DEFAULT_NAME): Unit = {

    getFactoryName(dbName) match {

      case "hikaricp" => {

        val hconf = hikariCPConfig(dbName)

        val hikariCPSource = new HikariDataSource(hconf)

        if (hconf.getDriverClassName != null && hconf.getDriverClassName.trim.nonEmpty) {

          Class.forName(hconf.getDriverClassName)

        }

        ConnectionPool.add(dbName, new DataSourceConnectionPool(hikariCPSource))

      }

      case _ => {

        val JDBCSettings(url, user, password, driver) = readJDBCSettings(dbName)

        val cpSettings = readConnectionPoolSettings(dbName)

        if (driver != null && driver.trim.nonEmpty) {

          Class.forName(driver)

        }

        ConnectionPool.add(dbName, url, user, password, cpSettings)

      }

    }

  }

  def setupAll(): Unit = {

    loadGlobalSettings()

    dbNames.foreach { dbName => setup(Symbol(dbName)) }

  }

  def close(dbName: Symbol = ConnectionPool.DEFAULT_NAME): Unit = {

    ConnectionPool.close(dbName)

  }

  def closeAll(): Unit = {

    ConnectionPool.closeAll

  }

}

object ConfigDBs extends ConfigDBs

 with TypesafeConfigReader

  with StandardTypesafeConfig

  with HikariConfigReader

case class ConfigDBsWithEnv(envValue: String) extends ConfigDBs

  with TypesafeConfigReader

  with StandardTypesafeConfig

  with HikariConfigReader

  with EnvPrefix {

  override val env = Option(envValue)

}

ConfigDBs.scala

import configdbs._

import scalikejdbc._

import org.joda.time._

import scala.util._   //Try

import scalikejdbc.TxBoundary.Try._

object ConfigureDBs extends App {

  ConfigDBsWithEnv("dev").setupAll()

  val dbname = 'mysql

  //clear table object

  try {

    sql"""

        drop table members

          """.execute().apply()(NamedAutoSession(dbname))

  }

  catch {

    case _: Throwable =>

  }

  //construct SQL object

  val createSQL: SQL[Nothing,NoExtractor] =SQL("""

    create table members (

      id bigint primary key auto_increment,

      name varchar() not null,

      description varchar(),

      birthday date,

      created_at timestamp not null

    )""")

  //run this SQL

  createSQL.execute().apply()(NamedAutoSession(dbname))   //autoCommit

  //data model

  case class Member(

                     id: Long,

                     name: String,

                     description: Option[String] = None,

                     birthday: Option[LocalDate] = None,

                     createdAt: DateTime)

  def create(name: String, birthday: Option[LocalDate], remarks: Option[String])(implicit session: DBSession): Member = {

    val insertSQL: SQL[Nothing,NoExtractor]  =

      sql"""insert into members (name, birthday, description, created_at)

           values (${name}, ${birthday}, ${remarks}, ${DateTime.now})"""

    val id: Long = insertSQL.updateAndReturnGeneratedKey.apply()

    Member(id, name, remarks, birthday,DateTime.now)

  }

  val users = List(

    ("John",new LocalDate("2008-03-01"),"youngest user"),

    ("Susan",new LocalDate("2000-11-03"),"middle aged user"),

    ("Peter",new LocalDate("1983-01-21"),"oldest user"),

  )

  val result: Try[List[Member]] =

    NamedDB(dbname) localTx { implicit session =>

      Try {

        val members: List[Member] = users.map { person =>

          create(person._1, Some(person._2), Some(person._3))

        }

        members

      }

    }

  result match {

    case Success(mlist) => println(s"batch added members: $mlist")

    case Failure(err) => println(s"${err.getMessage}")

  }

  //data row converter

  val toMember = (rs: WrappedResultSet) => Member(

    id = rs.long("id"),

    name = rs.string("name"),

    description = rs.stringOpt("description"),

    birthday = rs.jodaLocalDateOpt("birthday"),

    createdAt = rs.jodaDateTime("created_at")

  )

  val selectSQL: SQL[Member,HasExtractor] = sql"""select * from members""".map(toMember)

  val members: List[Member] = NamedDB(dbname) readOnly { implicit session =>

    selectSQL.list.apply()

  }

  println(s"all members: $members")

  NamedDB('h2mem).close()

}

巴特西

SDP（2）：ScalikeJDBC-Connection Pool Configuration

最新文章

热门文章