JDBC方式连接MySQL、Oracle、Hive、Spark等，通用工具类（Scala版）

一、思路

我们知道在使用JDBC方式连接数据库需要以下几个步骤：

1. 注册驱动程序
2. 获得数据库连接
3. 创建Statement对象
4. 向数据库发送SQL命令
5. 处理数据库的返回结果（ResultSet类）

本文在第2步时，将创建的数据库连接放入池中，高效管理；在第5步对返回结果进行处理时，返回Set[Map[String, Object]]。

二、通用工具类代码（Scala版）

import java.sql._

object JDBCUtil {
  private val url = ""
  private val user = ""
  private val password = ""
  private var driverClass=""
  private val threadLocal:ThreadLocal[Connection]=new ThreadLocal

  //注册驱动
  Class.forName(driverClass)

  //获取连接并放入连接池
  private def getConnection() = {
    var connection = threadLocal.get()
    if (connection == null) {
      connection = DriverManager.getConnection(url, user, password)
      threadLocal.set(connection)
    }
    connection
  }

  //关闭连接并释放连接池
  private def closeConnection() {
    val connection = threadLocal.get()
    if (connection != null) {
      connection.close()
    }
    threadLocal.remove()
  }

  def executeQuery(sql: String, database: String*): Set[Map[String, Object]] = {
    var conn: Connection = null
    var stat: Statement = null
    var resultSet: ResultSet = null
    var result = Set[Map[String, Object]]()
    try {
      conn = getConnection()
      stat = conn.createStatement()
      if(database!=null&&database.size>0){
        var useSql="use "
        useSql+=database(0)
        stat.executeUpdate(useSql)
      }
      resultSet = stat.executeQuery(sql)
      val rsm: ResultSetMetaData = resultSet.getMetaData()
      val colNum: Int = rsm.getColumnCount()
      while (resultSet.next()) {
        var map = Map[String, Object]()
        for (i <- 0 to colNum - 1) {
          map += (rsm.getColumnName(i + 1).toUpperCase() -> resultSet.getObject(i + 1))
        }
        result += map
      }
    } catch {
      case e: Exception =>
        throw new Exception(e);
    } finally {
      if (resultSet != null) {
        resultSet.close();
      }
      if (stat != null) {
        stat.close();
      }
      if (conn != null) {
        closeConnection();
      }
    }
    result
  }
}

三、具体实例应用

只需修改通用工具类中的四个变量：url、user、password、driverClass

1. JDBC连接MySQL

url = "jdbc:mysql://ip:port/database"
user = ""
password = ""
driverClass = "com.mysql.jdbc.Driver"

2. JDBC连接Oracle

url = "jdbc:oracle:thin:@ip:port/xe"
user = ""
password = ""
driverClass = "oracle.jdbc.driver.OracleDriver"

3. JDBC连接Spark

url = "jdbc:hive2://ip:port"
user = ""
password = ""
driverClass = "org.apache.hive.jdbc.HiveDriver"

Just enjoy it !