【精品】使用druid 获取数据库表的信息

Maven依赖

<dependency>
  <groupId>org.projectlombok</groupId>
  <artifactId>lombok</artifactId>
</dependency>
<dependency>
  <groupId>com.mysql</groupId>
  <artifactId>mysql-connector-j</artifactId>
  <version>8.0.32</version>
</dependency>

<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid</artifactId>
  <version>1.2.20</version>
</dependency>

自定义MySqlSchemaStatVisitor

public class MySQLVisitor extends MySqlSchemaStatVisitor {
    @Getter
    private List<Item> list = new ArrayList<>();

    @Override
    public boolean visit(SQLColumnDefinition x) {
        Item item = new Item();
        //获取列名
        String columnName = SQLUtils.normalize(x.getName().toString());
        //字段名
        item.setFieldName(columnName);
        //属性
        item.setPropertyName(StringUtil.underline2hump(columnName));
        //字段数据类型
        item.setFieldType(x.getDataType().getName());
        //属性数据类型
        item.setPropertyType(DBUtil.jdbcType2JavaType(x.getDataType().getName()));
        // 注释
        item.setFieldComment(x.getComment().toString().replace("'", ""));

        list.add(item);
        return false;
    }

    @Override
    public boolean visit(MySqlPrimaryKey x) {
        for (Item item : list) {
            final String t = x.getColumns().get(0).toString().replace("`", "");
            if (item.getFieldName().equals(t)) {
                item.setPk(true);
                return false;
            }
        }
        return false;
    }
}

获取数据表数据的工具类

public class GeneratorUtil {

    /**
     * 获取指定表的信息
     * @param tableName
     * @return
     */
    public static TableInfo getTableInfo(String tableName){
        final TableInfo tableInfo = new TableInfo();
        tableInfo.setTableName(tableName);

        //创建表的SQL语句
        String createSQL = null;
        try {
            createSQL = DBUtil.getCreateSQL(tableName);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(createSQL, DbType.mysql);
        SQLStatement sqlStatement = parser.parseStatement();

        MySqlCreateTableStatement mySqlCreateTableStatement = (MySqlCreateTableStatement) sqlStatement;
        //表备注
        tableInfo.setTableComment(mySqlCreateTableStatement.getComment().toString());
        //实体类类名
        tableInfo.setClassName(StringUtil.underlinePart2hump2(tableName));

        MySQLVisitor mySQLVisitor = new MySQLVisitor();
        mySqlCreateTableStatement.accept(mySQLVisitor);
        //字段列表
        tableInfo.setItemList(mySQLVisitor.getList());

        return tableInfo;
    }
}

测试代码

public class App {
    /**
     * 基础包名
     */
    private static String basePackage = "com.tiku";

