基于视图EO的增删改

概述

在使用Form进行EBS二次开发的过程中，经常会创建基于View（视图）的数据块，而在ADF中可以创建和应用基于View的Entity Object。对于该EO，可以通过添加和更改必要的代码，从而调用数据库中的PL/SQL程序包，完成相应EO的插入、更新、删除等操作。本技巧主要介绍了如何基于View创建EO，并为该EO添加插入、更新和删除操作。

下面我们以基于countries_regions_v视图的EO为例，为该EO添加增、删、改操作。

实现

1、创建Countries_Regions_v视图，代码如下：

CREATE OR REPLACE VIEW COUNTRIES_REGIONS_V AS SELECT C.COUNTRY_ID, C.COUNTRY_NAME, R.REGION_ID, R.REGION_NAME FROM COUNTRIES C, REGIONS R WHERE C.REGION_ID = R.REGION_ID;

2、编写PL/SQL程序包COUNTRIES_REGIONS_PKG，包含insert_row，update_row和delete_row分别完成增、改、删操作，代码如下：

声明部分： CREATE OR REPLACE PACKAGE COUNTRIES_REGIONS_PKG IS -- Author : Eleven.Xu -- Created : 2009-1-12 17:33:16 -- Purpose : Howto_基于View的EO的增删改 PROCEDURE INSERT_ROW(P_COUNTRY_ID VARCHAR2, P_COUNTRY_NAME VARCHAR2, P_REGION_ID NUMBER, P_REGION_NAME VARCHAR2); PROCEDURE UPDATE_ROW(P_COUNTRY_ID VARCHAR2, P_COUNTRY_NAME VARCHAR2, P_REGION_ID NUMBER, P_REGION_NAME VARCHAR2); PROCEDURE DELETE_ROW(P_COUNTRY_ID VARCHAR2); END COUNTRIES_REGIONS_PKG; 主体部分： CREATE OR REPLACE PACKAGE BODY COUNTRIES_REGIONS_PKG IS PROCEDURE INSERT_ROW(P_COUNTRY_ID VARCHAR2, P_COUNTRY_NAME VARCHAR2, P_REGION_ID NUMBER, P_REGION_NAME VARCHAR2) IS BEGIN INSERT INTO REGIONS (REGION_ID, REGION_NAME) VALUES (P_REGION_ID, P_REGION_NAME); INSERT INTO COUNTRIES (COUNTRY_ID, COUNTRY_NAME, REGION_ID) VALUES (P_COUNTRY_ID, P_COUNTRY_NAME, P_REGION_ID); END INSERT_ROW; PROCEDURE UPDATE_ROW(P_COUNTRY_ID VARCHAR2, P_COUNTRY_NAME VARCHAR2, P_REGION_ID NUMBER, P_REGION_NAME VARCHAR2) IS BEGIN UPDATE REGIONS R SET R.REGION_NAME = P_REGION_NAME WHERE R.REGION_ID = P_REGION_ID; UPDATE COUNTRIES C SET C.COUNTRY_NAME = P_COUNTRY_NAME WHERE C.COUNTRY_ID = P_COUNTRY_ID; END UPDATE_ROW; PROCEDURE DELETE_ROW(P_COUNTRY_ID VARCHAR2) IS T_REGION_ID NUMBER; BEGIN SELECT C.REGION_ID INTO T_REGION_ID FROM COUNTRIES C WHERE C.COUNTRY_ID = P_COUNTRY_ID; DELETE FROM COUNTRIES C WHERE C.COUNTRY_ID = P_COUNTRY_ID; DELETE FROM REGIONS R WHERE R.REGION_ID = T_REGION_ID; END; END COUNTRIES_REGIONS_PKG;

3、新建EO，基于视图Countries_Regions_v，名为CountriesRegionsEO，设置CountryId为主键，并设置四个属性的updatable属性分别为：

CountryId	While New
CountryName	Always
RegionId	While New
Region Name	Always

4、新建类，名为PLSQLEntityImpl.java，继承于EntityImpl类，重写方法doDML，并分别添加callInsertProcedure，callUpdateProcedure，callDeleteProcedure方法。

@Override protected void doDML(int operation, TransactionEvent transactionEvent) { //super.doDML(operation, transactionEvent); if (operation == DML_INSERT) callInsertProcedure(transactionEvent); else if (operation == DML_UPDATE) callUpdateProcedure(transactionEvent); else if (operation == DML_DELETE) callDeleteProcedure(transactionEvent); } /* Override in a subclass to perform non-default processing */ protected void callInsertProcedure(TransactionEvent e) { super.doDML(DML_INSERT, e); } /* Override in a subclass to perform non-default processing */ protected void callUpdateProcedure(TransactionEvent e) { super.doDML(DML_UPDATE, e); } /* Override in a subclass to perform non-default processing */ protected void callDeleteProcedure(TransactionEvent e) { super.doDML(DML_DELETE, e); }

5、生成CountriesRegionsEO的实现类，并使该类(CountriesRegionsEOImpl.java)继承于PLSQLEntityImpl.java类。重写callInsertProcedure，callUpdateProcedure，callDeleteProcedure方法，分别在三个方法内调用在步骤2中创建的PL/SQL程序包中的过程，添加帮助方法callStoredProcedure，完成对于程序包中过程的调用。

@Override protected void callInsertProcedure(TransactionEvent e) { callStoredProcedure("COUNTRIES_REGIONS_PKG.INSERT_ROW(?,?,?,?)", new Object[] { this.getCountryId(), this.getCountryName(), this.getRegionId(), this.getRegionName() }); } @Override protected void callUpdateProcedure(TransactionEvent e) { callStoredProcedure("COUNTRIES_REGIONS_PKG.UPDATE_ROW(?,?,?,?)", new Object[] { this.getCountryId(), this.getCountryName(), this.getRegionId(), this.getRegionName() }); } @Override protected void callDeleteProcedure(TransactionEvent e) { callStoredProcedure("COUNTRIES_REGIONS_PKG.DELETE_ROW(?)", new Object[] { this.getCountryId() }); } 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 <> // 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) { } } }

6、生成该EO的VO，并添加到AM，运行该AM，使用业务组件浏览器进行测试。这里添加新记录，点击保存后成功。（修改、删除均测试通过）

7、在完成了对于业务组件浏览器的测试后，新建页面，使用页面进行测试。

最终运行结果：

相关代码、程序包及脚本请查看：

howtoeoonview.rar