spring访问oracle函数,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());

}

}

}

我的异常网推荐解决方案：oracle存储过程,http://www.myexceptions.net/oracle-develop/177537.html