How to use a tablename variable for a java prepared statement insert
JavaSqlVariablesDynamicPrepared StatementJava Problem Overview
I am using a java PreparedStatment object to construct a series of batched INSERT queries. The query statement is of the format...
String strQuery = "INSERT INTO ? (col1, col2, col3, col4, col5) VALUES (?,?,?,?,?,?);";
...so both field values and the tablename are variables (ie. I have multiple tables with the same column format of which each insert will be directed to a different one). I can get the executes to work if I remove the "?" tablename variable and hard code but each prepared statement will be inserted into a different table so needs to remain a variable I populate immediately prior to executing the batch query using...
stmt.setString(1, "tableName1");
How can I let this be a dynamic variable please?
Java Solutions
Solution 1 - Java
You can't. You need to contruct the sql with string concatenation/placeholder with String.format. prepared statement is for the column values not for table name.
Solution 2 - Java
You can use placeholder in place of table name and then replacing that with your tablename.
String strQuery = "INSERT INTO $tableName (col1, col2, col3, col4, col5)
VALUES (?,?,?,?,?,?);";
and replace when u come to know the tablename
String query =strQuery.replace("$tableName",tableName);
stmt =conn.prepareStatement(query);
Solution 3 - Java
One alternative could be String.format
:
e.g.
String sql = String.format("INSERT INTO $1%s (col1, col2, col3, (etc)", myTablename);
Solution 4 - Java
If your table name is coming from your own code ONLY...
...you would need to add it to the original string:
String tableName = "some_table_name";
// some other code
String strQuery = "INSERT INTO " + tableName + " (col1, col2, col3, col4, col5) VALUES (?,?,?,?,?,?);";
If the table name is coming any other unreliable source (user input, a parameter that other code is passing in), do not do this and see the other answers!