How to map columns with special characters in Java namedparameterjdbctemplate batchupdate insert query?

I'm trying to fetch custom columns data from sa360 into MySQL DB. So I have a column having the name Blended KPI 85/10/5. So I have saved the column name in the DB as well Blended KPI 85/10/5. So first the data gets stored in a CSV file and then I'm reading the records from CSV file and capturing it in a List<Map<String, Object>> and later these records are to be stored into the DB. Since I'm having 5000+ records, I'm using batch insert. So I'm facing some issue syntax type error. Please see the below code snippet and the error.

I did try handling escape characters but with no success.

Values inside dailyRecords:

{account_id=2, brand_id=2, platform_id=1, campaign_id=71700000028596159, Blended_KPI_85/10/5=0.0, CPB_(85/10/5)=0.0}

Code:

String sql = "INSERT INTO campaign_table (`account_id` ,`brand_id` ,`platform_id` ,`campaign_id` , `Blended KPI 85/10/5` , `CPB (85/10/5)`  ) VALUES (:account_id, :brand_id, :platform_id, :campaign_id, :Blended_KPI_85/10/5 , :CPB_(85/10/5))"

namedParameterJdbcTemplate.batchUpdate(sql, dailyRecords.toArray(new Map[dailyRecords.size()]));

On executing, I'm getting the below error:

No value supplied for the SQL parameter 'Blended_KPI_85': No value registered for key 'Blended_KPI_85'

Solution

You cannot use the characters of /,(,) for a placeholder name because they are reserved characters for the SQL syntax. A quick workaround is to change the names of placeholders in the SQL statement and also change the keys as well in your data.

You can easily modify the keys of the Map inside your data by the help of collection streams if your Java version is 8 or above:

String sql = "INSERT INTO campaign_table (`account_id` ,`brand_id` ,`platform_id` ,`campaign_id` ,`Blended KPI 85/10/5` ,`CPB (85/10/5)`) VALUES (:account_id, :brand_id, :platform_id, :campaign_id, :Blended_KPI_85_10_5 , :CPB_85_10_5)"

Map[] params = dailyRecords.stream().map(m -> {
    m.put("Blended_KPI_85_10_5", m.get("Blended_KPI_85/10/5"));
    m.put("CPB_85_10_5", m.get("CPB_(85/10/5)"));
    return m;
}).toArray(Map[]::new);

namedParameterJdbcTemplate.batchUpdate(sql, params);

Note that I removed these characters and changed the placeholder names in your sql statement as below:

:Blended_KPI_85/10/5 => :Blended_KPI_85_10_5
:CPB_(85/10/5) => :CPB_85_10_5

Hope this helps. Cheers!