Scala curried functions appear to not work with JDBC connections

Question

I have an ETL framework I wrote in Scala, and in the name of removing the same try/catch and closing statements for every SQL query and update I perform, I made this trait that I mixin to all my SQL connections.

trait SqlConnection {

  private val defaultHandling = (stmt: PreparedStatement) => stmt.executeUpdate()
  protected val con: java.sql.Connection

  final def executeSimpleUpdate(sql: String): Unit = executeUpdate(sql)(defaultHandling)

  final def executeUpdate[T](sql: String)(statementHandling: PreparedStatement => T) = {
    val stmt = con.prepareStatement(sql)
    try { statementHandling(stmt) } finally { if (!stmt.isClosed) stmt.close() }
  }

  final def executeQuery[T](sql: String)(resultHandling: ResultSet => T) = {
    val stmt = con.prepareStatement(sql)
    try {
      val rs = stmt.executeQuery()
      try { resultHandling(rs) } finally { if (!rs.isClosed) rs.close() }
    } finally { if (!stmt.isClosed) stmt.close() }
  }

  final def close() = con.close()

}

However, when I run it, none of the commands get executed. I added printlns to try to isolate what lines or code were or weren't getting run, and that gave me this error.

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
    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:408)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
    at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1320)
    at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1312)
    at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4547)
    at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4512)
    at util.SqlConnection$class.executeQuery(Connection.scala:50)

I assume my problem here is misunderstanding how Scala handles function values and when the functions get executed.

Can anyone explain what is happening here or what a similar solution would be? Maybe use inlining?

There is not enough info here to solve your issue. How and where is the connection being opened? It's clear that by the time `prepareStatement` is called on the connection that it's already been closed so you need to figure out how that happened. If the connection is being shared elsewhere (which would not be a good idea) is that other code closing it? — cmbaxter, May 27 '14 at 10:57
This wasn't your question, but note that closing a `Statement` closes the `ResultSet`, so if you know that you're closing the `Statement`, you don't need to close the `ResultSet` separately. Also, I don't see any way to pass parameters to queries through your wrapper class. — Dan Getz, May 27 '14 at 14:57

score 0 · Accepted Answer · answered May 27 '14 at 15:30

0

Don't keep your connection as a field in your class. Wrap your database operations in a function that opens and closes the connection for you. Try this:

private def withConnection[A](f: Connection => A): A = {
   val con = JDBC.giveMeAConnection()// Do whatever you want to open a connection
   val result = f(con)
   con.close()
   result
}

final def executeQuery[T](sql: String)(resultHandling: ResultSet => T) = withConnection { conn =>
   // Use conn as you wish here.
}

answered May 27 '14 at 15:30

DCKing

4,253
2
28
43

I actually solved it and it was something completely unrelated, but thanks for the input. – May 27 '14 at 16:17
@EricHartsuyker good to hear. Even so, it's better style to treat your database access this way. – DCKing May 27 '14 at 22:13

Scala curried functions appear to not work with JDBC connections

1 Answers1