如何在Slick中调用存储过程并获取返回值(使用Scala)

好吧，经过大量研究和对矛盾文档的审查，我找到了答案.不幸的是，这不是我要找的东西:

Well, after much research and review of conflicting documentation, I found the answer. Unfortunately, it wasn't the one I was looking for:

对于返回完整表或已存储表的数据库函数 程序请使用普通SQL查询.返回的存储过程 当前不支持多个结果集.

For database functions that return complete tables or stored procedures please use Plain SQL Queries. Stored procedures that return multiple result sets are currently not supported.

最重要的是，Slick不支持开箱即用的存储函数或过程，因此我们必须编写自己的函数.

Bottom line, Slick does not support stored functions or procedures out of the box, so we have to write our own.

答案是通过获取会话对象退出Slick，然后使用标准JDBC管理过程调用.对于那些熟悉JDBC的人来说，这不是一件乐事...但是，幸运的是，使用Scala，我们可以通过模式匹配做一些非常不错的技巧，从而使工作变得更加容易.

The answer is to drop down out of Slick by grabbing the session object, and then use standard JDBC to manage the procedure call. For those of you familiar with JDBC, that's not a joy... but, fortunately, with Scala we can do some pretty nice tricks with pattern matching that make the job easier.

对我来说，第一步是构建一个干净的外部API.最终的样子是这样的:

The first step for me was putting together a clean external API. This is what it ended up looking like:

val db = Database.forDataSource(DB.getDataSource)
var response: Option[GPInviteResponse] = None

db.withSession {
    implicit session => {
        val parameters = GPProcedureParameterSet(
            GPOut(Types.INTEGER) ::
            GPIn(Option(i.token), Types.VARCHAR) ::
            GPIn(recipientAccountId, Types.INTEGER) ::
            GPIn(Option(contactType), Types.INTEGER) ::
            GPIn(contactValue, Types.VARCHAR) ::
            GPIn(None, Types.INTEGER) :: 
            GPIn(Option(requestType), Types.CHAR) ::
            GPOut(Types.INTEGER) ::  
            Nil
        )

        val result = execute(session.conn, GPProcedure.SendInvitation, parameters)
        val rc = result.head.asInstanceOf[Int]

        Logger(s"FUNC return code: $rc")
        response = rc match {
            case 0 => Option(GPInviteResponse(true, None, None))
            case _ => Option(GPInviteResponse(false, None, Option(GPError.errorForCode(rc))))
        }
    }
}

db.close()

这是一个快速的演练:我创建了一个简单的容器来对存储过程调用进行建模. GPProcedureParameterSet可以包含GPIn，GPOut或GPInOut实例的列表.这些中的每一个都将一个值映射到JDBC类型.容器看起来像这样:

Here's a quick walkthrough: I created a simple container to model a stored procedure call. The GPProcedureParameterSet can contain a list of GPIn, GPOut, or GPInOut instances. Each of these maps a value to a JDBC type. The container looks like this:

case class GPOut(parameterType: Int) extends GPProcedureParameter
object GPOut

case class GPIn(value: Option[Any], parameterType: Int) extends GPProcedureParameter
object GPIn

case class GPInOut(value: Option[Any], parameterType: Int) extends GPProcedureParameter
object GPInOut

case class GPProcedureParameterSet(parameters: List[GPProcedureParameter])
object GPProcedureParameterSet

object GPProcedure extends Enumeration {
    type GPProcedure = Value
    val SendInvitation = Value("{?=call app_glimpulse_invitation_pkg.n_send_invitation(?, ?, ?, ?, ?, ?, ?)}")
}

为了完整起见，我包括了GPProcedure枚举，以便可以将它们放在一起.

For completeness I'm including the GPProcedure enumeration so you can put it all together.

所有这些都交给了我的execute()函数.它又大又讨厌，闻起来像老式的JDBC，我敢肯定我会对Scala进行一些改进.我昨晚凌晨3点完成了这个工作……但它确实有效，而且效果很好.注意，这个特定的execute()函数返回一个包含所有OUT参数的List.我将不得不编写一个单独的executeQuery()函数来处理返回resultSet的过程. (但是区别是微不足道的:您只需要编写一个捕获resultSet.next的循环，然后将其全部填充到List或所需的任何其他结构中即可).

All of this gets handed to my execute() function. It's big and nasty, smells like old-fashioned JDBC, and I'm sure I'll improve the Scala quite a bit. I literally finished this up at 3am last night... but it works, and it works really well. Note that this particular execute() function returns a List containing all of the OUT parameters... I'll have to write a separate executeQuery() function to handle a procedure that returns a resultSet. (The difference is trivial though: you just write a loop that grabs a resultSet.next and stuff it all into a List or whatever other structure you would like).

