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