Apache Spark’s fine-grained processing of RDBMS records part 15: Literal values in Sql string template

With the class JdbcRecordWriteFromSparkSqlRow in place, we are pretty close to getting done with implementing the Update and Insert facilities. The majority of the remaining work, as you’ll see soon, is about constructing their SQL string templates. But before we do that, we need to take care of a little plumbing work for building these SQL strings.

Handling literal values in building the SQL string template

In the discussion of the class JdbcRecordWrite in the previous post, we encounter the method getSqlStmtString(). It’s meant to build the SQL string template, which may potentially contain literal values besides placeholders. These literal values could be null, or of any primitive MySQL types. Following are an example of the template for each Update and Insert:

update transaction_table set string_data_col = 'changed_string_data_value_1',
                             number_data_col = null
                         where unique_key_col_1 = ? and
                               unique_key_col_2 = 127

insert into transaction_table (unique_key_col_1, unique_key_col_2, dataset_id, string_data_col, number_data_col)
                       values (?, 127, 199, 'changed_string_data_value_1', null)

In these templates, there are literal values of null (for column number_data_col), type string (‘changed_string_data_value_1’ for column string_data_col), and type long (127 and 199 for columns unique_key_col_2 and dataset_id, respectively).

If you notice, the presentation of a string literal in the template is different from that of any other type. It requires the string literal to be surrounded by the single quote (thus, ‘changed_string_data_value_1’ instead of just changed_string_data_value_1). For any other primitive type, the plain literal value itself just shows up as it is without any decoration. Thus, we need to provide a small utility to provide special treatment to string literals when it comes to presenting them in a SQL template. This utility is wrapped in the trait SqlStringBuildingSupporter:

package com.myproject.experiment.jdbc

/** Provides utility to support building the sql string template that will compile to the prepared statement
  *
  */
trait SqlStringBuildingSupporter {
  /**
    * Generate the proper presentation format for a literal value in the template
    *
    * @param value: the literal value of concern
    * @return the literal value in the presentation format acceptable by the string template
    */
  protected def getLiteralValueForSqlString(value: Any): Any = {
    if (value == null) "null"
    else {
      value match {
        case s: String => "'" + s + "'"
        case v: Any => v
      }
    }
  }
}

Function getLiteralValueForSqlString() (line 13) will eventually be called by getSqlStmtString() to build the SQL template (see next post). It treats a string literal specially by placing the single quote as decoration around (line 17). In fact, the value of null is treated separately, too (line 14). The reason is technically, if we were to otherwise also subject the null value to the pattern matching (lines 16 – 19), a mismatch would cause an exception to be thrown because the runtime system can’t detect the type of a null value. For all other types, the literal value is returned unchanged (line 18).