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() doesnt 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
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()
"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
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()
相关文章
- java实现向服务端发送GET和POST请求详解编程语言
- Spring与多线程详解编程语言
- springMVC笔记二详解编程语言
- [javaSE] IO流(FIle对象递归文件列表)详解编程语言
- Ext Connection详解编程语言
- 解决telnet无法连接 Connection refused详解编程语言
- 一个div如何随着鼠标的移动而移动详解编程语言
- 算法-数组中出现次数超过一半的数字详解编程语言
- 算法-时间复杂度为O(n)的排序详解编程语言
- Json,String,Map之间的转换详解编程语言
- 为ExecutorService增加shutdown hook详解编程语言
- JSONP跨域请求详解编程语言
- Java 虚拟机类加载器详解编程语言
- CSS 分组和嵌套选择器详解编程语言
- Python3:input()输入函数的用法详解编程语言
- php递归详解编程语言
- Python 字符串详解编程语言
- 有关HikariPool-1 – Failed to validate connection com.mysql.cj.jdbc.ConnectionImp 错误的产生原因与解决方法详解编程语言
- SY-REPID 和 SY-CPROG使用详解及其区别编程语言
- 七大排序的Java实现(插入+希尔+冒泡+快速+选择+堆+归并)详解编程语言
- Scan program for authority-check详解编程语言
- ABAP抓取异常的方法:try,endtry详解编程语言
- python工具——basemap使用一基本使用详解编程语言