Mybatis 动态创建表

Mybatis 动态创建表

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

${} 和 #{}

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

复制一张表作为新表

mapper接口

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

xml文件

    <update id="copyTable" parameterType="string">
        CREATE TABLE ${newTable} SELECT * FROM ${oldTable};
    </update>

创建一张新表

创建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文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.pbad.springboot.Dao.UserMapper">

    <update id="copyTable" parameterType="string">
        CREATE TABLE ${newTable} SELECT * FROM ${oldTable};
    </update>

    <select id="existTable" resultType="java.lang.Integer">
        select count(*)
        from information_schema.TABLES
        where LCASE(table_name)=#{tableName}
    </select>

    <update id="dropTable">
        DROP TABLE IF EXISTS ${tableName}
    </update>

    <update id="createNewTable" parameterType="string">
        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)
        )
    </update>

    <insert id="insertDataIntoNewTable">
        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})
    </insert>

</mapper>

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表已经存在");
        }
    }