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="" version="2.1" xmlns:f="" xmlns:af=""> <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
597 total views, 2 views today