JDBC

Introduction to databases and SQL


Enterprise (business organization) data is stored in a database. The database is the heart of an enterprise system.  A database is the collection of related data and the relationships supporting the data. Database Management System (DBMS) is software that is used to access data in the database. The primary objective of a DBMS is to provide a convenient environment to retrieve and store database information. SQL (Structured Query Language) is used to communicate with the DBMS to deal with database data. IBM developed this language in 1970s. SQL comprises of the following important sub languages.
1.    Data Definition Language (DDL): - It is used to create, alter and drop tables.
2.    Data Manipulation Language (DML): - It is used to INSERT, UPDATE and DELETE data.
3.    Data Query Language (DQL): - It is used to retrieve data from the from the database table using SELECT.
In a Relational Database Management System, for example in Oracle, data is stored in the form of tables. Each table is made up of one or more rows. Each row contains a number of fields, corresponding to the columns or attributes of the table. Customers’ information, Employees’ information, Students’ information is represented in the form of corresponding tables.
Student table
ROLLNO
NAME
MARKS
1001
Rama
70
1002
David
60
1003
Rahim
65

In the above table, ROLLNO, NAME and MARKS are the columns of the table, which are the attributes of each student. Each row in the table is known as a record. Each record represents one student information. Before we create such a table and store data in it, we need to know few SQL data types. While creating a table we need to specify the column type. In the above table, ROLLNO type is NUMBER. NAME is VARCHAR and MARKS also NUMBER type.
Creating a table
  In order to create a table in the database, we follow the given syntax.
 CREATE TABLE TABLENAME(COLUMN1 TYPE(SIZE), COLUMN2 TYPE(SIZE));
SQL>create table student(rollno number(5),name varchar(12),marks number(3));
The above command at the SQL prompt creates a student table with three columns. rollno column is of type number that can have a maximum of 5 digits. name column is of type varchar that can store a string of length 12. marks column is of type number that can store a number with a maximum of 3 digits.
Q) Create a table EMPLOYEE with the following columns. EMPNO, NAME and SALARY.
A) We have to give the following command at the SQL prompt to create the table.
 create table employee(empno number(5),name varchar(12),salary number(7,2));
Note:- In the above command, for the salary column number(7,2) means, it can store a maximum of 7 digit decimal number. After decimal point, 2 digits can occur.
Inserting data into the database table
In order to insert records into the table, we use the following SQL syntax.
INSERT INTO TABLENAME VALUES(value1,value2,value3);
Note: - For inserting strings into the table we enclose them with single quotes.
Q) Write SQL command to store employee information into EMPLOYEE TABLE.
A) INSERT INTO EMPLOYEE VALUES(1001,’Rama’,5000);
EMPNO
NAME
SALARY
1001
Rama
5000

Note: - If we submit the above command at the SQL prompt, one employee record is stored into employee table. But it will not become permanent. To make the storage permanent we have to say
         SQL>commit;
Deleting data from the table
  In order to delete records from the table we use the DELETE command.
Syntax: - SQL> DELETE FROM TABLENAME;
If we use the above syntax, all the records will be deleted from the table. In order to delete specified record(s), we have to specify the criteria using WHERE clause.
Q) Write an SQL command to delete all the records from the employee table.
A) DELETE FROM EMPLOYEE;
Note: - With the above command all the records are deleted temporarily. To make the deletion permanent, we have to say SQL>commit;
Q) Write an SQL command to delete all the records from the employee table whose salary is greater than 5000.
A) SQL>DELETE FROM EMPLOYEE WHERE SALARY>5000;
Updating table data
We use UPDATE command to modify column values of the records of the table.
Syntax: - UPDATE TABLENAME SET COLUMNAME=NEW VALUE;
With the above syntax, all the records in the table will be modified. Specified column of each record is set to new value. If we want to modify the column values of specified record(s), we have to use WHERE CLAUSE.
Q) Write an SQL command that increases salaries of all employees by Rs. 2000.
A) SQL>UPDATE EMPLOYEE SET SALARY=SALARY+2000;
Q) Write an SQL command that increases the salary of each employee by Rs. 1000 whose salary is less than Rs. 5000.
A) SQL> UPDATE EMPLOYEE SET SALARY=SALARY+1000 WHERE SALARY<5000;

Retrieving table data
We use SELECT command to retrieve data from the database table.
Syntax: - SELECT COLUM1, COLUMN2 FROM TABLENAME;
The above syntax retrieves 2 columns of all the records of the table. If we want all the columns of each record, we use the following syntax.
          SELECT * FROM TABLENAME;
Here star indicates all the column values. If we want to retrieve all the columns of specified records we have to use WHERE clause to specify the criteria.
     SELECT * FROM TABLENAME WHERE CRITERIA;
Q) Write an SQL command to retrieve names and salaries of all the employees.
A) SQL> SELECT NAME, SALARY FROM EMPLOYEE;
Q) Write an SQL command to display all the details of each employee.
A) SQL> SELECT * FROM EMPLOYEE;
Q) Write an SQL command that displays employee number and salary of all the employees whose salary is more than 5000.
A) SQL>SELECT EMPNO, SALARY FROM EMPLOYEE WHERE SALARY>5000;

Note: - Database programmers perform so many operations on the table data than what we have discussed here.  Application developers, i.e. java programmers, .NET professionals etc. mostly perform DML and DQL operations only. It is strongly recommended for application developers to master SQL and PL/SQL programming skills.




Database programming using JDBC


Even though Java has many features, it became famous and widely accepted in the industry because of its platform independency.  The acceptance does not become hundred percent complete unless business community endorses java.  Every enterprise (business) application is required to access the database either for retrieving the data to be processed or for storing the processed data.  Java offers a clean and simple approach for database connectivity through JDBC using which a java application can connect virtually to any database.  Therefore enterprises endorsed java and rest is the history.
What is JDBC?
  JDBC is a trademark name from Sun. It is not an acronym for Java Database Connectivity. JDBC is an API. Using this API we can develop java applications that can communicate with databases. JDBC API support is made available to Java applications through 2 packages.
1.    java.sql
2.    javax.sql
java.sql package offers a set of fundamental interfaces and classes used for java-database communication. Extension package is used for advanced Java-database operations.
JDBC ARCHITECTURE








Java Application: - It can be a stand-alone java program, a servlet, a jsp, a Java bean or an EJB, which uses the JDBC API to get connected and perform operations on the database data.
JDBC API: - It is a set of classes and interfaces used in a java program for database operations. java.sql & javax.sql packages provide the necessary library support.
DriverManager: - Java program uses DriverManager to get the connection with the database.
Driver: - It is the software that establishes connection with the database.  It is the translation software that translates the JDBC method calls. This software enables the communication between java program and the database.
Database: - Enterprise data is stored in the database.
Standard steps to connect to the database from a Java program
1.    Loading the driver
2.    Preparing the connection string
3.    Requesting for the connection
4.    After performing database operations closing the connection.
Loading the Driver: - Important entity in the Java-Database communication is the driver. JDBC driver is a class. We have to load this Java class programmatically into memory.  We have so many kinds of drivers. Initially we make use of Sun Microsystems developed driver. Fully qualified name of that driver class is “sun.jdbc.odbc.JdbcOdbcDriver”. To load the class dynamically, we use the following static method.   Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Preparing the connection string: - We need to supply the database URL for connectivity purpose. This URL is known as connection string. When we are using Sun driver, connection string will be in the following format.
   String cs=”jdbc:odbc:dsn”;
Data Source Name (DSN) we have to configure externally. After configuring, we give some name to the configuration. That name we use in the connection string.
Requesting for connection: - Java program calls the following method on the DriverManager to get the database connection.
     Connection con=DriverManager.getConnection(cs,”username”,”password”);
DriverManager initializes the connection process on the driver. Driver creates the connection and gives to the DriverManager. In turn, it gives to the Java program.
Closing the connection: - After performing database operations (Insertion, deletion, updation or selection) we close the database connection.

