Groovy高效编程——DSL实战(更新于2008.05.25)

在Java中调用存储过程是一件比较繁琐的事情，为了提高开发效率，我写了一个针对Oracle存储过程调用的DSL。用法和代码如下所示：

我们先看一下语法：
1，调用存储过程：
call(name: 'procedure_name', type: 'procedure', url: 'jdbc:oracle:thin:username/password@localhost:1521:orcl') {
    inParameter {
        name    'varchar', 'Daniel'      // 依次为传入参数的名称，类型，值
        address 'varchar', 'Shanghai'
    }
    outParameter {
        info 'varchar'   // 依次为传出参数的名称，类型
    }
}

2，调用函数
call(name: 'function_name', type: 'function', url: 'jdbc:oracle:thin:username/password@localhost:1521:orcl') {
    inParameter {
        name    'varchar', 'Daniel'     // 依次为传入参数的名称，类型，值
        address 'varchar', 'Shanghai'
    }
    outParameter {   // 传出参数，函数的返回参数放在第一位
        info 'varchar'                  // 依次为传出参数的名称，类型；info是返回参数
        greeting1 'varchar'
        greeting2 'varchar'
    }
}

调用成功之后，我们可以通过传出参数名称来获取相应的结果值，例如：
// 读取并执行dsl代码
def results = dfp.executeScript(dslScriptCode)
println results.info // 打印指定字段的值

// 在代码中直接执行dsl。
def result = dfp.call(name: 'dsl_function3', type: 'function', url: 'jdbc:oracle:thin:username/password@localhost:1521:orcl') {
    inParameter {
        name    'varchar', 'Daniel'
        address 'varchar', 'Shanghai'
    }
    outParameter {
        info 'varchar'
        greeting1 'varchar'
        greeting2 'varchar'
    }
}
println result   // 打印全部结果

更详细的用法请参考下面的Test.groovy

再说明一下传出和传入参数位置的约定，
存储过程：
call some_procedure(?1, ?2, ?3...)
从第1个问号开始，先声明传入参数，再声明传出参数

函数：
?1 = call some_function(?2, ?3, ?4...)
从第2个问号开始，先声明传入参数，再声明传出参数

工程目录结构：

PROJECT_HOME
│ dsl.bs
│ dsl2.bs
│ dsl3.bs
│ Test.groovy
│
└─bluesun
    └─dsl
        │ DslForProcedure.groovy
        │ Template.groovy
        │
        └─delegate
                CallDelegate.groovy
                Delegate.groovy
                InParameterDelegate.groovy
                OutParameterDelegate.groovy

dsl.bs

call(name: 'dsl_function3', type: 'function', url: 'jdbc:oracle:thin:username/password@localhost:1521:orcl') {
    inParameter {
        name    'varchar', 'Daniel'
        address 'varchar', 'Shanghai'
    }
    outParameter {
        info 'varchar'
        greeting1 'varchar'
        greeting2 'varchar'
    }
}

dsl2.bs

call(name: 'dsl_procedure', type: 'procedure', url: 'jdbc:oracle:thin:username/password@localhost:1521:orcl') {
    inParameter {
        name    'varchar', 'Daniel'
        address 'varchar', 'Shanghai'
    }
    outParameter {
        info 'varchar'
    }
}

dsl3.bs

call(name: 'dsl_function_returns_cursor', type: 'function', url: 'jdbc:oracle:thin:username/password@localhost:1521:orcl') {
    inParameter {
        name    'varchar', 'Daniel'
        address 'varchar', 'Shanghai'
    }
    outParameter {
        info 'cursor'
    }
}

Test.groovy

import bluesun.dsl.*

def dfp = new DslForProcedure()

def dslScriptCode = new File('dsl.bs').text
def results = dfp.executeScript(dslScriptCode)
println results

def dslScriptCode2 = new File('dsl2.bs').text
def results2 = dfp.executeScript(dslScriptCode2)
println results2

def dslScriptCode3 = new File('dsl3.bs').text
def results3 = dfp.executeScript(dslScriptCode3)
results3.info.eachRow { row ->
    println "name:${row.name}, address:${row.address}"
}

def result4 = dfp.call(name: 'dsl_function3', type: 'function', url: 'jdbc:oracle:thin:username/password@localhost:1521:orcl') {
    inParameter {
        name    'varchar', 'Daniel'
        address 'varchar', 'Shanghai'
    }
    outParameter {
        info 'varchar'
        greeting1 'varchar'
        greeting2 'varchar'
    }
}

