Rain's Blog

这里用到了groovy.sql.Sql，查询数据库获取所有的表信息，然后借助Scriptom来操作Word，生成word文档。 这里看到的源码，其实是第4个版本，将生成word的表格改成了闭包写法。之前曾用java.sql.*包的一些API，改进后采用groovy.sql.Sql类。 详细的源码如下：

/**
 * ====================================================
 * @author: rain(http://rainboyan.com)
 * @changelog
 *
 * v11 2009/2/18 16:48
 *   - 添加消息：提示打印文件位置
 *
 * v10 2008/12/15 16:48
 *   - 重构代码，DbTable增加add()和size()方法
 *
 * v9 2008/12/14 22:55
 *   - 性能优化，采用多线程实现，“生产者-消费者”模式
 *
 * v8 2008/12/14 22:05
 *   - 调整Table边框样式，古典型1
 *
 * v7 2008/12/14 15:37
 *   - 重构代码，增加类DbTable和DbColumn
 *   - 调整Table列宽及对齐方式
 *   - 设置Table边框样式
 *   - 增加文档标题，日期，目录，页码，页眉和页脚
 *
 * v6 2008/12/14 15:00
 *   - 修改conf.properties
 *   - 重构代码，异常处理
 *   - 导出Word文档自动保存在用户目录下，
 *     文件命名：[db.name]-[db.version].doc
 *
 * v5 2008/12/13 14:23
 *   - 重构代码
 *   - 增加打印用时统计
 *   - 设置表格边框样式
 *
 * v4 2008/12/9 22:23
 *   - 重构代码，采用groovy.sql.Sql来实现
 *
 * v3 2008/12/9 22:12
 *   - 重构代码，增加DbTableRow类和printTable方法
 *
 * v2 2008/12/7 22:30
 *   - 实现了导出数据库表结构，打印至Word文档
 *   - 字段包括：
 *       TABLE Name | Field Name | Field Type | Field LENGTH | KEY | DEFAULT VALUE | DESCRIPTION
 *
 * v1 2008/12/6 20:23
 *   - 实现了导出数据库表结构，打印至控制台
 * ====================================================
 */

import java.util.concurrent.*
import groovy.sql.Sql
import org.codehaus.groovy.scriptom.*

static final DbTable POISON = new DbTable(name : "POISON")

// Word Constants
static final True = 1
static final False = 0

static final wdStyleHeading1 = -2
static final wdStyleHeading2 = -3
static final wdStyleHeading3 = -4
static final wdStyleHeading4 = -5
static final wdStyleHeading5 = -6
static final wdStyleHeading6 = -7
static final wdStyleBodyText = -67

static final wdTexture10Percent = 100
static final wdTexture20Percent = 200
static final wdTexture12Pt5Percent = 125
static final wdTexture15Percent = 150
static final wdTexture17Pt5Percent = 175
static final wdTexture22Pt5Percent = 225

static final wdAlignRowLeft = 0
static final wdAlignRowCenter = 1
static final wdAlignRowRight = 2

static final wdAlignVerticalTop = 0
static final wdAlignVerticalCenter = 1
static final wdAlignVerticalJustify = 2
static final wdAlignVerticalBottom = 3

static final wdLineBreak = 6
static final wdPageBreak = 7

static final wdBulletGallery = 1
static final wdNumberGallery = 2
static final wdOutlineNumberGallery = 3

static final wdListNumberStyleArabic = 0

static final wdDoNotSaveChanges = 0
static final wdSaveChanges = -1
static final wdPromptToSaveChanges = -2

static final wdLineStyleSingle = 1
static final wdLineStyleDouble = 7

static final wdAdjustNone = 0
static final wdAdjustProportional = 1
static final wdAdjustFirstColumn = 2
static final wdAdjustSameWidth = 3

static final wdAlignParagraphLeft = 0
static final wdAlignParagraphCenter = 1
static final wdAlignParagraphRight = 2

static final wdGoToPage = 1

static final wdAlignPageNumberLeft = 0
static final wdAlignPageNumberCenter = 1
static final wdAlignPageNumberRight = 2
static final wdAlignPageNumberInside = 3
static final wdAlignPageNumberOutside = 4

static final wdHeaderFooterPrimary = 1

static final wdTabLeaderDots = 1
static final wdIndexIndent = 0

static final wdFieldEmpty = -1
static final wdFieldDate = 31

static final wdSimplifiedChinese = 2052
static final wdCalendarWestern = 0

