What are different steps used in JDBC? Write down a small program showing all steps.

 The fundamental steps involved in the process of connecting to a database and executing a query consist of the following:

• Import JDBC packages.
• Load and register the JDBC driver.
• Open a connection to the database.
• Create a statement object to perform a query.
• Execute the statement object and return a query resultset.
• Process the resultset.
• Close the resultset and statement objects.
• Close the connection.

These steps are described in detail in the sections that follow.

1. Import JDBC Packages

This is for making the JDBC API classes immediately available to the application program. The following import statement should be included in the program irrespective of the JDBC driver being used:

import java.sql.*;

Additionally, depending on the features being used, Oracle-supplied JDBC packages might need to be imported. For example, the following packages might need to be imported while using the Oracle extensions to JDBC such as using advanced data types such as BLOB, and so on.

import oracle.jdbc.driver.*;

import oracle.sql.*;

2. Load and Register the JDBC Driver

This is for establishing a communication between the JDBC program and the Oracle database. This is done by using the static registerDriver() method of the DriverManager class of the JDBC API. The following line of code does this job:

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

JDBC Driver Registration

For the entire Java application, the JDBC driver is registered only once per each database that needs to be accessed. This is true even when there are multiple database connections to the same data server.

Alternatively, the forName() method of the java.lang.Class class can be used to load and register the JDBC driver:

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

However, the forName() method is valid for only JDK-compliant Java Virtual Machines and implicitly creates an instance of the Oracle driver, whereas the registerDriver() method does this explicitly.

3. Connecting to a Database

Once the required packages have been imported and the Oracle JDBC driver has been loaded and registered, a database connection must be established. This is done by using the getConnection() method of the DriverManager class. A call to this method creates an object instance of the java.sql.Connection class. The getConnection() requires three input parameters, namely, a connect string, a username, and a password. The connect string should specify the JDBC driver to be yes and the database instance to connect to.

The getConnection() method is an overloaded method that takes

Three parameters, one each for the URL, username, and password.

Only one parameter for the database URL. In this case, the URL contains the username and password.

The following lines of code illustrate using the getConnection() method:

Connection conn = DriverManager.getConnection(URL, username, passwd);

Connection conn = DriverManager.getConnection(URL);

4. Querying the Database

Querying the database involves two steps: first, creating a statement object to perform a query, and second, executing the query and returning a resultset.

a)Creating a Statement Object

This is to instantiate objects that run the query against the database connected to. This is done by the createStatement() method of the conn Connection object created above. A call to this method creates an object instance of the Statement class. The following line of code illustrates this:

Statement sql_stmt = conn.createStatement();

b) Executing the Query and Returning a ResultSet

Once a Statement object has been constructed, the next step is to execute the query. This is done by using the executeQuery() method of the Statement object. A call to this method takes as parameter a SQL SELECT statement and returns a JDBC ResultSet object. The following line of code illustrates this using the sql_stmt object created above:

ResultSet rset = sql_stmt.executeQuery

      ("SELECT empno, ename, sal, deptno FROM emp ORDER BY ename");

Alternatively, the SQL statement can be placed in a string and then this string passed to the executeQuery() function. This is shown below.

String sql = "SELECT empno, ename, sal, deptno FROM emp ORDER BY ename";

ResultSet rset = sql_stmt.executeQuery(sql);

Statement and ResultSet Objects

Statement and ResultSet objects open a corresponding cursor in the database for SELECT and other DML statements.

The above statement executes the SELECT statement specified in between the double quotes and stores the resulting rows in an instance of the ResultSet object named rset.

5. Processing the Results of a Database Query That Returns Multiple Rows

Once the query has been executed, there are two steps to be carried out:

a)Processing the output resultset to fetch the rows

b) Retrieving the column values of the current row

The first step is done using the next() method of the ResultSet object. A call to next() is executed in a loop to fetch the rows one row at a time, with each call to next() advancing the control to the next available row. The next() method returns the Boolean value true while rows are still available for fetching and returns false when all the rows have been fetched.

The second step is done by using the getXXX() methods of the JDBC rset object. Here getXXX() corresponds to the getInt(), getString() etc with XXX being replaced by a Java datatype.

