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

Insert New Row in ADF ViewObject Programatically

Here I am showing how to insert new row in ADF view object programmatically.

For this create a method in managed bean for your button on that you want to insert row.
and call this AMImpl method that makes use of ViewObjectImpl createRow method to add new row in RowSet

Created a method in AMImpl class and it'll be called in managed bean using binding layer

This method creates a row in ViewObject , and you can set(Insert) this row with some values using this simple snippet of code 

//Get ViewObject Instance
    ViewObjectImpl demoVo = this.getEmployeesDemo1();


// Creates a row in ViewObject
    Row r = demoVo.createRow();  
      
// You can set attribute values in this new row
    r.setAttribute("EmployeeId", 001); 

//Insert that row in ViewObject
    demoVo.insertRow(r);   
             
//Commit the changes, If you need
    this.getDBTransaction().commit();     
    demoVo.executeQuery();

This is how you can create row in ViewObject