Task: - stand-alone Java application that connects to the oracle database.
/*
  Source code: -ConnectToOracle.java
*/
import java.sql.*;
class ConnectToOracle
{
  public static void main(String args[]) throws Exception
{
          String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;
         String cs=”jdbc:odbc:student”;
        String user=”scott”;
       String pwd=”tiger”;
      Class.forName(driver);
     System.out.println(“Driver loaded”);
    Connection con=DriverManager.getConnection(cs,user,pwd);
   System.out.println(“Connected to the oracle database”);
  con.close();
}//main()
}//class
If the above program is successfully executed, we get the following output.
  Driver loaded
  Connected to the oracle database
Observations to be made
1.    In the connection string, “student” is the DSN. We need to configure it using Data Source Administrator.
2.    The user name “scott” and password “tiger” is the default user name and password to login to oracle database. The database administrators change them. Accordingly we have to change them in our program.
3.    forName method throws ClassNotFoundException. getConnection and close methods throw SQLException. Either we have to handle them explicitly by writing try catch OR pass on them by using throws class for the main. Otherwise, compiler generates syntactical errors and forces us to deal with exceptions.

 Performing Database operations
java.sql.Statement is used to perform database operations from the Java program. Its object is produced as follows.
   Statement st=con.createStatement();
“st” is the JDBC object used to submit SQL statements from a Java program. It has 2 important methods.
1.    executeUpdate()
2.    executeQuery()
The first method is used to submit DML statements to the database. I.e. when we want to insert a record, delete records or update records of a database table from a Java program we have to use executeUpdate method of the Statement object. The second method is used to submit SELECT statement to the database to retrieve data from the table. Both methods throw SQLException.
Task: - Java application inserting a record into the database table.
//  Source code: - InsertRecord.java
import java.sql.*;
class InsertRecord
{
  public static void main(String args[]) throws Exception
{
          String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;
         String cs=”jdbc:odbc:student”;
        String user=”scott”;
       String pwd=”tiger”;
      String sqlstmt=”INSERT INTO EMPLOYEE VALUES(1001,’Rama’,6000)”;
     Class.forName(driver);
     Connection con=DriverManager.getConnection(cs,user,pwd);
    Statement st=con.createStatement(); 
    int  updatecount=st.executeUpdate(sqlstmt);
   System.out.println(updatecount+ “  row created”);
  st.close();
  con.close();
}//main()
}//class
Observations to be made
1.    executeUpdate method returns an integer that indicates the number of rows effected in the database. In this program “updatecount” is holding that value.
2.    Before we close the connection, we need to close the Statement object.
Task: - Java application deleting multiple records from the database table.
/*
Source code: - DeleteRecords.java
*/
import java.sql.*;
class DeleteRecords
{
  public static void main(String args[]) throws Exception
{
          String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;
         String cs=”jdbc:odbc:student”;
        String user=”scott”;
       String pwd=”tiger”;
      String sqlstmt=”DELETE FROM EMPLOYEE WHERE SALARY>5000”;
     Class.forName(driver);
    System.out.println(“driver loaded”);
   Connection con=DriverManager.getConnection(cs,user,pwd);
   System.out.println(“Java program connected to the oracle database”);
   Statement st=con.createStatement(); 
   int  updatecount=st.executeUpdate(sqlstmt);
   System.out.println(updatecount+ “  rows deleted”);
  st.close();
  con.close();
}//main()
}//class
If the above program is successfully executed, we get the following output.
driver loaded
Java program connected to the oracle database
n rows deleted
Note: - ‘n’ depends upon the number of matching records present in the database table.


Retrieving data from database table
When we submit SELECT statement to the database, it returns a table of records. Using JDBC API we retrieve each column of each row in the returned records in object oriented manner. We submit the SELECT statement to the database as follows.    ResultSet rs=st.executeQuery(“SELECT …..”);
java.sql.ResultSet object holds the table of records returned from the database. When the ResultSet is opened, the cursor points to the zero record. Using the ResultSet methods we have to move the cursor to each record and get the column values. Most important method from ResultSet that is used to move the cursor is next method. ResultSet gives getter methods to retrieve the column values.
Task: - Java application that retrieves a single record from the table.
//Source code: - RetrieveRecord.java
import java.sql.*;
class RetrieveRecord
{
  public static void main(String args[]) throws Exception
{
          String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;
         String cs=”jdbc:odbc:student”;   String user=”scott”;   String pwd=”tiger”;
      String sqlstmt=”SELECT * FROM EMPLOYEE WHERE EMPNO=1001”;
     Class.forName(driver);
     Connection con=DriverManager.getConnection(cs,user,pwd);
    Statement st=con.createStatement(); 
   ResultSet rs=st.executeQuery(sqlstmt);
   if(rs.next())
 {
    System.out.println(“Employee Name:”+rs.getString(2));
   System.out.println(“Employee Salary:”+rs.getFloat(3));
}
  else
   System.out.println(“Employee does not exist”);  
  rs.close(); st.close();
  con.close();
}//main()
}//class

  If the above program successfully executes, name and salary of the employee whose empno is 1001 will be displayed. With that number if no employee exists the same is displayed.
Observations to be made
1.    next() method moved the cursor from zero record to the first record. It returns true if the record exists. Otherwise, it returns false.
2.    The kind of get method we have to call depends upon the column type. If the column type is VARCHAR, we called getString method. To this method we supplied the column number as argument. It returns the column value. If the column type is decimal number, we called getFloat method on the ResultSet object.
3.    getter methods of ResultSet act upon the columns of that row to which the cursor is currently pointing.
4.    We have to close the ResultSet befoe Statement.
Task: - Java application that retrieves multiple records from the table.
//Source code: - RetrieveRecords.java
import java.sql.*;
class RetrieveRecords
{
  public static void main(String args[]) throws Exception
{
        String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;
       String cs=”jdbc:odbc:student”;   String user=”scott”;   String pwd=”tiger”;
      String sqlstmt=”SELECT * FROM EMPLOYEE”;   Class.forName(driver);
     Connection con=DriverManager.getConnection(cs,user,pwd);
    Statement st=con.createStatement(); 
   ResultSet rs=st.executeQuery(sqlstmt);
   while(rs.next())
      System.out.println(rs.getInt(1)+” “+rs.getString(2)+” “+rs.getFloat(3));    
  rs.close(); st.close();   con.close();
}//main()
}//class
The above program displays all the records of the employee table. In each iteration of while loop cursor points to the next record and getter methods get column values.


java.sql.PreparedStatement

If we are executing similar SQL statements multiple times, using parameterized (or “prepared”) statements can be more efficient than executing a raw query each time.  The idea is to create a parameterized statement in a standard from that is sent to the database for compilation before actually being used.  We use a question mark to indicate the places where a value will be substituted into the statement.  Each time we use the prepared statement; we simply replace the marked parameters, using a setxxx method call corresponding to the entry we want to set (using 1 based indexing) and the type of parameter.  To execute the prepared statement object we call executeUpadate().
The PreparedStatement interface inherits from Statement interface and differs from it in two ways.
1.    Instances of PrepaedStatement contain an SQL statement that has already been compiled.  This is what makes a Statement “prepared”.

2.The SQL statement contained in a PreparedStatement object may have one or more IN parameters.  An IN parameter is a parameter whose value is not specified when the SQL statement is created.  Instead the statement has a question mark(?) as a placeholder for each IN parameter.  A value for each question mark must be supplied by the appropriate setxxx method before the statement is executed.  Because PreparedStatement objects are precompiled, their execution can be faster than that of Statement objects. Consequently, an SQL statement that is executed many times is often created as a PreparedStatement.  Being a subclass of Statement, PreparedStatement inherits all the functionality of Statement.  In addition, it adds a set of methods that are needed for setting the values to be sent to the database in the place of placeholders for IN parameters.  To create the preparedStatement we call the prepareStatement() method on the connection object.
PreparedStatement ps=con.prepareStatement(“insert into emp values(?,?,?)”);
   While creating the Statement object we do not supply any SQL statement for the  
    createStatement () method.  But here we are supplying the SQL statement.
