March 2014

Thursday, 27 March 2014

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);

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

Monday, 24 March 2014

Iterating ViewObject vertically (get all attributes of ViewObject) programmatically

Hello All,
This post talks about a requirement of getting all attributes (column names) of a viewObject programmatically

Create a fusion web application and business component using department table

you can see all attributes in Departments entity object

Now to get ViewObject's attributes name , there is a method written in AMImpl class , see it, i have added a facesMessage to show all names on a Message Board

    /**Method to get all attributes of a viewObject
     * */
    public void iterateVoVertically() {
        ViewObjectImpl vo = this.getDepartments1();
        ViewAttributeDefImpl[] attrDefs = vo.getViewAttributeDefImpls();
        int count = 0;
        StringBuilder infoMsg =
            new StringBuilder("<html><body><b><p style='color:red'> Attribute of Department ViewObject are-</p></b>");
        infoMsg.append("<ul>");


        for (ViewAttributeDefImpl attrDef : attrDefs) {
            byte attrKind =
                attrDefs[count].getAttributeKind(); //checks attribute kind for each element in an array of AttributeDefs
            if (attrKind != AttributeDef.ATTR_ASSOCIATED_ROW && attrKind != AttributeDef.ATTR_ASSOCIATED_ROWITERATOR) {
                String columnName = attrDef.getName();
                infoMsg.append("<li> <b>" + attrDef.getName() + "</b></li>");
                System.out.println("Column Name-" + columnName);
            }
        }
        infoMsg.append("</ul><br>");
        infoMsg.append("</body></html>");
        FacesMessage msg = new FacesMessage(infoMsg.toString());
        msg.setSeverity(FacesMessage.SEVERITY_INFO);
        FacesContext.getCurrentInstance().addMessage(null, msg);
    }

I have called this method through page binding and AM Client on a button of page

now on click of button all attributes of Departments ViewObject are shown in FacesMessage, you can use it in your code

Cheers :-)

Saturday, 8 March 2014

Launching browser print dialog using simple javascript function in ADF

Hello All ,
This posts talks about a requirement of printing a simple page (not much component as, form,tree,etc & not much data)
you can use a simple one liner javascript function to invoke browser's print dialog.

there is a page with Departments table on it, and a button to print this page

Called this simple javascript function on button click to open print dialog

window.print();

to execute javascript through managed bean use this method

    /**Method to execute Javascript
     * @param javascriptCode
     */
    public static void runJavaScriptCode(String javascriptCode) {
        FacesContext facesCtx = FacesContext.getCurrentInstance();
        ExtendedRenderKitService service = Service.getRenderKitService(facesCtx, ExtendedRenderKitService.class);
        service.addScript(facesCtx, javascriptCode);
    }

click on print button- In Google Chrome

In Mozilla-

after printing, the page look like this

Sample ADF Application- Download
Cheers :-)

Wednesday, 5 March 2014

Igonre null values in viewCriteria -Jdeveloper 12c (Not a bug but a change)

Hello All,
In jdev 12c there is some change in viewCriteria design window, there is a checkbox to ignore null values in criteria , in 11g release it was enabled for all values in criteria item

but in 12c it is disabled for all values of ViewCriteria bind variable

there is two different section for creating bind variable in 12c
1. ViewCriteria Bind Variable
2. Query Bind Variable(Required)
So when you create a bind var of string type in query variables.

and use it in viewCriteria , only then this 'ignore null values' checkbox will be enabled

and when you use viewCriteria bind varibles it is disabled but sometimes you need to change it, so can change it using ViewObject xml source

Select ViewCriteria and go to source

Now change GenerateIsNullClauseForBindVars="false" to true

Cheers :-)

Tuesday, 4 March 2014

Showing white-spaces properly in ADF table column- 11g & 12c

Hello All,
This posts talks about a requirement of showing white-spaces before any text/number in an af:table
Suppose i have , form and table of a ViewObject on page

Case 1- both table and form are editable , in this case you can see that white spaces are properly visible in both form and table

but normally in applications we have a read-only table and editable form so in this case you can see that white-spaces are not visible in table

But sometimes we need to show spaces in table same as in form so to do this select that field and go to property inspector and select component

Now set it's contentStyle (for input component as- af:inputText) or inlineStyle (for display component as- af:outputText)- white-space:pre;

now run your page and see-

Cheers :-)

Ashish Awasthi's Blog

Search this blog

Thursday, 27 March 2014

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

Monday, 24 March 2014

Iterating ViewObject vertically (get all attributes of ViewObject) programmatically

Saturday, 8 March 2014

Launching browser print dialog using simple javascript function in ADF

Wednesday, 5 March 2014

Igonre null values in viewCriteria -Jdeveloper 12c (Not a bug but a change)

Tuesday, 4 March 2014

Showing white-spaces properly in ADF table column- 11g & 12c