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());
}
}
}