Task: - Example program on PreparedStatement. Perform the database operations repeatedly and graphically.
In this application, we make use of Swing-JDBC-Database communication. For each database operation one button is given. For each button one separate listener class is written. In each listener class, event handler is implemented.  Loading the driver, creating the connection and building the PreparedStatement object are done in the constructor of the window class.
/*
   Source Code:  EmployeeForm.java
*/
import java.awt.*; import java.awt.event.*;
import javax.swing.*;
import java.sql.*;
class EmployeeForm extends JFrame
{
          JLabel l1,l2,l3,title,info;
          JTextField t1,t2,t3;
          JButton b1,b2,b3,b4,exit;
          Connection con;
          PreparedStatement insertps;
          PreparedStatement updateps;
          PreparedStatement deleteps;
          PreparedStatement selectps;
          EmployeeForm()
          {
                   setSize(355,300);
                   setLocation(100,100);
                   Container c=getContentPane();
                   title= new JLabel(" EMPLOYEE DETAILS ");
                    title.setFont(new Font("Dialog",Font.BOLD,15));
                   l1=new JLabel("EMPNO");
                   l2=new JLabel("NAME");
                   l3=new JLabel("SALARY");
                   t1=new JTextField(10);
                   t2=new JTextField(10);
                   t3=new JTextField(10);
                   b1=new JButton("insrt");
                   b2=new JButton("delet");
                   b3=new JButton("updt");
                   b4=new JButton("show");
                   exit=new JButton("exit");
                   c.setLayout(null);
                   title.setBounds(60,10,160,20);
                   c.add(title);
                   l1.setBounds(40,40,50,20);
                   c.add(l1);
                    t1.setBounds(95,40,108,20);
                   c.add(t1);
                   l2.setBounds(40,70,50,20);
                   c.add(l2);
                   t2.setBounds(95,70,108,20);
                   c.add(t2);
                   l3.setBounds(40,100,50,20);
                   c.add(l3);
                   t3.setBounds(95,100,108,20);
                   c.add(t3);
                   b1.setBounds(10,140,65,40);
                   c.add(b1);
                   b2.setBounds(77,140,65,40);
                   c.add(b2);
                   b3.setBounds(144,140,65,40);
                   c.add(b3);
                   b4.setBounds(211,140,65,40);
                   c.add(b4);
                   exit.setBounds(278,140,65,40);
                   c.add(exit);
                   info=new JLabel("Getting connected to the database");
                   info.setFont(new Font("Dialog",Font.BOLD,15));
                   info.setBounds(20,190,330,30);
                   c.add(info);
                   b1.addActionListener(new InsertListener());
                   b2.addActionListener(new DeleteListener());
                   b3.addActionListener(new UpdateListener());
                   b4.addActionListener(new ShowListener());
                   exit.addActionListener(new ExitListener());
                   setVisible(true);
                   getConnection();
           }//Constructor
          void getConnection()
          {
             try
             {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                String url="jdbc:odbc:student";
                con=DriverManager.getConnection(url,"scott","tiger");
                info.setText("Connection is established with the database");
                insertps=con.prepareStatement("insert into employee values(?,?,?)");
                updateps=con.prepareStatement("update employee set name=?
                            , salary=?  where empno=?");
                deleteps=con.prepareStatement("delete from employee where
                                     empno=?");
            selectps=con.prepareStatement("select * from employee where empno=?");
            }//try
          catch(ClassNotFoundException e)
          {
                   System.out.println("Driver class not found....");
                   System.out.println(e);
          }
          catch(SQLException e)
         {
                   info.setText("Unable to get connected to the database");
          }
     }//getConnection()
          class InsertListener implements ActionListener
          {
               public void actionPerformed(ActionEvent e)
               {
                   try
                    {
                       int empno=Integer.parseInt(t1.getText());
                       String name=t2.getText();
                       float salary=Float.parseFloat(t3.getText());
                       insertps.setInt(1,empno);
                       insertps.setString(2,name);
                       insertps.setFloat(3,salary);
                       insertps.executeUpdate();
                       info.setText("One row inserted successfully");
                       insertps.clearParameters();
                       t1.setText("");
                       t2.setText("");
                       t3.setText("");
                    }//try
                   catch(SQLException  se)
                   {
                             info.setText("Failed to insert a record...");
                    }
                   catch(Exception de)
                   {
                          info.setText("enter proper data before insertion...");
                     }
              }//actionPerformed()
          }//Listener class
          class DeleteListener implements ActionListener
          {
                public void actionPerformed(ActionEvent e)
               {                     
                   try
                    {
                      int empno=Integer.parseInt(t1.getText());
                      deleteps.setInt(1,empno);
                      deleteps.executeUpdate();
                      deleteps.clearParameters();
                      info.setText("One row deleted successfully");
                      t1.setText("");
                      t2.setText("");
                     t3.setText("");
                    }//try
                   catch(SQLException  se)
                   {
                      info.setText("Failed to delete a record...");
                     }
                   catch(Exception de)
                   {
                     info.setText("enter proper empno before deletion..");
                     }
              }//event handler
          }// listener class     
          class UpdateListener implements ActionListener
          {
                   public void actionPerformed(ActionEvent e)
                   {
                             try
                              {
                                int empno=Integer.parseInt(t1.getText());
                                String name=t2.getText();
                                float salary=Float.parseFloat(t3.getText());
                                updateps.setString(1,name);
                                updateps.setFloat(2,salary);
                                updateps.setInt(3,empno);
                                 updateps.executeUpdate();
                                 info.setText("One row updated successfully");
                                 updateps.clearParameters();
                                 t1.setText("");
                                 t2.setText("");
                                 t3.setText("");
                             }//try
                             catch(SQLException  se)
                             {
                                 System.out.println(se);
                                 info.setText("Failed to update the record...");
                               }
                             catch(Exception de)
                             {
                                  System.out.println(de);
                                info.setText("enter proper data  before selecting updation..");
                              }
                   }//event handler
          }
          class ShowListener implements ActionListener
          {
                   public void actionPerformed(ActionEvent e)
                   {
                             try
                              {
                                      int empno=Integer.parseInt(t1.getText());
                                      selectps.setInt(1,empno);
                                      selectps.execute();
                                      ResultSet rs=selectps.getResultSet();
                                      rs.next();
                                      t2.setText(rs.getString(2));
                                      t3.setText(""+rs.getFloat(3));
                                      info.setText("One row displayed successfully");
                                       selectps.clearParameters();
                              }//try
                             catch(SQLException  se)
                             {
                                      info.setText("Failed to show  the record...");
                               }
                             catch(Exception de)
                             {
                                  info.setText("enter proper empno  before selecting  show..");
                              }
                   }//event handler
          }//listener class
          class ExitListener implements ActionListener
          {
                   public void actionPerformed(ActionEvent e)
                    {
                             try
                             {
                                       insertps.close();deleteps.close();
                                      updateps.close();selectps.close();
                                      if(con!=null)  con.close();
                                      System.exit(0);
                             }
                             catch(SQLException se){System.out.println(se);}
                   }
          }
          public static void main(String args[])
         {
                   new  EmployeeForm();
         }
}//EmployeeForm class
Calling a stored procedure from a Java program
With a CallableStatement, we can execute a stored procedure or function in a database.  A stored procedure is stored in a database.  The call to the stored procedure is what a CallbleStatement contains.
Calling a stored procedure in a database involves five basic steps.
1.    Define the call to the database procedure: - As with prepared statement, you use special syntax to define a call to a stored procedure.  The procedure call uses escape syntax, where the appropriate ? defines input and output parameters.
2.    Prepare a CallableStatement for the procedure:  We obtain a CallableStatement from a connection by calling prepareCall().
         CallableStatement st=con.prepareCall(String escapesyntax);
3.    Register the output parameter types: - Before executing the procedure, you must declare the type of each output parameter. registerOutParameter (int parameterIndex, int jdbc Type)  registers the OUT parameter in ordinal position parameterIndex to the JDBC type jdbcType.  All the OUT parameters must be registered before a stored procedure is executed. The JDBC type specified by jdbcType for an OUT parameter determines the java Type that must be used in the method getxxx() to read the value of that parameter.
4.    provide values for the input parameters:- Before executing the procedure, you must supply the input parameter values.  Passing in any IN parameter values to a CallableStatement object is done using the setxxx methods inherited form PreparedStatement.  The type of the value being passed in determines which setxxx method to use.  The setxxx() method provides a java value that the driver converts to a JDBC value before sending it to the database.  For example, setFloat () is used to pass in a float value, setBoolean to pass in a Boolean value.
5.    execute the stored procedure:- To execute the database stored procedure, call execute() on the CallbleStatement.
Example Stored Procedure1 developed by the PL/SQL programmer

create or replace procedure proc2(n number, nm out varchar)
as
begin
        select name into nm from emp10 where id=n;
end proc2;

The above stored procedure name is proc2. It will take employee number as in parameter and gives employee name into the out parameter.
Task: - Example program to call the stored procedure
//Source code: - StoredProcedure.java
import java.sql.*;
class StoredProcedure
{
  public static void main(String args[]) throws Exception
{
        String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;
       String cs=”jdbc:odbc:student”;
       String user=”scott”;
      String pwd=”tiger”;
      Class.forName(driver);
     Connection con=DriverManager.getConnection(cs,user,pwd);
     CallableStatement cst=con.prepareCall("{call proc2(?,?)}");
     cst.registerOutParameter(2,Types.VARCHAR);
     cst.setInt(1,1001);
     cst.execute();
     String str=cst.getString(2);
    System.out.println("Employee Name:"+str);
    cst.close();
    con.close();
}//main
}//class

If the program is successfully executed, the following output is displayed.
Employee Name:Rama
Observations to be made
1.    The second parameter is of type VARCHAR. It is an out parameter. Therefore, we registered it by supplying javax.sql.Types class constant VARCHAR.
2.    To retrieve the out parameter value, we called the getString method on the CallableStatement object. The kind of method we call depends upon the Types class constant.
3.    To release the JDBC resources, we closed the CallableStatement object.

Example Stored Procedure2 developed by the PL/SQL programmer

create or replace procedure addintrest(id in number,bal  out number)as
 begin
   select balance into bal from account where id=id;
   bal :=bal+bal*.05;
   update account set balance=bal where id=id;
 end;
The above stored procedure name is addintrest. It will take account number as in parameter, calculates the interest and adds it to the balance and stores the resultant balance into the out parameter.
Task: - Example program to call the stored procedure
//Source code: - StoredProcedure.java
import java.sql.*;
class StoredProcedure
{
  public static void main(String args[]) throws Exception
{
        String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;
       String cs=”jdbc:odbc:student”;
       String user=”scott”;
      String pwd=”tiger”;
     Class.forName(driver);
     Connection con=DriverManager.getConnection(cs,user,pwd);
    CallableStatement cst=con.prepareCall(" { call addintrest(?,?)}");
    cst.registerOutParameter(2,Types.FLOAT);
   cst.setInt(1,accno);
  cst.execute();
 System.out.println("New Balance of  A/c "+ accno+ " is : Rs "+cst.getFloat(2));
cst.close();
con.close();
}//main
}/class

Batch Updates
A batch update is a set of multiple update statements that is submitted to the database for processing as a batch.  Sending multiple update statements to the database together as a unit can, in some situations, be much more efficient than sending each update statement separately. This ability to send updates as a unit, referred to as the batch update facility, is one of the features provided with the JDBC 2.0 API.
The addBatch() method of Statement and PreparedStatement is used to add individual statements to the batch.  The executeBatch() method is used to start the execution of all the statements grouped together.  This method returns an array of integers, and each element of the array represents the update count for the respective update statement.
int updateCounts=stmts.executeBatch();
We get a BatchUpdateException when you call the method executeBatch if
1.     one of the SQL statements you added to the batch produces a ResultSet (usually a query) or
2.     one of the SQL statements in the batch does not execute successfully for some other reason.
BatchUpdateException is derived from SQLException.   This means that you can use all of the methods available to an SQLException object with it.
A BatchUpdateException contains an array of update counts that is similar to the array returned by the method executeBatch.  In both cases, the update counts are in the same order as the commands that produced them.  This tells you how many commands in the batch executed successfully and which ones they are.  For example, if five commands executed successfully, the array will contain five numbers: the first one being the update count for the first command, the second one being the update count for the second command, and so on.  You should not add a query (a SLECT statement) to a batch of SQL commands because the method executeBatch, which returns an array of update counts, expects an update count from each SQL command that executes successfully.  This means that only commands that return an update count (commands such as INSERT INTO, UPDATE, DELETE) or that return 0 (such as CREATE TABLE, DFROP TABLE, ALTER TABLE) can be successfully executed as a batch with the executeBatch method.  clearBatch () method of the statement object explicitly clears the statements in the batch.

     Task: - Example program to implement batch updates

//Source code: - BatchUpdates.java
import java.sql.*;
class BatchUpdates
{
  public static void main(String args[]) throws Exception
{
        String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;
       String cs=”jdbc:odbc:student”;
       String user=”scott”;
      String pwd=”tiger”;
      Class.forName(driver);
     Connection con=DriverManager.getConnection(cs,user,pwd);
    Statement st=con.createStatement();
   st.addBatch("insert into student values(3,'David')");
   st.addBatch("delete from student where sno=1");
   st.addBatch("update student set sname='raju' where sno=2");
   int effectedRows[]=st.executeBatch();
   int rows=0;
  for(int i=0;i<effectedRows.length;i++)
          rows +=effectedRows[i];
System.out.println("No of rows effected "+rows);
st.close();
con.close();
}//main
}//class
Observations to be made
1.    Before we execute the above program we should have created student table in the database.
2.    In each iteration of the for loop, we are adding to the “rows” variable the number of rows effected by each SQL statement executed in that order.
3.    The size of the array will be 3 as we have grouped 3 SQL statements in the batch.

SCROLLABLE RESULTSET
The most significant addition to the JDBC 2.0 is the support for scrollable resultsets.  In earlier version of JDBC API, we can move the cursor across the ResultSet only in forward direction.  One of the new features in the JDBC 2.0 API is the ability to move a resultset cursor backward as well as forward.  There are also methods that let you move the cursor to a particular row and check the position of the cursor. In order to create a scrollable ResultSet we need to create the Statement object in a different fashion.
Connection interface has 2 overloaded methods to create the Statement object.
1.    createStatement()
2.    createStatement (int resultsettype, int resultsetconcurrency)
The second version of the createStatement (int, int) method supports the creation of Statement object instances that generate scrollable ResultSet objects.
The first argument is one of the three constants added to the Resultset API to indicate the type of a ResultSet object.
1.ResultSet.TYPE_FORWARD_ONLY
2. ResultSet.TYPE_SCROLL_INSENSITIVE
3.ResultSet.TYPE_SCROLL_SENSITIVE
The second argument is one of following two ResultSet constants for specifying whether a result set is read-only or updateable.
4. ResultSet.CONCUR_READ_ONLY
5. ResultSet.CONCUR_UPDATABLE
Specifying the constant TYPE_FORWARD_ONLY creates a non-scrollable ResultSet, that is one in which the cursor moves only forward.  If you do not specify any constants for the type and updatability of a ResultSet object, you will automatically get one that is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY (as is the case when you are using only the JDBC 1.0 API).
We get a scrollable ResultSet object if we specify one of the following ResultSet constants: TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE.  The difference between the two has to do with whether a result set reflects changes that are made to it while it is open and whether certain methods can be called to detect these changes.  Generally speaking a result set that is TYPE_SCROLL_INSENSITIVE does not reflect changes made while it is still open and one that is TYPE_SCROLL_SENSITIVE does.  All three types of result sets will make changes visible if they are closed and then reopened.
If the ResultSet is created as scrollable, we can use the following methods to move the cursor in forward or backward direction or to a particular row.
1.    absolute (int row):  Moves the cursor to the given row number in this ResultSet object.
2.    beforeFirst():  Moves the cursor to the front of this ResultSet object, just before the first row.
3.    afterLast():  Moves the cursor to the end of this ResultSet object, just after the last row.
4.    first():  Moves the cursor to the first row in this ResultSet object.
5.    last():  Moves the cursor to the last row in this ResultSet Object.
6.    next():  Moves the cursor down one row from its current position.
7.    previous():  Moves the cursor to the previous row in this ResultSet object.
8.    relative (int rows)  Moves the cursor a relative number of rows.
Four additional methods let us verify whether the cursor is at a particular position.
1.    isAfterLast():  Retrieves whether the cursor is after the last row in this ResultSet object.
2.    isBeforeFirst(): Retrieves whether the cursor is before the first row in this ResultSet object.
3.    isFirst(): Retrieves whether the cursor is on the first row of this ResultSet object.
4.    isLast(): Retrieves whether the cursor is on the last row of this ResultSet object.
Task: - Example program on scrollable ResultSet.
//Source code: - Scrollable.java
import java.sql.*;
class Scrollable
{
  public static void main(String args[]) throws Exception
{
        String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;
       String cs=”jdbc:odbc:student”;
       String user=”scott”;
      String pwd=”tiger”;
      Class.forName(driver);
     Connection con=DriverManager.getConnection(cs,user,pwd);
         Statement st= con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE 
                           ,ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = st.executeQuery("select * from student");
       System.out.println("Records in the table......");
      while(rs.next())
     {
          System.out.print(rs.getInt(1)+"");
          System.out.println(rs.getString(2));
     }
     rs.first();//It places the cursor at the first record.
    System.out.println("First Record......");
   System.out.print(rs.getInt(1)+"  ");
   System.out.println(rs.getString(2));
   rs.absolute(3);//It places the cursor at the 3rd record.
  System.out.println("Third Record......");
 System.out.print(rs.getInt(1)+"  ");
 System.out.println(rs.getString(2));
rs.last();//It places the cursor at the last record.
System.out.println("Last Record......");
System.out.print(rs.getInt(1)+"  ");
System.out.println(rs.getString(2));
rs.previous();
 rs.relative(-1);
System.out.println("Second Record......");
System.out.print(rs.getInt(1)+"  ");
System.out.println(rs.getString(2));
rs.close();
st.close();
con.close();
}//main()
}//class
Observations to be made
1.    Before we execute the program we should have created student table and enter 4 student records from the SQL prompt.
2.    The ResultSet produced is scrollable and non-updatable.

UPDATABLE RESULTSET
Another new features in the JDBC 2.0 API is the ability to update rows in a ResultSet using methods in the Java Programming language rather than having to send an SQL command.  But before you can take advantage of this capability, you need to create a ResultSet object that is updatable. In order to do this, we need to supply the ResultSet constant CONCUR_UPDATABLE to create the Statement object.

Updating a row Programmatically
An update is the modification of a column value in the current row.  In JDBC 1.0 API, we have to use an explicit SQL update statement for this operation.  According to the JDBC 2.0 API we can programmatically achieve the same.  There involves three steps in this.
1.    Move the cursor to the required row.
2.    call the updatexxx() method on the ResultSet object;
3.    calling the updateRow () method on the ResultSet object;
In order to move the cursor to a particular row we call any one of the methods mentioned above.  The ResusltSet, updatexxx methods take two parameters; the column to update and the new value to put in that column.  There are different update methods for different data types.  For example, updateFloat(), updateInt(), updateString () etc.

Inserting Rows Programmatically
With the JDBC 2.0 API, we can insert a new row into a table programmatically without SQL statements involve the following steps.
1.    To move the cursor to the insert row, call the method moveTolnsertRow().  Insert row is a special row associated with every ResultSet object.  This row is not actually part of the result set; we can think of it as a separate buffer in which to compose a new row.
2.    To set a value for each column in the row, call the appropriate updatexxx method for each value.
3.    call the method insertRow ().
This one method simultaneously inserts the row into both the ResultSet object and the database table from which the result set was selected.

Deleting Rows Programmatically
Deleting a row programmatically involves the following 2 steps.
1.    Move the cursor to the row you want to delete.
2.    Call the method deleteRow () on the ResultSet object.
Task: - Example program on updatable ResultSet.
//Source code: - Updatable.java
import java.sql.*;
class Updatable
{
  public static void main(String args[]) throws Exception
{
        String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;
       String cs=”jdbc:odbc:student”;
       String user=”scott”;
      String pwd=”tiger”;
      Class.forName(driver);
      Connection con=DriverManager.getConnection(cs,user,pwd);
      Statement st= con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE 
                           ,ResultSet.CONCUR_UPDATABLE);
     ResultSet rs=st.executeQuery("select * from student");
     rs.next();
     rs.updateInt(2,70);
    rs.updateRow();
    System.out.println("1 row effected...");
     rs.moveToInsertRow();
    rs.updateInt(1,104);
     rs.updateInt(2,67);
    rs.insertRow();
    System.out.println("1 row effected...");
    rs.close();
    st.close();
    con.close();
}
}
Note: - student table with rollno and marks as columns should be created.

java.sql.ResultSetMetaData
The interface java.sql.ResultSetMetaData provides information about the types and properties of the columns in a ResultSet object. We create this object by calling getMetaData() method on the ResultSet object.
ResultSetMetaData remd=rs.getMetaData();
Once we get the ResultSetMetaData object, we can know the details like the table name, the number of columns, the name of the column, the data type of the column etc.
Task: - Example program on ResultSetMetaData.
//Source code: - MetaTest.java
import java.sql.*;
class MetaTest
{
  public static void main(String args[]) throws Exception
{
        String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;
       String cs=”jdbc:odbc:student”; String user=”scott”;     String pwd=”tiger”;
      Class.forName(driver);
      Connection con=DriverManager.getConnection(cs,user,pwd);
     ResultSet  resultset=statement.executeQuery("SELECT * FROM EMPLOYEE ");
    ResultSetMetaData metadata=resultset.getMetaData();
   int count=metadata.getColumnCount();
  System.out.println("Number of columns in the EMPLOYEE  table:"+count);
  System.out.println("Column Details.....");
for(int i=1;i<=count;i++)
           System.out.println(metadata.getColumnName(i)+" 
                                       "+metadata.getColumnTypeName(i));
System.out.println("Numberofdecimal digits of SALARY:"+metadata.getPrecision(3));
System.out.println("decimal digits of EMPNO:"+metadata.getPrecision(1));
System.out.println("Number of decimal digits after decimal point of SALARY:"+metadata.getScale(3));
rs.close(); st.close();con.close();  
}//main()
}//class
SIMPLE TRANSACTION MANAGEMENT

Transaction is a group of SQL statements whose changes can be made permanent or undone only as a unit.
OR
Transaction is a set of one or more statements that are executed together as a unit so either all of the statements are executed or none of them are executed.

