Scala DriverManager类代码示例

//设置package包名称以及导入依赖的类
import java.sql.{Driver, DriverManager}

import org.scalatest.BeforeAndAfter
import org.scalatestplus.play.PlaySpec
import play.api.Logger
import scalikejdbc._
import scalikejdbc.config.DBs

import scala.collection.mutable.Stack

class DatabaseSpec extends PlaySpec with BeforeAndAfter {
  implicit var session = AutoSession

  before {
    DBs.setupAll()
  }

  after {
    DBs.closeAll()
  }

  "Database" must {
    "List of database drivers" in {
      val drivers = DriverManager.getDrivers

      while (drivers.hasMoreElements) {
        val driver:Driver = drivers.nextElement()
        Logger.info(driver.toString)
      }
    }
    "Fetch users" in {
      val sql: SQL[Nothing, NoExtractor] = sql"select * from users"
      val map: SQL[Map[String, Any], HasExtractor] = sql.toMap
      val list: SQLToList[Map[String, Any], HasExtractor] = map.list
      val users: List[Map[String, Any]] = list.apply()
    }
  }

  "Slick" must {
    "How to use Scala test" in {
      // TODO
    }

    "throw NoSuchElementException if an empty stack is popped" in {
      val emptyStack = new Stack[Int]
      a [NoSuchElementException] must be thrownBy {
        emptyStack.pop()
      }
    }
  }
} 

//设置package包名称以及导入依赖的类
package com.unity.analytics.spark.utils.parquet

import java.sql.DriverManager

import org.apache.spark.Logging
import org.json4s.jackson.Serialization


case class BackupMetadata(
                           backupId: String,
                           backupEntries: Array[BackupEntry]
                         )

case class BackupEntry(
                        srcDir: String,
                        destDir: String,
                        srcNumFiles: Int = 1,
                        destNumFiles: Int = 1
                      )

object BackupMetadata extends Logging {
  val tableName = "backup_metadata"
  implicit val formats = org.json4s.DefaultFormats

  def write(backupId: String, backupEntries: Array[BackupEntry], jdbcConfig: Map[String, String]): Unit = {
    val connection = DriverManager.getConnection(jdbcConfig.get("url").get)
    val backupEntriesJSON = Serialization.write[Array[BackupEntry]](backupEntries)
    val sql = s"""INSERT INTO ${BackupMetadata.tableName} (id, entries) VALUES ('$backupId', '$backupEntriesJSON') ON DUPLICATE KEY UPDATE entries = '$backupEntriesJSON'"""

    try {
      connection.prepareStatement(sql).execute()
    }
    finally {
      connection.close()
    }
  }

  def read(backupId: String, jdbcConfig: Map[String, String]): Option[BackupMetadata] = {
    //Read from MySQL
    val connection = DriverManager.getConnection(jdbcConfig.get("url").get)
    val sql = s"SELECT * FROM $tableName WHERE id = '$backupId'"
    try {
      val results = connection.prepareStatement(sql).executeQuery()
      while (results.next()) {
        val backupEntriesJSON = results.getString("entries")
        val backupEntries = Serialization.read[Array[BackupEntry]](backupEntriesJSON)
        return Some(BackupMetadata(backupId, backupEntries))
      }
    }
    catch {
      case e: Exception => {
        logError(s"Error loading backup BackupMetadata $backupId - ${e.getMessage}")
      }
    }
    finally {
      connection.close()
    }
    None
  }
} 

//设置package包名称以及导入依赖的类
package com.unity.analytics.spark.utils.parquet

import java.io.{File, StringReader}
import java.sql.DriverManager

import org.apache.commons.io.FileUtils
import org.h2.tools.{RunScript, Server}

class BackupMetadataTest extends BaseTest {

  val dbDriver = "org.h2.Driver"
  val server = Server.createTcpServer("-tcpPort", "9999")
  val jdbcDB = "dummydb"
  val jdbcUrl = s"jdbc:h2:mem:$jdbcDB;DATABASE_TO_UPPER=FALSE;MODE=MYSQL"
  val jdbcConfig = Map(
    "url" -> jdbcUrl
  )
  val backendConnection = DriverManager.getConnection(jdbcUrl)

  val backupId = "dummyBackupId"

  override def beforeAll(): Unit = {
    super.beforeAll()
    println("INITIALIZE H2")
    // Initialize H2
    val reader = new StringReader(FileUtils.readFileToString(new File("src/test/scala/resources/backup_metadata.sql")).replace('`', '"'))
    RunScript.execute(backendConnection, reader)
  }

