spring呼叫帶引數的oracle函式應注意的問題

spring可以方便的訪問oracle的儲存過程、函式。

spring文件舉了一個訪問sysdate的例子，它不需要輸入引數，使用如下：

public class TestStoredProcedure {

public static void main(String[] args) {
TestStoredProcedure t = new TestStoredProcedure();
t.test();
System.out.println("Done!");
}

void test() {
DriverManagerDataSource ds = new DriverManagerDataSource();
ds.setDriverClassName("oracle.jdbc.OracleDriver");
ds.setUrl("jdbc:oracle:thin:@localhost:1521:mydb");
ds.setUsername("scott");
ds.setPassword("tiger");

MyStoredProcedure sproc = new MyStoredProcedure(ds);
Map results = sproc.execute();
printMap(results);
}

private class MyStoredProcedure extends StoredProcedure {

private static final String SQL = "sysdate";

public MyStoredProcedure(DataSource ds) {
setDataSource(ds);
setFunction(true);
setSql(SQL);
declareParameter(new SqlOutParameter("date", Types.DATE));
compile();
}

public Map execute() {
// the 'sysdate' sproc has no input parameters, so an empty Map is supplied...
return execute(new HashMap());
}
}

private static void printMap(Map results) {
for (Iterator it = results.entrySet().iterator(); it.hasNext(); ) {
System.out.println(it.next());
}
}
}

但是如果訪問帶輸入引數的function怎麼辦？很簡單，只是要注意，輸出引數的宣告一定要放在輸入引數宣告的前面。否則，返回值永為null

如下：

public class TestStoredProcedure {

public static void main(String[] args) {
TestStoredProcedure t = new TestStoredProcedure();
t.test();
System.out.println("Done!");
}

void test() {
DriverManagerDataSource ds = new DriverManagerDataSource();
ds.setDriverClassName("oracle.jdbc.OracleDriver");
ds.setUrl("jdbc:oracle:thin:@localhost:1521:mydb");
ds.setUsername("scott");
ds.setPassword("tiger");

MyStoredProcedure sproc = new MyStoredProcedure(ds);
Map results = sproc.execute();
printMap(results);
}

private class MyStoredProcedure extends StoredProcedure {

private static final String SQL = "youfunc";

public MyStoredProcedure(DataSource ds) {
setDataSource(ds);
setFunction(true);
setSql(SQL);

//一定要注意順序，將OUt放在前面，否則，你的返回值永遠為null

declareParameter(new SqlOutParameter("myrt", Types.varchar));
declareParameter(new SqlParameter("pIN", Types.varchar)); compile();
}

public Map execute() {
// the 'sysdate' sproc has no input parameters, so an empty Map is supplied...

Map m=new HashMap();

m.put("PIn","test");
return execute(new HashMap());
}
}

private static void printMap(Map results) {
for (Iterator it = results.entrySet().iterator(); it.hasNext(); ) {
System.out.println(it.next());
}
}
}

[@more@]