println result4

DslForProcedure.groovy

package bluesun.dsl

import java.sql.*;
import groovy.sql.*;
import oracle.jdbc.driver.OracleTypes;

import bluesun.dsl.delegate.*

class DslForProcedure {
    def templateFile = new File('bluesun/dsl/Template.groovy')
    def templateContent = templateFile.text

    DslForProcedure() {
        this.metaClass = createMetaClass(this.class) { emc ->
            emc.'call' = scriptClosure
        }
    }

    def scriptClosure = { args, callClosure ->
        def binding = new Binding()
        binding['results'] = [:]
        binding['callType'] = args['type']
        binding['callName'] = args['name']
        binding['inParameters'] = [:]
        binding['outParameters'] = [:]
        callClosure.delegate = new CallDelegate(binding)
        callClosure.resolveStrategy = Closure.DELEGATE_FIRST
        callClosure()

        def simpleTemplateEngine = new groovy.text.SimpleTemplateEngine()
        def template = simpleTemplateEngine.createTemplate(templateContent)
        binding['url'] = args['url']
        def resultCode = template.make(binding.variables).toString()
        Script script = new GroovyShell(binding).parse(resultCode)
        def results = script.run()
        binding['results'] = results
        return binding['results']
    }

    def createMetaClass(Class clazz, Closure closure) {
        def emc = new ExpandoMetaClass(clazz, false)
        closure(emc)
        emc.initialize()
        return emc
    }

    def executeScript(dslScriptCode, rootName, closure) {
        Script dslScript = new GroovyShell().parse(dslScriptCode)

        dslScript.metaClass = createMetaClass(dslScript.class) { emc ->
            emc."$rootName" = closure
        }
        return dslScript.run()
    }

    def executeScript(dslScriptCode) {
        executeScript(dslScriptCode, 'call', scriptClosure)
    }
}

Template.groovy

import java.sql.*;
import groovy.sql.*;
import oracle.jdbc.driver.OracleTypes;

Sql sql = Sql.newInstance('<%=url%>', 'oracle.jdbc.driver.OracleDriver');
results = [:]
<%
isFunctionCall = ('function' == callType.toLowerCase())

def generateReturnForFunction() {
    if (isFunctionCall) {
    def returnType = (outParameters.entrySet() as List).value[0][0];
    generateOutParameter(returnType)
    out.print('=')
    }
}

def generateOutParameter(type) {
    type = type.toUpperCase()
    out.print('CURSOR' != type ? '${Sql.out(OracleTypes.' + type + ')}' : '${Sql.resultSet OracleTypes.' + type + '}')
}

def generateInParameter(name, type) {
    type = type.toUpperCase()
    out.print('${Sql.in(OracleTypes.' + type + ', ' + name + ')}')
}

def generateInParameters() {
    inParameters.eachWithIndex { inParameter, i ->
        generateInParameter(inParameter.key, inParameter.value[0])
        if (i != inParameters.size() - 1)
            out.print(',')
    }
}

def generateOutParameters() {
    if (outParameters.size() > (isFunctionCall ? 1 : 0))
        out.print(',')

    outParameters.eachWithIndex { outParameter, i ->
        if ((isFunctionCall && i > 0) || !isFunctionCall) {
            generateOutParameter(outParameter.value[0])
            if (i != outParameters.size() - 1)
                out.print(',')
        }
    }
}

def generateVariablesInClosure() {
    outParameters.eachWithIndex { outParameter, i -> out.print(outParameter.key); if (i != outParameters.size() - 1) out.print(',') }
}

def generateAssignStatement(outParameter) {
    out.println('\t' + 'results.' + outParameter.key + '=' + outParameter.key)
}

def generateAssignStatements() {
    outParameters.eachWithIndex { outParameter, i ->
        generateAssignStatement(outParameter)
    }
}
%>
sql.call(
    """{<%generateReturnForFunction()%> call <%=callName%>(
                            <%
                                generateInParameters()
                                generateOutParameters()
                            %>
                         )
    }"""
) {  <% generateVariablesInClosure() %> ->
<%
    generateAssignStatements()
%>
}
results

Delegate.groovy

package bluesun.dsl.delegate

abstract class Delegate {
abstract methodMissing(String name, Object args)
def propertyMissing(String name) {}
}

CallDelegate.groovy

package bluesun.dsl.delegate

import java.sql.*;
import groovy.sql.*;
import oracle.jdbc.driver.OracleTypes;

