熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Java編程 >> Java核心技術 >> 正文

Groovy高效編程—生成Oracle表結構信息文檔

2013-11-23 19:00:42  來源: Java核心技術 

  利用Groovy對數據庫進行操作是極其方便的有時為了熟悉數據庫中的表需要將表結構導出並保存為EXCEL格式

  下面所展示的源代碼就能夠很好的滿足我們的需求(這段代碼依賴jxl和Oracle的jdbc驅動)

  功能保持不變的條件下代碼做了一些小調整利用Groovy中的強大特性Mixin使代碼更優雅

  導出效果

  


    confproperties   filename=table_structuresxls   tablestoexport=%   columnwidth=   url=jdbc:oracle:thin:@::orcl   user=DANIEL   password=   driver=oraclejdbcdriverOracleDriver   GroovySqlgroovy   /*   * Copyright the original author or authors   *   * Licensed under the Apache License Version (the License);   * you may not use this file except in compliance with the License   * You may obtain a copy of the License at   *   *   * Unless required by applicable law or agreed to in writing software   * distributed under the License is distributed on an AS IS BASIS   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND either express or implied   * See the License for the specific language governing permissions and   * limitations under the License   *   * Auth   */   import groovysqlSql   import jxl *   import jxlwrite *   Properties properties = new Properties();   propertiesload( new FileInputStream( confproperties ));   def filename = propertiesgetProperty( filename )   def tablesToExport = propertiesgetProperty( tablestoexport )   def columnWidth = propertiesgetProperty( columnwidth )   def url = propertiesgetProperty( url )   def user = propertiesgetProperty( user )   def password = propertiesgetProperty( password )   def driver = propertiesgetProperty( driver )   def sql = SqlnewInstance(url user password driver)   def sqlStmt =   select   atable_name   lumn_name   (select   nstraint_type   from   all_constraints d   all_cons_columns e   where   cowner = downer and   downer = eowner and   ctable_name = dtable_name and   dtable_name = etable_name and   lumn_name = lumn_name and   nstraint_name = nstraint_name and   nstraint_type = P and   rownum =   ) as constraint_type   adata_type   adata_length   adata_precision   adata_scale   anullable   adata_default   ments   ments as tab_comments   from   all_tab_columns a   all_col_comments b   all_tab_comments c   where   aowner = bowner and   bowner = cowner and   atable_name = btable_name and   btable_name = ctable_name and   lumn_name = lumn_name and   atable_name like ? and   aowner = ?      Map tables = new HashMap()   sqleachRow(sqlStmt [tablesToExport user]){ row >   Map column = new HashMap()   columnput( column_name lumn_name);   columnput( constraint_type nstraint_type);   columnput( data_type rowdata_type);   columnput( data_length rowdata_length);   columnput( data_precision rowdata_precision);   columnput( data_scale rowdata_scale);   columnput( nullable rownullable);   columnput( data_default rowdata_default);   columnput( comments ments);   String tableName = rowtable_name   String tableComments = rowtab_comments   Set columns = tablesget(tableName) ?lumns   if ( null == columns) {   columns = new HashSet();   columns << column   tablesput(tableName [tableComments:tableComments columns:columns])   } else {   columns << column   }   }   println to export table structures   class WritableSheetCategory {   static insertRow(WritableSheet writableSheet List row int x int y) {   roweachWithIndex { col i >   Label cell = new Label(x + i y col)   writableSheetaddCell(cell)   }   }   }   WritableWorkbook writableWorkBook =   WorkbookcreateWorkbook(   new File(filename))   WritableSheet writableSheet = writableWorkBookcreateSheet( 第一頁 )   WritableFont writableFontForTableName =   new WritableFont(WritableFontTIMES WritableFontBOLD)   WritableCellFormat writableCellFormatForTableName =   new WritableCellFormat(writableFontForTableName)   // writableCellFormatForTableNamesetAlignment(jxlformatAlignmentCENTRE)   writableCellFormatForTableNamesetVerticalAlignment(jxlformatVerticalAlignmentCENTRE)   WritableFont writableFontForTableComments =   new WritableFont(WritableFontTIMES WritableFontNO_BOLD)   WritableCellFormat writableCellFormatForTableComments =   new WritableCellFormat(writableFontForTableComments)   // writableCellFormatForTableCommentssetAlignment(jxlformatAlignmentCENTRE)   writableCellFormatForTableCommentssetVerticalAlignment(jxlformatVerticalAlignmentCENTRE)   int line =   List titleRow = [    表名 表注釋 字段名稱 是否主鍵 字段類型 字段長度
整數位數 小數位數 允許空值 缺省值 字段注釋   ]   try {   columnWidth = IntegerparseInt(columnWidth)   } catch (Exception e) {   columnWidth =   Systemerrprintln(egetMessage())   }   for ( int i = ; i < titleRowsize(); i ++ ) {   writableSheetsetColumnView(i columnWidth)   }   use (WritableSheetCategory) {   writableSheetinsertRow(titleRow line ++ )   }   tableseach { tableName tableInfo >   String tableComments = tableInfotableComments   Set columns = lumns   Label tableNameCell = new Label( line tableName writableCellFormatForTableName)   writableSheetaddCell(tableNameCell)   Label tableCommentsCell = new Label( line tableComments ? +
tableComments : writableCellFormatForTableComments)   writableSheetaddCell(tableCommentsCell)   columnseach { column >   List row = [   lumn_name ? + lumn_name :   nstraint_type ? + nstraint_type :   columndata_type ? + columndata_type :   columndata_length ? + columndata_length :   columndata_precision ? + columndata_precision :   columndata_scale ? + columndata_scale :   columnnullable ? + columnnullable :   columndata_default ? + columndata_default :   ments ? + ments :   ]   use (WritableSheetCategory) {   writableSheetinsertRow(row line ++ )   }   }   rgeCells( line columnssize() line )   rgeCells( line columnssize() line )   line +=   }   writableWorkBookwrite();   writableWorkBookclose();   println done!



From:http://tw.wingwit.com/Article/program/Java/hx/201311/26135.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.