ADF11g-030： 通过SQL生成动态table

一、介绍

本文介绍如何根据节目输入的sql来生成动态table，方法有些另类，后端使用的是纯Java API来做的。是因为我在使用viewobject操作时，发现太麻烦了。这里列出一些主要步骤和程序代码。

二、创建ADF Application，Model层代码如下

    public DynamicTableData getDynamicTableDataFromSQL(String sql) {        DynamicTableData tableData = new DynamicTableData();        List<String> columns = new ArrayList<String>();        List<Map<String, Object>> data = new ArrayList<Map<String, Object>>();        Statement stat =            this.getDBTransaction().createStatement(-1);        ResultSet rs = null;        try {            rs = stat.executeQuery(sql);            ResultSetMetaData metaData = rs.getMetaData();            int count = metaData.getColumnCount();            for(int i=1; i<=count; i++) {                columns.add(metaData.getColumnName(i));            }            while(rs.next()) {                Map<String, Object> row = new HashMap<String, Object>();                for(int i=0; i<columns.size(); i++) {                    String column = columns.get(i);                    Object value = rs.getObject(column);                    row.put(column, value);                }                data.add(row);            }            tableData.setColumns(columns);            tableData.setData(data);        } catch (SQLException e) {            e.printStackTrace();        }        return tableData;    }

package model;import java.io.Serializable;import java.util.List;import java.util.Map;public class DynamicTableData implements Serializable {    /**     * 列集合     */    private List<String> columns;    /**     * 数据集合，一个map对象代表一行数据，key是列名称,value是列数据库     */    List<Map<String, Object>> data;        public DynamicTableData() {        super();    }        public DynamicTableData(List<String> columns, List<Map<String, Object>> data) {        this.columns = columns;        this.data = data;    }    public void setColumns(List<String> columns) {        this.columns = columns;    }    public List<String> getColumns() {        return columns;    }    public void setData(List<Map<String, Object>> data) {        this.data = data;    }    public List<Map<String, Object>> getData() {        return data;    }}

说明：将方法getDynamicTableDataFromSQL公布到Client Interface

三、View层代码

    1.Manage Bean，将manage bean在taskflow文件中配置好。

package view;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import model.DynamicTableData;import oracle.adf.model.BindingContext;import oracle.binding.BindingContainer;import oracle.binding.OperationBinding;public class DynamicTableBean {    private List tableData;    private List columns;    private DynamicTableData dynamicTableData;        public DynamicTableBean() {        /* tableData = new ArrayList();        Map map1 = new HashMap();        map1.put("name", "AAAa");        map1.put("age", "44");        Map map2 = new HashMap();        map2.put("name", "bbbb");        map2.put("age", "55");        tableData.add(map1);        tableData.add(map2);        columns = new ArrayList();        columns.add("name");        columns.add("age"); */    }        public String executeQuery() {        BindingContainer bindings = getBindings();        OperationBinding operationBinding = bindings.getOperationBinding("getDynamicTableDataFromSQL");        DynamicTableData result = (DynamicTableData)operationBinding.execute();        this.dynamicTableData = result;        return null;    }        public void setTableData(List tableData) {        this.tableData = tableData;    }    public List getTableData() {        if(dynamicTableData != null) {            tableData = dynamicTableData.getData();        }        return tableData;    }    public void setColumns(List columns) {        this.columns = columns;    }    public List getColumns() {        if(dynamicTableData != null) {            columns = dynamicTableData.getColumns();        }        return columns;    }    public void setDynamicTableData(DynamicTableData dynamicTableData) {        this.dynamicTableData = dynamicTableData;    }    public DynamicTableData getDynamicTableData() {        return dynamicTableData;    }    public BindingContainer getBindings() {        return BindingContext.getCurrent().getCurrentBindingsEntry();    }}

     2.页面代码

<?xml version='1.0' encoding='UTF-8'?><jsp:root xmlns:jsp="http://java.sun.com/JSP/Page" version="2.1"          xmlns:f="http://java.sun.com/jsf/core"          xmlns:h="http://java.sun.com/jsf/html"          xmlns:af="http://xmlns.oracle.com/adf/faces/rich">  <jsp:directive.page contentType="text/html;charset=UTF-8"/>  <f:view>    <af:document id="d1">      <af:messages id="m1"/>      <af:form id="f1">        <af:panelGroupLayout id="pgl1" layout="vertical">          <af:panelGroupLayout id="pgl2" layout="vertical">            <af:panelFormLayout id="pfl1">              <af:inputText value="#{bindings.sql.inputValue}"                            label="#{bindings.sql.hints.label}"                            required="#{bindings.sql.hints.mandatory}"                            columns="50"                            maximumLength="#{bindings.sql.hints.precision}"                            shortDesc="#{bindings.sql.hints.tooltip}" id="it1"                            rows="4">                <f:validator binding="#{bindings.sql.validator}"/>              </af:inputText>              <af:commandButton                                text="Execute Query"                                disabled="#{!bindings.getDynamicTableDataFromSQL.enabled}"                                id="cb1"                                action="#{viewScope.dynamicTableBean.executeQuery}"/>            </af:panelFormLayout>          </af:panelGroupLayout>          <af:panelGroupLayout id="pgl3" layout="vertical">            <af:table value="#{viewScope.dynamicTableBean.tableData}" var="row"                      rowBandingInterval="0" id="t1" width="100%">              <af:forEach var="def" items="#{viewScope.dynamicTableBean.columns}">                <af:column headerText="#{def}" sortable="true" id="column1">                  <af:outputText value="#{row[def]}" id="ot1"/>                </af:column>              </af:forEach>            </af:table>          </af:panelGroupLayout>        </af:panelGroupLayout>      </af:form>    </af:document>  </f:view></jsp:root>