Please disable your adblock and script blockers to view this page

Search this blog

Thursday 27 March 2014

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

2 comments :

  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…..

    ReplyDelete
  2. should we not be closing the statement as well?

    ReplyDelete