ADF_155：AM使用指南之五：在AM中执行SQL语句

开发环境：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代码如下：

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