【IT168 技术文档】利用Groovy对数据库进行操作是极其方便的，有时为了熟悉数据库中的表，需要将表结构导出，并保存为EXCEL格式。
下面所展示的源代码就能够很好的满足我们的需求。(这段代码依赖jxl和Oracle的jdbc驱动)
功能保持不变的条件下，代码做了一些小调整，利用Groovy中的强大特性Mixin，使代码更优雅。
导出效果：
conf.properties filename=table_structures.xls tables.to.export=% column.width=15 url=jdbc:oracle:thin:@127.0.0.1:1521:orcl user=DANIEL password=123456 driver=oracle.jdbc.driver.OracleDriver GroovySql.groovy /* * Copyright 2008 the original author or authors. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * 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. * * Author: 山风小子( http://www.blogjava.net/BlueSUN ) * Email : realbluesun@hotmail.com */ import groovy.sql.Sql import jxl. * import jxl.write. * Properties properties = new Properties(); properties.load( new FileInputStream( " conf.properties " )); def filename = properties.getProperty( ' filename ' ) def tablesToExport = properties.getProperty( ' tables.to.export ' ) def columnWidth = properties.getProperty( ' column.width ' ) def url = properties.getProperty( ' url ' ) def user = properties.getProperty( ' user ' ) def password = properties.getProperty( ' password ' ) def driver = properties.getProperty( ' driver ' ) def sql = Sql.newInstance(url, user, password, driver) def sqlStmt = """ select a.table_name, a.column_name, (select d.constraint_type from all_constraints d, all_cons_columns e where c.owner = d.owner and d.owner = e.owner and c.table_name = d.table_name and d.table_name = e.table_name and b.column_name = e.column_name and d.constraint_name = e.constraint_name and d.constraint_type = ' P ' and rownum = 1 ) as constraint_type, a.data_type, a.data_length, a.data_precision, a.data_scale, a.nullable, a.data_default, b.comments, c.comments as tab_comments from all_tab_columns a, all_col_comments b, all_tab_comments c where a.owner = b.owner and b.owner = c.owner and a.table_name = b.table_name and b.table_name = c.table_name and a.column_name = b.column_name and a.table_name like ? and a.owner = ? """ Map tables = new HashMap() sql.eachRow(sqlStmt, [tablesToExport, user]){ row -> Map column = new HashMap() column.put( ' column_name ' , row.column_name); column.put( ' constraint_type ' , row.constraint_type); column.put( ' data_type ' , row.data_type); column.put( ' data_length ' , row.data_length); column.put( ' data_precision ' , row.data_precision); column.put( ' data_scale ' , row.data_scale); column.put( ' nullable ' , row.nullable); column.put( ' data_default ' , row.data_default); column.put( ' comments ' , row.comments); String tableName = row.table_name String tableComments = row.tab_comments Set columns = tables.get(tableName) ? .columns if ( null == columns) { columns = new HashSet(); columns << column tables.put(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) { row.eachWithIndex { col, i -> Label cell = new Label(x + i, y, col) writableSheet.addCell(cell) } } } WritableWorkbook writableWorkBook = Workbook.createWorkbook( new File(filename)) WritableSheet writableSheet = writableWorkBook.createSheet( " 第一页 " , 0 ) WritableFont writableFontForTableName = new WritableFont(WritableFont.TIMES, 10 , WritableFont.BOLD) WritableCellFormat writableCellFormatForTableName = new WritableCellFormat(writableFontForTableName) // writableCellFormatForTableName.setAlignment(jxl.format.Alignment.CENTRE) writableCellFormatForTableName.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE) WritableFont writableFontForTableComments = new WritableFont(WritableFont.TIMES, 10 , WritableFont.NO_BOLD) WritableCellFormat writableCellFormatForTableComments = new WritableCellFormat(writableFontForTableComments) // writableCellFormatForTableComments.setAlignment(jxl.format.Alignment.CENTRE) writableCellFormatForTableComments.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE) int line = 0 List titleRow = [ ' 表名 ' , ' 表注释 ' , ' 字段名称 ' , ' 是否主键 ' , ' 字段类型 ' , ' 字段长度 ' ,' 整数位数 ' , ' 小数位数 ' , ' 允许空值 ' , ' 缺省值 ' , ' 字段注释 ' ] try { columnWidth = Integer.parseInt(columnWidth) } catch (Exception e) { columnWidth = 15 System.err.println(e.getMessage()) } for ( int i = 0 ; i < titleRow.size(); i ++ ) { writableSheet.setColumnView(i, columnWidth) } use (WritableSheetCategory) { writableSheet.insertRow(titleRow, line ++ , 0 ) } tables.each { tableName, tableInfo -> String tableComments = tableInfo.tableComments Set columns = tableInfo.columns Label tableNameCell = new Label( 0 , line, tableName, writableCellFormatForTableName) writableSheet.addCell(tableNameCell) Label tableCommentsCell = new Label( 1 , line, tableComments ? "" +tableComments : "" , writableCellFormatForTableComments) writableSheet.addCell(tableCommentsCell) columns.each { column -> List row = [ column.column_name ? "" + column.column_name : "" , column.constraint_type ? "" + column.constraint_type : "" , column.data_type ? "" + column.data_type : "" , column.data_length ? "" + column.data_length : "" , column.data_precision ? "" + column.data_precision : "" , column.data_scale ? "" + column.data_scale : "" , column.nullable ? "" + column.nullable : "" , column.data_default ? "" + column.data_default : "" , column.comments ? "" + column.comments : "" ] use (WritableSheetCategory) { writableSheet.insertRow(row, 2 , line ++ ) } } writableSheet.mergeCells( 0 , line - columns.size(), 0 , line - 1 ) writableSheet.mergeCells( 1 , line - columns.size(), 1 , line - 1 ) line += 2 } writableWorkBook.write(); writableWorkBook.close(); println " done! "
