How to manage DB connection in Scala using functional programming style?

[admin]

I have a piece of Scala code using DB connection:

def getAllProviderCodes()(implicit conf : Configuration) : List[String] = {
  var conn: java.sql.Connection = null
  try {
    conn = DriverManager.getConnection(DBInfo.dbUrl(conf), DBInfo.dbUserName(conf), DBInfo.dbPassword(conf))
    return ResultSetIterator.create(
              conn.prepareStatement("SELECT pcode FROM providers").executeQuery()
           ){_.getString("pcode")}.toList
  } catch {
    case e: Exception =>
      logger.warn("Something went wrong with creating the connection: " + e.getStackTrace)
  } finally {
    if (conn != null) {
      conn.close()
    }
  }
  List()
}

It's very OOP-Java-like style, so I'd like to know is there a way to write it in more functional way? I tried to succeed in applying <a href="https://www.scala-lang.org/api/2.9.3/scala/util/Try.html" rel="nofollow noreferrer" title="Try">Try</a> monad, but failed: my biggest concern is that we have state here, as well as

finally

block. Maybe there's some kind of pattern for such cases?

Thank you in advance.

<strong>UPD:</strong> Here's the example from <a href="https://scalerablog.wordpress.com/2...hy-scala-util-try-doesnt-have-finally-clause/" rel="nofollow noreferrer">here</a> of what IMHO the solution will look like:

val connection = database.getConnection()
val data: Seq[Data] = Try{
  val results = connection.query("select whatever")
  results.map(convertToWhatIneed)
} recover {
  case t: Throwable => 
    Seq.empty[Data]
} get
connection.close()

But as I've mentioned in the comment, I have to close the connection, then I have to place all the things regarding to connection inside Try to keep it pure... and then I to the variant with "try-catch-finally" inside Try block.