  test("Test reading and writing backup metadata") {
    val entries = Array(
      BackupEntry("hdfs://hello", "s3a://hello", 100, 10),
      BackupEntry("/source", "/destination", 200, 2)
    ).sortBy(_.destDir)
    BackupMetadata.write(backupId, entries, jdbcConfig)
    val metadata = BackupMetadata.read(backupId, jdbcConfig).get
    assert(metadata.backupId === backupId)
    assert(metadata.backupEntries.length === entries.length)
    val output = metadata.backupEntries.sortBy(_.destDir)
    entries.zip(output).foreach(e => {
      assert(e._1 === e._2)
    })
  }
} 

//设置package包名称以及导入依赖的类
package com.dao

import java.sql.{Connection, DriverManager, SQLException}


object OracleUtil {
  val driverClass = "oracle.jdbc.driver.OracleDriver"
  val jdbcUrl = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.30.12.26)(PORT=1521))(FAILOVER=ON)(LOAD_BALANCE=ON))(CONNECT_DATA= (SERVICE_NAME=msds)))"
  val username = "acctpaymertest1"
  val password = "acctpaymer*1"

  try {
    Class.forName(driverClass)
  } catch {
    case e: ClassNotFoundException => throw e
    case e: Exception => throw e
  }

  @throws(classOf[SQLException])
  def getConnection: Connection = {
    DriverManager.getConnection(jdbcUrl, username, password)
  }

  @throws(classOf[SQLException])
  def doTrancation(trancations: Set[String]): Unit = {
    val connection = getConnection
    connection.setAutoCommit(false)
    trancations.foreach(connection.createStatement().execute(_))
    connection.commit
    connection.close
  }
} 

//设置package包名称以及导入依赖的类
package pl.gigiel.vocabulary.tool

import java.sql.DriverManager
import java.sql.Connection
import org.h2.tools.Server

object SimpleApp extends App {
  //val fileName = "E:\\_tgl\\workspace-tomaszacer\\vocabulary-tool-confidential\\resources\\jack-london-white-fang.txt"
  //val all = words(fileName)
  //println(all.length)
  //println(all)

  db
  println("ok")

  def words(fileName: String): List[String] = {
    val source = scala.io.Source.fromFile(fileName)
    val wordPattern = """([A-Za-z\-'])+""".r
    val words = try source.getLines.flatMap(wordPattern.findAllIn).map(_.toLowerCase).toList.distinct.sorted finally source.close()

    return words
  }

  def db: Unit = {
    val driver = "org.h2.Driver"
    val url = "jdbc:h2:mem:vocabulary-db;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false"
    val username = "vocabulary-username"
    val password = "vocabulary-password"

    var server: Server = null
    var connection: Connection = null

    try {
      Class.forName(driver) // unnecessary in jdbc 4+
      server = Server.createTcpServer("-tcpPort", "9123", "-tcpAllowOthers").start
      // jdbc:h2:tcp://localhost:9123/mem:vocabulary-db;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false
      connection = DriverManager.getConnection(url, username, password)

      val stmt = connection.createStatement()
      stmt.execute("create table infos(id int primary key, info varchar(255))")
      stmt.execute("insert into infos(id, info) values(1, 'abc')")
      val rs = stmt.executeQuery("select id, info from infos")
      while (rs.next()) {
        val id = rs.getString("id")
        val info = rs.getString("info")
        println("id, info = " + id + ", " + info)
      }
      io.StdIn.readInt
    } catch {
      case e: Throwable => e.printStackTrace
    } finally {
      connection.close
      server.stop
    }
  }
} 

//设置package包名称以及导入依赖的类
package com.example.db


import java.sql.{Connection, DriverManager}

import com.twitter.util.Future
import org.postgresql.util.HostSpec


object DB {
  val url = "jdbc:postgresql://localhost:5432/postgres?user=Sergey&password=root"
  val connection = DriverManager.getConnection(url)

  def isValidToken(token: String) = {
    val statement = connection.prepareStatement("SELECT access_token FROM user_access WHERE access_token = ?")
    statement.setString(1, token)
    Future {
      statement.executeQuery()
    } map { resultSet =>
      resultSet.next()
    }
  }

  def createToken(userId: BigInt): Future[String] = {
    val token = java.util.UUID.randomUUID().toString
    val statement = connection.prepareStatement("INSERT INTO user_access VALUES (?, ?)")
    statement.setBigDecimal(2, BigDecimal(userId).bigDecimal)
    statement.setString(1, token)
    Future {
      statement.executeUpdate()
    } map {
      case 1 => token
      case _ => ""
    }
  }
} 

//设置package包名称以及导入依赖的类
package db.core

import java.sql.DriverManager
import db.config._
import java.sql.Connection
import snaq.db.ConnectionPool

case class DB(c:TraitDBConfig) {
  
  lazy val pool = new ConnectionPool("poolname", 1, 200, 300, 180000, c.url, c.dbuser, c.dbpass) // pool parameters hardcoded for now
  var connection:Connection = _
  def getConnection = if (c.usePool) pool.getConnection(3000) else {
    if (connection == null || !connection.isValid(3000)) {
      connection = DriverManager.getConnection(c.url, c.dbuser, c.dbpass)      
    }
    connection  
  }
} 

