ADF method for calling DB procedures, executing other SQL statements with input and output parameters

How to call DB procedure with input and output parameters. Here I provide an example of ADF method which can be used with any number of in/pout parameters used in any order:

    public void runStatement(String stt, boolean commit, ArrayList<Map<String, Object>> pars) {
        
        CallableStatement st = this.getDBTransaction().createCallableStatement((stt), 0);
        try {
            for (int i = 0; i < pars.size(); i++) {
                if (pars.get(i).get("type").equals("in")) {
                    if (pars.get(i).get("datatype").equals("String")) {
                        st.setString(i + 1, (String)pars.get(i).get("value"));
                    }
                } else {
                    if (pars.get(i).get("datatype").equals("String")) {
                        st.registerOutParameter(i + 1, Types.VARCHAR);
                    }
                }
            }
            st.execute();
            for (int i = 0; i < pars.size(); i++) {
                if (pars.get(i).get("type").equals("out")) {
                    if (pars.get(i).get("datatype").equals("String")) {
                        pars.get(i).put("value", st.getString(i+1));
                    }
                }
            }
            if (commit) {
                getDBTransaction().commit();
            }
        } catch (SQLException e) {
            throw new JboException(e);
        } finally {
            try {
                st.close();
            } catch (Exception exc) {
                // something to do
            }
        }
    }

As you can see here I use List of Map. Containing Map has 3 mappings: type, datatype, value. Type stands for in/out, datatype could be any datatype which should be handled in this method explicitly. In this example I handle just String. If you need any other, e.g. Integer, you should extend this method by some more ifs... Value stands for any value which is delivered to DB procedure as input parameter or got back as output parameter.

Example of constructing parameter array:

    public String createEmployee(String name, String surname) {

        ArrayList<Map<String, Object>> pars = new ArrayList<Map<String, Object>>(3);

        Map<String,Object> parMap = new HashMap<String, Object>();
        parMap.put("type", "in");
        parMap.put("datatype", "String");
        parMap.put("value", surname);
        pars.add(0, parMap);

        parMap = new HashMap<String, Object>();
        parMap.put("type", "in");
        parMap.put("datatype", "String");
        parMap.put("value", name);
        pars.add(1, parMap);

        parMap = new HashMap<String, Object>();
        parMap.put("type", "out");
        parMap.put("datatype", "String");
        pars.add(2, parMap);

        runStatement("begin program_pkg.create_employee(?,?,?); end;", true, pars);
        return (String)pars.get(2).get("value");
    }

This method calls DB procedure which creates employee and returns autogenerated ID.

How to output text with line breaks in ADF ?

   If we use regular "af:outputtext" component to print text with line breaks (new line symbols like "\n" or  "\r\n")  in ADF - we don't succeed. Text is displayed in one line regardless of those symbols. Instead we need to use  "af:outputFormatted" component. But it is not enough. While it does not understand "\n" or "\r\n" symbols, we need to use HTML <br> tag instead. Thus need to translate text manually by using custom java method. How it could be done is described in my other post - How to pass parameter to method using expression language (EL) in ADF? .

How to pass parameter to method using expression language (EL) in ADF?

Apparently there is not possible to pass parameter to java method using expression language. But it is possible to use Map as a return type for method. So - utilization of Map can benefit as a workaround for our needs.

Example:

Suppose in ADF faces page we need to print text with all line break symbols replaced with HTML <br> tags.


  • Create bean "utilsBean" with method "getBrFormattedString"

    public Map<String, String> getBrFormattedString() {
        return new HashMap<String, String>() {
            @Override
            public String get(Object key) {
                String str = (String)key;
                str = str.replaceAll("(\\r|\\n|\\r\\n)+", "<br>");
                return str;
            }
        };

    }

  • Create "af:outputFormatted" component

 <af:outputFormatted value="#{utilsBean.brFormattedString[requestScope.messageDetails]}"
                                        id="of1"/>

As you can see, in this example Map is constructed dynamically and finally it acts as a regular method. We can put there any functionality we need. We could use any kind of object instead of String as well - Map<Object, Object>.