动态表的生成、判断

1、拼接表名称，判断数据表名在数据库中是否存在

@Mapper
@Component
public interface CommonMapper {
    /**
     * 判断表是否存在
     *
     * @param tableName 表名称
     * @return 结果
     * @author yunnuo
     */
    @Select(" SELECT COUNT(*) as count FROM information_schema.TABLES WHERE table_name = #{tableName}")
    Integer existsTable(@Param("tableName") String tableName);
}

1表示存在，0表示不存在，在做动态表名前要做表存在校验，否则可能会报Table 'xxxx' doesn't exist!

2、如果数据库中没有这张表，就创建新表

mapper类的方法

@Mapper
public interface CommonMapper {

    /**
     * 动态建表
     *
     * @param tableName
     * @return int
     */
    int createNewTable(@Param("tableName") String tableName);
}

xml中编写表的生成sql，可以采用navicat导出表结构

<?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="springboot.mybatisplus.mapper.CommonMapper">

    <update id="createNewTable" parameterType="String">
        CREATE TABLE ${tableName} (
          class_id int(8) NOT NULL ,
          class_name varchar(100) NOT NULL ,
          student_count int(10) NOT NULL ,
          teacher_count int(8) NOT NULL ,
          PRIMARY KEY (class_id)
        )ENGINE InnoDB CHARACTER SET utf8 COLLATE utf8_bin
    </update>

</mapper>

 3、数据插入

先拼接表名，然后在插入数据的时候，需要采用${tablename}传入表名称

public interface TestStudentMapper{
   // 插入数据
   public int insertEntity(@param("tableName")String tableName, @param("testStudent")TestStudent testStudent);
}



xml中，则可以按照平常mybatis那样插入数据

<?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.hello.score.mapper.TestStudentMapper">
    
    <resultMap type="TestStudent" id="TestStudentResult">
        <result property="id"    column="id"    />
        <result property="studentNo"    column="student_no"    />
        <result property="studentName"    column="student_name"    />
    </resultMap>

        
    <insert id="insertEntity" parameterType="TestStudent">
        insert into ${tableName}
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="testStudent.id != null">id,</if>
            <if test="testStudent.studentNo != null">student_no,</if>
            <if test="testStudent.studentName != null">student_name,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="testStudent.id != null">#{testStudent.id},</if>
            <if test="testStudent.studentNo != null">#{testStudent.studentNo},</if>
            <if test="testStudent.studentName != null">#{testStudent.studentName},</if>
         </trim>
    </insert>

</mapper>

 

Mybatis实现动态建表_mybatisplus创建表_yuhuofei2021的博客-CSDN博客

Mybatis mapper层 同时传入实体类和单独的参数_mapper传入对象_小目标青年的博客-CSDN博客