static final wdStyleTypeParagraph = 1
static final wdStyleTypeCharacter = 2
static final wdStyleTypeTable = 3


class DbTable {
    String name
    String desc
    List columns = []

    def add(col) {
        columns << col
    }

    def size() {
        columns.size()
    }

    String toString() {
        name
    }
}
class DbColumn {
    String name
    String typeName
    Integer displaySize
    Boolean nullable
}

def crawler = { db, queue ->
    def params = []
    params << db['db.url']
    params << db['db.username']
    params << db['db.password']
    params << db['db.driverClassName']

    def sql = Sql.newInstance(*params)
    def conn = sql.getConnection()
    def dmd = conn.getMetaData()

    println dmd.getDatabaseProductName()
    println dmd.getDatabaseMajorVersion() + "." + dmd.getDatabaseMinorVersion()


    def types = ["TABLE"]
    rs = dmd.getTables(null, null, "%", *types)
    def dt = null
    while(rs.next()) {
        dt = new DbTable()
        def tableName = rs.getString(3)
        dt.name = tableName
        println "Table: " + tableName
        sql.query("select * from " + tableName) { rs ->
            def meta = rs.metaData
            int numColumns = meta.getColumnCount()
            for (int i = 1; i < numColumns+1; i++) {
            def c = new DbColumn()
            c.name = meta.getColumnName(i)
            c.typeName = meta.getColumnTypeName(i)
            c.displaySize = meta.getColumnDisplaySize(i)
            c.nullable = (meta.isNullable(i) == 1)
                dt.add c
            }
        }
        queue.put(dt)
    }
    while (true) {
        try {
            queue.put(POISON)
            println "Put POISON"
            break
        } catch (InterruptedException e1) { /* try again */ }
    }
}

def printTable = { doc, dt ->
    def range = doc.Range()
    range.SetRange(doc.Range().End, doc.Range().End)
    range.Style = wdStyleHeading3
    range.InsertAfter(dt.name)

    range.InsertParagraphAfter()

    range.SetRange(doc.Range().End, doc.Range().End)
    range.Style = wdStyleBodyText

    def table = doc.Tables.Add(range, dt.size() + 2, 7)
    table.Style = "古典型 1"
    table.ApplyStyleHeadingRows = True
    table.ApplyStyleLastRow = False
    table.ApplyStyleFirstColumn = False
    table.ApplyStyleLastColumn = False
    //table.Borders.InsideLineStyle = wdLineStyleSingle
    //table.Borders.OutsideLineStyle  = wdLineStyleSingle
    table.Columns(1).SetWidth(50, wdAdjustFirstColumn)
    table.Columns(2).SetWidth(70, wdAdjustFirstColumn)
    table.Columns(3).SetWidth(70, wdAdjustFirstColumn)
    table.Columns(4).SetWidth(60, wdAdjustFirstColumn)
    table.Columns(5).SetWidth(40, wdAdjustFirstColumn)
    table.Columns(6).SetWidth(60, wdAdjustFirstColumn)
    table.Columns(7).SetWidth(90, wdAdjustFirstColumn)

    def row = table.Rows(1)
    row.Cells.Height = 30
    row.Cells.Shading.Texture = wdTexture20Percent
    row.Cells.VerticalAlignment = wdAlignVerticalCenter
    row.range.Style = "DbTableStyle"
    row.Range.Bold = True
    row.Range.Italic = False
    row.Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
    row.Cells(1).Range.InsertAfter "TABLE NAME"
    row.Cells(2).Range.InsertAfter "FIELD NAME"
    row.Cells(3).Range.InsertAfter "FIELD TYPE"
    row.Cells(4).Range.InsertAfter "FIELD LENGTH"
    row.Cells(5).Range.InsertAfter "KEY"
    row.Cells(6).Range.InsertAfter "DEFAULT VALUE"
    row.Cells(7).Range.InsertAfter "DESCRIPTION"

    row = table.Rows(2)
    row.range.Style = "DbTableStyle"
    row.Cells(1).Range.InsertAfter dt.name

    for (int i = 1; i < dt.columns.size() + 1; i++) {
        row = table.Rows(i+2)
        row.range.Style = "DbTableStyle"
        row.Cells(1).Range.InsertAfter i.toString()
        row.Cells(2).Range.InsertAfter dt.columns[i-1].name
        row.Cells(3).Range.InsertAfter dt.columns[i-1].typeName
        row.Cells(4).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
        row.Cells(4).Range.InsertAfter dt.columns[i-1].displaySize.toString()
        row.Cells(5).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
        row.Cells(5).Range.InsertAfter dt.columns[i-1].nullable ? "O" : "M"
    }
    range.SetRange(doc.Range().End, doc.Range().End)
    range.Style = wdStyleBodyText
    range.InsertParagraphBefore()
}

