JDBC - The Steps

JDBC : The Steps

In this article we are highlighting the steps to connect and process data in JDBC. For this article we are using Oracle Database. For other databases the drivers will vary but the steps will remain the same.

The steps involved in the process of connecting to a database and executing a query are as follows:

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

Load and Register the JDBC Driver

The first step is to establish a communication between the JDBC program and the database. This is done by using the static registerDriver() method of the DriverManager class of the JDBC API.

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

Alternatively, we can use 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");

Connecting to a Database

Once the JDBC driver has been loaded and registered, a database connection needs 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.

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

Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@training:1521:Oracle",  "oratest", "oratest");

Querying the Database

Querying the database involves two steps:

·         Creating a statement object to perform a query

·         Executing the query and returning a resultset.

Creating a Statement Object

The next step is to instantiate objects that run the query against the database connected. This is done by the createStatement() method of the Connection object created above. A call to this method creates an object instance of the Statement

Statement stmt = conn.createStatement();

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.

ResultSet rset = stmt.executeQuery       ("SELECT empno, ename, sal, deptno FROM emp ");

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:

After the query execution we need to perform 2 things

·         Process the output resultset to fetch the rows

·         Retrievethe column values of the current row

The first step is 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 second step is to fetch the values in the columnsby 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.

String str; while (rset.next())  {  str = rset.getInt(1)+ " "+ rset.getString(2)+ "          "+rset.getFloat(3)+ " "rset.getInt(4)+ "\n";  }

Here 1, 2, 3, and 4 in rset.getInt(), rset.getString(), getFloat(), and getInt() respectively denote the position of the columns in the SELECT statement, that is, the first column empno, second column ename, third column sal, and fourth column deptno of the SELECT statement respectively.

The parameters for the getXXX() methods can be specified by position of the corresponding columns as numbers 1, 2, and so on, or by directly specifying the column names enclosed in double quotes, as getString("ename") and so on, or a combination of both.

Closing the ResultSet and Statement

Once the ResultSet and Statement objects have been used, they must be closed.

rset.close(); stmt.close();

Closing the Connection

The last step is to close the database connection which is done by a call to the close() method.

conn.close();

Here is a complete example of JDBC Select using Type 4 driver and Oracle Database

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class JDBC01 { public static void main(String[] args) {                 try {                                                                 Class.forName("oracle.jdbc.OracleDriver");                 } catch (ClassNotFoundException e) {                                 e.printStackTrace();                 }                 try {                                 Connection con =DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:orcl","scott","tiger");                                 Statement s=con.createStatement();                                 String query01="select * from emp";                                 ResultSet rs=s.executeQuery(query01);                                 while(rs.next()){                 System.out.println(rs.getInt("empno")+"    "+rs.getString("ename")+"   "+rs.getDouble("sal"));                                           }                 } catch (SQLException e) {                                 e.printStackTrace();                 } } }