工作之余,用java写了一个导出数据库结构的小工具,附上核心代码
Table.java
- package org.dev.livvy.db;
- import java.util.List;
- /**
- * Created with IntelliJ IDEA.
- * User: GuoZheng
- * Date: 13-1-10
- * Time: 下午2:02
- * To change this template use File | Settings | File Templates.
- */
- public class Table {
- /**
- * 表名称
- */
- private String name;
- /**
- * 存储空间(库名)
- */
- private String space;
- /**
- * 表中的列
- */
- List<Column> columns;
- public String getName() {
- return name;
- }
- public void setName(String name) {
- this.name = name;
- }
- public String getSpace() {
- return space;
- }
- public void setSpace(String space) {
- this.space = space;
- }
- public List<Column> getColumns() {
- return columns;
- }
- public void setColumns(List<Column> columns) {
- this.columns = columns;
- }
- @Override
- public String toString() {
- return "Table{" +
- "name='" + name + '\'' +
- ", space='" + space + '\'' +
- ", columns=" + columns +
- '}';
- }
- }
Column.java
- package org.dev.livvy.db;
- /**
- * Created with IntelliJ IDEA.
- * User: GuoZheng
- * Date: 13-1-10
- * Time: 下午2:09
- * To change this template use File | Settings | File Templates.
- */
- public class Column {
- /**
- * 表名称
- */
- private String tableName;
- /**
- * 列名称(字段名称)
- */
- private String name;
- /**
- * 是否主键
- */
- private int isPk;
- /**
- * 默认值
- */
- private String value;
- /**
- * 是否为空
- */
- private int isNotNull;
- /**
- * 数据类型
- */
- private String type;
- /**
- * 数据长度
- */
- private int length;
- /**
- * 代码类型
- */
- private int codeType;
- public String getTableName() {
- return tableName;
- }
- public void setTableName(String tableName) {
- this.tableName = tableName;
- }
- public String getName() {
- return name;
- }
- public void setName(String name) {
- this.name = name;
- }
- public int getPk() {
- return isPk;
- }
- public void setPk(int pk) {
- isPk = pk;
- }
- public String getValue() {
- return value;
- }
- public void setValue(String value) {
- this.value = value;
- }
- public int getNotNull() {
- return isNotNull;
- }
- public void setNotNull(int notNull) {
- isNotNull = notNull;
- }
- public String getType() {
- return type;
- }
- public void setType(String type) {
- this.type = type;
- }
- public int getLength() {
- return length;
- }
- public void setLength(int length) {
- this.length = length;
- }
- public int getCodeType() {
- return codeType;
- }
- public void setCodeType(int codeType) {
- this.codeType = codeType;
- }
- @Override
- public String toString() {
- return "Column{" +
- "tableName='" + tableName + '\'' +
- ", name='" + name + '\'' +
- ", isPk=" + isPk +
- ", value='" + value + '\'' +
- ", isNotNull=" + isNotNull +
- ", type='" + type + '\'' +
- ", length=" + length +
- ", codeType=" + codeType +
- '}';
- }
- }
DBAnalysis.java数据库分析类
- package org.dev.livvy.db;
- import java.sql.*;
- import java.util.ArrayList;
- import java.util.Iterator;
- import java.util.List;
- /**
- * Created with IntelliJ IDEA.
- * User: GuoZheng
- * Date: 13-1-10
- * Time: 上午11:18
- * To change this template use File | Settings | File Templates.
- */
- public class DBAnalysis {
- private Connection connection;
- private DBAnalysis(String connStr, String db, String username, String password) throws ClassNotFoundException, SQLException {
- Class.forName("com.mysql.jdbc.Driver");
- connection = DriverManager.getConnection(connStr + db, username, password);
- }
- private static DBAnalysis instance = null;
- private static DBAnalysis getInstance(String connStr, String db, String username, String password) throws SQLException, ClassNotFoundException {
- if (instance == null) {
- instance = new DBAnalysis(connStr, db, username, password);
- }
- return instance;
- }
- private static Connection getConnection(String connStr, String db, String username, String password) throws SQLException, ClassNotFoundException {
- return getInstance(connStr, db, username, password).connection;
- }
- /**
- * 获取表的主键
- * @param conn 数据库连接
- * @param tableName 表名
- * @return 表中的主键
- * @throws SQLException
- */
- private static List getPks(Connection conn, String tableName) throws SQLException {
- List pks = new ArrayList();
- ResultSet rsPks = conn.getMetaData().getPrimaryKeys(null, null, tableName);
- while (rsPks.next()) {
- pks.add(rsPks.getString("COLUMN_NAME"));
- }
- rsPks.close(); //关闭
- return pks;
- }
- /**
- * 获取所有的列信息
- * @param conn 数据库连接
- * @param tableName 表名
- * @return 列的详细信息
- * @throws SQLException
- */
- private static List<Column> getColumns(Connection conn,String tableName) throws SQLException {
- List<Column> cols = new ArrayList<Column>();
- //获取这个表的主键 ,并存储在list中
- List pks = getPks(conn,tableName);
- Statement stmt = conn.createStatement();
- ResultSet rs = stmt.executeQuery("select * from " + tableName); //此处需要优化 limit 1 top 1 rownum <= 1 根据不同数据库
- ResultSetMetaData rsCols = rs.getMetaData();
- int columnCount = rsCols.getColumnCount();
- for (int i = 1; i <= columnCount; i++) {
- Column col = new Column();
- col.setTableName(rsCols.getTableName(i));
- col.setName(rsCols.getColumnName(i));
- col.setType(rsCols.getColumnTypeName(i));
- col.setPk(pks.contains(rsCols.getColumnName(i)) ? 1 : 0);
- col.setLength(rsCols.getColumnDisplaySize(i));
- col.setNotNull(rsCols.isNullable(i) == 0 ? 1 : 0);
- cols.add(col);
- }
- rs.close();
- stmt.close();
- return cols;
- }
- /**
- * 获取所有表信息
- * @param connStr 数据库连接字符串
- * @param db 连接的库
- * @param username 数据库用户名
- * @param password 数据库密码
- * @return 库中表信息
- * @throws SQLException
- * @throws ClassNotFoundException
- */
- public static List<Table> collectAllTables(String connStr, String db, String username, String password) throws SQLException, ClassNotFoundException {
- Connection conn = getConnection(connStr, db, username, password);
- return collectAllTables(conn,db);
- }
- /**
- * 获取所有表信息
- * @param conn 数据库连接 s
- * @param db 数据库
- * @return 库中表信息
- * @throws SQLException
- */
- public static List<Table> collectAllTables(Connection conn,String db) throws SQLException {
- DatabaseMetaData dmd = conn.getMetaData();
- //获取库中的所有表
- ResultSet rsTables = dmd.getTables(null, null, null, new String[]{"TABLE"});
- List<Table> tables = new ArrayList<Table>();
- //将表存到list中
- while (rsTables.next()) {
- Table tb = new Table();
- tb.setSpace(db);
- //获取表名称
- String tbName = rsTables.getString("TABLE_NAME");
- tb.setName(tbName);
- //获取表中的字段及其类型
- List<Column> cols = getColumns(conn,tbName);
- tb.setColumns(cols);
- tables.add(tb);
- }
- rsTables.close();
- return tables; //connection未关闭
- }
- }