Please disable your adblock and script blockers to view this page

Search this blog

Showing posts with label JDBC. Show all posts
Showing posts with label JDBC. Show all posts

Tuesday 19 April 2016

Define WHERE Clause on dynamic ViewObject created on the fly

Sunday 22 March 2015

Java Database Connectivity- JDBC Tutorial, PreparedStatement, ResultSet

JDBC is java based API or technology to access data from database and use in Java Desktop or Enterprize application. In short we can say JDBC is an interface between Java and Database, using JDBC you can interact with any of database as Oracle, MySQL, Access.
JDBC is released with JDK(Java Development Kit)1.1 on 19 Feb 1997 and It is a part of Java Standard Edition. if you know about DBMS(Database Management Systems) then you should know about Database Drivers, drivers are of 4 types.
  1. Type 1 Driver - JDBC-ODBC bridge
  2. Type 2 Driver - Native-API Driver
  3. Type 3 Driver - Network-Protocol Driver(MiddleWare Driver)
  4. Type 4 Driver - Native-Protocol Driver(Pure Java Driver)-Thin Driver
If you don't have java installed , follow the link below to donload it

JDK1.7- Download jdk1.7
Now we will learn that how to implement Database connection in Java. To start with JDBC you must have a little knowledge of DBMS and SQL(Structured Query Language) to interact with Database
here i will show you that how to connect with Oracle Database
Follow these steps to connect with Oracle Database

1. Load Driver (Thin Driver)

The very first step towards database connection is Loading Driver, as discussed we use pure java driver in JDBC connectivity there are two steps to load driver for connection

A. Using DriverManager class
The syntax is - DriverManager.registerDriver(DriverName);

  1.         try {
  2.             DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
  4.         } catch (SQLException e) {
  5.         }

B. Using Class.Forname class
The syntax is - Class.forName(”DriverName”);

  1.         try {
  2.              Class.forName("oracle.jdbc.driver.OracleDriver");
  4.         } catch (SQLException e) {
  5.         }

2. Create Connection

Connection interface is inside java.sql package and extends Wrapper interface. now to create Connection we register driver with DriverManager class.
Syntax is- DriverManager.getConnection("url string", "username", "password");

  1.  try {
  2.      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
  3.      con=DriverManager.getConnection(
  4.      "jdbc:oracle:thin:@", "hr", "hr");
  5.  } catch (SQLException e) {
  6.  }

You have to change url string with your ip address and SID according to your database setting

3. Execute PreparedStatement and return Resultset

Now we see that how to execute SQL query using PreparedStatement and store its result in ResultSet object
Here i am getting username and password from LOGIN_DET table (you can use your own query here)

  1.         Connection con;
  2.         try {
  3.             DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
  4.             con = DriverManager.getConnection("
  5.            jdbc:oracle:thin:@", "hr", "hr");
  6.             PreparedStatement psmt = con.prepareStatement("SELECT * FROM LOGIN_DET");
  7.             ResultSet rs = psmt.executeQuery();
  8.             if ( {
  9.                 while ( {
  10.                 System.out.println("Username is--" + rs.getString(1)
  11.                + "and Password is-->" + rs.getString(2) +
  12.                "Email Id is--->" + rs.getString("E_ID"));
  13.                 }
  14.             }
  15.             psmt.close();
  16.             con.close();
  17.         } catch (SQLException e) {
  18.         }

This is the brief overview of using JDBC with Oracle Database, For others you have to change only DriverName respectively
Happy Learning :)

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


  • 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 ( {
                    code = ((BigDecimal) rs.getObject(1)).intValue();
                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 ( {
                    System.out.println("Department Id-" + rs.getInt(1) + " and Department Name-" + rs.getString(2));
            } catch (SQLException e) {
                throw new JboException(e);

  • After executing see the output- 
 Cheers :-) happy coding