      A transaction ends with a commit or rollback statement.  If it ends with a commit statement, all the changes made to the database become permanent. If the transaction fails or ends with rollback, none of the statements takes effect.  In java, the java.sql.Connection object by default is in auto-commit mode, which means that it automatically commits changes after executing each statement.  Therefore each individual SQL statement is treated as a transaction and will be automatically committed right after it is executed.
The way to allow two or more statements to be grouped into a transaction is to disable auto-commit mode.  If auto-commit mode has been disabled, the method commit() must be called explicitly in order to make the change permanent.  All the statements executed after the previous call to the method commit() will be included in the current transaction and will be committed together as a unit.  If the transaction is failed, rollback() will undo all the changes.
java.sql.Connection has 3 methods to deal with simple transactions.
             1. setAutoCommit()
             2. commit()
             3. rollback()
setAutoCommit(boolean autoCommit) :- Sets the connection’s auto-commit mode to the given state i.e. enable or disabled state.
commit(): - Makes all changes made since the previous commit (rollback) permanent and releases any database locks currently held by this connection object.
rollback(): -  Undoes all changes made in the current transaction and releases any database locks currently held by this connection object.



Types of JDBC drivers

JDBC drivers are divided into four types or levels. Each type defines a JDBC driver implementation with increasingly higher levels of performance and reduced deployment administration.

1.    JDBC-ODBC bridge driver
2.    Native-API partly Java driver
3.    Net-protocol all Java driver.
4.    Native-protocol all Java driver

JDBC-ODBC bridge driver translates all JDBC calls into ODBC calls and sends them to the ODBC driver.
Pros: - It allows access to almost any database.
Cons: - Performance is degraded. Client side administration is more.

Native-API partly driver translates all JDBC calls into database specific calls.
Pros: - It offers additional performance than type 1 driver.
Cons: - Client side administration

Net-Protocol/all-Java driver translates JDBC method calls into middle-tier server understandable calls.
Pros: - No client side administration. Better performance.
Cons: - 3-tier architecture is mandatory even for simple communication.

Native-protocol all Java driver translates JDBC method calls into Native database dependent protocol calls.
Pros: - Highest performance. No client side administration.
Cons: - With type 4 drivers, we need a different driver for different databases.

Note: - In order to learn JDBC concepts, Type 1 driver is sufficient. Type 4 driver is mostly used for all industry strength applications.

JDBC API
java.sql.DriverManager: - Its basic service is managing a set of JDBC drivers.
NOTE: The DataSource interface, new in the JDBC 2.0 API, provides another way to connect to a data source. This class has the following important methods.
1.    Connection getConnection(Strig url,String user,String pwd):-Attempts to establish a connection to the given database URL. The DriverManager attempts to select an appropriate driver from the set of registered JDBC drivers.
Parameters:
url - a database url of the form jdbc:subprotocol:subname
user - the database user on whose behalf the connection is being made
password - the user's password 
    2.  public static void registerDriver(Driver driver)
                           throws SQLException
Registers the given driver with the DriverManager. A newly-loaded driver class should call the method registerDriver to make itself known to the DriverManager.
Parameters:
driver - the new JDBC Driver that is to be registered with the DriverManager
Throws:
SQLException - if a database access error occurs
java.sql.Connection interface: -  connection (session) with a specific database. SQL statements are executed and results are returned within the context of a connection. A Connection object's database is able to provide information describing its tables, its supported SQL grammar, its stored procedures, the capabilities of this connection, and so on. This information is obtained with the getMetaData method. 
Note: When configuring a Connection, JDBC applications should use the appropritate Connection method such as setAutoCommit or setTransactionIsolation. Applications should not invoke SQL commands directly to change the connection's configuration when there is a JDBC method available. By default a Connection object is in auto-commit mode, which means that it automatically commits changes after executing each statement. If auto-commit mode has been disabled, the method commit must be called explicitly in order to commit changes; otherwise, database changes will not be saved. Following is the list of some important methods in Connection interface.
 
1. Statement createStatement()
                          throws SQLException
Creates a Statement object for sending SQL statements to the database. SQL statements without parameters are normally executed using Statement objects. If the same SQL statement is executed many times, it may be more efficient to use a PreparedStatement object.
Result sets created using the returned Statement object will by default be type TYPE_FORWARD_ONLY and have a concurrency level of CONCUR_READ_ONLY. 
2.  PreparedStatement prepareStatement(String sql) throws SQLException
Creates a PreparedStatement object for sending parameterized SQL statements to the database.
A SQL statement with or without IN parameters can be pre-compiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.
Parameters:
sql - an SQL statement that may contain one or more '?' IN parameter place holders.
Returns:
a new default PreparedStatement object containing the pre-compiled SQL statement

3. CallableStatement prepareCall(String sql)   throws SQLException

Creates a CallableStatement object for calling database stored procedures. The CallableStatement object provides methods for setting up its IN and OUT parameters, and methods for executing the call to a stored procedure.
Parameters:
sql - an SQL statement that may contain one or more '?' parameter placeholders. Typically this statement is specified using JDBC call escape syntax.
Returns:
a new default CallableStatement object containing the pre-compiled SQL statement.
4.   void setAutoCommit(boolean autoCommit)   throws SQLException
Sets this connection's auto-commit mode to the given state. If a connection is in auto-commit mode, then all its SQL statements will be executed and committed as individual transactions. Otherwise, its SQL statements are grouped into transactions that are terminated by a call to either the method commit or the method rollback. By default, new connections are in auto-commit mode.
  Parameters:
autoCommit - true to enable auto-commit mode; false to disable it 
5.  void commit()  throws SQLException
Makes all changes made since the previous commit/rollback permanent and releases any database locks currently held by this Connection object. This method should be used only when auto-commit mode has been disabled. 
6. void rollback()     throws SQLException
        Undoes all changes made in the current transaction and releases any database 
         locks currently held by this Connection object. This method should be used
        only when auto-commit mode has been disabled. 
 
7. void close()    throws SQLException
      Releases this Connection object's database and JDBC resources immediately 
      instead of waiting for them to be automatically released.
8. boolean isClosed()    throws SQLException
       Retrieves whether this Connection object has been closed. A connection is closed  
      if the method close has been called on it or if certain fatal errors have occurred.
     This method is guaranteed to return true only when it is called after the method    
     Connection.close has been called. 
This method generally cannot be called to determine whether a connection to a database is valid or invalid. A typical client can determine that a connection is invalid by catching any exceptions that might be thrown when an operation is attempted. 
 
 
java.sql.Statement interface: - This object is used for executing a static SQL statement and returning the results it produces. By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists. The following are the important methods of this object.
1.  ResultSet executeQuery(String sql)  throws SQLException
Executes the given SQL statement, which returns a single ResultSet object. 
Parameters: 
sql - an SQL statement to be sent to the database, typically a static SQL SELECT statement 
Returns: 
a ResultSet object that contains the data produced by the given query; never null 
2. int executeUpdate(String sql) throws SQLException
Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement. 
Parameters: 
sql - an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement. 
Returns: 
either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing.
3. void close()  throws SQLException
Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed. It is generally good practice to release resources as soon as you are finished with them to avoid tying up database resources. Calling the method close on a Statement object that is already closed has no effect.
Note:When a Statement object is closed, its current ResultSet object, if one exists, is also closed. 
 
4.  boolean execute(String sql)    throws SQLException
Executes the given SQL statement, which may return multiple results. In some (uncommon) situations, a single SQL statement may return multiple result sets and/or update counts. Normally you can ignore this unless you are (1) executing a stored procedure that you know may return multiple results or (2) you are dynamically executing an unknown SQL string.
The execute method executes an SQL statement and indicates the form of the first result. You must then use the methods getResultSet or getUpdateCount to retrieve the result, and getMoreResults to move to any subsequent result(s).
Parameters:
sql - any SQL statement
Returns:
true if the first result is a ResultSet object; false if it is an update count or there are no results.
5.  ResultSet getResultSet()  throws SQLException
Retrieves the current result as a ResultSet object. This method should be called only once per result.
Returns:
the current result as a ResultSet object or null if the result is an update count or there are no more results.
6. int getUpdateCount()  throws SQLException
Retrieves the current result as an update count; if the result is a ResultSet object or there are no more results, -1 is returned. This method should be called only once per result.
Returns:
the current result as an update count; -1 if the current result is a ResultSet object or there are no more results. 
7. void addBatch(String sql)    throws SQLException
Adds the given SQL command to the current list of commmands for this Statement object. The commands in this list can be executed as a batch by calling the method executeBatch.
Parameters:
sql - typically this is a SQL INSERT or UPDATE statement 

8.  void clearBatch()    throws SQLException
Empties this Statement object's current list of SQL commands. 

9. int[] executeBatch()  throws SQLException
Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts. The int elements of the array that is returned are ordered to correspond to the commands in the batch, which are ordered according to the order in which they were added to the batch.
java.sql.ResultSet interface: - A table of data representing a database result set, which is usually generated by executing a statement that queries the database. A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set. The following is the list of important methods in the ResultSet
1. boolean next()    throws SQLException
Moves the cursor froward one row from its current position. A ResultSet cursor is initially positioned before the first row; the first call to the method next makes the first row the current row; the second call makes the second row the current row, and so on.
When a call to the next method returns false, the cursor is positioned after the last row. Any invocation of a ResultSet method which requires a current row will result in a SQLException being thrown. If the result set type is TYPE_FORWARD_ONLY, it is vendor specified whether their JDBC driver implementation will return false or throw an SQLException on a subsequent call to next.
If an input stream is open for the current row, a call to the method next will implicitly close it. A ResultSet object's warning chain is cleared when a new row is read.
Returns:
true if the new current row is valid; false if there are no more rows
2.  void close()   throws SQLException
Releases this ResultSet object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed. 
3. boolean wasNull()  throws SQLException
Reports whether the last column read had a value of SQL NULL. Note that you must first call one of the getter methods on a column to try to read its value and then call the method wasNull to see if the value read was SQL NULL.
Returns:
true if the last column value read was SQL NULL and false otherwise 

