First they ignore you
then they ridicule you
then they fight you
then you win
    -- Mahatma Gandhi
Chinese => English     英文 => 中文             
随笔-221  评论-1047  文章-0  trackbacks-0
在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[0instanceof Closure) {
            def inParameterClosure 
= args[0]
            inParameterClosure.delegate 
= new InParameterDelegate(binding)  
            inParameterClosure.resolveStrategy 
= Closure.DELEGATE_FIRST 
            inParameterClosure()
        } 
else if ('outParameter' == name && args[0instanceof 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 山风小子 阅读(6679) 评论(4)  编辑  收藏 所属分类: Groovy & Grails