ScalikeJdbc中SQL插值报错：ResultSetExtractorException问题求助

Let's break down what's causing this error and how to fix it quickly.

The Root Cause #

Your error scalikejdbc.ResultSetExtractorException: Failed to retrieve value because For input string: "tab_id" stems from a common mistake with SQL interpolation in ScalikeJDBC: your distributionColumn is being treated as a string literal instead of a column name in the generated query.

When you interpolate a raw string directly into the SQL template, ScalikeJDBC wraps it in single quotes. That means your actual executed query looks like this:

SELECT COUNT(*) AS count, MIN('tab_id') AS min, MAX('tab_id') AS max FROM your_table

Instead of calculating the min/max of the tab_id column, the database just returns the string "tab_id" as the min and max values. When your mapResult function tries to convert this string to a numeric type (like Int or Long), it throws the input string conversion error you're seeing.

The Fix #

You need to convert distributionColumn into a SQLSyntax object just like you did with dbTableName, so ScalikeJDBC treats it as a column name rather than a string literal.

Here's the corrected code:

// Convert your column name to SQLSyntax (use column() for safer, type-aware handling)
val distributionColumnSyntax: SQLSyntax = SQLSyntax.column(distributionColumn)
// If dealing with dynamic, user-provided values (ensure inputs are safe first!)
// val distributionColumnSyntax: SQLSyntax = SQLSyntax.createUnsafely(distributionColumn)

val dbTableSQLSyntax: SQLSyntax = SQLSyntax.createUnsafely(dbTableName)
val result = sql"""
  SELECT COUNT(*) AS count, MIN($distributionColumnSyntax) AS min, MAX($distributionColumnSyntax) AS max 
  FROM $dbTableSQLSyntax
""".stripMargin.map(mapResult).single().apply().get()

Additional Checks #

Double-check your mapResult function to ensure it pulls values from the result set correctly. For numeric columns, use nullable-aware methods to handle cases where no data exists (resulting in NULL for min/max):

def mapResult(rs: WrappedResultSet): YourResultType = {
  YourResultType(
    count = rs.long("count"),
    min = rs.longOpt("min"), // Opt variants handle nulls gracefully
    max = rs.longOpt("max")
  )
}

Best Practice Note #

Whenever interpolating table or column names into ScalikeJDBC queries, always use SQLSyntax objects instead of raw strings. This avoids SQL injection risks and ensures the database interprets your identifiers correctly. Prefer SQLSyntax.column() or SQLSyntax.table() over createUnsafely when possible—they provide better type safety for known identifiers.

内容的提问来源于stack exchange，提问作者y2k-shubham