Mybatis dynamic creation table (unknown column): realize dynamic addition, deletion, modification and query

Time:2020-6-29

Business scenario:

The big data web platform sometimes needs to view the results of different indicators according to different types and filter conditions, and the system can not achieve real-time results, so it is necessary to select indicators and conditions in the web system, create tasks for non real-time calculation, write the calculation results into the results table after a period of time, visit the page and view the processing results

Design process:

  • The web system provides different indexes and conditions for input
  • Send spark calculation in JSON form according to the entered index
  • Resolve JSON to create task (task table already exists), and save task related information into configuration table, such as task ID, task name, assigned role, menu, task model, etc;
  • According to the task model, create the result table dynamically (with the task association ratio, such as table name + {task ID}), and write it after calculation
  • The assigned role logs into the web system and selects the corresponding menu to view the icon of task production

Mybatis dynamically creating tables

The logic is the same as that of mybatis DML: XML provides SQL > mapper mapping > test

1.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.demo.DynaDemoMapper">
    <! -- create table -- >
    <! -- 1.ddl using the update tag -- >
    <! -- 2. DDL uses ${} value -- >
    In fact, dynamic column is just a common foreach. The common problem is using {} value to cause syntax error
    <update id="createAutoTaskResult" parameterType="map">
        create table ${tableName} (
        date date,
        <foreach collection="cloums" item="listItem" separator=",">
            ${listItem} varchar(30)
        </foreach>
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    </update>

    <! -- 1. The table name value is ${} -- >
    <! -- 2. because it is an unknown column, the returned result can no longer be an object. Here, map is used. In fact, list<map> is returned. If there is a unique value such as ID in the table, map can be simply returned, and ID -- > is used for key
    <select id="selectTaskResult" parameterType="map" resultType="java.util.Map">
        select * from ${tableName} where
        date between #{startDate,jdbcType=DATE} and #{endDate,jdbcType=DATE}
    </select>

</mapper>

1.mapper

public interface DynaDemoMapper {
    void createAutoTaskResult(@Param("tableName")String tableName, @Param("cloums")List<String> cloums);

    /** 
     *@ Description: simply return the map with ID as key
     * @MapKey("id")
     * Map<Long, Object> selectTaskResult(@Param("tableName")String tableName, @Param("startDate")Date startDate, @Param("endDate")Date endDate)
     */

    List<Map<String, Object>> selectTaskResult(@Param("tableName")String tableName, @Param("startDate")Date startDate, @Param("endDate")Date endDate);
}