springboot整合MyBatis实现动态创建表

原创

caofeng2012 2021-07-06 16:41:01 ©著作权

©著作权归作者所有：来自51CTO博客作者caofeng2012的原创作品，请联系作者获取转载授权，否则将追究法律责任

1：业务场景单表数据量太大，需要用到分表的操作时，例如保存日志数据
代码展示如下：
pom依赖：

<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!--web-->
        <!-- 这是一个web应用 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- swagger文档 -->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.9.2</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.9.2</version>
        </dependency>

        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.0</version>
        </dependency>

        <!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>

        <!-- 打包时拷贝MyBatis的映射文件 -->
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/sqlmap/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
            <!--打包时不包括resources下面的资源-->
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.*</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>

配置类：

@SpringBootConfiguration
@MapperScan("com.example.demo.dao") //扫描dao
public class MybatiesConfig {

    @Autowired
    private DataSource dataSource;

    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setTypeAliasesPackage("com.example.demo.model"); //扫描model
        PathMatchingResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
        sqlSessionFactoryBean.setMapperLocations(resourcePatternResolver.getResources("classpath*:**/sqlmap/*.xml")); //扫描xml
        return sqlSessionFactoryBean.getObject();
    }
}


//swagger 文档的配置类
@SpringBootConfiguration
@EnableSwagger2
public class Swagger {

    @Bean
    public Docket createRestApi(){
        return new Docket(DocumentationType.SWAGGER_2).apiInfo(apiInfo())
                .select()
                .apis(RequestHandlerSelectors.any())
                .paths(PathSelectors.any())
                .build();
    }


    private ApiInfo apiInfo(){
        return new ApiInfoBuilder()
                .title("springboot api doc")
                .description("springboot 动态创建表格的api")
                .version("1.0")
                .build();
    }
}

其中需要注意的点主要有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.example.demo.mapper.UserLogMapper">
  <resultMap id="BaseResultMap" type="com.example.demo.model.UserLog">
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="user_name" jdbcType="VARCHAR" property="userName" />
    <result column="operation" jdbcType="VARCHAR" property="operation" />
    <result column="method" jdbcType="VARCHAR" property="method" />
    <result column="params" jdbcType="VARCHAR" property="params" />
    <result column="time" jdbcType="BIGINT" property="time" />
    <result column="ip" jdbcType="VARCHAR" property="ip" />
  </resultMap>

  <sql id="Base_Column_List">
    id, user_name, operation, method, params, time, ip
  </sql>

  <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from ${tableName}
    where id = #{id,jdbcType=BIGINT}
  </select>

  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
    delete from ${tableName}
    where id = #{id,jdbcType=BIGINT}
  </delete>

  <insert id="insert" parameterType="com.example.demo.model.UserLog">
    insert into ${tableName} (id, user_name, operation, 
      method, params, time, 
      ip)
    values (#{userLog.id,jdbcType=BIGINT}, #{userLog.userName,jdbcType=VARCHAR}, #{userLog.operation,jdbcType=VARCHAR}, 
      #{userLog.method,jdbcType=VARCHAR}, #{userLog.params,jdbcType=VARCHAR}, #{userLog.time,jdbcType=BIGINT}, 
      #{userLog.ip,jdbcType=VARCHAR})
  </insert>

  <insert id="insertSelective" parameterType="com.example.demo.model.UserLog">
    insert into ${tableName}
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="userLog.id != null">
        id,
      </if>
      <if test="userLog.userName != null">
        user_name,
      </if>
      <if test="userLog.operation != null">
        operation,
      </if>
      <if test="userLog.method != null">
        method,
      </if>
      <if test="userLog.params != null">
        params,
      </if>
      <if test="userLog.time != null">
        time,
      </if>
      <if test="userLog.ip != null">
        ip,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="userLog.id != null">
        #{userLog.id,jdbcType=BIGINT},
      </if>
      <if test="userLog.userName != null">
        #{userLog.userName,jdbcType=VARCHAR},
      </if>
      <if test="userLog.operation != null">
        #{userLog.operation,jdbcType=VARCHAR},
      </if>
      <if test="userLog.method != null">
        #{userLog.method,jdbcType=VARCHAR},
      </if>
      <if test="userLog.params != null">
        #{userLog.params,jdbcType=VARCHAR},
      </if>
      <if test="userLog.time != null">
        #{userLog.time,jdbcType=BIGINT},
      </if>
      <if test="userLog.ip != null">
        #{userLog.ip,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>

  <update id="updateByPrimaryKeySelective" parameterType="com.example.demo.model.UserLog">
    update ${tableName}
    <set>
      <if test="userLog.userName != null">
        user_name = #{userLog.userName,jdbcType=VARCHAR},
      </if>
      <if test="userLog.operation != null">
        operation = #{userLog.operation,jdbcType=VARCHAR},
      </if>
      <if test="userLog.method != null">
        method = #{userLog.method,jdbcType=VARCHAR},
      </if>
      <if test="userLog.params != null">
        params = #{userLog.params,jdbcType=VARCHAR},
      </if>
      <if test="userLog.time != null">
        time = #{userLog.time,jdbcType=BIGINT},
      </if>
      <if test="userLog.ip != null">
        ip = #{userLog.ip,jdbcType=VARCHAR},
      </if>
    </set>
    where id = #{userLog.id,jdbcType=BIGINT}
  </update>

  <update id="updateByPrimaryKey" parameterType="com.example.demo.model.UserLog">
    update ${tableName}
    set user_name = #{userLog.userName,jdbcType=VARCHAR},
      operation = #{userLog.operation,jdbcType=VARCHAR},
      method = #{userLog.method,jdbcType=VARCHAR},
      params = #{userLog.params,jdbcType=VARCHAR},
      time = #{userLog.time,jdbcType=BIGINT},
      ip = #{userLog.ip,jdbcType=VARCHAR}
    where id = #{userLog.id,jdbcType=BIGINT}
  </update>

  <select id="selectAll" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from ${tableName}
  </select>

  <!--记住在sql语句中，我们是通过查询information_schema.TABLES来查询表格数量的-->
  <select id="existTable" parameterType="String" resultType="Integer">  
    select count(*)  
    from information_schema.TABLES  
    where table_name=#{tableName} 
  </select>

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

  <!--记住这个地方是update-->
  <update id="createTable" parameterType="String">
    CREATE TABLE ${tableName} (
   `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
   `user_name` varchar(50) DEFAULT NULL COMMENT '用户名',
   `operation` varchar(50) DEFAULT NULL COMMENT '用户操作',
   `method` varchar(200) DEFAULT NULL COMMENT '请求方法',
   `params` varchar(5000) DEFAULT NULL COMMENT '请求参数',
   `time` bigint(20) NOT NULL COMMENT '执行时长(毫秒)',
   `ip` varchar(64) DEFAULT NULL COMMENT 'IP地址',
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=2897 DEFAULT CHARSET=utf8 COMMENT='用户操作日志';
  </update>
</mapper>