java - 使用MyBatis和MySql以编程方式创建表

我想创建一个动态创建表的方法，只是将表名作为变量传递。
我已经定义了xml映射器

<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>

我的Java接口映射器很简单：

public interface TableCreatorMapper {
     public void createNewTableIfNotExists(String tableName);
}

但当我调用接口时

tableCreatorMapper.createNewTableIfNotExists("test");

我得到以下异常：

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

如果我改为在查询中添加表名的``

 CREATE TABLE IF NOT EXISTS `#{tableName}`(
        `ID` varchar(20) NOT NULL,
        PRIMARY KEY (`ID`)
        )
        ENGINE=InnoDB

我明白了

### 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).

知道为什么吗？

最佳答案

尝试

CREATE TABLE IF NOT EXISTS ${_parameter}
        (
        `ID` varchar(20) NOT NULL,
        PRIMARY KEY (`ID`)
        )
        ENGINE=InnoDB

#{name}用于preparedstatement中的参数（请参见Parameters中的字符串替换）。