Scala Statement类代码示例

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

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

import scala.collection.immutable.IndexedSeq

case class DBAccessWorker(connection: Connection, sql: String) {

  private def execute(execSql: String) = {
    val stmt: Statement = connection.createStatement
    val rs: ResultSet = stmt.executeQuery(execSql)
    val columnCnt: Int = rs.getMetaData.getColumnCount

    val columns: IndexedSeq[String] = 1 to columnCnt map rs.getMetaData.getColumnName
    val results: Iterator[IndexedSeq[String]] = Iterator.continually(rs).takeWhile(_.next()).map{ rs =>
      columns map rs.getString
    }

    (columns, results)
  }

  def execute(): SqlResults = {
    val (d1, d2) = execute(sql)
    SqlResults(d1, d2)
  }

  def executeStatistics(): Statistics = {
    val (d1, d2) = execute(s"EXPLAIN $sql")
    Statistics(d1, d2)
  }
}

case class SqlResults(columns: IndexedSeq[String], results: Iterator[IndexedSeq[String]])

case class Statistics(columns: IndexedSeq[String], results: Iterator[IndexedSeq[String]]) 

//设置package包名称以及导入依赖的类
package us.illyohs.bansdb.util

import javax.sql.DataSource
import java.sql.Connection
import java.sql.SQLException
import java.sql.Statement
import java.util.UUID

import org.spongepowered.api.Sponge
import org.spongepowered.api.service.sql.SqlService
import us.illyohs.bansdb.util.ConfigUtil._


object DBUtil {
  private var sqlServ: SqlService = null
  private var ds: DataSource = null

  def execute(execute: String): Unit = {
    sqlServ = Sponge.getServiceManager.provide(classOf[SqlService]).get()
    val URL: String = "jdbc:mysql://" + getURL + ":" + getPort + "/" + "?user=" + getUsername + "&password=" + getPass
    try {
      ds = sqlServ.getDataSource(URL)
      val connection: Connection = ds.getConnection
      val statement: Statement = connection.createStatement
      statement.execute(execute)
      statement.close
      connection.close
    }
    catch {
      case e: SQLException => e.printStackTrace
    }
  }

  def init: Unit = {
    execute("CREATE TABLE IF NOT EXISTS WARN_DB (warnee TEXT, sender TEXT, warnings SMALLINT, reason TEXT)")
    execute("CREATE TABLE IF NOT EXISTS BAN_DB (banee TEXT, sender TEXT, reason)")
  }


  def getWarning: Int = {
    null
  }

  def isPlayerBanned(uUID: UUID): Boolean = {
    return false
  }

  def isPlayerMuted(): Boolean = {
    return false
  }
} 

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

import java.sql.{Connection, ResultSet, SQLException, Statement}

object Helpers {

  def getCurrentTimeStamp = {
		val today = new java.util.Date()
		new java.sql.Timestamp(today.getTime())
	}

  def printSQLException(e: SQLException) {
    val msg: String =
      "SQL State: " + e.getSQLState + "\n" +
      "Message: " + e.getMessage + "\n\n"
    println(msg)
  }

  def executeSQL(sql: String, stmt: Statement, ignore: Set[String] = Set()) {
    try stmt.execute(sql)
    catch {
      case e: SQLException =>
        if(ignore(e.getSQLState())) println("SQLException ignored")
        else printSQLException(e)
    }
  }

  
  def createTables(sql: String*)(implicit conn: Connection) {
    sql foreach(s => executeSQL(s, conn.createStatement, Set("X0Y32")))
  }

  def dropTables(tables: String*)(implicit conn: Connection) {
    tables foreach(table => executeSQL("DROP TABLE " + table, conn.createStatement))
  }


  trait ResultSetToList {
    def toList[T](retrieve: ResultSet => T): List[T]
  }
  implicit def wrapResultSet(rs: ResultSet) = new ResultSetToList {
    def toList[T](retrieve: ResultSet => T): List[T] =
      new Iterator[T] {
        def hasNext = rs.next()
        def next() = retrieve(rs)
      }
      .toList
  }

} 

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

import models.{Types, User}

import java.sql.{Connection, PreparedStatement, ResultSet, SQLException, Statement}

object UserQuery {
  def create(
    connection : Connection,
    main_email : String,
    password : String,
    firstname : String,
    lastname : String,
    avatar : String
  ) : Types.userid = {
    val statement : PreparedStatement =
      connection.prepareStatement(
        "INSERT INTO users.user (" +
          "main_email, " +
          "password, " +
          "firstname, " +
          "lastname, " +
          "avatar) " +
          "VALUES(?, ?, ?, ?, ?)",
        Statement.RETURN_GENERATED_KEYS
      )

    statement.setString(0, main_email)
    statement.setString(1, password)
    statement.setString(2, firstname)
    statement.setString(3, lastname)
    statement.setString(4, avatar)

    val generatedKeys : ResultSet = statement.getGeneratedKeys()
    if (generatedKeys.next()) {
      generatedKeys.getLong(1)
    }
    else {
      throw new SQLException("Adding ad failed. No ID obtained.")
    }
  }
} 

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

import java.sql.Connection
import java.sql.ResultSet
import java.sql.SQLException
import java.sql.Statement
import com.boost.bigdata.utils.log.LogSupport

class Session(connection: Connection) extends LogSupport{

  private var conn: Connection = connection
  private var stmt: Statement = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)

  def executeQuery(sql: String): ResultSet = {
    var rs: ResultSet = null
    try {
      if (stmt != null) {
        rs = stmt.executeQuery(sql)
      }
    }
    catch {
      case e: SQLException => {
        log.warn(e.getMessage)
      }
    }
    rs
  }

  def executeUpdate(sql: String): Boolean = {
    var ret: Boolean = false
    try {
      if (stmt != null) {
        stmt.executeUpdate(sql)
        ret = true
      }
    }
    catch {
      case e: SQLException => {
        log.warn(e.getMessage)
      }
    }
    ret
  }

  def disconnect {
    MySql.closeStatement(stmt)
    stmt = null
    MySql.closeConnection(conn)
    conn = null
  }
}

object Session{
  def apply(con: Connection) = new Session(con)
}