1、优先使用information_schema来检查,如果没有查询这个的权限则使用show tables来检查。
mapper:
package com.chenp.demo.dao;
import java.util.Map;
import org.apache.ibatis.annotations.Param;
/**
* 通用的mapper
* @author cp218
* @data 2022/12/9 15:57
*
*/
public interface CommonMapper {
/**
* 使用information_schema检查表是否存在
* @param tableSchema
* @param tableName
* @return
*/
Integer checkTableExistsWithSchema(@Param("tableSchema")String tableSchema, @Param("tableName")String tableName);
/**
* 使用show tables检查表是否存在
* @param tableName
* @return
*/
Map<String, String> checkTableExistsWithShow(@Param("tableName")String tableName);
}
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.chenp.demo.mapper.CommonMapper">
<select id="checkTableExistsWithSchema"
resultType="java.lang.Integer">
SELECT
COUNT(1)
FROM information_schema.tables
WHERE
table_schema = #{tableSchema}
AND table_name = #{tableName}
</select>
<select id="checkTableExistsWithShow"
resultType="java.util.Map">
show tables like #{tableName}
</select>
</mapper>
通用service:
package com.chenp.demo.service;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import com.yangzhilong.mapper.CommonMapper;
import lombok.extern.slf4j.Slf4j;
@Service
@Slf4j
public class CommonService {
private static final String TABLE_SCHEMA = "cp_user";
@Autowired
private CommonMapper commonMapper;
/**
* 检查表是否存在
* @param tableName
* @return
*/
public boolean checkTableExists(String tableName) {
try {
Integer count = commonMapper.checkTableExistsWithSchema(TABLE_SCHEMA, tableName);
return count == 1;
} catch (Exception e) {
log.error("使用information_schema检测表失败", e);
Map<String, String> list = commonMapper.checkTableExistsWithShow(tableName);
if(!CollectionUtils.isEmpty(list)) {
return true;
}
}
return false;
}
}
版权声明:本文为Mr_Chp原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。