Scala SQLException类代码示例

//设置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 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 services.database

import java.sql.{ResultSet, SQLException}


  def objectOfResultSet[A] (resultSet : ResultSet, toObject : (ResultSet ? A)) : A =
  {
    if (resultSet.next())
      toObject(resultSet)
    else
    {
      throw new SQLException("objectOfResultSet: no result found.")
    }
  }
} 

//设置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)
} 

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

import java.sql.SQLException

import com.malliina.logstreams.auth.UserError.{AlreadyExists, DoesNotExist}
import com.malliina.logstreams.auth.UserService
import com.malliina.logstreams.db.Mappings.{password, username}
import com.malliina.play.auth.BasicCredentials
import com.malliina.play.models.{Password, Username}
import org.apache.commons.codec.digest.DigestUtils
import slick.jdbc.H2Profile.api._

import scala.concurrent.Future

object DatabaseAuth {
  def apply(userDB: UserDB): DatabaseAuth = new DatabaseAuth(userDB)

  def hash(username: Username, password: Password): Password =
    Password(DigestUtils.md5Hex(username.name + ":" + password.pass))
}

class DatabaseAuth(db: UserDB) extends UserService {
  import UserDB.users
  implicit val ec = db.ec

  override def add(creds: BasicCredentials): Future[Either[AlreadyExists, Unit]] = {
    db.run(users += DataUser(creds.username, hash(creds))).map(_ => Right(())) recover {
      case sqle: SQLException if sqle.getMessage contains "primary key violation" =>
        Left(AlreadyExists(creds.username))
    }
  }

  override def update(creds: BasicCredentials): Future[Either[DoesNotExist, Unit]] =
    withUser(creds.username)(_.map(_.passHash).update(hash(creds)))

  override def remove(user: Username): Future[Either[DoesNotExist, Unit]] =
    withUser(user)(_.delete)

  override def isValid(creds: BasicCredentials): Future[Boolean] =
    db.run(userQuery(creds.username).filter(_.passHash === hash(creds)).exists.result)

  override def all(): Future[Seq[Username]] = db.runQuery(users.map(_.user))

  private def withUser[T](user: Username)(code: Query[Users, DataUser, Seq] => DBIOAction[Int, NoStream, _]) =
    db.run(code(userQuery(user))).map(c => analyzeRowCount(c, user))

  private def userQuery(user: Username): Query[Users, DataUser, Seq] = users.filter(_.user === user)

  private def analyzeRowCount(rowCount: Int, username: Username): Either[DoesNotExist, Unit] =
    if (rowCount == 0) Left(DoesNotExist(username)) else Right(())

  private def hash(creds: BasicCredentials) = DatabaseAuth.hash(creds.username, creds.password)
} 

//设置package包名称以及导入依赖的类
package yangbajing.utils.s.persist

import java.sql.{Connection, SQLException}


object SQLUtils {

  def insert(sql: String, value: Seq[Any])(implicit conn: Connection): Int = {
    val isAutoCommit = conn.getAutoCommit
    try {
      conn.setAutoCommit(false)
      val pstmt = conn.prepareStatement(sql)
      value.zipWithIndex.foreach { case (v, idx) =>
        println(idx + 1 + " " + v)
        pstmt.setObject(idx + 1, v)
      }
      val ret = pstmt.executeUpdate()
      conn.commit()
      ret
    } catch {
      case e: SQLException =>
        conn.rollback()
        throw e
    } finally {
      conn.setAutoCommit(isAutoCommit)
      conn.close()
    }
  }

  def insertBatch(sql: String, values: Seq[Array[_]])(implicit conn: Connection): Array[Int] = {
    val isAutoCommit = conn.getAutoCommit
    try {
      conn.setAutoCommit(false)
      val pstmt = conn.prepareStatement(sql)
      values.foreach { value =>
        value.zipWithIndex.foreach { case (v, idx) =>
          pstmt.setObject(idx + 1, v)
        }
        pstmt.addBatch()
      }
      val rets = pstmt.executeBatch()
      conn.commit()
      rets
    } catch {
      case e: SQLException =>
        conn.rollback()
        e.printStackTrace()
        throw e.getNextException
    } finally {
      conn.setAutoCommit(isAutoCommit)
      conn.close()
    }
  }

} 

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

import java.sql.SQLException

