ADF, Database, SQL and PL/SQL

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.