工作中經(jīng)常會(huì)出現(xiàn)不同的環(huán)境之間數(shù)據(jù)庫的差異趣苏，然而這些差異并不容易通過肉眼察覺拦惋，我們可以通過程序來幫助我們對比媚送。如下舉例MySQL數(shù)據(jù)庫下測試環(huán)境和集成環(huán)境的差異
主要通過INFORMATION_SCHEMA(DSL)來查詢數(shù)據(jù)庫的結(jié)構(gòu)
關(guān)鍵代碼
- 獲取數(shù)據(jù)庫的所有表
private static List<String> getTables(JdbcTemplate jdbcTemplate){
String getTablesSql = "SELECT table_name as tableName FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE()";
List<Map<String, Object>> data = jdbcTemplate.queryForList(getTablesSql);
List<String> tables = new ArrayList<>();
for (Map<String, Object> map : data){
tables.add((String) map.get("tableName"));
}
return tables;
}
- 根據(jù)表名獲取字段信息
private static List<String> getColumns(JdbcTemplate jdbcTemplate, String tableName){
String getColumnSql = "select column_name columnName from information_schema.columns where table_schema =DATABASE() and table_name = '"+tableName+"'";
List<Map<String, Object>> data = jdbcTemplate.queryForList(getColumnSql);
List<String> columns = new ArrayList<>();
for (Map<String, Object> map : data){
columns.add((String) map.get("columnName"));
}
return columns;
}
- 根據(jù)需要傳入jdbcTemplate數(shù)據(jù)源即可完成需要的對比
