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

问题描述

我想创建一种方法来动态创建表，只需将表名作为变量传递即可.我已经定义了我的xml映射器

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>

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

And my Java Interface Mapper is simply:

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

如果我改为更改查询，在表名上添加``

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

我知道

### 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中的参数(请参见参数).

#{name} is for parameters in PreparedStatement (see String Substitution in Parameters).

这篇关于使用MyBatis和MySql以编程方式创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！