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

Sharing is Caring

Hello all,
This post is about  executing SQL query in an ADF Application, In this post, I am going to show how to execute SQL query in the managed bean or any of implementation class of Model, this is quite easy

    • I have a fusion web application having a 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-

 

    • The 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 the connection, we can execute SQL query using Statement or PreparedStatement
  • Go to your ApplicationModule to get DataSource Name, in Configuration tab open AMLocal

 

Executing SQL query
    •  Copy DataSource Name, and use it to get Connection and to execute the 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 Learning

Related Posts

An Oracle ACE, Blogger, Reviewer, Technical Lead working on Oracle ADF

0 thoughts on “Executing SQL query in an ADF Application using DBTransaction & JDBC DataSource”

  1. Mark Hurd Oracle clearly feels this pressure of companies moving to cloud services and is trying to drum up any momentum it can to its own cloud services to offset the declines of its core business of selling software licenses and databases…..

Leave a Reply

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