mysql表结构查询

方法1

这种方法能够查询到一些表信息，但是不够详细

PreparedStatement pst = connection.prepareStatement("select * from t_gateway_logs where 1=2");
ResultSet rs = pst.executeQuery();
ResultSetMetaData rsd = rs.getMetaData();
for(int i = 0; i < rsd.getColumnCount(); i++) {
    System.out.print("java类型："+rsd.getColumnClassName(i + 1));
    System.out.print("  数据库类型:"+rsd.getColumnTypeName(i + 1));
    System.out.print("  字段名称:"+rsd.getColumnName(i + 1));
    System.out.print("  字段长度:"+rsd.getColumnDisplaySize(i + 1));
    System.out.print("  是否为空:"+rsd.isNullable(i + 1));
    System.out.print("  是否自增:"+rsd.isAutoIncrement(i + 1));
    System.out.println();
}

方法二

查询出来的内容非常多。

查询出库中所有表

ResultSet tableRet = connection.getMetaData().getTables(null, "%","%",new String[]{"TABLE"});
while(tableRet.next()) {
    System.out.println(tableRet.getString("TABLE_NAME"));
}

查询某个表的主键信息

ResultSet pkInfo = connection.getMetaData().getPrimaryKeys(null, "%", "test_table");
while (pkInfo.next()) {
    System.out.println("TABLE_CAT:" + pkInfo.getString("TABLE_CAT") + " ");
    System.out.println("TABLE_NAME:" + pkInfo.getString("TABLE_NAME") + " ");
    System.out.println("COLUMN_NAME:" + pkInfo.getString("COLUMN_NAME") + " ");
    System.out.println("PK_NAME:" + pkInfo.getString("PK_NAME") + " ");
    System.out.println("TABLE_SCHEM:" + pkInfo.getString("TABLE_SCHEM") + " ");
    System.out.println("KEY_SEQ:" + pkInfo.getString("KEY_SEQ") + " ");
}

查询某个表的详细信息

ResultSet colRet = connection.getMetaData().getColumns(null, "%", "test_table", "%");
while (colRet.next()) {
    System.out.println(colRet.getString("TABLE_CAT"));
    System.out.println(colRet.getString("TABLE_SCHEM"));
    System.out.println(colRet.getString("TABLE_NAME"));
    System.out.println(colRet.getString("COLUMN_NAME"));
    System.out.println(colRet.getString("DATA_TYPE"));
    System.out.println(colRet.getString("TYPE_NAME"));
    System.out.println(colRet.getInt("COLUMN_SIZE"));
    System.out.println(colRet.getInt("BUFFER_LENGTH"));
    System.out.println(colRet.getInt("DECIMAL_DIGITS"));
    System.out.println(colRet.getInt("NUM_PREC_RADIX"));
    System.out.println(colRet.getInt("NULLABLE"));
    System.out.println(colRet.getString("REMARKS"));
    System.out.println(colRet.getString("COLUMN_DEF"));
    System.out.println(colRet.getInt("SQL_DATA_TYPE"));
    System.out.println(colRet.getInt("SQL_DATETIME_SUB"));
    System.out.println(colRet.getInt("CHAR_OCTET_LENGTH"));
    System.out.println(colRet.getInt("ORDINAL_POSITION"));
    System.out.println(colRet.getBoolean("IS_NULLABLE"));
    System.out.println(colRet.getInt("SCOPE_CATALOG"));
    System.out.println(colRet.getInt("SCOPE_SCHEMA"));
    System.out.println(colRet.getInt("SCOPE_TABLE"));
    System.out.println(colRet.getInt("SOURCE_DATA_TYPE"));
    System.out.println(colRet.getBoolean("IS_AUTOINCREMENT"));
    System.out.println(colRet.getBoolean("IS_GENERATEDCOLUMN"));
}