databaseutil.java

TestDataBuilder是一个采用Java编写的

            conn.close();
            conn = null;
        }
    }

    public synchronized void setBreak(boolean break_){
        this.break_ = break_;
    }
    
    /**
     * 得到一个DBMS中的所有数据库信息.及其中的表信息.
     * <p>
     * <code>getCatalogs</code>
     * </p>
     * 
     * @param conn
     * @return
     * @throws SQLException
     * @author LiuXiaojie 2007-4-6
     */
    public synchronized List getCatalog(Connection conn, int level) throws SQLException{
        List catalogs = new ArrayList();
        DatabaseMetaData dbMetadata =  conn.getMetaData();
        ResultSet catalogResult = dbMetadata.getCatalogs();
        
        ResultSetMetaData resultMetaData = catalogResult.getMetaData();
        
        // 依次取出所有的数据库信息.
        while(resultMetaData.getColumnCount() > 0){
            if(!catalogResult.next() || this.getBreak()){
                this.setBreak(false);
                break;
            }
            
            String catalogName = catalogResult.getString(1);
            Database db =  getDBMetaInfo(dbMetadata, catalogName, level);
            catalogs.add(db);            
        }
        return catalogs;
    }

    public synchronized Database getCatalog(Connection conn, String dbName, int level) throws SQLException{
        DatabaseMetaData dbMetadata =  conn.getMetaData();
        Database db =  getDBMetaInfo(dbMetadata, dbName, level);
        return db;
    }

    public static boolean isOracleDriver(DatabaseMetaData dbMetadata) throws SQLException{
        if(dbMetadata != null){
            String temp = dbMetadata.getDatabaseProductName().toUpperCase();
            return temp.contains("ORACLE");
        }
        return false;
    }
    
    public static boolean isHSQLDriver(DatabaseMetaData dbMetadata) throws SQLException{
        if(dbMetadata != null){
            String temp = dbMetadata.getDatabaseProductName().toUpperCase();
            return temp.contains("HSQL");
        }
        return false;
    }
    
    public static boolean isSQLServerDriver(DatabaseMetaData dbMetadata) throws SQLException{
        if(dbMetadata != null){
            String temp = dbMetadata.getDatabaseProductName().toUpperCase();
            return temp.contains("MICROSOFT SQL SERVER");
        }
        return false;
    }
    
    public static boolean isMysqlDriver(DatabaseMetaData dbMetadata) throws SQLException{
        if(dbMetadata != null){
            String temp = dbMetadata.getDatabaseProductName().toUpperCase();
            return temp.contains("MYSQL");
        }
        return false;
    }
    
    /**
     * 取出一个库的信息.
     * <p>
     * <code>getDBMetaInfo</code>
     * </p>
     * 
     * @param dbMetadata
     * @param catalogName
     * @param level
     * @return
     * @throws SQLException
     * @author LiuXiaojie 2007-7-13
     */
    private Database getDBMetaInfo(DatabaseMetaData dbMetadata, String catalogName, int level) throws SQLException {
        
        String databaseProductName = dbMetadata.getDatabaseProductName().toUpperCase();
        if(logger.isDebugEnabled()){
            logger.debug("isMysqlDriver.DatabaseProductName:" + databaseProductName);
        }
        
        Database db = new Database();
        db.setDbName(catalogName);
        
        if(level > LEVEL_DB){
            List<Table> tables = new ArrayList<Table>();
            db.setTables(tables); 
            String schema = null;
            if(isOracleDriver(dbMetadata)){
                schema = this.getConfig().getUserName().toUpperCase();
                db.setDbName("ORCL=>" + schema);
            }else if(isHSQLDriver(dbMetadata)){
            	schema = getHSQLDBName(dbMetadata.getURL());
            	db.setDbName(schema);
            }

            if(logger.isDebugEnabled()){
                logger.debug(String.format("&&&&&&&&&&&&&&&&  开始读取数据库[%s]的元信息&&&&&&&&&&&&&&&&&&&&&&&&&", db.getDbName()));
            }
            
            ResultSet tableResult = dbMetadata.getTables(catalogName, 
                            schema, null,
                            new String[]{Table.TABLE});
            // 依次取出一个库中的所有表的信息.
            while(tableResult != null) {
                if(!tableResult.next()){
                    break;
                }
                try{
                    Table table = new Table();
                    String type = tableResult.getString("TABLE_TYPE");

                    table.setType(type);
                    
                    String tableName = tableResult.getString("TABLE_NAME");
                    table.setTableName(tableName);
                    if(logger.isDebugEnabled()){
                        logger.debug(String.format("================ 开始读取表[%s]的元信息. ============", table.getTableName()));
                    }
                    // 添加主键字段。。。
                    ResultSet primaryKeyRs = dbMetadata.getPrimaryKeys(catalogName, null, tableName);
                    while(primaryKeyRs.next()){
                        PrimaryKey primaryKey = table.getPrimaryKey();
                        if(primaryKey == null){
                            primaryKey = new PrimaryKey(primaryKeyRs.getString("PK_NAME"));
                            table.setPrimaryKey(primaryKey);
                        }
                        primaryKey.addField(primaryKeyRs.getString("COLUMN_NAME"));
                    }
                    
                    // foreignKey
                    ResultSet fkRs = dbMetadata.getImportedKeys(null, null, tableName);

                    while(fkRs.next()){
                        String refTableName = fkRs.getString("PKTABLE_NAME");
                        table.addImportedTable(refTableName);
                        ForeignKey foreignKey = table.getForeignKeyByRefTable(refTableName);
                        foreignKey.setFkName(fkRs.getString("FK_NAME"));
                        foreignKey.addField(fkRs.getString("FKCOLUMN_NAME"), fkRs.getString("PKCOLUMN_NAME"));
                    }
                   
                    ResultSet refFkRs = dbMetadata.getExportedKeys(null, null, tableName);
                    while(refFkRs.next()){
                        String refedTableName = refFkRs.getString("FKTABLE_NAME");
                        table.addExportedTable(refedTableName);
                    }

                    tables.add(table);                 
                    if(level > LEVEL_TABLE){
                        
                        ResultSet columnResult = dbMetadata.getColumns(catalogName, null, tableName, null);
                        // 依次取出一个表中的所有列的信息.
                        while(columnResult.next()){
                            try{
                                Column column = convert2Column(columnResult, dbMetadata);   
                                table.getColumns().add(column);
                            }catch(Exception ex){
                                ex.printStackTrace();
                            }
                        }
                    }// end of if(level > LEVEL_TABLE){
                }catch(Exception ex) {
                    ex.printStackTrace();
                }
            } // end of while(true) {
         }// end of if(level > LEVEL_DB){
        return db;
    }

    public static String getHSQLDBName(String url){
    	String name = "hsqldb";
    	int beginIndex = url.lastIndexOf(':');
    	int endIndex = url.indexOf(';');
    	if(endIndex == -1){
    		endIndex = url.length();
    	}
    	
    	name = url.substring(beginIndex,endIndex);
    	
    	return name;
    }
    
    private Column convert2Column(ResultSet columnResult, DatabaseMetaData dbMetadata) throws SQLException {
        Column column = new Column();
        column.setColumnName(columnResult.getString("COLUMN_NAME"));
        column.setType(columnResult.getInt("DATA_TYPE"));
        column.setTypeName(columnResult.getString("TYPE_NAME"));
        column.setSize(columnResult.getInt("COLUMN_SIZE"));
        column.setNullable(columnResult.getBoolean("NULLABLE"));
        column.setDecimalDigits(columnResult.getInt("DECIMAL_DIGITS")); 
        column.setRemarks(columnResult.getString("REMARKS"));
        if(logger.isDebugEnabled()){
            showResultSetValue(columnResult);
        }
        if(isMysqlDriver(dbMetadata)){
            mysqlRsConvert2Column(columnResult, column);
        }else if(isOracleDriver(dbMetadata)){
            oracleRsConvert2Column(columnResult, column);
        }else if(isSQLServerDriver(dbMetadata)){
            sqlServerRsConvert2Column(columnResult, column);
        }
        
        return column;
    }
    
    void mysqlRsConvert2Column(ResultSet columnResult, Column column) throws SQLException{
        Boolean isAutoIncrement = columnResult.getBoolean("IS_AUTOINCREMENT");
        column.setAutoIncrement(isAutoIncrement);
    }
    
    void oracleRsConvert2Column(ResultSet columnResult, Column column){
        
    }
    
    void sqlServerRsConvert2Column(ResultSet columnResult, Column column) throws SQLException{
        boolean isAutoIncreaseColumn = false;
        String typeName = columnResult.getString("TYPE_NAME");
        if(typeName != null){
            if(typeName.indexOf("identity") != -1){
                isAutoIncreaseColumn = true;
            }
        }
        column.setAutoIncrement(isAutoIncreaseColumn);
    }
    
    private void showResultSetValue(ResultSet columnResult) throws SQLException{
         ResultSetMetaData metaData = columnResult.getMetaData();
        int count = metaData.getColumnCount();
        logger.debug(String.format("===================%s.%s=========================",
                        columnResult.getString("TABLE_NAME"),
                        columnResult.getString("COLUMN_NAME")));
        for(int i=1;i <=count;i++){
            String columnName = metaData.getColumnName(i);
            Object value = columnResult.getObject(i);
            String type = null;
            if(value != null){
                type = value.getClass().getName();
            }
            logger.debug(String.format("%20s=%s [%s]", columnName, value, type));
        }
    }
    
    /**
     * 得到一个表的所有主键值.
     * <p>
     * <code>getTablePKS</code>
     * </p>
     * 
     * @param conn
     * @param catalogName
     * @param tableName
     * @return
     * @throws SQLException
     * @author LiuXiaojie 2007-5-23
     */
    public static List getTablePKS(Connection conn , String catalogName, String tableName) throws SQLException{
        DatabaseMetaData dbMetadata =  conn.getMetaData();
        List columns = new ArrayList();
        ResultSet columnResult = dbMetadata.getPrimaryKeys(catalogName, null, tableName);
        while(!columnResult.next()){
            try{             
                columns.add(columnResult.getString("COLUMN_NAME"));
            }catch(Exception ex){
                ex.printStackTrace();
            }
        }
        
        return columns;
    }
    
    public static Object executeFunc(Connection conn, String func) throws SQLException{
    	Statement statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    	ResultSet r = statement.executeQuery(func);
    	if(r.next()){
    		return r.getObject(1);
    	}
    	return null;
    }
    
    public String getBasePath() {
        return basePath;
    }

    public void setBasePath(String basePath) {
        this.basePath = basePath;
    }

    public Connection getConn() throws SQLException {
        this.connect();
        return conn;
    }
    
    public DatabaseConfig getConfig() {
        return config;
    }

    public void setConfig(DatabaseConfig config) {
        this.config = config;
    }


}