//设置package包名称以及导入依赖的类
package com.datawizards.dbtable2class.dialects

import java.sql.{DriverManager, ResultSet}
import org.apache.log4j.Logger
import com.datawizards.dbtable2class.model.ColumnMetadata
import scala.collection.mutable.ListBuffer

trait Dialect {
  protected val log: Logger = Logger.getLogger(getClass.getName)

  def mapColumnTypeToScalaType(column: ColumnMetadata): String

  def extractTableColumns(dbUrl: String, connectionProperties: java.util.Properties, database: String, schema: String, table: String): Iterable[ColumnMetadata] = {
    Class.forName(driverClassName)
    val connection = DriverManager.getConnection(dbUrl, connectionProperties)
    val query = extractTableColumnsQuery(database, schema, table)
    val rs = connection.createStatement().executeQuery(query)
    val buffer = new ListBuffer[ColumnMetadata]
    while(rs.next()) {
      buffer += extractColumnMetadata(rs)
    }
    connection.close()

    val extractedColumns = buffer.toList
    if(extractedColumns.size == 0){
      throw new RuntimeException(s"No columns found for specified table ${database}.${schema}.${table}. Please check if table is correct.")
    }
    extractedColumns
  }

  protected def extractColumnMetadata(rs: ResultSet): ColumnMetadata =
    ColumnMetadata(
      columnName = rs.getString(columnWithColumnName),
      typeName = rs.getString(columnWithTypeName)
    )

  protected def driverClassName: String
  protected def extractTableColumnsQuery(database: String, schema: String, table: String): String
  protected def columnWithColumnName: String
  protected def columnWithTypeName: String

} 

//设置package包名称以及导入依赖的类
package domain

import java.sql.DriverManager

import adapter.dto.ConnectDBDto

case class DBConnection(driver: String, url: String) {
  def getConnection = {
    Class.forName(driver).newInstance()
    DriverManager.getConnection(url)
  }
}

object DBConnection {
  def apply(dto: ConnectDBDto): DBConnection = dto.dbName match {
    case "MySQL" =>
      DBConnection(
        "com.mysql.jdbc.Driver",
        s"jdbc:mysql://${dto.host}:${dto.port}/?user=${dto.user}&password=${dto.pass.getOrElse("")}"
      )
    case _ => throw new Exception
  }

  val CACHE_KEY = "db.connection"

} 

//设置package包名称以及导入依赖的类
package com.aronia.core

import java.sql.{Connection, DriverManager}

import com.typesafe.scalalogging.LazyLogging
import com.aronia.core.AroniaUtils._
import com.aronia.core.spiders.GithubStargazers
import us.codecraft.webmagic.Spider

import scala.collection.mutable.ArrayBuffer


private[aronia] class AroniaContext extends LazyLogging {

  var spiders = new ArrayBuffer[Spider]
  val config = new AroniaConfig(true)
  private val defaultGlobalConfig = "ads_env.conf"

  // read environment variables from external file
  val gitConfig = {
    val configFile = config.get(config.ADS_ENV_CONFIG, null)
    if (configFile == null) {
      logger.info(s"Using default ads configurations ($defaultGlobalConfig)")
      loadKeyValueResource(defaultGlobalConfig)
    } else {
      logger.info(s"Using user-specified configuration file ($configFile)")
      loadKeyValueFile(configFile)
    }
  }

  config.setAll(gitConfig)
  // TODO: determine the priority of jvm and config file

  // global mysql backend
  classOf[com.mysql.jdbc.Driver]
  private val host = config.get(config.MYSQL_HOST, "127.0.0.1")
  private val port = config.get(config.MYSQL_PORT, "3306")
  private val username = config.get(config.MYSQL_USERNAME, "root")
  private val passwd = config.get(config.MYSQL_PASSWORD, "password")
  private val jdbcAddr = s"jdbc:mysql://$host:$port?user=$username&password=$passwd"

  logger.info(s"Connecting to $jdbcAddr")
  val mysqlBackend: Connection = DriverManager.getConnection(jdbcAddr)

  // configure all spiders
  private val githubStarSpider = (new GithubStargazers)
    .createSpider(this, Array(config.GITHUB_AWESOME_REPO), 10)
  spiders += githubStarSpider

  def go(): Unit = {
    logger.info("Running all spiders")
    spiders.foreach(_.run)
  }

  def clear(): Unit = {
    if (mysqlBackend != null)
      mysqlBackend.close()
  }

} 

//设置package包名称以及导入依赖的类
import java.sql.DriverManager
import java.sql.Connection

