查询数据库中是否存在表

由于公司中有部分表是动态生成的，这里记录一下，查询库中表是否存在的方法

DAO层

public List<String> tableContains(@Param("dbName") String dbName,@Param("tableName")String tableName);

XML层

select table_name from information_schema.tables 
   where table_schema= #{dbName} and table_name = #{tableName}

一个库中，批量查询表，返回表信息 

public List<String> filterTables(@Param("dbName") String dbName,@Param("tables")List<String> tables);

<select id="filterTables" resultType="String">
   select table_name from information_schema.tables 
      where table_schema= #{dbName}
      <if test="tables !=null and tables.size > 0">
         AND table_name in
         <foreach collection="tables" item="item" index="index" open="(" close=")" separator=",">
            #{item}
         </foreach>
      </if>
</select> 

 

删除表

public void dropTable(@Param("dbName") String dbName,@Param("tableName")String tableName);

<update id="dropTable" parameterType="java.lang.String">
   DROP TABLE IF EXISTS ${dbName}.${tableName}
</update>

 

//获取列是否存在
public Integer findColumnExist(@Param("dbName") String dbName,@Param("tableName")String tableName,@Param("columnName")String columnName);

 

<!-- 查询列是否存在 -->
<select id="findColumnExist" resultType="Integer"> 
   SELECT
       COUNT(1)
   FROM
      information_schema. COLUMNS
   WHERE
      table_schema = #{dbName}
   AND table_name = #{tableName}
   AND column_name= #{columnName}
</select>