ScalikeJDBC + SQlite:无法在建立连接后更改只读标志

Question

试着让ScalikeJDBC和SQLite工作。根据提供的示例有一个简单的代码：

import scalikejdbc._, SQLInterpolation._

object Test extends App {
  Class.forName("org.sqlite.JDBC")
  ConnectionPool.singleton("jdbc:sqlite:test.db", null, null)

  implicit val session = AutoSession

  println(sql"""SELECT * FROM kv WHERE key == 'seq' LIMIT 1""".map(identity).single().apply()))
}

它例外地失败了：

Exception in thread "main" java.sql.SQLException: Cannot change read-only flag after establishing a connection. Use SQLiteConfig#setReadOnly and QLiteConfig.createConnection().
at org.sqlite.SQLiteConnection.setReadOnly(SQLiteConnection.java:447)
at org.apache.commons.dbcp.DelegatingConnection.setReadOnly(DelegatingConnection.java:377)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setReadOnly(PoolingDataSource.java:338)
at scalikejdbc.DBConnection$class.readOnlySession(DB.scala:138)
at scalikejdbc.DB.readOnlySession(DB.scala:498)
...

我已经尝试过scalikejdbc 1.7和2.0，错误仍然存在。作为sqlite驱动程序，我使用"org.xerial" % "sqlite-jdbc" % "3.7.+"。

我能做些什么来纠正错误呢？

Answer 1

我发现原因是您使用的是"org.xerial" % "sqlite-jdbc" % "3.7.15-M1"。这个版本看起来还不稳定。

使用"3.7.2"与@kawty相同。

Answer 2

下面将创建两个独立的连接，一个用于只读操作，另一个用于写操作。

ConnectionPool.add("mydb", s"jdbc:sqlite:${db.getAbsolutePath}", "", "")
ConnectionPool.add(
  "mydb_ro", {
    val conf = new SQLiteConfig()
    conf.setReadOnly(true)
    val source = new SQLiteDataSource(conf)
    source.setUrl(s"jdbc:sqlite:${db.getAbsolutePath}")
    new DataSourceConnectionPool(source)
  }
)

Answer 3

在@Synesso回答的基础上，我稍微扩展了一下，可以从配置文件中获取配置值并设置连接设置：

import scalikejdbc._
import scalikejdbc.config.TypesafeConfigReader

case class SqlLiteDataSourceConnectionPool(source: DataSource, 
                                           override val settings: ConnectionPoolSettings)
  extends DataSourceConnectionPool(source)


// read settings for 'default' database
val cpSettings = TypesafeConfigReader.readConnectionPoolSettings()
val JDBCSettings(url, user, password, driver) = TypesafeConfigReader.readJDBCSettings()

// use those to create two connection pools
ConnectionPool.add("db", url, user, password, cpSettings)
ConnectionPool.add(
      "db_ro", {
        val conf = new SQLiteConfig()
        conf.setReadOnly(true)
        val source = new SQLiteDataSource(conf)
        source.setUrl(url)
        SqlLiteDataSourceConnectionPool(source, cpSettings)
      }
    )

// example using 'NamedDB'
val name: Option[String] = NamedDB("db_ro") readOnly { implicit session =>
  sql"select name from users where id = $id".map(rs => rs.string("name")).single.apply()
}