Closing the ResultSet and Statement

Once the ResultSet and Statement objects have been used, they must be closed explicitly. This is done by calls to the close() method of the ResultSet and Statement classes. The following code illustrates this:

rset.close();

sql_stmt.close();

If not closed explicitly, there are two disadvantages:

• Memory leaks can occur
• Maximum Open cursors can be exceeded

Closing the ResultSet and Statement objects frees the corresponding cursor in the database.

6. Closing the Connection

The last step is to close the database connection opened in the beginning after importing the packages and loading the JDBC drivers. This is done by a call to the close() method of the Connection class.

The following line of code does this:

conn.close();

Explicitly Close your Connection

Closing the ResultSet and Statement objects does not close the connection. The connection should be closed by explicitly invoking the close() method of the Connection class.

                      OR,

Steps to write JDBC Program

There are 6 steps to connect any java application with the database using JDBC. They are as follows:

Load the JDBC driver class or register the JDBC driver.

Establish the connection

Create a statement

Execute the SQL commands on the database and get the result

Print the result

Close the connection

1. Register the driver class

In this step, we load the JDBC driver class into JVM. This step is also called as registering the JDBC driver. The forName() method of Class class is used to register the driver class. This method is used to dynamically load the driver class. This step can be completed in two ways.

• class.forName("fully qualified classname")
• DriveManager.registerDriver(object of driver class)

Syntax of forName() method

public static void forName(String className)throws ClassNotFoundException  

Sun.Jdbc.Odbc.JdbcOdbcDriver is a driver class provided by Sun MicroSystem and it can be loaded into jvm like the following.

Syntax

class.forName("Sun.Jdbc.Odbc.JdbcOdbcDriver");

Syntax

Sun.Jdbc.Odbc.JdbcOdbcDriver jod=new Sun.Jdbc.Odbc.JdbcOdbcDriver();

DriverManager.registerDriver(jod);

2. Create the connection object

In this step connection between a java program and a database will be opened. To open the connection, we call getConnection() method of DriverManager class.

For getConnection() method we need to pass three parameters.

• URL
• username
• password

URL: URL is used to select one register JDBC driver among multiple registered drivers by DriverManager class.

username and password: username and password are used for authentication purpose.

Syntax of getConnection() method

1) public static Connection getConnection(String url)throws SQLException  

2) public static Connection getConnection(String url,String name,String password)  

throws SQLException  

Example to establish connection with the Oracle database

Connection con=new DriverManager.getConnection(url, username, password);

Example:

Connection con=new DriverManager.getConnection(Jdbc:Odbc:< dsn >", "scott","tiger");

3. Create the Statement object

To transfer sql commands from java program to database we need statement object. To create a statement object we call createStatement() method of connection interface. The createStatement() method of Connection interface is used to create statement. The object of statement is responsible to execute queries with the database.

Syntax of createStatement() method

public Statement createStatement()throws SQLException  

Example to create the statement object

Statement stmt=new createStatement();

4. Executing queries

Call any one of the following three methods of Statement interface is used to execute queries to the database and to get the output.

executeUpdate(): Used for non-select operations.

executequery(): Used for select operation.

execute(): Used for both select or non-select operation.

5. Print the result.

Syntax

System.out.println(output);

6. Closing connection: Close the connection.

By closing connection, object statement and ResultSet will be closed automatically. The close() method of the Connection interface is used to close the connection.

Syntax of close() method

Syntax of close() method

public void close()throws SQLException  

Example for close connection

con.close();

Example

import java.sql.*;

class CreateTable 

{

public static void main(String[] args) throws Exception

{

//step-1

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

System.out.println("driver is laoded");

//step-2

Connection con=DriverManager.getConnection("jdbc:odbc:ramadsn","system","system");

System.out.println("connection is established");

//step-3

Statement stmt=con.createStatement();

System.out.println("statement object is cretaed");

//step-4

int i=stmt.executeUpdate("create table student(sid number(3),sname varchar2(10),marks number(5))");

//step-5

System.out.println("Result is="+i);

System.out.println("table is created");

//step-6

stmt.close();

con .close();

}

}