1. 云图网首页
  2. 业界资讯

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=5 
    poolMaxSize=7 
    poolConnectionTimeoutMillis=1000 
    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="123" 
db.mysql.poolInitialSize=5 
db.mysql.poolMaxSize=7 
db.mysql.poolConnectionTimeoutMillis=1000 
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=1000 
scalikejdbc.global.loggingSQLAndTime.warningLogLevel=warn 
scalikejdbc.global.loggingSQLAndTime.singleLineMode=false 
scalikejdbc.global.loggingSQLAndTime.printUnprocessedStackTrace=false 
scalikejdbc.global.loggingSQLAndTime.stackTraceDepth=10

上面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(30) not null, 
      description varchar(1000), 
      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 = 0) = 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", 1000)) 
hconf.setValidationTimeout(c.getMillisecondsOr("validationTimeout", 1000)) 
hconf.setIdleTimeout(c.getMillisecondsOr("idleTimeout", 600000)) 
hconf.setMaxLifetime(c.getMillisecondsOr("maxLifetime", 1800000)) 
hconf.setLeakDetectionThreshold(c.getMillisecondsOr("leakDetectionThreshold", 0)) 
hconf.setInitializationFailFast(c.getBooleanOr("initializationFailFast", false)) 
c.getStringOpt("connectionTestQuery").foreach(hconf.setConnectionTestQuery) 
c.getStringOpt("connectionInitSql").foreach(hconf.setConnectionInitSql) 
val numThreads = c.getIntOr("numThreads", 20) 
hconf.setMaximumPoolSize(c.getIntOr("maxConnections", numThreads * 5)) 
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 = 10 
maxConnections = 12 
minConnections = 4 
keepAliveConnection = true 
} 
mysql { 
driver = "com.mysql.jdbc.Driver" 
url = "jdbc:mysql://localhost:3306/testdb" 
user = "root" 
password = "123" 
poolInitialSize = 5 
poolMaxSize = 7 
poolConnectionTimeoutMillis = 1000 
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 = 1000 
scalikejdbc.global.loggingSQLAndTime.warningLogLevel = warn 
scalikejdbc.global.loggingSQLAndTime.singleLineMode = false 
scalikejdbc.global.loggingSQLAndTime.printUnprocessedStackTrace = false 
scalikejdbc.global.loggingSQLAndTime.stackTraceDepth = 10 
}

好了，下面是增加了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(30) not null, 
description varchar(1000), 
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 = 5 
poolMaxSize = 7 
poolConnectionTimeoutMillis = 1000 
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 = "123" 
db.mysql.poolInitialSize = 5 
db.mysql.poolMaxSize = 7 
db.mysql.poolConnectionTimeoutMillis = 1000 
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 = 1000 
scalikejdbc.global.loggingSQLAndTime.warningLogLevel = warn 
scalikejdbc.global.loggingSQLAndTime.singleLineMode = false 
scalikejdbc.global.loggingSQLAndTime.printUnprocessedStackTrace = false 
scalikejdbc.global.loggingSQLAndTime.stackTraceDepth = 10 
} 
dev { 
db { 
h2 { 
driver = "org.h2.Driver" 
url = "jdbc:h2:tcp://localhost/~/slickdemo" 
user = "" 
password = "" 
poolFactoryName = "hikaricp" 
numThreads = 10 
maxConnections = 12 
minConnections = 4 
keepAliveConnection = true 
} 
mysql { 
driver = "com.mysql.jdbc.Driver" 
url = "jdbc:mysql://localhost:3306/testdb" 
user = "root" 
password = "123" 
poolInitialSize = 5 
poolMaxSize = 7 
poolConnectionTimeoutMillis = 1000 
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 = 1000 
scalikejdbc.global.loggingSQLAndTime.warningLogLevel = warn 
scalikejdbc.global.loggingSQLAndTime.singleLineMode = false 
scalikejdbc.global.loggingSQLAndTime.printUnprocessedStackTrace = false 
scalikejdbc.global.loggingSQLAndTime.stackTraceDepth = 10 
}

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 = 0) = 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", 1000)) 
hconf.setValidationTimeout(c.getMillisecondsOr("validationTimeout", 1000)) 
hconf.setIdleTimeout(c.getMillisecondsOr("idleTimeout", 600000)) 
hconf.setMaxLifetime(c.getMillisecondsOr("maxLifetime", 1800000)) 
hconf.setLeakDetectionThreshold(c.getMillisecondsOr("leakDetectionThreshold", 0)) 
hconf.setInitializationFailFast(c.getBooleanOr("initializationFailFast", false)) 
c.getStringOpt("connectionTestQuery").foreach(hconf.setConnectionTestQuery) 
c.getStringOpt("connectionInitSql").foreach(hconf.setConnectionInitSql) 
val numThreads = c.getIntOr("numThreads", 20) 
hconf.setMaximumPoolSize(c.getIntOr("maxConnections", numThreads * 5)) 
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(30) not null, 
description varchar(1000), 
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() 
 
}

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

原创文章，作者：奋斗，如若转载，请注明出处：https://blog.ytso.com/12815.html

赞 (0)
0
上一篇 2021年7月19日
下一篇 2021年7月19日

相关推荐

发表回复

登录后才能评论