Spring Boot整合Mybatis 创建临时表

1. xml中添加

这里用了select into 的方法

    <select id="createTempTable"  >
        select uid into ${test} from My_user
    </select>
    <update id="dropTable" >
        drop table if exists ${test}
    </update>
    <select id="selectFromTmpTable" resultType="java.util.HashMap">
        select * from ${test}
    </select>

也可以先用create table然后再insert into select

    <update id="createTempTable"  >
        create table ${test}
        (
            uid int
        )
    </update>
    <insert id="insertTemp">
            insert into ${test} (uid) select uid from My_user
    </insert>
    <delete id="dropTable" >
        drop table if exists ${test}
    </delete>
    <select id="selectFromTmpTable" resultType="java.util.HashMap">
        select * from ${test}
    </select>

2. Mapper接口中添加

    void createTempTable(@Param("test")String test);
    void dropTable(@Param("test") String test);
    List<Map<String, Object>> selectFromTmpTable(@Param("test") String test);

3. Service层中添加

    void createTempTable();
    void dropTable();
    List<Map<String, Object>> selectFromTmpTable();

    @Override
    public void createTempTable(){
        myUserRepository1.createTempTable("test");
    }
    @Override
    public void dropTable(){
        myUserRepository1.dropTable("test");
    }
    @Override
    public List<Map<String, Object>> selectFromTmpTable(){
        return myUserRepository1.selectFromTmpTable("test");
    }

4. 控制器类中添加

   @RequestMapping("/createTemp")
    public String createTemp(){
        try {
            myUserService.createTempTable();
        }
        catch (Exception e)
        {
            myUserService.dropTable();
            myUserService.createTempTable();
        }
        return "create temp table successfully";
    }
    @RequestMapping("/findTemp")
    public List<Map<String, Object>> findTemp(){
        return myUserService.selectFromTmpTable();
    }
    @RequestMapping("/dropTemp")
    public String dropTemp(){
        myUserService.dropTable();
        return "drop temp table successfully";
    }

5. 运行结果

源码下载
https://download.csdn.net/download/Oceansssss/79491361