更新表是否存在校验

whycxzp

2021-07-13 ea2ce207cb45501831436c405df8c0ad4be35497

更新表是否存在校验

 src/main/java/com/whyc/mapper/ClearTableMapper.java

@@ -11,4 +11,6 @@
    void dropTable(List<TableDTO> tableList);

    List<TableDTO> findTableListLike(List<TableDTO> tableList);

    List<TableDTO> findAllTables();
}

 src/main/java/com/whyc/service/ClearTableService.java

@@ -130,6 +130,24 @@
        tableList.add(new TableDTO("web_site","tb_devtmp_record"));
        tableList.add(new TableDTO("web_site","tb_stationtraffic"));

        //进行一次过滤,查询出来所有的库中表
        List<TableDTO> tableList2 = mapper.findAllTables();
        for (int i = 0; i < tableList.size(); i++) {
            boolean compareFlag = false;
            for (int j = 0; j < tableList2.size(); j++) {
                if((tableList.get(i).getDbName()+tableList.get(i).getTableName())
                .equals(tableList2.get(j).getDbName()+tableList2.get(j).getTableName())){
                    compareFlag = true;
                    break;
                }
            }
            //匹配ok
            if(!compareFlag){
                tableList.remove(i);
                i--;
            }
        }

        mapper.clearTable(tableList);
    }


 src/main/resources/mapper/ClearTableMapper.xml

@@ -14,10 +14,13 @@
            select TABLE_SCHEMA as dbName,TABLE_NAME as tableName from information_schema.TABLES t where t.TABLE_SCHEMA =#{item.dbName} and t.TABLE_NAME like concat('%',#{item.tableName},'%')
        </foreach>
    </select>
    <select id="findAllTables" resultType="com.whyc.dto.TableDTO">
        select TABLE_SCHEMA as dbName,TABLE_NAME as tableName from information_schema.TABLES
    </select>

    <update id="dropTable">
        <foreach collection="list" item="item" separator=";">
            drop table if exists ${item.dbName}.${item.tableName}
            drop table ${item.dbName}.${item.tableName}
        </foreach>
    </update>
</mapper>