format issue in scala, while having wildcards characters

Question

I have a sql query suppose

sqlQuery="select * from %s_table where event like '%holi%'"
listCity=["Bangalore","Mumbai"]
for (city<- listCity){
   print(s.format(city))
}

Expected output:

select * from Bangalore_table where event like '%holi%'
select * from Mumbai_table where event like '%holi%'

Actual output:

unknown format conversion exception: Conversion='%h'

Can anyone let me how to solve this, instead of holi it could be anything iam looking for a generic solution in scala.

Answer 1

If you want the character % in a formatting string you need to escape it by repeating it:

sqlQuery = "select * from %s_table where event like '%%holi%%'"

More generally I would not recommend using raw SQL. Instead, use a library to access the database. I use Slick but there are a number to choose from.

Also, having different tables named for different cities is really poor database design and will cause endless problems. Create a single table with an indexed city column and use WHERE to select one or more cities for inclusion in the query.