Creating a table programmatically using MyBatis and MySql

Question

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?

agad · Accepted Answer · 2015-08-28T12:35:34.337

3

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

edited Aug 28 '15 at 12:35

answered Aug 28 '15 at 12:23

agad

2,192
1
20
32

Hi, thanks for the suggestion but then I'd get: org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'tableName' in 'class java.lang.String' at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75) – MarMan Aug 28 '15 at 12:29
1

Please try updated answer; if it doesn't work, simple [binding](https://mybatis.github.io/mybatis-3/dynamic-sql.html) should work. – agad Aug 28 '15 at 12:37

score 2 · Answer 2 · answered May 17 '16 at 08:23

in DAO, use annotation @Param void createTableIfNotExist(@Param("uuid") String uuid);

in MAPPER, use $

<update id="createTableIfNotExist" parameterType="java.lang.String">
  CREATE TABLE IF NOT EXISTS `table_${uuid}` 
  (
        `id` bigint(18) NOT NULL,
        `info` varchar(18) NOT NULL,
        PRIMARY KEY (`id`)
  )
  ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='this table is generated by java code.'
</update>

<bind> could be used in MAPPER too.

Creating a table programmatically using MyBatis and MySql

2 Answers2

Linked