在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.bscall(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.groovypackage?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
山風小子 閱讀(6739)
評論(4) 編輯 收藏 所屬分類:
Groovy & Grails