 4. ResultSetMetaData getMetaData()   throws SQLException
Retrieves the number, types and properties of this ResultSet object's columns.
Returns: the description of this ResultSet object's columns 
5.  String getString(int columnIndex)      throws SQLException
Retrieves the value of the designated column in the current row of this ResultSet object as a String in the Java programming language.
Parameters:
columnIndex - the first column is 1, the second is 2, ...
Returns:
the column value; if the value is SQL NULL, the value returned is null
6.  int getInt(int columnIndex)  throws SQLException
Retrieves the value of the designated column in the current row of this ResultSet object as an int in the Java programming language.
Parameters:
columnIndex - the first column is 1, the second is 2, ...
Returns:
the column value; if the value is SQL NULL, the value returned is 0 
7. Date getDate(int columnIndex)    throws SQLException
Retrieves the value of the designated column in the current row of this ResultSet object as a java.sql.Date object in the Java programming language.
Parameters:
columnIndex - the first column is 1, the second is 2, ...
Returns:
the column value; if the value is SQL NULL, the value returned is null
8. SQLWarning getWarnings() throws SQLException
Retrieves the first warning reported by calls on this ResultSet object. Subsequent warnings on this ResultSet object will be chained to the SQLWarning object that this method returns.
The warning chain is automatically cleared each time a new row is read. This method may not be called on a ResultSet object that has been closed; doing so will cause an SQLException to be thrown.
Note: This warning chain only covers warnings caused by ResultSet methods. Any warning caused by Statement methods (such as reading OUT parameters) will be chained on the Statement object.
Returns:
the first SQLWarning object reported or null if there are none
9.    void clearWarnings()  throws SQLException
 
Clears all warnings reported on this ResultSet object. After this method is called, the method getWarnings returns null until a new warning is reported for this ResultSet object. 