def printer = { db, queue ->

    def word = new ActiveXObject("Word.Application")
    word.Visible = false

    try {

        def startTime = new java.util.Date()
        println startTime

        def doc = word.Documents.Add()

        def template = doc.ListTemplates().Add()
        def level1 = template.ListLevels(1)
        level1.NumberFormat = "%1"
        level1.NumberStyle = wdListNumberStyleArabic
        level1.StartAt = 1
        doc.Styles(wdStyleHeading3).LinkToListTemplate(template)
        def dbTableStyle = doc.Styles.Add("DbTableStyle", wdStyleTypeCharacter)
        dbTableStyle.Font.Size = 9

        def range = doc.Range()
        range.Style = wdStyleBodyText
        range.ParagraphFormat.Alignment = wdAlignParagraphCenter
        range.Font.Bold = True
        range.Font.Size = 22
        range.InsertAfter("Database Model\n\r" + db['db.name'] + "\n\r")
        range.InsertParagraphAfter()
        range.SetRange(doc.Range().End, doc.Range().End)
        range.Style = wdStyleBodyText
        range.ParagraphFormat.Alignment = wdAlignParagraphCenter
        range.Font.Size = 12
        range.InsertDateTime("yyyy'年'M'月'd'日'", False)
        range.SetRange(doc.Range().End, doc.Range().End)
        range.InsertParagraphAfter()
        range.InsertBreak(wdPageBreak)

        range.SetRange(doc.Range().End, doc.Range().End)
        range.Style = wdStyleBodyText
        range.ParagraphFormat.Alignment = wdAlignParagraphLeft
        range.Font.Bold = True
        range.Font.Size = 16
        range.InsertAfter("Table Of Contents")
        range.InsertParagraphAfter()
        range.SetRange(doc.Range().End, doc.Range().End)
        doc.TablesOfContents.Add(range, False, True)
        doc.TablesOfContents(1).TabLeader = wdTabLeaderDots
        doc.TablesOfContents(1).UseHyperlinks = True
        doc.TablesOfContents.Format = wdIndexIndent
        range.SetRange(doc.Range().End, doc.Range().End)
        range.InsertBreak(wdPageBreak)
        range.InsertParagraphAfter()

        try {
            while (true) {
                DbTable table = queue.take()
                if (table == POISON) {
                    println "Get POISON"
                    break
                }
                else {
                    println "Print: " + table.name
                    printTable(doc, table)
                }
            }
        }
        catch (InterruptedException e) {
            Thread.currentThread().interrup() // fail
        }

        // Add Page Number
        doc.Sections(1).Headers(wdHeaderFooterPrimary).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
        doc.Sections(1).Headers(wdHeaderFooterPrimary).Range.Text = "Database Model " + db['db.name']
        doc.Sections(1).Footers(wdHeaderFooterPrimary).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
        doc.Sections(1).Footers(wdHeaderFooterPrimary).Range.Text = db['db.version']
        doc.Sections(1).Footers(wdHeaderFooterPrimary).PageNumbers.Add(wdAlignPageNumberOutside, False)

        doc.TablesOfContents(1).Update()

        def destFileName = System.properties['user.home'] + File.separator + db['db.name'] + "-" + db['db.version'] + ".doc"
        // Save Document
        doc.SaveAs(destFileName)

        println "Build Success: " + destFileName

        //word.Visible = true

        def endTime = new java.util.Date()
        println endTime
        def totalCost = endTime.time - startTime.time
        println totalCost / (60*60*1000)

    }
    catch (Exception e) {
        e.printStackTrace()
    }
    finally {
        // Exit
        word.Quit(wdDoNotSaveChanges)
    }

}


def startPrinting = {
    BlockingQueue queue = new LinkedBlockingQueue(100)
    static Executor exec = Executors.newFixedThreadPool(2)

    def db = new Properties()
    db.load(getClass().getResourceAsStream("/db.properties"))

    exec.execute({crawler(db, queue)} as Runnable)

    exec.execute({printer(db, queue)} as Runnable)

    exec.shutdown()
}

startPrinting()