    public static void main(String[] args) throws Exception {
        //获取表信息
        final TableInfo tableInfo = GeneratorUtil.getTableInfo("tb_question_type");
        System.out.println(tableInfo);
    }
}

• 待测数据库

• 结果

相关类

Item.java

@Getter
@Setter
@ToString
public class Item {
    /**
     * 字段名
     */
    private String fieldName;
    /**
     * 字段注释
     */
    private String fieldComment;
    /**
     * JDBC类型
     */
    private String fieldType;
    /**
     * 是否是主键
     */
    private boolean pk;
    /**
     * 属性名
     */
    private String propertyName;
    /**
     * Java类型
     */
    private String propertyType;
}

TableInfo.java

@Getter
@Setter
public class TableInfo {
    /**
     * 表名
     */
    private String tableName;
    /**
     * 表名对应的实体类的类名
     */
    private String className;
    /**
     * 表名注释
     */
    private String tableComment;
    /**
     * 字段属性列表
     */
    private List<Item> itemList;
}

DBUtil.java

public class DBUtil {
    /**
     * 获取连接
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        String url = "jdbc:mysql://localhost:3306/db_tiku?useSSL=false&serverTimezone=UTC&characterEncoding=utf8&useUnicode=true";
        return DriverManager.getConnection(url, "root", "root");
    }

    /**
     * 释放资源
     * @param conn
     * @param stmt
     * @param rs
     */
    public static void closeAll(Connection conn, Statement stmt, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            } finally {
                if (conn != null) {
                    try {
                        conn.close();
                    } catch (SQLException ex) {
                        ex.printStackTrace();
                    }
                }
            }
        }
    }

    /**
     * 获取表注释
     * @param tableName
     * @return
     * @throws SQLException
     */
    public static String getCreateSQL(String tableName) throws SQLException {
        String sql = null;
        Connection conn = getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SHOW CREATE TABLE " + tableName);
        if (rs.next()) {
            //获取创建表的SQL语句
            sql = rs.getString(2);
        }
        closeAll(conn, stmt, rs);
        return sql;
    }

    /**
     * 将JDBC数据类型转换成对应的Java类型
     * @param type
     * @return
     */
    public static String jdbcType2JavaType(String type) {
        type = type.toUpperCase();
        String javaType = null;
        if (type.contains("CHAR") || type.contains("TEXT") || type.contains("ENUM") || type.contains("SET")) {
            javaType = "java.lang.String";
        } else if ("DATE".equals(type)) {
            javaType = "java.time.LocalDate";
        } else if ("DATETIME".equals(type)) {
            javaType = "java.time.LocalDateTime";
        } else if (type.contains("BIGINT")) {
            javaType = "java.lang.Long";
        } else if (type.contains("TINYINT")) {
            javaType = "java.lang.Integer";
        } else if (type.contains("INT")) {
            javaType = "java.lang.Integer";
        } else if (type.contains("BIT")) {
            javaType = "java.lang.Boolean";
        } else if (type.contains("FLOAT") || type.contains("REAL")) {
            javaType = "java.lang.Double";
        } else if (type.contains("DOUBLE") || type.contains("NUMERIC")) {
            javaType = "java.lang.Double";
        } else if (type.contains("BLOB") || type.contains("BINARY")) {
            javaType = "byte[]";
        } else if (type.contains("JSON")) {
            javaType = "java.lang.String";
        } else if (type.contains("DECIMAL")) {
            javaType = "java.math.BigDecimal";
        } else {
            System.out.println("type:" + type);
        }
        return javaType;
    }
}

StringUtil.java

public class StringUtil {

    /**
     * 首字母小写
     * @param str
     * @return
     */
    public static String first2LowerCase(String str) {
        return Character.isLowerCase(str.charAt(0)) ? str : Character.toLowerCase(str.charAt(0)) + str.substring(1);
    }

    /**
     * 首字母大写
     * @param s
     * @return
     */
    public static String first2UpperCase(String s) {
        return Character.isUpperCase(s.charAt(0)) ? s : Character.toUpperCase(s.charAt(0)) + s.substring(1);
    }

    /**
     * 下划线转驼峰
     * answer_sheet_id---answerSheetId
     * @param str
     * @return
     */
    public static String underline2hump(String str) {
        if (str.contains("_")) {
            while (str.contains("_")) {
                StringBuffer sb = new StringBuffer();
                int index = str.indexOf("_");
                for (int i = 0; i < str.length(); i++) {//把下划线后面的首字母变为大写
                    if (i == index + 1 && !(str.charAt(i) >= 65 && str.charAt(i) <= 90)) {
                        sb.append((char) (str.charAt(i) - 32));
                        continue;
                    }
                    sb.append(str.charAt(i));
                }
                str = sb.toString().replaceFirst("_", "");
            }
        }
        return str;
    }

    /**
     * 下划线转驼峰，不包含第一个下划线及其之前的部分
     * tb_answer_sheet_item  ---  AnswerSheetItem
     * @param str
     * @return
     */
    public static String underlinePart2hump2(String str) {
        String tb = str.trim();
        String temp = first2UpperCase(str);//user--User
        if (tb.indexOf('_') > 0) {//tb_user ---- User
            temp = first2UpperCase(tb.substring(tb.indexOf('_') + 1));
        }//User_sth
        return underline2hump(temp);//user_sth----UserSth
    }


    public static void main(String[] args) {
        System.out.println(underline2hump("answer_sheet_id"));
        System.out.println(underlinePart2hump2("tb_answer_sheet_item"));
    }
}