Mybatis 动态创建表

注意,创建表时使用的时${} 而不是#{}

${} 和 #{}

• ${} 的变量替换是在动态SQL解析阶段, 仅仅为一个纯碎的 string 替换,会存在sql注入问题
• #{} 在变量赋值前会被解析为一个占位符?,之后在DBMS中将括号内的值插入,因此传入当传入 "’‘1’’"会出现语法错误

mapper接口

    // 复制原有的表
    void copyTable(@Param("newTable")String newTable,@Param("oldTable")String oldTable);

xml文件

    
        CREATE TABLE ${newTable} SELECT * FROM ${oldTable};
    

创建sql表语句

CREATE TABLE ${tableName} (
          id bigint(20) NOT NULL AUTO_INCREMENT,
          entityId bigint(20) NOT NULL,
          dx double NOT NULL,
          dy double NOT NULL,
          dz double NOT NULL,
          ntype varchar(32) NOT NULL,
          gnssTime bigint(20) NOT NULL,
          speed float DEFAULT NULL,
          direction float DEFAULT NULL,
          attributes varchar(255) DEFAULT NULL,
          PRIMARY KEY (id)
)

根据sql创建实体类Trackpoint

package com.pbad.springboot.Domain;

public class Trackpoint {
    private Long id;
    private Long entityId;
    private Double dx;
    private Double dy;
    private Double dz;
    private String ntype;
    private String gnssTime;
    private Float speed;
    private Float direction;
    private String attributes;


    public Trackpoint() {
    }
    
   ....set()，get()方法
}

mapper接口

public interface UserMapper {
    // 复制原有的表
    void copyTable(@Param("newTable")String newTable,@Param("oldTable")String oldTable);

    //表是否存在
    int existTable(String tableName);

    //删除表
    int dropTable(@Param("tableName")String tableName);

    //创建一张新表
    int createNewTable(@Param("tableName")String tableName);

    //对新增的表新增数据
    int insertDataIntoNewTable(@Param("tableName")String tableName,@Param("trackpoint") Trackpoint trackpoint);
}

xml文件

    
        CREATE TABLE ${newTable} SELECT * FROM ${oldTable};
    

    
        select count(*)
        from information_schema.TABLES
        where LCASE(table_name)=#{tableName}
    

    
        DROP TABLE IF EXISTS ${tableName}
    

    
        CREATE TABLE ${tableName} (
          id bigint(20) NOT NULL AUTO_INCREMENT,
          entityId bigint(20) NOT NULL,
          dx double NOT NULL,
          dy double NOT NULL,
          dz double NOT NULL,
          ntype varchar(32) NOT NULL,
          gnssTime bigint(20) NOT NULL,
          speed float DEFAULT NULL,
          direction float DEFAULT NULL,
          attributes varchar(255) DEFAULT NULL,
          PRIMARY KEY (id)
        )
    

    
        insert into ${tableName}
            (entityId,dx,dy,dz,ntype,gnssTime,speed,direction,attributes)
        values
            (#{trackpoint.entityId},
             #{trackpoint.dx},
             #{trackpoint.dy},
             #{trackpoint.dz},
             #{trackpoint.ntype},
             #{trackpoint.gnssTime},
             #{trackpoint.speed},
             #{trackpoint.direction},
             #{trackpoint.attributes})
    

Junit单元测试

    
    @Test
    void creatTable() {
        String tableName = "trackpoint";
        //判断trackpoint表是否存在
        int i = userMapper.existTable(tableName);
        System.out.println(i);
        if (i != 1) {
            //新增trackpoint表
            userMapper.createNewTable(tableName);
            System.out.println("创建成功");
            //新增数据
            Trackpoint trackpoint = new Trackpoint();
            trackpoint.setId(1L);
            trackpoint.setEntityId(1L);
            trackpoint.setDx(1.00);
            trackpoint.setDy(1.00);
            trackpoint.setDz(1.00);
            trackpoint.setNtype("1");
            trackpoint.setGnssTime("1");
            trackpoint.setSpeed(Float.valueOf(1));
            trackpoint.setDirection(Float.valueOf(1));
            trackpoint.setAttributes("1");
            userMapper.insertDataIntoNewTable(tableName,trackpoint);
            System.out.println("插入数据成功");
        }else {
            System.out.println("trackpoint表已经存在");
        }
    }