cn.bootx.table.modify.mysql.handler.MySqlTableModifyDao Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of table-modify-mysql Show documentation
There is a newer version: 1.5.5
package cn.bootx.table.modify.mysql.handler;

import cn.bootx.table.modify.mysql.entity.MySqlTableColumn;
import cn.bootx.table.modify.mysql.entity.MySqlTableIndex;
import cn.bootx.table.modify.mysql.entity.MySqlTableInfo;
import cn.hutool.core.util.StrUtil;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 数据库操作类
 * @author xxm
 * @since 2023/8/3
 */
@Slf4j
@Repository
@RequiredArgsConstructor
public class MySqlTableModifyDao {
    private final JdbcTemplate jdbcTemplate;

    /**
     * 新建表
     */
    @SuppressWarnings("SqlSourceToSinkFlow")
    public void createTable(String createTableSql){
        jdbcTemplate.execute(createTableSql);
    }

    /**
     * 更新表
     */
    @SuppressWarnings("SqlSourceToSinkFlow")
    public void modifyTable(String modifyTableSql){
        jdbcTemplate.execute(modifyTableSql);
    }

    /**
     * 查据表名询表信息
     * @param tableName 表结构的map
     * @return MySqlTableInfo
     */
    public boolean existsByTableName(String tableName){
        String sql = "select count(*) from information_schema.tables " +
                "   where table_name = ? and table_schema = (select database())";
        return Boolean.TRUE.equals(jdbcTemplate.queryForObject(sql, new SingleColumnRowMapper<>(Boolean.class), tableName));
    }


    /**
     * 查据表名询表信息
     * @param tableName 表结构的map
     * @return MySqlTableInfo
     */
    public MySqlTableInfo findTableByTableName(String tableName){
        String sql = "SELECT " +
                "    `table_catalog` as tableCatalog, " +
                "    `table_schema` as tableSchema, " +
                "    `table_name` as tableName, " +
                "    `table_type` as tableType, " +
                "    `engine` as engine, " +
                "    `version` as version, " +
                "    `row_format` as rowFormat, " +
                "    `table_rows` as tableRows, " +
                "    `avg_row_length` as avgRowLength, " +
                "    `data_length` as dataLength, " +
                "    `max_data_length` as maxDataLength, " +
                "    `index_length` as indexLength, " +
                "    `data_free` as dataFree, " +
                "    `auto_increment` as autoIncrement, " +
                "    `create_time` as createTime, " +
                "    `update_time` as updateTime, " +
                "    `check_time` as checkTime, " +
                "    `table_collation` as tableCollation, " +
                "    `checksum` as checksum, " +
                "    `create_options` as createOptions, " +
                "    `table_comment` as tableComment " +
                "FROM information_schema.TABLES " +
                "WHERE table_schema = (select database()) and table_name = ?";
        return jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(MySqlTableInfo.class),tableName);
    }

    /**
     * 根据表名查询库中该表的字段结构等信息
     * @param tableName 表结构的map
     * @return 表的字段结构等信息
     */
    public List findColumnByTableName(String tableName){
        String sql = "SELECT " +
                "    `table_schema` AS tableSchema, " +
                "    `table_name` as tableName, " +
                "    `column_name` as columnName, " +
                "    `ordinal_position` as ordinalPosition, " +
                "    `column_default` as columnDefault, " +
                "    `is_nullable` as isNullable, " +
                "    `data_type` as dataType, " +
                "    `character_maximum_length` as characterMaximumLength, " +
                "    `character_octet_length` as characterOctetLength, " +
                "    `numeric_precision` as numericPrecision, " +
                "    `numeric_scale` as numericScale, " +
                "    `character_set_name` as characterSetName, " +
                "    `collation_name` as collationName, " +
                "    `column_type` as columnType, " +
                "    `column_key` as columnKey, " +
                "    `extra` as extra, " +
                "    `privileges` as privileges, " +
                "    `column_comment` as columnComment " +
                "FROM information_schema.COLUMNS  " +
                "WHERE table_schema = (select database()) and table_name = ?";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(MySqlTableColumn.class),tableName);
    }

    /**
     * 查询当前表存在的索引(除了主键索引primary)
     * @param tableName 表名
     * @return 索引名列表
     */
    public List findIndexByTableName(String tableName){
        String sql = "SELECT " +
                "`index_name` as indexName, " +
                "`index_type` as indexType, " +
                "`index_comment` as indexComment, " +
                "`column_name` as columnName, " +
                "`non_unique` as nonUnique, " +
                "`seq_in_index` as seqInIndex  " +
                "FROM information_schema.statistics  " +
                "WHERE table_schema = ( SELECT DATABASE ())  " +
                "AND lower( index_name ) != 'primary'  " +
                "AND table_name = ?";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(MySqlTableIndex.class),tableName);
    }


    /**
     * 查询当前表存在的主键索引
     * @param tableName 表名
     * @return 索引名列表
     */
    public List findPrimaryIndexByTableName(String tableName){
        String sql = "SELECT " +
                "`index_name` as indexName, " +
                "`index_type` as indexType, " +
                "`index_comment` as indexComment, " +
                "`column_name` as columnName, " +
                "`non_unique` as nonUnique, " +
                "`seq_in_index` as seqInIndex  " +
                "FROM information_schema.statistics  " +
                "WHERE table_schema = ( SELECT DATABASE ())  " +
                "AND lower( index_name ) = 'primary'  " +
                "AND table_name = ?";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(MySqlTableIndex.class),tableName);
    }

    /**
     * 根据表名删除表
     * @param tableName 表名
     */
    @SuppressWarnings("SqlSourceToSinkFlow")
    public void dropTableByName(String tableName){
        String sql = StrUtil.format("DROP TABLE IF EXISTS `{}`;",tableName);
        jdbcTemplate.execute(sql);
    }
}