class CallDelegate extends Delegate {
    def binding
    CallDelegate(binding) {
        this.binding = binding
    }
    def methodMissing(String name, Object args) {
        if ('inParameter' == name && args[0] instanceof Closure) {
            def inParameterClosure = args[0]
            inParameterClosure.delegate = new InParameterDelegate(binding)
            inParameterClosure.resolveStrategy = Closure.DELEGATE_FIRST
            inParameterClosure()
        } else if ('outParameter' == name && args[0] instanceof Closure) {
            def outParameterClosure = args[0]
            outParameterClosure.delegate = new OutParameterDelegate(binding)
            outParameterClosure.resolveStrategy = Closure.DELEGATE_FIRST
            outParameterClosure()
        }
    }
}

InParameterDelegate.groovy

package bluesun.dsl.delegate

import java.sql.*;
import groovy.sql.*;
import oracle.jdbc.driver.OracleTypes;

class InParameterDelegate extends Delegate {
    def binding
    InParameterDelegate(binding) {
        this.binding = binding
    }
    def methodMissing(String name, Object args) {
        def inParameters = binding['inParameters']
        inParameters[name] = args
        binding[name] = args[1]
    }
}

OutParameterDelegate.groovy

package bluesun.dsl.delegate

import java.sql.*;
import groovy.sql.*;
import oracle.jdbc.driver.OracleTypes;

class OutParameterDelegate extends Delegate {
    def binding
    OutParameterDelegate(binding) {
        this.binding = binding
    }
    def methodMissing(String name, Object args) {
        def outParameters = binding['outParameters']
        outParameters[name] = args
    }
}

被调用的存储过程：
dsl_function3：

CREATE OR REPLACE FUNCTION DANIEL.dsl_function3 (P_NAME IN VARCHAR2, P_ADDRESS IN VARCHAR2, P_GREETING1 OUT VARCHAR2, P_GREETING2 OUT VARCHAR2)
   RETURN VARCHAR2
AS
   V_RESULT   VARCHAR2 (100);
BEGIN
   SELECT 'NAME: ' || P_NAME || ', ADDRESS: ' || P_ADDRESS
     INTO V_RESULT
     FROM DUAL;

   P_GREETING1 := 'Hello, ' || P_NAME;
   P_GREETING2 := 'Hi, ' || P_NAME;

   RETURN V_RESULT;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RAISE;
END dsl_function3;
/

dsl_procedure：

CREATE OR REPLACE PROCEDURE DANIEL.dsl_procedure (P_NAME IN VARCHAR2, P_ADDRESS IN VARCHAR2, P_RESULT OUT VARCHAR2)
AS
BEGIN
   SELECT 'NAME: ' || P_NAME || ', ADDRESS: ' || P_ADDRESS
     INTO P_RESULT
     FROM DUAL;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RAISE;
END dsl_procedure;
/

dsl_function_returns_cursor：

CREATE OR REPLACE FUNCTION DANIEL.dsl_function_returns_cursor (P_NAME IN VARCHAR2, P_ADDRESS IN VARCHAR2)
   RETURN SYS_REFCURSOR
AS
   V_RESULT   SYS_REFCURSOR;
BEGIN
    OPEN V_RESULT FOR
        SELECT '山风小子' as name, 'China' as address FROM DUAL
            UNION
        SELECT P_NAME, P_ADDRESS FROM DUAL;
   RETURN V_RESULT;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RAISE;
END dsl_function_returns_cursor;
/

运行结果：

D:\_DEV\groovy_apps\DSL>groovy Test.groovy
[info:NAME: Daniel, ADDRESS: Shanghai, greeting1:Hello, Daniel, greeting2:Hi, Daniel]
[info:NAME: Daniel, ADDRESS: Shanghai]
name:Daniel, address:Shanghai
name:山风小子, address:China
[info:NAME: Daniel, ADDRESS: Shanghai, greeting1:Hello, Daniel, greeting2:Hi, Daniel]

D:\_DEV\groovy_apps\DSL>

如果您对DSL的创建比较陌生，可以去看一下在下的另外一篇随笔《Groovy高效编程——创建DSL》。

附：朝花夕拾——Groovy & Grails

posted on 2008-05-24 18:12 山风小子阅读(6724) 评论(4) 编辑收藏所属分类: Groovy & Grails

常用链接

留言簿(71)

随笔分类

随笔档案

相册

Documentations

Groovy & Grails

友情链接

好友 & 邻居

最新随笔

搜索

积分与排名

最新评论

阅读排行榜

评论排行榜