Play-Scala:在DB.withConnection中调用另一个函数

Question

我想用DB.connection在一个函数中调用一个函数，如果我只使用下面的代码，我就得到了一个错误：could not find implicit value for parameter connection: java.sql.Connection。

def insert(data: Recap, action_type: String, username: String, parent_page: String) =  DB.withConnection { implicit c => 
        //other code is omitted
            case Some(last_inserted_id) => {
                if(parent_page == "SO")
                    insert_SO(data, employee_id, status, last_inserted_id)//problem
                else if(parent_page == "PO")
                    insert_PO(data, employee_id, status, last_inserted_id)//problem
        //other code is omitted
    }

def insert_SO(data: Recap, employee_id: String, status: Int, last_inserted_id: Long) = {
        var so_id_list: Set[Long] = Set()
        for(ls <- data.recap_items)
            for(rs <- ls.invoice_items)
                so_id_list = so_id_list ++ Set(rs.id)

        for(ls <- so_id_list){
            if(status == 20){
                SQL("""UPDATE salesorder SET recap_id = {recap_id}, approved_id = {approved_id}, approved_datetime = NOW(), status = 20 WHERE salesorder.id = {so_id}""").on(
                'recap_id -> last_inserted_id, 'approved_id -> employee_id, 'so_id -> ls).executeUpdate()
       //other code is omitted
    }
def insert_PO(data: Recap, employee_id: String, status: Int, last_inserted_id: Long) = {
        var po_id_list: Set[Long] = Set()
        for(ls <- data.recap_items)
            for(rs <- ls.invoice_items)
                po_id_list = po_id_list ++ Set(rs.id)

        for(ls <- po_id_list){
            if(status == 20){
                SQL("""UPDATE PurchaseOrder SET recap_id = {recap_id}, approved_id = {approved_id}, approved_datetime = NOW(), status = 20 WHERE PurchaseOrder.id = {po_id}""").on(
                'recap_id -> last_inserted_id, 'approved_id -> employee_id, 'po_id -> ls).executeUpdate()
        //other code is omitted
    }

我相信如果我将DB.withConnection添加到insert_SO和insert_PO函数中，它会工作，但是我不确定这是否是一个很好的实践，因为我用DB创建了另一个连接。

Answer 1

您需要一个Connection来运行一个查询，并且可以从play.api.db.DB助手中检索一个查询。

  def insert_SO(data: Recap, employee_id: String, status: Int, last_inserted_id: Long) = {
    DB.withConnection { implicit c =>
        // other operations
        SQL(......).executeUpdate()
    }
  } 

每次调用连接时，withConnection都会获取和关闭连接。

尝试忽略一个for循环，使用Batch Query代替for loop

def insert_SO(data: Recap, employee_id: String, status: Int, last_inserted_id: Long) = {    
 DB.withConnection { implicit c =>
  //other operations
  val posQuery = SQL("""UPDATE salesorder SET recap_id = {recap_id}, approved_id = {approved_id}, approved_datetime = NOW(), status = 20 WHERE salesorder.id = {so_id}""")
  val posBatchQuery = (posQuery.asBatch /: so_id_list){(sql, ls) =>
    sql.addBatchParams(last_inserted_id,employee_id,ls)
  }  
  val posBatchAry = posBatchQuery.execute
  }
}