How to create and drop tables in MySQL databse using Spring's NamedParameterJdbcTemplate?

Question

I know how to insert, update and delete data in MySQL table using NamedParameterJdbcTemplate. But how to create and drop tables?

Answer 1

You would create a table, or drop a table, the same way you execute a DML statement... SQL statement is passed as a string to the database engine.

I suspect what you are asking is if it's possible to supply an identifier (e.g. a table_name) in a SQL statement, by supplying it as a value for a bind placeholder in the execution of a prepared statement. e.g.

 CREATE TABLE ? (mycol INT) 

or

 CREATE TABLE :my_table_name (mycol INT)  

The short answer is that it's not possible to do that.

An identifier in the statement (for example a table name) must appear as in the SQL text. Just like the keywords SELECT and FROM must appear in the SQL text, and can't be supplied as value through bind placeholders.

We can demonstrate that restriction in a SELECT statement, attempting to supply the name of a table or view as a bind placeholder. It doesn't matter whether that's positional ? or named placeholder.

This doesn't work:

SELECT t.col c FROM ? t LIMIT 1

And this doesn't work:

SELECT 2 AS :col_alias FROM :v_table_name LIMIT 1  

This restriction regarding identifiers makes sense if you have an understanding of the operations that the SQL optimizer performs in preparing and executing a statement.

This restriction applies to SELECT statements. And to DML statements (INSERT,UPDATE,DELETE) and to DDL statements (CREATE,ALTER,DROP).

---

Another potential issue with executing a DDL statement will cause an implicit commit. We need to take care that to handle that correctly in the context of a transaction.

---

If you need to execute a dynamic DDL statement, supplying an identifier with a variable, there are two basic approaches:

1) dynamically constructing SQL text as a string so the table name (and other identifiers) are part of the SQL text, and not bind placeholders.

2) create a stored procedure that will perform the statement, and call the stored procedure. Arguments to the stored procedure can be passed as bind placeholders.