在ADF中，AM负责与数据库连接打交道，开发人员最好不要直接操作数据库连接。
那么该如何通过AM执行用户自己的SQL语句呢？以下分三种情况说明：Statment、PreparedStatement、CallableStatement。
1. 调用CallableStatement需要先创建Procedure
CREATE OR REPLACE
PROCEDURE greeting_proc(text_param IN VARCHAR2, msg_text OUT VARCHAR2)
IS
BEGIN
msg_text := 'Hello ' || text_param;
END;
2. 定制AppModuleImpl.java类
完整的AppModuleImpl代码如下：
package model;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import model.common.AppModule;
import oracle.jbo.JboException;
import oracle.jbo.server.ApplicationModuleImpl;
import oracle.jbo.server.ViewObjectImpl;
// ---------------------------------------------------------------------
// --- File generated by Oracle ADF Business Components Design Time.
// --- Fri Jun 29 16:03:32 KST 2012
// --- Custom code may be added to this class.
// --- Warning: Do not modify method signatures of generated methods.
// ---------------------------------------------------------------------
public class AppModuleImpl extends ApplicationModuleImpl implements AppModule {
/**
* This is the default constructor (do not remove).
*/
public AppModuleImpl() {
}
/**
* Container's getter for JobsView1.
* @return JobsView1
*/
public ViewObjectImpl getJobsView1() {
return (ViewObjectImpl)findViewObject("JobsView1");
}
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;
}
public String testStatement() {
String result = null;
String sql = "SELECT COUNT(*) FROM jobs";
Statement st = null;
ResultSet rs;
try {
st = getDBTransaction().createStatement(this.getDBTransaction().DEFAULT);
rs = st.executeQuery(sql);
if (rs.next()) {
result = rs.getBigDecimal(1).toString();
}
rs.close();
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
}
}
}
System.out.println("!!!!!!!!!!!!!!!!!! testStatement: " + result);
return result;
}
public String testPreparedStatement() {
String result = null;
String sql = "SELECT job_title FROM jobs WHERE job_id = ?";
PreparedStatement pst = null;
ResultSet rs;
try {
pst = getDBTransaction().createPreparedStatement(sql, this.getDBTransaction().DEFAULT);
pst.setString(1, "AD_VP");
rs = pst.executeQuery();
if (rs.next()) {
result = (String)rs.getObject(1);
}
rs.close();
pst.close();
} catch (SQLException ex) {
throw new JboException(ex);
} finally {
if (pst != null) {
try {
pst.close();
} catch (SQLException e) {
}
}
}
System.out.println("!!!!!!!!!!!!!!!!!! testPreparedStatement: " + result);
return result;
}
public String testCallableStatement() {
String result = null;
//String sql = "BEGIN greeting_proc(text_param=?,msg_text=?);END;";
String sql = "BEGIN greeting_proc(:text_param,:msg_text);END;";
CallableStatement cst = null;
try {
cst = getDBTransaction().createCallableStatement(sql, this.getDBTransaction().DEFAULT);
cst.setObject("text_param", "Ma Ping");
cst.registerOutParameter("msg_text", Types.VARCHAR);
cst.execute();
result = (String)cst.getObject("msg_text");
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
if (cst != null) {
try {
cst.close();
} catch (SQLException e) {
}
}
}
System.out.println("!!!!!!!!!!!!!!!!!! testCallableStatement: " + result);
return result;
}
}
3. 把三个公共方法暴露在Client Interface中
4. 创建页面，把三个方法Binding到三个Button
5. 运行
先后分别点击按钮Test Statement、Test PreparedStatement、Test CallableStatement。
输出如下：
!!!!!!!!!!!!!!!!!! testStatement: 23
!!!!!!!!!!!!!!!!!! testPreparedStatement: Administration Vice President
!!!!!!!!!!!!!!!!!! testCallableStatement: Hello Ma Ping
Project 下载：ADF_AM_ExecuteSQL.7z
参考文献：
1. http://jjzheng.blogspot.com/2010/11/run-single-query-in-application-module.html
2. http://andrejusb.blogspot.com/2011/04/invoking-stored-procedures-and.html
3. http://lalitsuryawanshi.blogspot.com/2009/06/access-dbtransaction-from-managedbean.html
4. http://www.baigzeeshan.com/2010/05/calling-plsql-procedure-and-function-in.html
5. https://forums.oracle.com/forums/thread.jspa?threadID=855247
6. http://blog.csdn.net/luyushuang/article/details/6257516
