I have a generic repository with a method as:

object Queries {
  def getByFieldId(field: String, id: Int): String = {
    s"""
       |SELECT
       |  DF.id AS fileId,
       |  DF.name AS fileName,
       |  AG.id AS groupId,
       |  AG.name AS groupName
       |FROM $tableName DFG
       |INNER JOIN directory_files DF on DF.id = DFG.file_id
       |INNER JOIN ad_groups AG on AG.id = DFG.group_id
       |WHERE DFG.$field = $id
       |""".stripMargin
  }
}

def getByFieldId(field: String, id: Int): Try[List[Object]] = {
   try {
       val sqlQuery = Queries.getByFieldId("ad_group", 1)
       statement = conn.getPreparedStatement(sqlQuery)
       setParameters(statement, params)
    
       resultSet = statement.executeQuery()
       val metadata = resultSet.getMetaData
    
       val columnCount = metadata.getColumnCount
       val columns: ListBuffer[String] = ListBuffer.empty
       for (i <- 1 to columnCount) {
         columns  = metadata.getColumnName(i)
       }
    
       var item: List[Object] = List.empty
       while (resultSet.next()) {
         val row = columns.toList.map(x => resultSet.getObject(x))
         item = row
       }
    
       Success(item)
  } catch {
       case e: Any => Failure(errorHandler(e))
  } finally conn.closeConnection(resultSet, statement)
}

The problems is that my result set ignore the query aliases and return columns as (id, name, id, name) instead of (fileId, fileName, groupId, groupName).

One solution found is to use column index instead of col names, but I'm not sure if this solution will cover entire app and will not break some other queries.

Maybe, another found solution is here and if I'm right, I can still use colNames but need to get them together with colTypes, then inside resultSet.next() to call getType method for each as:

 // this part of code is not tested
 // this idea came to me writing this topic
 while (resultSet.next()) {
     val row = columns.toList.map(x => {
        x.colType match {
           case "string" => resultSet.getString(x.colName)
           case "integer" => resultSet.getInt(x.colName)
           case "decimal" => resultSet.getDecimal(x.colName)
           case _ => resultSet.getString(x.colName)
     })
     item = row
 }

CodePudding user response：

You may try to use getColumnLabel instead of getColumnName

as documented

Gets the designated column's suggested title for use in printouts and displays. The suggested title is usually specified by the SQL AS clause. If a SQL AS is not specified, the value returned from getColumnLabel will be the same as the value returned by the getColumnName method.

Note that this is highly dependent on the used RDBM.

For Oracle both methods return the alias and there is no chance to get the original column name.