object ExceptionMapper {
  def mapException[T](block: => T): T = try {
    block
  }
  catch {
    case e: SQLException => {
      val className = e.getClass.getName

      className match {
        case "org.postgresql.util.PSQLException" => e.getSQLState() match {
          case "23505" => throw new UniqueConstraintException(e)
          case _ => throw e
        }
        case "org.h2.jdbc.JdbcSQLException" => e.getSQLState() match {
          case "23505" => throw new UniqueConstraintException(e)
          case _ => throw e
        }

        case _ => throw e
      }
    }
    case e: Throwable => throw e
  }
} 

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

import java.sql.{Connection, SQLException}

import com.zaxxer.hikari.{HikariConfig, HikariDataSource}
import webby.api.App

import scala.util.Try


abstract class HikariCPPlugin extends DbPlugin {
  private var ds: HikariDataSource = _

  override def onStart() {
    val config = getConfig
    ds = new HikariDataSource(config)
    if (isTestConnection) {
      try {
        ds.getConnection.close()
      } catch {
        case e: SQLException =>
          throw new RuntimeException("Plugin: " + getClass.getSimpleName + ", unable to connect to DB", e)
      }
    }
  }

  override def onStop() {
    Try(ds.close()).get
  }

  override def getConnection: Connection = ds.getConnection
  override def shutdownPool() = ds.close()

  def isTestConnection = App.profile.isJenkinsOrProd

  // ------------------------------- Abstract methods -------------------------------

  def getConfig: HikariConfig
} 

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

import java.sql.SQLException
import play.api.Logger

import scalikejdbc._
import skinny.orm._
import skinny.orm.feature.TimestampsFeature

import domains.user._

class UserRepositoryImpl extends SkinnyMapper[UserEntity] with TimestampsFeature[UserEntity] with UserRepository {
  override lazy val tableName = "users"
  override lazy val defaultAlias = createAlias("u")
  private[this] lazy val a = defaultAlias

  override def extract(rs: WrappedResultSet, rn: ResultName[UserEntity]): UserEntity = UserEntity(
    id = Some(rs.int(rn.id)),
    name = rs.string(rn.name),
    role = rs.string(rn.role)
  )

  def listAll(): List[UserEntity] = findAll()

  def insert(user: UserEntity): Option[Long] = {
    try {
      Some(
        createWithNamedValues(
          column.name -> user.name,
          column.role -> user.role
        )
      )
    } catch {
      // Duplicate Entry?????????
      case e: SQLException if e.getErrorCode == 1062 => {
        Logger.warn(e.getLocalizedMessage)
        None
      }
    }
  }
} 

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

import scalikejdbc._, jsr310._
import skinny.orm._
import skinny.orm.feature.TimestampsFeature
import models.Ayaneru
import java.sql.SQLException
import play.api.Logger
import java.time.ZonedDateTime

trait AyaneruDAO {
  def create(ayaneru: Ayaneru): Option[Long]
  def findById(id: Int): Option[Ayaneru]
  def all(): Seq[Ayaneru]
  def update(ayaneru: Ayaneru): Unit
}

class AyaneruDAOImpl extends SkinnyCRUDMapper[Ayaneru] with AyaneruDAO with TimestampsFeature[Ayaneru] {
  override lazy val defaultAlias = createAlias("a")
  override lazy val tableName = "ayanerus"
  private[this] lazy val a = defaultAlias

  override def extract(rs: WrappedResultSet, rn: ResultName[Ayaneru]): Ayaneru = Ayaneru(
    id = Option(rs.int(rn.id)),
    originalURL = rs.string(rn.originalURL),
    imageURL = Option(rs.string(rn.imageURL)),
    createdAt = Option(rs.get[ZonedDateTime](rn.createdAt)),
    updatedAt = Option(rs.get[ZonedDateTime](rn.updatedAt))
  )

  def create(ayaneru: Ayaneru): Option[Long] = {
    try {
      Some(
        createWithNamedValues(
          column.originalURL -> ayaneru.originalURL,
          column.imageURL -> ayaneru.imageURL
        )
      )
    } catch {
      // Duplicate Entry?????????
      case e: SQLException if e.getErrorCode == 1062 => {
        Logger.warn(e.getLocalizedMessage)
        None
      }
    }
  }

  def findById(id: Int): Option[Ayaneru] = where(sqls.eq(a.id, id)).apply().headOption

  def all(): Seq[Ayaneru] = findAll()

  def update(ayaneru: Ayaneru): Unit = {
    for (id <- ayaneru.id) yield {
      updateById(id.toLong).withAttributes('original_url -> ayaneru.originalURL, 'image_url -> ayaneru.imageURL)
    }
  }
}