Groovy中Apache Commons CSV printHeaders类型转换异常求助

原正常运行代码 #

原本用于读取Denodo数据库表数据并写入CSV的Groovy代码可正常执行：

Sql dbConnection = Sql.newInstance(dbConnectionParameters)

new FileWriter("items.csv").withWriter { writer ->

    CSVPrinter printer = new CSVPrinter(writer, CSVFormat.RFC4180)

    List resultSet = dbConnection.rows("select * from item limit 5") 

    println "there are ${resultSet.size()} items in the item table"

    resultSet.each {row ->
        printer.printRecord(row.values())
    }
}

尝试添加表头后的错误代码 #

为了给CSV添加数据库表列名作为表头，尝试修改代码后出现异常：

Sql dbConnection = Sql.newInstance(dbConnectionParameters)

new FileWriter("items.csv").withWriter { writer ->

    CSVPrinter printer = new CSVPrinter(writer, CSVFormat.DEFAULT)

    List resultSet = dbConnection.rows("select * from item limit 5") { meta ->
        printer.printHeaders(meta as ResultSet)
    }

    println "there are ${resultSet.size()} items in the item table"

    resultSet.each {row ->
        printer.printRecord(row.values())
    }
}

异常信息 #

Caught: groovy.lang.MissingMethodException: No signature of method:
com.denodo.vdb.jdbcdriver.VDBJDBCResultSetMetaData.getMetaData() is
applicable for argument types: () values: [] Possible solutions:
getMetaClass() groovy.lang.MissingMethodException: No signature of
method:
com.denodo.vdb.jdbcdriver.VDBJDBCResultSetMetaData.getMetaData() is
applicable for argument types: () values: [] Possible solutions:
getMetaClass()

问题根源 #

CSVPrinter.printHeaders()方法要求传入java.sql.ResultSet类型参数，但Groovy的dbConnection.rows()闭包中提供的meta是ResultSetMetaData对象（Denodo驱动实现的VDBJDBCResultSetMetaData），类型不匹配。强制转换后，Denodo的元数据对象没有getMetaData()方法，导致抛出MissingMethodException。

解决方案 #

方法1：通过eachRow()获取元数据并手动打印表头 #

使用eachRow()方法，它能同时拿到元数据和每一行数据，首次迭代时收集列名作为表头打印：

Sql dbConnection = Sql.newInstance(dbConnectionParameters)

new FileWriter("items.csv").withWriter { writer ->
    CSVPrinter printer = new CSVPrinter(writer, CSVFormat.RFC4180)
    List resultSet = []
    
    dbConnection.eachRow("select * from item limit 5") { meta, row ->
        // 第一次循环时打印表头
        if (resultSet.isEmpty()) {
            List headers = (1..meta.columnCount).collect { meta.getColumnName(it) }
            printer.printRecord(headers)
        }
        resultSet << row
        printer.printRecord(row.values())
    }

    println "there are ${resultSet.size()} items in the item table"
}

方法2：预配置CSVFormat表头后调用printHeaders() #

先单独获取列名，配置到CSVFormat中，再调用无参的printHeaders()（此方法会使用Format中定义的表头）：

Sql dbConnection = Sql.newInstance(dbConnectionParameters)

new FileWriter("items.csv").withWriter { writer ->
    CSVFormat format = CSVFormat.RFC4180
    List headers = []
    
    // 执行limit 0的查询获取元数据，避免读取实际数据
    dbConnection.withStatement { stmt ->
        def rs = stmt.executeQuery("select * from item limit 0")
        def meta = rs.metaData
        headers = (1..meta.columnCount).collect { meta.getColumnName(it) }
        rs.close()
    }
    
    // 将列名设置为CSV表头
    format = format.withHeader(headers as String[])
    CSVPrinter printer = new CSVPrinter(writer, format)
    
    // 打印表头
    printer.printHeaders()
    
    // 查询并打印数据
    List resultSet = dbConnection.rows("select * from item limit 5")
    println "there are ${resultSet.size()} items in the item table"
    resultSet.each { row ->
        printer.printRecord(row.values())
    }
}

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