   10. String getCursorName()             throws SQLException
 
Retrieves the name of the SQL cursor used by this ResultSet object.
In SQL, a result table is retrieved through a cursor that is named. The current row of a result set can be updated or deleted using a positioned update/delete statement that references the cursor name. To insure that the cursor has the proper isolation level to support update, the cursor's SELECT statement should be of the form SELECT FOR UPDATE. If FOR UPDATE is omitted, the positioned updates may fail.
The JDBC API supports this SQL feature by providing the name of the SQL cursor used by a ResultSet object. The current row of a ResultSet object is also the current row of this SQL cursor.
Returns:
the SQL name for this ResultSet object's cursor

 11.  void setFetchDirection(int direction)   throws SQLException

Gives a hint as to the direction in which the rows in this ResultSet object will be processed. The initial value is determined by the Statement object that produced this ResultSet object. The fetch direction may be changed at any time.
Parameters:
direction - an int specifying the suggested fetch direction; one of ResultSet.FETCH_FORWARD,ResultSet.FETCH_REVERSE.
12. int getFetchDirection()     throws SQLException
Retrieves the fetch direction for this ResultSet object.
Returns:
the current fetch direction for this ResultSet object
java.sql.PreparedStatement interface: - An object that represents a precompiled SQL statement. A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times. The following are the important methods of this interface.
1.     ResultSet executeQuery()  throws SQLException
 
Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.
Returns:
a ResultSet object that contains the data produced by the query; never null
     2. int executeUpdate()      throws SQLException
Executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement. 

