目录
数据源之DataSource遇见MetaData
最近有个需求，需要获取一个库下所有的表或者根据条件模糊匹配表，同时还需要获取某张表下所有的字段信息，于是乎研究了数据源DataSource对象，发现了一些小秘密，在这里给大家分享一下，如有不对的地方欢迎评论指正或者关于DataSource的其他小秘密也欢迎在评论区留言，如在开发中有用的我将同步采纳并更新博客，谢谢！
环境准备
maven依赖如下：
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.12</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
</dependencies>
1、从DataBaseMetaData中获取所有表及表的所有字段信息
public static void main(String[] args) {
try (Connection connection = getDataSource().getConnection()) {
List<String> allTables = getAllTables(connection, null);
List<String> primaryKeys = getPrimaryKeys(connection, "tb_user_info");
List<Map<String, String>> allColumns = getAllColumns(connection, "tb_user_info");
System.out.println("all tables:");
allTables.stream().forEach(table -> System.out.println(table + "\n"));
System.out.println("table's primaryKeys:");
primaryKeys.stream().forEach(primaryKey -> System.out.println(primaryKey + "\n"));
System.out.println("table's all columns:");
allColumns.stream().forEach(map -> {
map.forEach((k, v) -> {
System.out.println(k + ":" + v + "\t");
});
System.out.println("\n");
});
} catch (SQLException e) {
e.printStackTrace();
}
}
public static DataSource getDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false");
dataSource.setUsername("root");
dataSource.setPassword("123456");
return dataSource;
}
输出结果：
all tables:
tb_user_info
table's primaryKeys:
auto_id
login_name
user_id
table's all columns:
isAutoIncrement:YES
isNullAble:NO
dataType:4
columnSize:10
nullAble:0
tableCat:test
typeName:INT
columnDef:null
ordinalPosition:1
remarks:
tableName:tb_user_info
columnName:auto_id
isAutoIncrement:NO
isNullAble:NO
dataType:4
columnSize:10
nullAble:0
tableCat:test
typeName:INT
columnDef:null
ordinalPosition:2
remarks:用户id
tableName:tb_user_info
columnName:user_id
...
1.1、如何获取一个库下的所有表
获取某个库下的所有表：ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String types[]) throws SQLException;
/**
* get all tables from DataSource - Connection - DatabaseMetaData
* use %tableNamePattern% to get fuzzy matching all tables
* eg: ResultSet tables2 = metaData.getTables(null,null,"%tableNamePattern%",new String[]{"TABLE"});
*
* @param connection
* @param tableNamePattern
* @return
* @throws SQLException
*/
public static List<String> getAllTables(Connection connection, String tableNamePattern) throws SQLException {
DatabaseMetaData metaData = connection.getMetaData();
ResultSet tables = metaData.getTables(null, null, tableNamePattern, new String[]{"TABLE"});
List<String> tableList = new ArrayList<>();
while (tables.next()) {
String tableName = tables.getString(3);
tableList.add(tableName);
}
return tableList;
}
1.2、如何获取一个表中的所有主键
获取某张表下的所有主键：ResultSet getPrimaryKeys(String catalog, String schema,String table) throws SQLException;
/**
* get all PrimaryKeys from DataSource - Connection - DatabaseMetaData
*
* @param connection
* @param tableNamePattern
* @return
* @throws SQLException
*/
public static List<String> getPrimaryKeys(Connection connection, String tableNamePattern) throws SQLException {
DatabaseMetaData metaData = connection.getMetaData();
ResultSet primaryKeys = metaData.getPrimaryKeys(null, null, tableNamePattern);
List<String> primaryKeyList = new ArrayList<>();
while (primaryKeys.next()) {
String columnName = primaryKeys.getString("COLUMN_NAME");
primaryKeyList.add(columnName);
}
return primaryKeyList;
}
1.3、如何获取一个表中所有的字段及字段定义信息
获取某张表下的所有column信息：ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)throws SQLException;
/**
* get all columns from table by DataSource - Connection - DatabaseMetaData
*
* @param connection
* @param tableNamePattern
* @return
* @throws SQLException
*/
public static List<Map<String, String>> getAllColumns(Connection connection, String tableNamePattern) throws SQLException {
DatabaseMetaData metaData = connection.getMetaData();
ResultSet columns = metaData.getColumns(null, null, tableNamePattern, null);
List<Map<String, String>> columnList = new ArrayList<>();
while (columns.next()) {
// 可自定义column对象用于存储列信息
Map<String, String> columnMap = new HashMap<>();
String tableCat = columns.getString("TABLE_CAT"); // 库名 schema
columnMap.put("tableCat", tableCat);
String tableName = columns.getString("TABLE_NAME"); // 表名
columnMap.put("tableName", tableName);
String columnName = columns.getString("COLUMN_NAME"); // 列名
columnMap.put("columnName", columnName);
String dataType = columns.getString("DATA_TYPE"); // 数据类型
columnMap.put("dataType", dataType);
String typeName = columns.getString("TYPE_NAME"); // 列的数据类型：INT、VARCHAR
columnMap.put("typeName", typeName);
String columnSize = columns.getString("COLUMN_SIZE"); // 列的数据类型的大小： 1 10 32
columnMap.put("columnSize", columnSize);
String nullAble = columns.getString("NULLABLE"); // 允许为空：0 不允许 1 允许
columnMap.put("nullAble", nullAble);
String remarks = columns.getString("REMARKS"); // 备注
columnMap.put("remarks", remarks);
String columnDef = columns.getString("COLUMN_DEF"); // 列的默认值
columnMap.put("columnDef", columnDef);
String ordinalPosition = columns.getString("ORDINAL_POSITION"); // 表字段创建的顺序
columnMap.put("ordinalPosition", ordinalPosition);
String isNullAble = columns.getString("IS_NULLABLE"); // 表字段是否允许为空："YES":允许为null "NO"：不允许为null
columnMap.put("isNullAble", isNullAble);
String isAutoIncrement = columns.getString("IS_AUTOINCREMENT"); // 是否是自增列 "YES"：是 "NO"：不是
columnMap.put("isAutoIncrement", isAutoIncrement);
columnList.add(columnMap);
}
return columnList;
}
1万+
