#实用#封装一个获取数据库信息的工具类

package com.lyr.autocode_v3.common.core.db.entity;

import lombok.Data;

import java.util.Collections;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @Author lyr
 * @create 2020/8/3 1:53
 */
@Data
public class Table {
    /**
     * 数据库表名字
     */
    private String tableName;
    /**
     * 主键( 如果是联合主键， 用 逗号 分割)
     */
    private String keys;
    /**
     * 数据库生成的代码名字
     */
    private String javaClassName;
    private String tableComment;
    // /**
    //  * 主键
    //  */
    // private String primaryKey;
    /**
     * 列属性
     */
    private List<Column> columns;

    /**
     * table 创建时间
     */
    private String tableCreateDate;

    public List<Column> getColumnKeys() {
        if (null == columns) {
            return Collections.emptyList();
        }
        return columns.stream()
                .filter(column -> column.getColumnKey() != null)
                .collect(Collectors.toList());
    }
}

package com.lyr.autocode_v3.common.core.db.entity;

import lombok.Data;

/**
 * @Author lyr
 * @create 2020/8/3 1:54
 */
@Data
public class Column {
    /**
     * 数据库列名字
     */
    private String columnName;
    /**
     * 数据库的类型
     */
    private String javaPropertyName;
    private String columnType;
    /**
     * 类型
     */
    private String javaClassType;
    /**
     * 是否是主键
     */
    private String columnKey;

    /**
     * 备注
     */
    private String comment;

    /**
     * defaultProperty of mysql
     */
    private String defaultProperty;

}

mapper 映射

package com.lyr.autocode_v3.common.core.db.mapper;

import com.lyr.autocode_v3.common.core.db.entity.Table;
import com.lyr.autocode_v3.common.core.db.util.StringUtils;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

/**
 * @Author lyr
 * @create 2020/8/13 13:12
 */
public class TableMapper implements RowMapper<Table> {

    public static final String QUERY_ALL_TABLE = "select `table_name`, `create_time`,`table_comment` from information_schema.tables where table_schema=?";

    @Override
    public Table mapRow(ResultSet resultSet, int i) throws SQLException {
        Table table = new Table();
        table.setTableComment(resultSet.getString("table_comment"));
        table.setTableCreateDate(resultSet.getString("create_time"));
        String tableName = resultSet.getString("table_name");
        //数据库表的名字
        table.setTableName(tableName);
        //数据库下划线转  java 的 类名字
        table.setJavaClassName(getJavaClassName(tableName));

        return table;
    }

    /**
     * 数据库的常用前缀
     */
    private static final List<String> PREFIX_LIST = Arrays.asList("t_","tb_","tbl_","v_");
    private static String getJavaClassName(String tableName) {
        String tmp = StringUtils.removePrefix(tableName, PREFIX_LIST);
        return StringUtils.underlineToBigHump(tmp);
    }
}

package com.lyr.autocode_v3.common.core.db.mapper;

import com.lyr.autocode_v3.common.core.db.entity.Column;

import com.lyr.autocode_v3.common.core.db.util.StringUtils;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @Author lyr
 * @create 2020/8/13 13:33
 */
public class ColumnMapper implements RowMapper<Column> {
    public static final String QUERY_ALL_COLUMN = "show full fields from ";
    @Override
    public Column mapRow(ResultSet resultSet, int i) throws SQLException {
        String columnName = resultSet.getString("Field");
        String comment = resultSet.getString("Comment");
        String type = resultSet.getString("Type");
        String key = resultSet.getString("Key");
        String defaultProperty = resultSet.getString("Default");
        Column column = new Column();
        column.setColumnKey(key);
        column.setComment(comment);
        column.setColumnName(columnName);
        column.setColumnType(type);
        column.setDefaultProperty(defaultProperty);
        //
        column.setJavaPropertyName(getJavaPropertyName(columnName));
        column.setJavaClassType(getJavaClassType(type));
        return column;
    }

    /**
     * 获取属性名字
     * @param columnName
     * @return
     */
    private String getJavaPropertyName(String columnName ) {
        return StringUtils.underlineToSmallHump(columnName);
    }

    /**
     * 获取属性类型
     * @param t 类型
     * @return 属性类型
     */
    private String getJavaClassType(String t) {
        if(null==t||t.isEmpty()) {
            return "Object";
        }
        String type = t.toLowerCase();
        if(type.startsWith("varchar")) {
            return "String";
        }else if(type.startsWith("int")) {
            return "Long";
        }else if(type.startsWith("char")) {
            return "String";
        }else if(type.startsWith("float")) {
            return "java.math.BigDecimal";
        }else if(type.startsWith("double")) {
            return "java.math.BigDecimal";
        }else if(type.startsWith("bigint")) {
            return "Long";
        }else if(type.startsWith("decimal")) {
            return "java.math.BigDecimal";
        }else if(type.startsWith("mediumint")||type.startsWith("tinyint")) {
            return "Integer";
        }else if(type.startsWith("date")||
                type.startsWith("year")||
                type.startsWith("timestamp")
                ||type.startsWith("datetime")
        ) {
            return "java.util.Date";
        }else if(type.startsWith("text")) {
            return "String";
        }else{
            return "String";
        }
    }
}

测试代码

    @Test
    public void printTables() {
        new JdbcTemplate(getDataSource())
                .query(TableMapper.QUERY_ALL_TABLE,new TableMapper(),"blog")
                .forEach(t -> System.out.println(t));
    }


    @Test
    public void printColumns() {
        new JdbcTemplate(getDataSource())
                .query(ColumnMapper.QUERY_ALL_COLUMN+"tbl_user",new ColumnMapper())
                .forEach(t -> System.out.println(t));
    }