mysql - 玩Framework Mysql连接，避免数据库未连接时异常

Question

我看到很多关于如何使用 jdbc 和 Play Framework 2.x 以及 scala on stack overflow 连接到数据库的问题，我终于设法为 mysql 数据库正确配置它。

我的问题是不同的，因为我想避免在第一次连接时触发异常:

[info] application - Creating Pool for datasource 'mysql'
[error] application - 

! @72aoamohb - Internal server error, for (GET) [/] ->

play.api.Configuration$$anon$1: Configuration error[Cannot connect to database [mysql]]
    at play.api.Configuration$.configError(Configuration.scala:154)
    at play.api.Configuration.reportError(Configuration.scala:806)
    at play.api.db.DefaultDBApi$$anonfun$connect$1.apply(DefaultDBApi.scala:48)
    at play.api.db.DefaultDBApi$$anonfun$connect$1.apply(DefaultDBApi.scala:42)
    at scala.collection.immutable.List.foreach(List.scala:381)
    at play.api.db.DefaultDBApi.connect(DefaultDBApi.scala:42)
    at play.api.db.DBApiProvider.get$lzycompute(DBModule.scala:72)
    at play.api.db.DBApiProvider.get(DBModule.scala:62)
    at play.api.db.DBApiProvider.get(DBModule.scala:58)
    at com.google.inject.internal.ProviderInternalFactory.provision(ProviderInternalFactory.java:81)
Caused by: play.api.Configuration$$anon$1: Configuration error[Failed to initialize pool: Could not create connection to database server. Attempted reconnect 3 times. Giving up.]
    at play.api.Configuration$.configError(Configuration.scala:154)
    at play.api.PlayConfig.reportError(Configuration.scala:996)
    at play.api.db.HikariCPConnectionPool.create(HikariCPModule.scala:70)
    at play.api.db.PooledDatabase.createDataSource(Databases.scala:199)
    at play.api.db.DefaultDatabase.dataSource$lzycompute(Databases.scala:123)
    at play.api.db.DefaultDatabase.dataSource(Databases.scala:121)
    at play.api.db.DefaultDatabase.getConnection(Databases.scala:142)
    at play.api.db.DefaultDatabase.getConnection(Databases.scala:138)
    at play.api.db.DefaultDBApi$$anonfun$connect$1.apply(DefaultDBApi.scala:44)
    at play.api.db.DefaultDBApi$$anonfun$connect$1.apply(DefaultDBApi.scala:42)
Caused by: com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:512)
    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:105)
    at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:71)
    at play.api.db.HikariCPConnectionPool$$anonfun$1.apply(HikariCPModule.scala:58)
    at play.api.db.HikariCPConnectionPool$$anonfun$1.apply(HikariCPModule.scala:54)
    at scala.util.Try$.apply(Try.scala:192)
    at play.api.db.HikariCPConnectionPool.create(HikariCPModule.scala:54)
    at play.api.db.PooledDatabase.createDataSource(Databases.scala:199)
    at play.api.db.DefaultDatabase.dataSource$lzycompute(Databases.scala:123)
    at play.api.db.DefaultDatabase.dataSource(Databases.scala:121)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
    at com.mysql.jdbc.Util.getInstance(Util.java:383)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:958)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:937)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:872)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1038)
    at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:339)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2247)
    at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2100)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2084)
Caused by: java.net.ConnectException: Connexion refusée
    at java.net.PlainSocketImpl.socketConnect(Native Method)
    at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
    at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
    at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
    at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
    at java.net.Socket.connect(Socket.java:589)
    at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:214)
    at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:298)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2247)
    at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2100)

这是我的配置文件:

db {

  # You can declare as many datasources as you want.
  # By convention, the default datasource is named `default`

  # Mysql database configuration
  mysql.driver = com.mysql.jdbc.Driver
  mysql.username = "root"
  mysql.password = "pass"
  mysql.schema = "DBCOMPARATOR_DEFAULT"
  mysql.port = "3306"
  mysql.url="jdbc:mysql://"${general.hostname}":"${db.mysql.port}"/"${db.mysql.schema}"?autoReconnect=true"

}

当我的 Mysql 数据库启动并运行并且模式准备就绪时，一切正常，我的应用程序启动。但是，当我的数据库未在应用程序启动时运行时，我想避免此异常。

完成此操作后，是否可以重新连接到数据库并执行请求，就好像数据库从一开始就没有停机一样？

预先感谢您的帮助。

马特。

Answer 1

所以答案看起来很简单。如果您想避免使用已停止的 mysql 数据库(或任何其他数据库)启动您的 Web 应用程序时出错，请不要使用 Play 默认配置，而是选择直接在您的 Controller 中使用经典的 jdbc 连接。

这是我的配置文件:

libraryDependencies += jdbc

general.hostname = "localhost"

# ----------- Custom db configuration for "manual" jdbc connections
custom.db {
  # -- Mysql jdbc configuration
  mysql.driver = "com.mysql.jdbc.Driver"
  mysql.username = "root"
  mysql.password = "pass"
  mysql.schema = "DBCOMPARATOR_DEFAULT"
  mysql.port = "3306"
  mysql.options = "autoReconnect=true&failOverReadOnly=false&maxReconnects=10"
  mysql.url="jdbc:mysql://"${general.hostname}":"${custom.db.mysql.port}"/"${custom.db.mysql.schema}"?"${custom.db.mysql.options}
}

我自己定义了“custom.db”，这样 Play 就不会将其视为默认数据库配置。现在，这是我的 Controller 代码(不是错误证明，因为它不会测试所有配置变量的有效存在):

import java.sql.{DriverManager, Connection}
import javax.inject.Inject

import play.api.Configuration
import play.api.mvc.{Action, Controller}

/**
  * Created by msb on 08/12/16.
  */
class DatabasesController @Inject() (configuration : Configuration) extends Controller {

  def mysql_connection_test = Action {
    val driver : Option[String] = configuration.getString("custom.db.mysql.driver")
    val url : Option[String] = configuration.getString("custom.db.mysql.url")
    val username : Option[String] = configuration.getString("custom.db.mysql.username")
    val password : Option[String] = configuration.getString("custom.db.mysql.password")

    var connection : Option[Connection] = None
    try {
      Class.forName(driver.get)
      connection = Some(DriverManager.getConnection(url.get, username.get, password.get))

      System.out.println(connection)

    } catch {
      case e : Exception => e.printStackTrace()
    }

    Ok("Still has to be tested")
  }
}

我从本教程中获取它:Alvin Alexander scala documentation and examples

我也想过用Filters来捕捉异常，但是好像不够干净。这段代码仍然可以改进，例如使用类。

希望这可以帮助任何遇到同样问题的人。