Executing SQL query in an ADF Application using DBTransaction & JDBC DataSource

hello all,
this post is about executing SQL query in your ADF Application
Sometimes we need to execute some query in our managed bean or any of implementation class of Model, this is quite easy

• I have a fusion web application having connection with hr schema of Oracle DB
• now i have to get Max Department Id of Departments Table using this statement



SELECT max(DEPARTMENT_ID) CODE FROM DEPARTMENTS



• So to execute this query i have created a method in AMImpl class using DBTransaction



    /**Method to Execute DB SQL Query using DBTransaction
     * @param query
     * @return
     */
    protected Integer executeQuery(String query) {
        ResultSet rs;
        Integer code = null;
        try {
            rs = getDBTransaction().createStatement(0).executeQuery(query);
            if (rs.next()) {
                code = ((BigDecimal) rs.getObject(1)).intValue();
            }

            rs.close();
            return code;

        } catch (SQLException e) {
            throw new JboException(e);
        }
    }



• now called this method and passed my SQL statement





        Integer deptID = executeQuery("SELECT max(DEPARTMENT_ID) CODE FROM DEPARTMENTS");
        System.out.println("Department Id-" + deptID);


And Output is-



• Second way is by using JDBC DataSource , for this first we have to get Connection using DataSource Name- see this method



    /**Method to get Connection using JDBC DataSource Name
     * @param dsName
     * @return
     * @throws NamingException
     * @throws SQLException
     */
    public static Connection getConnectionDS(String dsName) throws NamingException, SQLException {
        Connection con = null;
        DataSource datasource = null;

        Context initialContext = new InitialContext();
        if (initialContext == null) {
        }
        datasource = (DataSource) initialContext.lookup(dsName);
        if (datasource != null) {
            con = datasource.getConnection();
        } else {
            System.out.println("Failed to Find JDBC DataSource.");
        }
        return con;
    }



• Now after getting connection , we can execute SQL query using Statement or PreparedStatement
• Go to your ApplicationModule to get DataSource Name, in Configuration tab open AMLocal

•  Copy DataSource Name , and use it to get Connection and to execute query



        Connection con = null;
        try {
            con = getConnectionDS("java:comp/env/jdbc/APPDS");
        } catch (SQLException e) {
        } catch (NamingException e) {
        }
        try {
            PreparedStatement stmt = con.prepareStatement("SELECT * FROM DEPARTMENTS");
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.println("Department Id-" + rs.getInt(1) + " and Department Name-" + rs.getString(2));
            }

            rs.close();


        } catch (SQLException e) {
            throw new JboException(e);
        }



• After executing see the output- 

 Cheers :-) happy coding

Conditional Execution of Model (EO) level Validation- Oracle ADF

You all know about ADF model level validation , we can apply variety of validation on model (Entity Object), as for length, compare, key, regular expression, script and unique key etc.

I am not going to describe whole process for model layer validation, for detailed tutorial on EO level validation see -Dynamic (parameterize) model level validation using message token
Now see how we can execute these validation conditionally -

• When we apply unique key validation on Entity Object- this window appears-

• Now go to Validation Execution and write your condition in given box for that you want to execute validation

• Now go to Failure Handling tab and write your failure handling message, and you are done





• Now run your application and see how validation executes for your condition
• this functionality is really helpful and sometimes avoids writing lot of managed bean code