Sat. May 8th, 2021

Requirement: I have a button with the name “Execute SQL From Managed Bean“. On click of this button, it should execute an SQL query for the Departments table and should display the Department Id and the Department Name in the console.

Solution: For the solution of the above requirement follow the steps as shown below:

Step 1: Create an Oracle ADF Fusion Web Application. Create an Application Module.

Please have a look at the Data Source, as we will be using this Data Source Name inside our Managed Bean code.

Step 2: Create a demo.jspx page. Drag and drop and af:button and set text=”Execute SQL From Managed Bean” and actionListener=”#{pageFlowScope.MyBean.executeSQLFromBeanAL}”

Thus, the complete code for demo.jspx page is shown below:

<?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:af="http://xmlns.oracle.com/adf/faces/rich">
    <jsp:directive.page contentType="text/html;charset=UTF-8"/>
    <f:view>
        <af:document title="demo.jspx" id="d1">
            <af:form id="f1">
                <af:button text="Execute SQL From Managed Bean" id="b1"
                           actionListener="#{pageFlowScope.MyBean.executeSQLFromBeanAL}"/>
            </af:form>
        </af:document>
    </f:view>
</jsp:root>

And, the complete code for MyBean.java class is shown below:

package susantotech.com;

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.faces.event.ActionEvent;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;

import javax.sql.DataSource;

import oracle.jbo.JboException;

public class MyBean {
    public MyBean() {
    }

    public static Connection getConnection(String dataSourceName) throws NamingException, SQLException {
        Connection connection = null;
        DataSource datasource = null;
        Context initialContext = new InitialContext();
        if (initialContext == null) {
        }
        datasource = (DataSource) initialContext.lookup(dataSourceName);
        if (datasource != null) {
            connection = datasource.getConnection();
        } else {
            System.out.println("Error occured with JDBC DataSource.");
        }
        return connection;
    }

    public void executeSQLFromBeanAL(ActionEvent actionEvent) {
        Connection connection = null;
        try {
            connection = getConnection("java:comp/env/jdbc/HRDS");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (NamingException e) {
            e.printStackTrace();
        }
        try {
            PreparedStatement stmt = connection.prepareStatement("SELECT * FROM departments");
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.println("Department Id is : " + rs.getInt(1) + " and Department Name is : " +
                                   rs.getString(2));
            }
            rs.close();
        } catch (SQLException e) {
            throw new JboException(e);
        }
    }
}

Save all and run the application. Thus, the ran application is shown below:

Click on the “Execute SQL From Managed Bean” button and check the output in the JDeveloper console.

Hence, the solution to our requirement.

If you like the post please comment, share, and do join me on Facebook. Please subscribe to my YouTube Channel for video tutorials.

Thanks & Regards,
Susanto Paul

 231 total views,  3 views today

By Susanto Paul

Susanto is an Oracle ACE, a qualified MCA, MBA, and a highly-skilled Senior Oracle Specialist. He is an enthusiastic Blogger and YouTuber who helps learners to solve their complex problems more efficiently. He has 9+ years of experience in multiple technologies like AWS, Oracle ADF, Oracle APEX, Oracle JET, Oracle VBCS, Oracle IDCS, Oracle PL/SQL, Oracle Integration Cloud, Java, JDBC, Servlets, JSP, Spring, Hibernate, HTML5, CSS3, JavaScript, TypeScript, NodesJS, Angular, MySQL, Oracle WebLogic Server, JUnit, JMeter, Selenium Web Driver, etc. He is a certified: Oracle Certified Professional Java SE 6 Programmer, Oracle ADF 11g Certified Implementation Specialist, Oracle Cloud Platform Application Integration 2020 Certified Specialist, Oracle Cloud Infrastructure Foundations 2020 Certified Associate, and Oracle Cloud Infrastructure Developer 2020 Certified Associate

Leave a Reply

Your email address will not be published. Required fields are marked *