利用Groovy对数据库进行操作是极其方便的有时为了熟悉数据库中的表需要将表结构导出并保存为EXCEL格式
下面所展示的源代码就能够很好的满足我们的需求(这段代码依赖jxl和Oracle的jdbc驱动)
功能保持不变的条件下代码做了一些小调整利用Groovy中的强大特性Mixin使代码更优雅
导出效果
confpropertiesfilename=table_structuresxlstablestoexport=%columnwidth=url=jdbc:oracle:thin:@::orcluser=DANIELpassword=driver=oraclejdbcdriverOracleDriverGroovySqlgroovy/** 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 groovysqlSqlimport 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 = selectatable_namelumn_name(selectnstraint_typefromall_constraints dall_cons_columns ewherecowner = downer anddowner = eowner andctable_name = dtable_name anddtable_name = etable_name andlumn_name = lumn_name andnstraint_name = nstraint_name andnstraint_type = P andrownum = ) as constraint_typeadata_typeadata_lengthadata_precisionadata_scaleanullableadata_defaultmentsments as tab_commentsfromall_tab_columns aall_col_comments ball_tab_comments cwhereaowner = bowner andbowner = cowner andatable_name = btable_name andbtable_name = ctable_name andlumn_name = lumn_name andatable_name like ? andaowner = ?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_nameString tableComments = rowtab_commentsSet columns = tablesget(tableName) ?lumnsif ( null == columns) {columns = new HashSet();columns << columntablesput(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 = tableInfotableCommentsSet columns = lumnsLabel 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!
