com.dounine.dbutil.DBUtil.scala Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of db-util Show documentation
scala version db-util
The newest version!
package com.dounine.dbutil

import java.sql
import java.sql._
import java.time.{LocalDate, LocalDateTime, LocalTime, ZoneId}
import java.util.Date

import scala.collection.mutable.ListBuffer
import scala.collection.{Map, mutable}


class DBUtil(url: String = "") {

  private var connection: Connection = _
  private val driverName = "com.mysql.jdbc.Driver"

  init()

  def init(): Unit = {
    if (connection == null || connection.isClosed) {
      Class.forName(driverName)
      if (url != "") {
        connection = DriverManager.getConnection(url)
      }
    }
  }

  def close(): Unit = {
    if (!connection.isClosed) {
      connection.close()
    }
  }

  def execute(sql: String, params: mutable.ListBuffer[Any]): Unit = {
    try {
      val statement = connection.prepareStatement(sql)
      this.fillStatement(statement, params)
      statement.executeUpdate
    } catch {
      case e: SQLException =>
        e.printStackTrace()
    }
  }

  private def fillStatement(statement: PreparedStatement, params: mutable.ListBuffer[Any]): Unit = {
    params
      .indices
      .map(_ + 1)
      .foreach(index => {
        params(index - 1) match {
          case value: java.lang.String => statement.setString(index, value.toString)
          case value: Integer => statement.setInt(index, value.asInstanceOf[Int])
          case value: Long => statement.setLong(index, value.asInstanceOf[Long])
          case value: Short => statement.setShort(index, value.asInstanceOf[Short])
          case value: Blob => statement.setBlob(index, value.asInstanceOf[Blob])
          case value: Boolean => statement.setBoolean(index, value.asInstanceOf[Boolean])
          case value: LocalDate =>
            statement.setDate(
              index,
              new sql.Date(Date.from(value.asInstanceOf[LocalDate].atStartOfDay().atZone(ZoneId.systemDefault()).toInstant).getTime)
            )
          case value: LocalDateTime => statement.setTimestamp(index, Timestamp.valueOf(value.asInstanceOf[LocalDateTime]))
          case value: LocalTime => statement.setTime(index, Time.valueOf(value.asInstanceOf[LocalTime]))
          case value: java.math.BigDecimal => statement.setBigDecimal(index, value.asInstanceOf[java.math.BigDecimal])
          case value: Double => statement.setDouble(index, value.asInstanceOf[Double])
          case value: Float => statement.setFloat(index, value.asInstanceOf[Float])
          case default@_ => throw new Exception(default.getClass.getName + " 类型不支持、请检查.")
        }
      })
  }

  def upsert(conn: Connection, query: Map[String, Any], update: Map[String, Any], tableName: String, overrides: Boolean): Int = {
    val names = mutable.ListBuffer[String]()
    val values = mutable.ListBuffer[String]()
    val params = mutable.ListBuffer[Any]()
    val updates = ListBuffer[AnyRef]()

    val keysArr = scala.Array(query.keys, update.keys)
    val valuesArr = scala.Array(update.values)

    keysArr.indices.foreach(i => {
      val item = keysArr(i)
      item.foreach {
        key => {
          names += s"`$key`"
          values += "?"
        }
      }
      i match {
        case 0 =>
          params ++= query.values
        case 1 | 2 =>
          params ++= valuesArr(i - 1).toList
      }
    })
    if (overrides) {
      update.foreach {
        item => {
          updates += s" `${item._1}` = ? "
          params += item._2
        }
      }
    } else {
      update.foreach {
        item => {
          updates += s" `${item._1}` = `${item._1}` + ? "
          params += item._2
        }
      }
    }

    val sql = s"INSERT INTO `$tableName` (${names.mkString(",")}) VALUES(${values.mkString(",")}) ON DUPLICATE KEY UPDATE ${updates.mkString(",")}"

    val statement = conn.prepareStatement(sql)
    fillStatement(statement, params)
    statement.executeUpdate
  }

  def upsert(query: Map[String, Any], update: Map[String, Any], tableName: String, overrides: Boolean): Int = {
    upsert(connection, query, update, tableName, overrides)
  }

  def list(sql: String, fields: scala.Seq[String]): Seq[Seq[String]] = {
    val rs = query(sql)
    var list = scala.collection.mutable.ArrayBuffer[Seq[String]]()
    while (rs.next()) {
      list += fields.map(rs.getString)
    }
    list
  }


  def exist(sql: String): Boolean = {
    val rs = query(sql)
    if (rs.next()) {
      true
    } else false
  }

  def query(sql: String): ResultSet = {
    connection.createStatement().executeQuery(sql)
  }

  def prepareStatement(sql: String): PreparedStatement = {
    connection.prepareStatement(sql)
  }
}