Brief overview of JDBC

JDBC(Java DataBase Connectivity) is a Java API for connecting a wide range of Databases through Java program. Using JDBC API you can also access data from sources like spreadsheets and flat files. These apis are part of standard Java platform (JAVA SE). The actual driver to communicate with data sources is not part of the platform though (link).

JDBC API has two packages java.sql and javax.sql.
Below are the steps for successful communication with the data sources:

Step 1: Register Driver

Before starting the real communication with the data source, you need to load data source drivers in the memory. Recall that Java's class Class provides a static method (forName()) to load a class. It can be used to load and return a Class reference, but here, we just need to load appropriate driver class. 

    String driverName = "com.mysql.jdbc.Driver";

    Class.forName(driverName);

 

Step 2: Establish a Connection

If step 1 executes successfully without any error/exception then we are ready to move to the next step and create a Connection object. DriverManager provides a factory method to create the Connection object. 

    static final String USER = "user.id";

    static final String PASSWORD = "user.password";

    static final String URL = "jdbc.url";

    Connection con = DriverManager.getConnection(URL, USER, PASSWORD);

 

Step 3: Create Statement/s

SQL Statement is created using Connection object. This object is used for executing static SQL statement and returning the result.

    Statement stmt = con.createStatement();

Supported Statements are:

1. Statement: Used for implementing SQL statements without any parameter.
2. PreparedStatement: Used for precompiling SQL statements that might contain parameter as well.
3. CallableStatement: Used for executing stored procedures (may contain input as well as output parameter).

 

Step 4 : Execute Query 

Finally, execute the query by calling the appropriate method on Statement object.

1. execute: Used if query could return one or more ResultSet objects.
2. executeQuery: Returns one ResultSet object.
3. executeUpdate: Returns an integer representing the number of rows affected by the query. Use this method during INSERT, UPDATE and DELETE. 

     String sqlQuery = "Select name from EMPLOYEE";

     ResultSet rs = stmt.executeQuery(sqlQuery);

 

Step 5: Extract data from ResultSet

ResultSet object has response/result data which can be extracted using cursor/iterator. This is done by calling the appropriate get method (getXXX()).

    while(rs.next()){
         String name = rs.getString("name");
         //retrieve other attributes
    }

 

Step 6: Close Connection

Finally close connection and release resources held.

     }finally{
        if(stmt != null) {
           stmt.close();  //ResultSet also gets closed implicitly. 
       }
    }