Groovy SQL eachRow无ResultSet时异常无法捕获的问题求助

Groovy SQL的eachRow()方法执行INSERT这类不返回ResultSet的SQL语句时，会产生无法被常规catch块捕获的错误。虽然Groovy SQL提供了execute()方法处理无ResultSet的语句，但如果SQL语句由用户提供、无法提前确定类型时，就没法判断该用哪种方法。原本预期eachRow()在无ResultSet时抛出可捕获异常，但实际并未实现。

尝试的代码片段：

System.in.readLines().each {
    dbsql = it
    try {
        println "processing sql"
        dbconn.eachRow(dbsql) {
            n++
        }
        println "processed result ($n rows)"
    } catch(SQLException ex) {
      rslt = 2
      println ">>>> query failed against ${dbhost}/${dbname}:"
      println "  ${ex}"
    } catch(Exception ex) {
      rslt = 255
      println ">>>> general failure"
      println "  ${ex}"
    }
}

实际运行时未触发任何catch块，得到如下错误输出：

Dec 01, 2023 12:45:13 AM groovy.sql.Sql eachRow
WARNING: Failed to execute:  insert into mytable (name) values ('John Doe'); because: This SQL statement does not return a single ResultSet
>>>> query failed against dbhost/wrk:
  java.sql.SQLException: This SQL statement does not return a single ResultSet

只有SELECT这类返回ResultSet的语句能正常运行，现需适配这种未知SQL语句场景的Groovy SQL使用方案。

可以通过以下两种方式解决该问题：

1. 先判断SQL类型，选择对应执行方法 #

通过SQL语句前缀判断（需处理大小写和注释场景），区分查询类与非查询类语句：

System.in.readLines().each {
    def rawSql = it.trim()
    // 去除开头的单行注释
    def cleanSql = rawSql.replaceAll(/^--.*\n/, "").trim().toUpperCase()
    def n = 0
    def rslt = 0
    try {
        println "processing sql"
        if (cleanSql.startsWith("SELECT") || cleanSql.startsWith("WITH")) {
            dbconn.eachRow(rawSql) {
                n++
                // 按需处理行数据
            }
            println "processed result ($n rows)"
        } else {
            def affectedRows = dbconn.executeUpdate(rawSql)
            println "statement executed, affected $affectedRows rows"
        }
    } catch(SQLException ex) {
        rslt = 2
        println ">>>> query failed against ${dbhost}/${dbname}:"
        println "  ${ex}"
    } catch(Exception ex) {
        rslt = 255
        println ">>>> general failure"
        println "  ${ex}"
    }
}

这种方式适合大多数常规场景，若需处理复杂注释或特殊SQL语法，可扩展注释清理逻辑。

2. 使用通用execute()方法处理所有语句 #

Groovy SQL的execute()方法支持所有类型的SQL语句，返回的SqlResult对象可通过元数据判断是否存在ResultSet，无需提前识别SQL类型：

System.in.readLines().each {
    dbsql = it
    def n = 0
    def rslt = 0
    try {
        println "processing sql"
        def sqlResult = dbconn.execute(dbsql)
        if (sqlResult.hasResultSet()) {
            sqlResult.resultSet.each {
                n++
                // 按需处理行数据
            }
            println "processed result ($n rows)"
        } else {
            println "statement executed, affected ${sqlResult.updateCount} rows"
        }
    } catch(SQLException ex) {
        rslt = 2
        println ">>>> query failed against ${dbhost}/${dbname}:"
        println "  ${ex}"
    } catch(Exception ex) {
        rslt = 255
        println ">>>> general failure"
        println "  ${ex}"
    }
}

这种方法通用性更强，完全适配未知SQL语句的场景，异常也能被正常捕获处理。

内容的提问来源于stack exchange，提问作者H. Dog