Turn Desktop View Off
by Dinesh Thakur

The execution of SQL statement results in a table which consists of rows and columns containing the resultant data. The data can be retrieved from the table by using methods of the ResultSet interface. Some methods of the ResultSet interface are as follows.

• boolean next(): This method is used to move the ResultSet cursor to the next row of the data. Initially, the ResultSet cursor is pointed before the first row. It returns the false value if there are no more rows in the ResultSet object.

• String getString (int index): This method returns the string value that is stored in the column indicated by the column number, index. For example, if the value of index is 2 then it will return the value stored in second column number.

• String getString (String column):This method returns the string value that is stored in the column indicated by the column name column.

JDBC Program to establish connection and retrieve data from Student database

import java.sql.*;

public class SimpleJDBCProgram

{

      public static void main(String args[])

      {

         try

            { //loading the driver class

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

                  //defines the datasource for the driver

                  String url = "jdbc:odbc:ITLESL";

                  //creating the connection through DriverManager

                  Connection con = DriverManager.getConnection ("url", "login", "password");

                  //creating the statement object

                  Statement st= con.createStatement();

                  //executing the query through Resultset object

                  ResultSet rs = st.executeQuery("Select Roll,Marks, Grade FROM Student");

                  //output the resultset data

                  while (rs.next())

                          {

                              //retrieve columns

                              int roll= rs.getInt("Roll");

                              int marks= rs.getInt("Marks");

                              String grade= rs.getString("Grade");

                              //display values

                              System.out.println("Roll No :"+ roll);

                              System.out.println("Marks :"+marks);

                              System.out.println("Grade :"+grade);

                          }

                              //cleaning up the environment

                              rs.close();

                              st.close() ;

                              con.close();

               }

                        catch(Exception e)

                            {

                                 System.out.println("Error "+e);

                             }

      }

}

In this program, the following steps are taken to access the data from the database.

1. Appropriate drivers for the database are loaded by using Class.forName ().

2. The getConnection() method of DriverManager class of JDBC is used to create the Connection object. An application may have more than one connection objects to connect with one or more databases.

3. The first parameter URL ("jdbc:odbc: ITLESL")identifies the database. This URL has three parts: protocol, subprotocol and DSN name. To connect to the database username and password are also required which are specified by the other two parameters of getConnection () method ,login and password.

4. The statement Statement st= con. createStatement () is used to createStatement object, st for querying the database. It is created by calling the createStatement () method using the Connection object, con.

5. In this example, st.executeQuery is used to execute the SELECT statement of SQL. The try{ .. } catch {.. } is used to catch any exceptions that may arise as a result of executing this query statement. The result of this query may consist of set of tuples, which is assigned to rs object of type Result Set. Initially, it places the cursor at the beginning of the first tuple. The statement rs. next () moves the cursor to the next tuple.

6. The next () method is used to fetch one tuple at a time from the resultset rs. The statement int roll = rs. get Int ("Roll") ; is used to access data from the database and System.out.println("Roll No :"+roll); is used to display data.

7. The connection must be closed after it is no more required at the end of the procedure. The statement rs. close (),st. close (),con. close () closes all the open connections

JDBC program to establish connection and retrieve data from Employee database

import java.io.*;

import java.sql.*;

public class ExampleSelectStatement

{

      public static void main(String args[])

      {

         Connection con=null;

         try

           {

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

              //loads driver

              con=DriverManager.getConnection("jdbc:odbc:Emp");

              // establishes connection

              Statement st= con.createStatement();

              //creates Statement object

              ResultSet rs = st.executeQuery("select * from Employee");

              // creates ResultSet object

              System.out.println("---------------------------Employee Details------------------------");

              System.out.println("\n");

              System.out.print("Eno ") ;

              System.out.print("Name ") ;

              System.out.print("Department ") ;

              System.out.print("Designation ") ;

              System.out.println("Salary ") ;

              System.out.println("---------------------------------------------------------------------");

             // displays data till there are no more //rows

             while(rs.next())

                 {

                      System.out.print(rs.getString("ENumber"));

                      System.out.print(" ");

                      System.out.print(rs.getString("EName"));

                      System.out.print(" ");

                      System.out.print(rs.getString("Department"));

                      System.out.print(" ");

                      System.out.print(rs.getString("Designation"));

                      System.out.print(" ");

                      System.out.println(rs.getString("Salary"));

                  }

                       // closes the connection

                       rs.close();

                       st.close() ;

                       con.close();

                       System.in.read();

           }

                catch(Exception e)

               {

                    System.out.println(e.getMessage());

               }

      }

}