这是最讨厌的Scala<-> JDBC映射execute()函数:

Here's the big nasty Scala<->JDBC mapping execute() function:

def execute(connection: Connection, procedure: GPProcedure, ps: GPProcedureParameterSet) = {
    val cs = connection.prepareCall(procedure.toString)
    var index = 0

    for (parameter <- ps.parameters) {
        index = index + 1
        parameter match {
            // Handle any IN (or INOUT) types: If the optional value is None, set it to NULL, otherwise, map it according to
            // the actual object value and type encoding:
            case p: GPOut => cs.registerOutParameter(index, p.parameterType)
            case GPIn(None, t) => cs.setNull(index, t)
            case GPIn(v: Some[_], Types.NUMERIC | Types.DECIMAL) => cs.setBigDecimal(index, v.get.asInstanceOf[java.math.BigDecimal])
            case GPIn(v: Some[_], Types.BIGINT) => cs.setLong(index, v.get.asInstanceOf[Long])
            case GPIn(v: Some[_], Types.INTEGER) => cs.setInt(index, v.get.asInstanceOf[Int])
            case GPIn(v: Some[_], Types.VARCHAR | Types.LONGVARCHAR) => cs.setString(index, v.get.asInstanceOf[String])
            case GPIn(v: Some[_], Types.CHAR) => cs.setString(index, v.get.asInstanceOf[String].head.toString)
            case GPInOut(None, t) => cs.setNull(index, t)

            // Now handle all of the OUT (or INOUT) parameters, these we just need to set the return value type:
            case GPInOut(v: Some[_], Types.NUMERIC) => {
                cs.setBigDecimal(index, v.get.asInstanceOf[java.math.BigDecimal]); cs.registerOutParameter(index, Types.NUMERIC)
            }
            case GPInOut(v: Some[_], Types.DECIMAL) => {
                cs.setBigDecimal(index, v.get.asInstanceOf[java.math.BigDecimal]); cs.registerOutParameter(index, Types.DECIMAL)
            }
            case GPInOut(v: Some[_], Types.BIGINT) => {
                cs.setLong(index, v.get.asInstanceOf[Long]); cs.registerOutParameter(index, Types.BIGINT)
            }
            case GPInOut(v: Some[_], Types.INTEGER) => {
                cs.setInt(index, v.get.asInstanceOf[Int]); cs.registerOutParameter(index, Types.INTEGER)
            }
            case GPInOut(v: Some[_], Types.VARCHAR) => {
                cs.setString(index, v.get.asInstanceOf[String]); cs.registerOutParameter(index, Types.VARCHAR)
            }
            case GPInOut(v: Some[_], Types.LONGVARCHAR) => {
                cs.setString(index, v.get.asInstanceOf[String]); cs.registerOutParameter(index, Types.LONGVARCHAR)
            }
            case GPInOut(v: Some[_], Types.CHAR) => {
                cs.setString(index, v.get.asInstanceOf[String].head.toString); cs.registerOutParameter(index, Types.CHAR)
            }
            case _ => { Logger(s"Failed to match GPProcedureParameter in executeFunction (IN): index $index (${parameter.toString})") }
        }
    }

    cs.execute()

    // Now, step through each of the parameters, and get the corresponding result from the execute statement. If there is
    // no result for the specified column (index), we'll basically end up getting a "nothing" back, which we strip out.

    index = 0

    val results: List[Any] = for (parameter <- ps.parameters) yield {
        index = index + 1
        parameter match {
            case GPOut(Types.NUMERIC) | GPOut(Types.DECIMAL) => cs.getBigDecimal(index)
            case GPOut(Types.BIGINT) => cs.getLong(index)
            case GPOut(Types.INTEGER) => cs.getInt(index)
            case GPOut(Types.VARCHAR | Types.LONGVARCHAR | Types.CHAR) => cs.getString(index)
            case GPInOut(v: Some[_], Types.NUMERIC | Types.DECIMAL) => cs.getInt(index)
            case GPInOut(v: Some[_], Types.BIGINT) => cs.getLong(index)
            case GPInOut(v: Some[_], Types.INTEGER) => cs.getInt(index)
            case GPInOut(v: Some[_], Types.VARCHAR | Types.LONGVARCHAR | Types.CHAR) => cs.getString(index)
            case _ => {
                Logger(s"Failed to match GPProcedureParameter in executeFunction (OUT): index $index (${parameter.toString})")
            }
        }
    }

    cs.close()

    // Return the function return parameters (there should always be one, the caller will get a List with as many return
    // parameters as we receive):

    results.filter(_ != (()))
}