Creating JDBC Application

There are following six steps involved in building a JDBC application −

• Import the packages − Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.
• Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with the database.
• Create Statement
• Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to the database.
• Extract data from result set − Requires that you use the appropriate  ResultSet.getXXX() 

method to retrieve the data from the result set.

• Clean up the environment − Requires explicitly closing all database resources versus relying on the JVM's garbage collection.

JDBC - Database Connection 

The programming involved to establish a JDBC connection simple steps:

1. Import JDBC Packages − Add import statements to your Java program to import required classes in your Java code.

To use the standard JDBC package, which allows you to select, insert, update, and delete data in SQL tables, add the following imports to your source code −

import java.sql.* ;  // for standard JDBC programs

2. Register JDBC Driver − This step causes the JVM to load the desired driver implementation into memory so it can fulfill your JDBC requests.

The most common approach to register a driver is to use Java's Class.forName() method, to dynamically load the driver's class file into memory, which automatically registers it.

Class.forName("oracle.jdbc.driver.OracleDriver");

The second approach you can use to register a driver, is to use the static DriverManager.registerDriver() method.

The registerDriver() method can be used if you are using a non-JDK compliant JVM, such as the one provided by Microsoft.

Driver myDriver = new oracle.jdbc.driver.OracleDriver();

      DriverManager.registerDriver( myDriver );

3. Database URL Formulation − This is to create a properly formatted address that points to the database to which you wish to connect.

After loaded the driver, a connection can be established using the  DriverManager.getConnection() method.

The three overloaded DriverManager.getConnection() methods −

• getConnection(String url)
• getConnection(String url, Properties prop)
• getConnection(String url, String user, String password)

Here each form requires a database URL. A database URL is an address that points to your database.

4. Create Connection Object − Finally, code a call to the DriverManager object's 

getConnection( ) method to establish actual database connection.

Connection conn = DriverManager.getConnection(URL, USER, PASS);

     5. Create statement -  

     createStatement() of Connection class is used to make object of Statements.

        Eg: Statement stat=con.createStatement();

—  Statement object can call executeQuery(“SQL Command”) to execute a select statement.

—  Use executeUpdate(“SQL Command”) to execute any data updation commands

—  Three types of statements each reflecting a specific SQL statements

—  Statement

—  PreparedStatement

—  CallableStatement 

    6. ExecuteQuery()    

    An executeQuery() method retrives the selected records as an object of ResultSet class. It stores data     in tabular format. Rowid and ColID can be used to identify each data. Rows are records of table and     columns are fields of table

        A cursor is attached to fetch data from any row . Allows the program to scroll through each row             and read all the columns of the data

    7. Close the statement and connection

    At the end of your JDBC program, it is required explicitly to close all the connections to the database     to end each database session (conn.close()). However, if you forget, Java's garbage collector will             close the connection when it cleans up stale objects.

Ex: JDBC Example

import java.sql.*;

public class FirstExample {

   static final String DB_URL = "jdbc:mysql://localhost/STUDENT";

   static final String USER = "guest";

   static final String PASS = "guest123";

   static final String QUERY = "SELECT id, first, last, age FROM Employees";

   public static void main(String[] args) {

      // Open a connection

      try{

        Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);

         Statement stmt = conn.createStatement();

         ResultSet rs = stmt.executeQuery(QUERY);) {

         // Extract data from result set

         while (rs.next()) {

            // Retrieve by column name

            System.out.print("ID: " + rs.getInt("id"));

            System.out.print(", Age: " + rs.getInt("age"));

            System.out.print(", First: " + rs.getString("first"));

            System.out.println(", Last: " + rs.getString("last"));

         }

      } catch (SQLException e) {

         e.printStackTrace();

      }

   }

}