I want to create a method to dynamically create tables just passing the table name as a variable. I have defined my xml mapper
<mapper namespace="com.mappers.TableCreatorMapper">
<cache />
<insert id="createNewTableIfNotExists" parameterType="String" >
CREATE TABLE IF NOT EXISTS #{tableName}
(
`ID` varchar(20) NOT NULL,
PRIMARY KEY (`ID`)
)
ENGINE=InnoDB
</insert>
</mapper>
And my Java Interface Mapper is simply:
public interface TableCreatorMapper {
public void createNewTableIfNotExists(String tableName);
}
but when I call my interface
tableCreatorMapper.createNewTableIfNotExists("test");
I get the following exception:
org.springframework.jdbc.BadSqlGrammarException:
### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''test'
(
`ID` varchar(20) NOT NULL,
PRIMARY KEY (`ID`)
' at line 1
### The error may involve com.mappers.TableCreatorMapper.createNewTableIfNotExists-Inline
### The error occurred while setting parameters
### SQL: CREATE TABLE IF NOT EXISTS ? ( `ID` varchar(20) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''test'
(
`ID` varchar(20) NOT NULL,
PRIMARY KEY (`ID`)
' at line 1
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''test'
(
`ID` varchar(20) NOT NULL,
PRIMARY KEY (`ID`)
' at line 1
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
at org.sp
If I instead change the query adding the ``for the table name:
CREATE TABLE IF NOT EXISTS `#{tableName}`(
`ID` varchar(20) NOT NULL,
PRIMARY KEY (`ID`)
)
ENGINE=InnoDB
I get
### The error occurred while setting parameters
### SQL: CREATE TABLE IF NOT EXISTS `?`( `ID` varchar(20) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB
### Cause: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
; SQL []; Parameter index out of range (1 > number of parameters, which is 0).; nested exception is java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
Any idea why?
try
CREATE TABLE IF NOT EXISTS ${_parameter}
(
`ID` varchar(20) NOT NULL,
PRIMARY KEY (`ID`)
)
ENGINE=InnoDB
#{name} is for parameters in PreparedStatement (see String Substitution in Parameters).