Placing multiple variables in a String ready for SQL Prepared Statement - Cleanest way

Question

I have to build an SQL string for a prepared statement, it needs multiple variables inserting. What is the cleanest way to do this?

Raw SQL for MYSQL database, variables in bold

INSERT INTO output(their_sku, their_description, net_cost) SELECT product_code, product_description, net_cost FROM import

The names come from Enums that could possibly need modifying, hence no hard coding table names or columns.

All my attempts look messy, and coming from Python I am missing the fStrings. Attempts below.

String sql = String.format("INSERT INTO %s(%s, %s, %s) SELECT product_code, product_description, net_cost FROM %s", Tables.OUTPUT, OutputTableFields.THEIR_SKU, OutputTableFields.THEIR_DESCRIPTION, OutputTableFields.NET_COST, Tables.IMPORT);

Quite unreadable

String sql = "INSERT INTO " + Tables.OUTPUT + "(" + OutputTableFields.THEIR_SKU + ", " + OutputTableFields.THEIR_DESCRIPTION + ", " + OutputTableFields.NET_COST + ") SELECT product_code, product_description, net_cost FROM " + Tables.IMPORT"

At least the SQL and Enums are in order but it looks awful

Is there a best practice way of setting this out?

Thankyou.

Answer 1

I'm not sure this helps too much in your case, but you might also consider using text blocks available since JDK15. A text block makes it neater to specify a long string which can be broken up into many lines.

You can combine with s.formatted(args...) which is equivalent of String.format(s, args...) as follows:

String sql = """
  INSERT INTO %s(%s, %s, %s) 
  SELECT product_code, product_description, net_cost FROM %s
  """
  .formatted(Tables.OUTPUT, OutputTableFields.THEIR_SKU, OutputTableFields.THEIR_DESCRIPTION, OutputTableFields.NET_COST, Tables.IMPORT);

=> sql is:
"INSERT INTO blah(blah, blah, blah)
SELECT product_code, product_description, net_cost FROM blah
"

Note that the above introduces line break, if not wanting these you can escape by adding a trailing \ like this:

String sql = """
  INSERT INTO %s(%s, %s, %s) \
  SELECT product_code, product_description, net_cost FROM %s\
  """
  .formatted(Tables.OUTPUT, OutputTableFields.THEIR_SKU, OutputTableFields.THEIR_DESCRIPTION, OutputTableFields.NET_COST, Tables.IMPORT);

=> sql is:
"INSERT INTO blah(blah, blah, blah) SELECT product_code, product_description, net_cost FROM blah"

You could also use static imports to shorten the formatted(...) part or use resource bundle files for the text.