object JdbcSqlite {
  def main(args: Array[String]) {
    var c: Connection = null
    try {
      Class.forName("org.sqlite.JDBC")
      c = DriverManager.getConnection("jdbc:sqlite:planets.sqlite")
    } catch {
      case e: Throwable => e.printStackTrace
    }
    c.close()
  }
} 

//设置package包名称以及导入依赖的类
package io.sbelkin.jdbc.csvresolution


import java.sql.{Connection, DriverManager}
object Csv {

  var connection:Connection = null
  var path: String = null

  def main(path:String) : Unit = {
    setPath(path)
    select()
  }

  def setPath(path:String): Unit ={
    this.path = path
  }

  def connectionStart() : Unit = {
    if (path.length == 0) {
      throw new IllegalStateException("Must have a path for CSV Jdbc set.")
    } else {
      println("Using following path %s, as the data source.".format(path))
    }
    val driver:String = "org.relique.jdbc.csv.CsvDriver"
    Class.forName(driver)
    connection = DriverManager.getConnection("jdbc:relique:csv:" + path)
  }

  def connectionEnd() : Unit = {
    connection.close()
  }

  def select() : Unit = {
    connectionStart()
    try {
      val statement = connection.createStatement()
      val resultSet = statement.executeQuery("SELECT id,name FROM test")
      while ( resultSet.next() ) {
        val id = resultSet.getString("id")
        val name = resultSet.getString("name")
        println("id, name = " + id + ", " + name)
      }
    } catch {
      case e => e.printStackTrace
    } finally {
      connectionEnd()
    }
  }
} 

//设置package包名称以及导入依赖的类
package io.sbelkin.jdbc.csvresolution

import java.sql.{Connection, DriverManager}

import com.typesafe.config.Config


object Mysql {

  var connection:Connection = null
  var config:Config = null

  def main(config: Config): Unit = {
    setConfig(config);
    select()
  }

  def setConfig(config: Config) : Unit = {
    this.config = config;
  }

  def connectionStart() : Unit = {
    Class.forName(config.getString("database.driverClass"))
    val url = config.getString("database.url") +"/"+ config.getString("database.database")
    connection = DriverManager.getConnection(url, config.getString("database.user"), config.getString("database.password"))
  }
  def connectionClose() : Unit = {
    connection.close()
  }

  def select(): Unit =  {
    connectionStart()
    try {
      val statement = connection.createStatement()
      val resultSet = statement.executeQuery("SELECT host, user FROM user")
      while ( resultSet.next() ) {
        val host = resultSet.getString("host")
        val user = resultSet.getString("user")
        println("host, user = " + host + ", " + user)
      }
    } catch {
      case e => e.printStackTrace
    } finally {
      connectionClose()
    }
  }
} 

//设置package包名称以及导入依赖的类
package petclinic

import cats.data.EitherT
import com.typesafe.config.ConfigFactory
import scala.concurrent.{ ExecutionContext, Future }
import scala.util.{ Failure, Success, Try }
import scala.util.control.NonFatal
import scala.sys.process._
import java.sql.{ Connection, DriverManager }

package object mysql {

  final val Config = ConfigFactory.load()

  final val Ip =
    Try("docker-machine ip".!!).orElse(Try(Config.getString("mysql.ip"))).getOrElse("localhost")
  final val Driver   = "com.mysql.jdbc.Driver"
  final val Url      = s"jdbc:mysql://$Ip/petclinic"
  final val username = Try(Config.getString("mysql.username")).getOrElse("root")
  final val password = Try(Config.getString("mysql.password")).getOrElse("root")

  def withConnection[A](f: Connection => Either[PetClinicError, A])(
      implicit ec: ExecutionContext): Response[A] =
    EitherT(Future {
      var connection: Connection = null
      val comp =
        try {
          connection = DriverManager.getConnection(Url, username, password)
          Success(f(connection))
        } catch {
          case NonFatal(e) => Failure(e)
        } finally {
          if (connection != null && !connection.isClosed)
            connection.close()
        }
      comp.get
    })
} 

//设置package包名称以及导入依赖的类
package io.eels.component.jdbc

import java.sql.{Connection, DriverManager, ResultSet}

import com.sksamuel.exts.Logging
import io.eels.component.jdbc.dialect.JdbcDialect
import io.eels.schema.StructType

trait JdbcPrimitives extends Logging {

  def connect(url: String): Connection = {
    logger.debug(s"Connecting to jdbc source $url...")
    val conn = DriverManager.getConnection(url)
    logger.debug(s"Connected to $url")
    conn
  }

  def schemaFor(dialect: JdbcDialect, rs: ResultSet): StructType = {
    val schema = JdbcSchemaFns.fromJdbcResultset(rs, dialect)
    logger.trace("Fetched schema:\n" + schema.show())
    schema
  }
}