          3.  void setFloat(int parameterIndex,  float x)  throws SQLException
Sets the designated parameter to the given Java float value. The driver converts this to an SQL REAL value when it sends it to the database.
Parameters:
parameterIndex - the first parameter is 1, the second is 2, ...
x - the parameter value 
       4.  void setString(int parameterIndex, String x) throws SQLException
Sets the designated parameter to the given Java String value. The driver converts this to an SQL VARCHAR or LONGVARCHAR value (depending on the argument's size relative to the driver's limits on VARCHAR values) when it sends it to the database.
Parameters:
parameterIndex - the first parameter is 1, the second is 2, ...
x - the parameter value 
      5. void  clear Parameters()  throws SQLException
Clears the current parameter values immediately.
In general, parameter values remain in force for repeated use of a statement. Setting a parameter value automatically clears its previous value. However, in some cases it is useful to immediately release the resources used by the current parameter values; this can be done by calling the method clearParameters. 
   6     boolean execute()    throws SQLException
Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement. Some prepared statements return multiple results; the execute method handles these complex statements as well as the simpler form of statements handled by the methods executeQuery and executeUpdate.
The execute method returns a boolean to indicate the form of the first result. You must call either the method getResultSet or getUpdateCount to retrieve the result; you must call getMoreResults to move to any subsequent result(s). 
  7.     void addBatch()    throws SQLException
            Adds a set of parameters to this PreparedStatement object's batch of
             commands.


java.sql.CallableStatement interface: - The interface used to execute SQL stored procedures. The JDBC API provides a stored procedure SQL escape syntax that allows stored procedures to be called in a standard way for all rdbmss. This escape syntax has one form that includes a result parameter and one that does not. If used, the result parameter must be registered as an OUT parameter. The other parameters can be used for input, output or both. Parameters are referred to sequentially, by number, with the first parameter being 1. 
1.  void registerOutParameter(int parameterIndex, int sqlType)
Registers the OUT parameter in ordinal position parameterIndex to the JDBC type sqlType. All OUT parameters must be registered before a stored procedure is executed.
The JDBC type specified by sqlType for an OUT parameter determines the Java type that must be used in the get method to read the value of that parameter.
If the JDBC type expected to be returned to this output parameter is specific to this particular database, sqlType should be java.sql.Types.OTHER. The method getObject(int) retrieves the value.
Parameters:
parameterIndex - the first parameter is 1, the second is 2, and so on
sqlType - the JDBC type code defined by java.sql.Types. If the parameter is of JDBC type NUMERIC or DECIMAL, the version of registerOutParameter that accepts a scale value should be used. 
2.     getDate(String parameterName)       throws SQLException
Retrieves the value of a JDBC DATE parameter as a java.sql.Date object.
Parameters:
parameterName - the name of the parameter
Returns:
the parameter value. If the value is SQL NULL, the result is null.
javax.sql.ResultSetMetaData interface: - An object that can be used to get information about the types and properties of the columns in a ResultSet object.
1. int getColumnCount()     throws SQLException
         Returns the number of columns in this ResultSet object. 


2. boolean isAutoIncrement(int column)      throws SQLException
          Indicates whether the designated column is automatically numbered. 
          Parameters: column - the first column is 1, the second is 2, ... 
          Returns: true if so; false otherwise 
3. String getColumnLabel(int column)    throws SQLException
       Gets the designated column's suggested title for use in printouts and displays.  The suggested title is usually specified by the SQL AS clause. If a SQL AS is not specified, the value returned from getColumnLabel will be the same as the value returned by the getColumnName method. 
Parameters: column - the first column is 1, the second is 2, ... 
Returns: the suggested column title
4. String getColumnName(int column)     throws SQLException
       Get the designated column's name. 
Parameters: column - the first column is 1, the second is 2, ... 
Returns: column name
5. int getPrecision(int column)     throws SQLException
Get the designated column's specified column size. For numeric data, this is the maximum precision. For character data, this is the length in characters. For datetime datatypes, this is the length in characters of the String representation (assuming the maximum allowed precision of the fractional seconds component). For binary data, this is the length in bytes. For the ROWID datatype, this is the length in bytes. 0 is returned for data types where the column size is not applicable. 
Parameters: 
column - the first column is 1, the second is 2, ... 
Returns: precision
6. int getScale(int column)           throws SQLException
        Gets the designated column's number of digits to right of the decimal point. 0 is returned for data types where the scale is not applicable. 
Parameters: column - the first column is 1, the second is 2, ... 
Returns: scale
7. String getTableName(int column)             throws SQLException
    Gets the designated column's table name. 
Parameters: column - the first column is 1, the second is 2, ... 
Returns: table name or "" if not applicable.
 
Example Programs
Task: - Program that can execute any kind of SQL statement.
//Source code: - DynamicSQL.java
import java.sql.*;
class - DynamicSQL
{
  public static void main(String args[]) throws Exception
{
        String driver=”sun.jdbc.odbc.JdbcOdbcDriver”;
       String cs=”jdbc:odbc:student”;
       String user=”scott”; 
       String pwd=”tiger”;
      Class.forName(driver);
      Connection con=DriverManager.getConnection(cs,user,pwd);
      Statement st=con.createStatement();
      String sql=args[0];
     if(st.execute(sql))
    {
          ResultSet rs=st.getResultSet();
          while(rs.next())
             System.out.println(rs.getString(1)+”"+String(2)+"   "+rs.getString(3));
            rs.close();
   }//if
   else
      System.out.println("Number of rows updated:"+st.getUpdateCount());
   st.close();
   con.close();
   System.out.println("Connection closed....");
}//main
}//class
Observations to be made
1.     We need to supply SQL statement at the command line by keeping double quotes on either supplied.
2.     execute method returns true if the executed statement is a ResultSet.
     
Task: - PreparedStatement example
 
import java.io.*
;import java.sql.*;
class PrepareStatementExample
{
          public static void main(String[] args)
          {
                   String ename;
                   int eno;
                   float sal;
                   Connection con=null;
                   PreparedStatement ps=null;
                   BufferedReader br=null;
                   try
                   {
                        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
          con=DriverManager.getConnection("jdbc:odbc:student","scott","tiger");
                      ps=con.prepareStatement("insert into employee  values(?,?,?)");
                    br=new BufferedReader(new InputStreamReader(System.in));
                   for(int i=1;i<=3;i++)
                   {
                      System.out.println("Enter Employee "+i+" details");
                      System.out.print("EMPNO:");
                     eno=Integer.parseInt(br.readLine());
                    System.out.print("EMP NAME:");
                   ename=br.readLine();
                    System.out.print("SALARY:");
                   sal= Float.parseFloat(br.readLine());
                   ps.setInt(1,eno);
                   ps.setString(2,ename);
                   ps.setFloat(3,sal);
                   ps.executeUpdate();
                }//for
            }//try
              catch(ClassNotFoundException ce)
             {
                             System.out.println("Driver class not found");
             }
           catch(IOException ie)
          {
          System.out.println("Problem in reading the data from the keyboard:"+ie);
         }
         catch(SQLException se)
        {
          System.out.println("Unable to  connect to database or unable to perform
                    database operation"+se);
        }
        finally
       {
             try
            {
                   if(br !=null)
                        br.close();
                  if(ps !=null)
                             ps.close();
                  if(con != null )
                   con.close();
            }
             catch(IOException ce)
             {
             System.out.println(ce);
           }//finally
    }//main()
}//class
Observations to be made
1.     From the keyboard we are taking data repeatedly. Therefore PreparedStatement is a good solution.
2.     For the first time we are handling the exceptions explicitly.
3.     Releasing the resources is done in the finally block.
Task: - Program to insert an image into the table.
/*
SQL>create table employee(eno number(4),ename varchar2(12),esalary number(7,2),ephoto blob);
*/
//Program to store employee details  including photo in the database.
import java.sql.*;
import java.io.*;
class PhotoInsert
{
            public static void main(String[] args)
            {
                        Connection con=null;
                        PreparedStatement ps=null;
                   FileInputStream photoStream=null;
                        try
                    {
                        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                        con=DriverManager.getConnection("jdbc:odbc:java","scott","tiger");
                        System.out.println("Connection Established......");
                        ps=con.prepareStatement("insert into employee values(?,?,?,?)");
                        ps.setInt(1,101);
                        ps.setString(2,"Rama");
                        ps.setFloat(3,5000);
                        File photo=new File("employee.gif");
                      photoStream= new FileInputStream(photo);
                        ps.setBinaryStream(4,photoStream,(int)photo.length());
                        ps.executeUpdate();
                    System.out.println("Employee record inserted..");
                        System.out.println(photo.length());
                 }//try
                catch(ClassNotFoundException cfe)
                  {
                        System.out.println("Driver class not found");
                        System.out.println(cfe);
                       
                  } //catch
                  catch(SQLException e)
                     {
                          System.out.println(e);
                      }
                        catch(IOException e)
                     {
                          System.out.println(e);
                     }
                        finally
                    {
                          try
                      {
                         photoStream.close();
                             ps.close();
                            con.close();
                            System.out.println("Connection closed");
                        }
                          catch(Exception e)
                          {
                             System.out.println(e);
                        }
                 } //finally
            }//main()
}//class

Observations to be made
1.     Before executing the program we need to create a column with blob type in the employee table.
2.     To insert an image into the table, we call setBinaryStream method on the PreparedStatement object.
3.     Data Source name is “java”
4.     Releasing the resources done in the finally block.

/* Program to read the photo of an employee from the database */
import java.sql.*;
import java.io.*;
class PhotoRetrieve
{
            public static void main(String[] args) throws Exception
            {
               Connection con=null;
               PreparedStatement ps=null;
               ResultSet rs=null;
             FileOutputStream fileOut=null;
               InputStream in=null;
               try
             {
                        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                        con=DriverManager.getConnection("jdbc:odbc:java","scott","tiger");
                        System.out.println("Connection Established......");
                        ps=con.prepareStatement( "select * from employeel");
                     rs=ps.executeQuery();
                     rs.next();
                     in = rs.getBinaryStream("ephoto");
                     fileOut = new FileOutputStream("emp.gif");
                     int bytesRead = 0;
                     byte[] buffer = new byte[14170];
                     while((bytesRead =in.read(buffer))!= -1)
                           fileOut.write(buffer, 0, bytesRead);
                    System.out.println("Photo is read from the database");
              }//try
               catch(ClassNotFoundException e)
               {
                        System.out.println(e);
               }
                catch(SQLException e)
                {
                   System.out.println(e);
                }
                catch(IOException e)
                {
                          System.out.println(e);
                }
                finally
              {
                        try
                    {
                            fileOut.close();
                           in.close();
                           rs.close();
                           ps.close();
                           con.close();
                          System.out.println("connection closed");
                    }
                        catch(Exception e)
                      {
                           System.out.println(e);
                     }
               }//finally
    }//main()
}//class

Leave a Reply