开发环境:JDeveloper 11.1.2.2.0 + Oracle XE Database 10gR2。
在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代码如下:
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
在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
http://maping930883.blogspot.com/2012/06/adf155amamsql.html