如何在ADF 的Application Module中调用存储过程

1.调用无参数的存储过程

public void callProcWithNoArgs() {
getDBTransaction().executeCommand(
"begin devguidepkg.proc_with_no_args; end;");
}

2.调用含入参的存储过程

protected void callStoredProcedure(String stmt, Object[] bindVars) {
PreparedStatement st = null;
	try {
		// 1. Create a JDBC PreparedStatement for
		st = getDBTransaction().createPreparedStatement("begin "+stmt+";end;",0);
		if (bindVars != null) {
		// 2. Loop over values for the bind variables passed in, if any
			for (int z = 0; z < bindVars.length; z++) {
				// 3. Set the value of each bind variable in the statement
				st.setObject(z + 1, bindVars[z]);
			}
		}
		// 4. Execute the statement
		st.executeUpdate();
	}catch (SQLException e) {
		throw new JboException(e);
	}finally {
		if (st != null) {
		try {
			// 5. Close the statement
			st.close();
		}catch (SQLException e) {}
		}
	}
}
public void callProcWithThreeArgs(Number n, Date d, String v) {
	callStoredProcedure("devguidepkg.proc_with_three_args(?,?,?)",	new Object[]{n,d,v});
}

3.含入参的调用存储函数

public static int NUMBER = Types.NUMERIC;
public static int DATE = Types.DATE;
public static int VARCHAR2 = Types.VARCHAR;
protected Object callStoredFunction(int sqlReturnType, String stmt,Object[] bindVars) {
	CallableStatement st = null;
	try {
		// 1. Create a JDBC CallabledStatement
		st = getDBTransaction().createCallableStatement("begin ? := "+stmt+";end;",0);
		// 2. Register the first bind variable for the return value
		st.registerOutParameter(1, sqlReturnType);
		if (bindVars != null) {
			// 3. Loop over values for the bind variables passed in, if any
			for (int z = 0; z < bindVars.length; z++) {
				// 4. Set the value of user-supplied bind vars in the stmt
				st.setObject(z + 2, bindVars[z]);
			}
		}
		// 5. Set the value of user-supplied bind vars in the stmt
		st.executeUpdate();
		// 6. Return the value of the first bind variable
		return st.getObject(1);
	}catch (SQLException e) {
		throw new JboException(e);
	}finally {
		if (st != null) {
			try {
				// 7. Close the statement
				st.close();
			}catch (SQLException e) {}
			}
		}
	}
}

public String callFuncWithThreeArgs(Number n, Date d, String v) {
	return (String)callStoredFunction(VARCHAR2,"devguidepkg.func_with_three_args(?,?,?)",
new Object[]{n,d,v});
}

4.获取当前的数据库事务

private Connection getCurrentConnection() throws SQLException {
/* Note that we never execute this statement, so no commit really happens */
	PreparedStatement st = getDBTransaction().createPreparedStatement("commit",1);
	Connection conn = st.getConnection();
	st.close();
	return conn;
}