利用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
