The ResultSetMetaDa ta interface provides methods that allow you to get metadata for the resultset, that is, the number, types and properties of the columns in a ResultSet object. This information is used to collect the data with same semantics but stored in different databases in different formats.

To get this information of columns in the ResultSet object using methods of the ResultSetMetaData interface, it is required to invoke the getMetaData () method of the ResultSet object that returns a reference to an object of type ResultSetMetaData.

Some of the methods of ResultSetMetaData interface are as follows.

• Int getColumnCount() :This method returns the number of columns in the resultset.

• String getColumnName(int index) :This method returns the name of column specified by index value index in the resultset.

• int getColumnType(int index) :This method returns the type of column specified by index value index in the resultset.

program to demonstrate implementation of ResultSetMetaData and ResultSet interface.

import java.sql.*;

class ExampleResultSetMetaData

{

   public static void main(String args[])

   {

       Connection con;

       Statement stmt;

       ResultSet rs;

       try

         {

              Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

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

              stmt = con.createStatement();

              rs = stmt.executeQuery(“Select * from Employee WHERE Department=’Math'”);

              ResultSetMetaData rsmd = rs.getMetaData();

              int col= rsmd.getColumnCount();

              //returns number of columns

              int row = 1;

              while (rs.next())

                    {

                         System.out.println(“Row” +row+”: “);

                         for (int i = 1; i <= col; i++)

                             {

                                 System.out.print(” Column”+ i + “: “);

                                 System.out.println(rs.getString(i));

                             }

                                 System.out.println(” “);

                                 row++;

                   }

                         stmt.close();

                         con.close();

       }

                  catch(Exception e)

                     {

                         System.err.print(“ClassNotFoundException: “);

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

                     }

    }

}

PreparedStatement interface extends the Statement interface so it has access to all the methods of Statement interface. It is used to execute precompiled parameterized SQL statements.

This helps statements to execute much faster thus increases the efficiency of the program. It allows you to specify SQL query, in which unknown values are replaced by ‘? ‘. The user provides the unknown values later at the run-time. The setString () and set Int () methods can be used to specify the values for the parameters. To understand the concept of prepared statement, consider the following statements.

PreparedStatement ps = con.prepareStatement(“INSERT INTO Student VALUES(?, ? , ? ) “) ;

ps.setString(l, “A010”);  //assigns value to first attribute

ps.setint(2, 90);              //assigns value to second attribute

ps.setString(3, “A”);      //assigns value to third attribute

ps.executeUpdate();

The values can be assigned to the corresponding attributes through variable name also instead of using literal values representing index value. Like, set String () and set Int () methods, there are various methods for setting value of other data type. Some of these are setBoolean (), setFloat (), setByte (), setDate (),etc. The method void clearParameters () can be used to clear all the values held currently in the parameters.

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

               }

      }

}

The statement interface provides methods that are used to execute static SQL statements. The SQL statements are queries, insertions, updates and deletions, etc.

Some of the methods of the Statement interface are as follows.

• void close(): This method closes database connections associated with Statement’s object and releases JDBC resources.

• boolean execute (String sql): This method is used to execute an SQL statement that might produce multiple result sets or multiple counts. It returns true if multiple result sets are produced and returns false if multiple counts are generated.

• int executeUpdate(String sql): This method is used to execute an SQL statement that gives information about number of affected rows. For example, if we execute the statements like INSERT, DELETE, the result will only be a number of rows affected.

• ResultSet executeQuery(String sql): This method executes a query and returns a Resu1ts et object. A result set isjust like a table containing the resultant data.

• ResultSet getResultSet () : This method returns the first result set or multiple count generated on the execution of execute (String sql) method. If there is no result set, it returns null value.

• int getUpdateCount():After the execution of the execute (String sql) method updates counts may be generated. This method returns the first update count and clears it. More update counts can be retrieved by getMoreResults ().Note that this method returns value -1 if there is no update count or when only result sets are generated on execution of execute (String sql) method.

• boolean getMoreResults () : This method is used to retrieve the next result set in multiple result set or to the next count in multiple update count generated on execution of execute (String sql) method. It returns a true value if multiple sets are available and returns a false value if multiple update counts are available.

The Connection interface helps to establish a connection with the required database. Other than establishing connection, it provides lot of functionality including transaction management, maintaining database sessions and creating SQL statements.

Some of the commonly used methods of connection interface are as follows.

• void close(): This method closes database connection associated with Connection’s object and releases JDBC resources.

• Statement createStatement(): This method creates a Statement object which is used to send SQL statement to the database. It is usually used when SQL statement without parameters is to be executed.

• CallableStatement prepareCall (String sql): This method creates an instance of CallableStatement which is used to handle database stored procedures.

• PreparedStatement prepareStatement (String sql): This method creates PreparedStatement object which is used to create SQL statement specified in string sql. It is usually used when parameterized SQL statement is to be executed.

The DriverManager class is responsible for loading the driver specific classes. The drivers are registered with the DriverManager class either when an instance of driver is created using registerDriver (Driver driver) method of the DriverManager class or DriverManager class is initialized.

The static method forName () of the Class class can be used by DriverManager class to locate and load the drivers listed in system variable jdbc.drivers is as follows.

Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”)

Here, the driver class specified in the string parameter is loaded dynamically at the run time.

Some of the methods of the DriverManager are as follows.

• Connection getConnection (String url): This method attempts to establish a connection to a given database URL–does not require password.

• Connection getConnection(String url, String user, String password): This method attempts to establish a connection to a given database URL.

• void registerDriver (Driver driver): This method is used to register a driver specified by driver with the DriverManager.

• void deregisterDriver (Driver driver): This method removes the driver specified by driver from the DriverManager’s list.

Microsoft’s ODBC is the most commonly used driver to connect to the database as it can connect to almost all databases on most of the platforms. However, ODBC uses the concept of pointers and other constructs that are not supported by Java.

Therefore, JDBC-ODBC bridge driver was developed which translates the JDBC API to the ODBC API and vice versa. This bridge acts as interface that enables all DBMS which support ODBC (Open Data Base Connectivity) to interact with Java Applications. JDBC-ODBC bridge is implemented as a class file and a native library. The name of the class file is JdbcOdbc.class. On windows platform, native library called JDBCODBC.DLL is used.

Figure shows the JDBC application architecture in which a front-end application uses JDBCAPI for interacting with JDBC Driver Manager. Here, JDBC Driver Manager is the backbone of JDBC architecture. It acts as interface that connects a Java application to the driver specified in the Java program. Next, is the JDBC-ODBC bridge which helps the JDBC to access ODBC data sources.

Data about data is called metadata. That means data about column names, data type names of database software etc. JDBC supported Metadata programming areas follows:

1 Database Metadata

2 ResultSet Metadata

Database Metadata

By this programmer can get limitations and capabilities of underlying Database software like database software name, versions, max chars in table, etc. Database- Metadata object means it the object of a java class that implements java.sql. Database Metadata (interface). Programmer call various methods on Database Metadata object to gather much information about underlying database software.

Example:

DatabaseMetaDatadbmd=con.getMetaData();

Use

DatabaseMetaData programming is useful while developing GUI tools for Database software like sqlyog, mysql front, etc.

JDBC Metadata Programming is in no way related to performing CURD operations on database. It is given to gather additional details about database software and its tables. If certain JDBC driver cannot retrieve certain information about database then it returns zero “0” or “null” value while working with DatabaseMetaData object.

Example application

//DBMetaData.java

import java.io.*;

import java.sql.*;

public class DBMetaData

{

                  public static void main (String args[ ]) throws SQLException,ClassNotFoundException

                  {

                       Class.forName(“Sun.jdbc.odbc.JdbcOdbcDrivet”) ;

                       Connection con=DriverManager.getConnection(“jdbc:odbc:oradsn”,”scott”,”tiger”);

                       DatabaseMetaData dbmd=con.getMetaData();

                       System.out.println(“database version:”+dbmd.getDatabaseProductVersion()+”\n”);

                       System.out.println(“database name:”+dbmd.getDatabaseProductName()+”\n”);

                       System.out.println(“sql keywords:” +dbmd.getSQLKeywords() +”\n”);

                       System.out.println(“numeric functions: “+dbmd.getNumericFunctions () +”\n”) ;

                       System.out.println(“String functions: “+dbmd.getStringFunctions ()+”\n” );

                       System.out.println (“System functions:” +dbmd.getSystemFunctions()+”\n”) ;

                       System.out.println(“Searc string Escape:” +dbmd.getSearchStringEscape ()+”\n”);

                       System.out.println (“getMaxRowSize: “+dbmd.getMaxRowSize()+”\n”);

                       System.out.println (“getMaxstatementLength:”+dbmd.getMaxStatementLength() +”\n”);

                       System.out.println(“get maximum tables in a select query:”+dbmd.getMaxTablesInSelect()+”\n”);

                       System.out.println(“get maximum length of table name:”+dbmd.getMaxTableNameLength()+”\n”);

                       System.out.println(“jdbcapi version is :”+dbmd.getJDBCMajorVersion()+”\n”);

                 }

}

ResultSetMetaData

It is used to gather more information about database table that is represented by JBDC ResultSet object. It is used to get no of columns, column name, column data types, etc., information from database table.

ResultSetMetaData object means it is the object of JBDC driver supplied Java class that implements java.sql.ResultSet.MetaData(interface). This object is very useful while generating reports based on database table data; like progress report, sales report, etc.

Method calling

ResultSet rs =st.executeQuery(“select * from emp”);

ResultSetMetaData rsmd=rs.getMetaData();

Here we have taken college table as follows:

//ResuleSetMetaDataTest

import java.sql.*;

import java.util.*;

public class ResultSetMetaDataTest

{

               public static void main(String args[]) throws SQLException,ClassNotFoundException

              {

                     //read inputs

                     Scanner sc = new Scanner(System.in);

                     System.out.println(“Enter table name”);

                     String tname=sc.next();

                     //create jdbc connection

                     Class.forName(“oracle.jdbc.driver.OracleDriver”) ;

                     Connection cn=DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:oracl”,”scott”,”tiger”);

                     Statement st=cn.createStatement();

                     ResultSet rs=st.executeQuery (“select * from “+tname);

                     ResultSetMetaData rsmd=rs.getMetaData();

                     int colcnt=rsmd.getColumnCount();

                     //System.out.println(“Columns present in the table are:\n”) ;

                     for(int i=1;i<=colcnt;i++)

                        {

                            System.out.print (rsmd.getColumnLabel(i)+”\t\t” ) ;

                        }

                            System.out.println(“\n”);

                            //Systeml.out.println(“datatypes of the columns are:\n”);

                            for(int i=1;i<=colcnt;++i)

                               {

                                    System.out.print (rsmd.getColumnTypeName(i)+”\t”);

                               }

                                    System.out.println(“\n”);

                                    //System.out.println(“records of the table are:\n”);

                                    while(rs.next())

                                      {

                                           System.out.println(rs.getString(1)+”\t\t”+rs.getString(2)+”\t\t”+rs.getString(3));

                                            /*for (int i=1;  i<=colcnt; ++i)

                                                    {

                                                        System.out.println(rs. getString(i) +”\ t “) ;

                                                     }*/

                                      }

                                  rs.close() ;

                                  st.close() ;

                                  cn.close();

                }

}

ResultSet is an interface present in java.sql package and provided under JDBC core API. The java.sql. ResultSet interface is defined to describe an object called ResultSet object, implemented by third-party vendor as a part of JDBC driver.

The ResultSet object represent the data in a tabular form that is retrieved using SQL query. It implies that a ResultSet object represents a data in a table whose cursor is returned by executing an SQL query. We can obtain a ResultSet by using the executeQuery() or getResultSet() method of JDBC Statement object.

Only a single ResultSet object can be opened at a time, however we can obtain multiple ResultSet objects by using one statement. When we try to open a ResultSet using a statement that is already associated with an open ResultSet, the existing ResultSet is implicitly closed. A ResultSet is also closed when the JDBC statement used to generate it is closed.

Methods of ResultSet

The java.sql.ResultSet interface provides certain methods to work with the ResultSet object.

• public boolean absolute(int row)

This method is used to move the cursor to the given number of rows in a ResultSet object.

• public void afterLast()

This method is used to move the cursor just after the last row in ResultSet object.

• public void beforeFirst()

This method is used to move the cursor before the first row in ResultSet object.

• getXXX()

XXX=int, long, byte, character, String, double or any large object type. This method retrieves the column value of the specified types from the current row. The type can be any of the Java predefined data type such as int, long, byte etc.

• public ResultSetMetadata getMetaData()

This method retrieves the number, type and properties of the ResultSet object.

• public boolean first()

Move the cursor to the first row in a ResultSet object.

• public boolean last()

This methods move the cursor to the last

• public int getRow()

This method gives the current row number associated with the ResultSet object.

• public void beforeFirst()

This method moves the cursor before first row.

• public void previous()

This method moves cursor backward one row. It returns true if the cursor is newly positioned on a row and false if the cursor is positioned before the first row.

Using ResultSet

After obtaining a ResultSet object, you can use it to read the data encapsulated in it, that is the data stored in the ResultSet.

You can retrieve data from a ResultSet in two simple steps:

• Move the cursor position to the required row.

• Read the column data using the getxxx() methods.

Moving the Cursor Position

When you obtain a ResultSet, initially the cursor is positioned before the first row, that is beforeFirst, you use the next() method of ResultSet to move the cursor positioned to the next record in ResultSet.

Reading the Column Value

After moving the cursor to the respective row, you can use the getter method of ResultSet to retrieve the data from the row where the cursor is positioned. The methods of ResultSet are overloaded to support the navigation of column by using its index or name.

The ResultSet object supports two versions for each of the JDBC type. One of these two indexes takes column index of int type, where column index start with 1 and other takes column name of java .lang. String type. Column name passed into getter method are not case sensitive. If a select list contains same column more than the first instance of column returns.

By default Result Set is non scrollable. it means the cursor can be moved in forward direction only. if we want to move the cursor randomly ,that is ,in forward and in backward direction, then we need a scrollable result set

Example: Retrieving all the row from table (Using Non scrollable ResultSet)We have to retrieve all records from emp table.

import java.sql.*;

public class AllRow

{

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

                 {

                        //load the type-4 driver

                        Class.forName (“oracle.jdbc.driver.OracleDriver”) ;

                        //get connection

                        Connection cn=DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:XE”,”system”,”manager”);

                        //create CallableStatement object

                        Statement st=cn.createStatement();

                        //execute sgl query

                        ResultSet rs=st.executeQuery(“Select * from emp”);

                        //print the result

                        while(rs.next())

                               {

                                    int empno =rs.getInt(1);

                                    String name=rs.getString(2);

                                    int sal = rs.getInt(3);

                                    System.out.println(“empno” +empno+”ename”+name+”sal”+sal);

                                }

                                 rs.close();

                                 st.close();

                                 cn.close();

                  }

}

Output

empno 7000 ename Smith sal    7000

empno 7005 ename Raj     sal    8000

empno 7009 ename Ravi   sal    9000

Example:2 Scrollable ResultSet

import java.sql.*;

public class ScrollableTest

{

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

                        {

                                //load the type-4 driver

                                Class.forName(“oracle.jdbc.driver.oracleDriver”);

                                //getconnection

                                Connection cn=DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:XE”,”system”,”manager”);

                                //create Statement object

                                Statement st=cn.createStatement(1004,1007);

                                //execute sql query

                                ResultSet rs=st.executeQuery(“Select * from emp”);

                                rs.afterLast(); //Move the cursor after the last row

                                //print the result

                                while(rs.previous()) //Move in backward direction

                                       {

                                             System.out.println(rs.getInt(1)+”\t”+rs.getString(2)+”\t”+rs.getInt(3));

                                       }

                                             rs.close();

                                             st.close();

                                             cn.close();

                          }

}

Output:

7009 Ravi   9000

7005 Raj     8000

7000 Smith 7000

CallableStatement of JDBC is derived from interface of PreparedStatement. In JDBC, CallableStatement is best statement because by using this statement we can execute SQL operation on database and we can also call stored procedure of database. The only difference between CallableStatement and PreparedStatement is that the latter is not useful to call a stored procedure but it is possible through the former.

CallableStatement in JDBC can be used for both executing the command on a database and also calling the stored procedure of database. So, we have two syntax to obtain CallableStatement.

Syntax-1

CallableStatement cs=cn.prepareCall(“sql command”);

Syntax-2

CallableStatement cs=con.prepareCall( {call procedurename(parameter) });

What is the difference between Procedure and Function in frontend programming?

• Procedure does not return any value.

Function returns only one value.

What is the difference between Procedure and Function in backend programming?

• Procedure can return zero or more value.

Function can return only one value.

Syntax for creating procedure in oracle database:

create or replace procedure Name(parameter)

is

declare variables;

begin

statement;

end;

• A procedure can contain any of three types of parameters

• IN parameter

• OUT parameter

• INOUT parameter

• By default the parameter is type IN.

IN: IN parameter is used to accept input values to a procedure.

OUT: Parameter is used to return output value from procedure.

INOUT: Parameter is used to take input value and also return output values.

• The following procedure is used for calculating experience of an employee.

Take Input as employee number and return output as employee name and is experienced.

The following is a JDBC program for calling the above procedure created in Oracle database server using Callable Statement of JDBC.

Step 1:Login to Oracle server

Step 2:SQL>ed demo

Step 3:Create procedure emp_experience

Syntax:

Create or replace procedure emp_experience(eno in number, empname out varchar2, experience out number)

is

doj date;

today date;

begin

Select sysdate into today from dual;

Select hiredate into doj from employee where empno=eno;

experience:=(today-doj)/365;

Select ename into empname from employee where empno=eno;

end;

Step 4: Save and exit

//compilation of procedure

Step 5:SQL>@demo

Step 6:Write Java code

import java.sql.*;

import java.util.*;

public class CallableTest1

{

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

       {

               //load type-4 driver

               Class.forName(“oracle.Jdbc.driver.OracleDriver”);

               //get connecticin

               Connection cn=DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:XE”,”system”,”manger”);

               //create Callable Statement object

               CallableStatement cs=cn.callableStatement(“insert into employee_info values(?,?,?) “);

               Scanner sc=new Scanner(System.in);

               System.out.println(“Enter the employee no”);

               int eid=sc.nextInt();

               //set id and name

               cs.setInt(1,eid);

               //registeter OUT parameter

               cs.registerOutParameter(2,Types.VARCHAR);

               cs.registerOutParameter(3, Types.INTEGER);

               //call the procedure

               cs.execute() ;

               //read empname

               String str=cs.getString(2);

               int k=cs.getInt(3);

               System.out.println(“empno”+eid) ;

               System.out.println(“emp narne”+str);

               System.out.println(“empexperience”+k+”year”) ;

               cs.close() ;

               cn.close() ;

      }

}

Output:

Enter employee number

7900

emp no 7900

emp name Dinesh

emp experience 31 years

In above example, while calling a procedure using CallableStatement:

• We register_out Parameter procedure call. The reason why OUTparameter is registered is that if you do not register then by default CallableStatement assumes all parameters as IN parameter.

• If the parameter is IN modeor IN type then Input values must be set before calling the procedure.

• If parameter is registered as OUT then CallableStatement object store the output values as returned by the OUT parameter.

• CallableStatement cannot understand Java Datatype or Database Datatype. So, we need to specify JDBC Type which acts as a mediator between java and database.

• Types.VARCHAR is mediator type between java String type and database varchar2.

• In JDBC all mediator data types are given in java.sql package.

                                                DATA TYPE

In the following procedure empno is IN parameter and return Bonus is OUT parameter

SQL>ed demo2

Create or replace procedure emp_bonus(eno number,bonus out number)

is

temp number(9);

begin

select sal into temp from  emp where empno=eno;

if temp between  1 and 2000 then

bonus:=temp*0.15;

else if temp between 2001 and 5000 then

bonus:=temp*0.20;

else

bonus=temp*0.30;

end if;

end;

SQL>@demo2

procedure created

The following JDBC program is calling the above procedure using CallableStatement.

Example

import java.sql.*;

import java.util.*;

public class CallableTest2

{

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

              {

                      //load the type-4 driver

                      Class.forName(“oracle.jdbc.driver.OracleDriver”);

                      //get connection.

                      Connection cn=DriverManager.getConnection (“jdbc:orac1e:thin:@localhost:1521:XE”,”system”,”manager”);

                      //create callableStatement object

                      CallableStatement cs=cn.prepareCall (“{call emp_bonus(?,?) }”);

                      Scanner sc=new Scanner(System.in);

                      System.out.println(“Enter the employee no”);

                      int eno=sc.nextInt();

                      //set IN parameter

                      cs.setInt (1, eno);

                      //register out parameter

                      cs.registerOutParameter(2,Types.DOUBLE);

                      //call the procedure

                      cs.execute();

                      //read out parameter

                      double d=cs.getDouble (2);

                      System.out.println (“emp no”+eno);

                      System.out.println(“empbonus”+d);

                      cs.close();

                      cn.close();

               }

}

Output;

Enter employee no

7900

Emp no 7900

Emp bonus 1000.0.

Syntax to Create Function in Oracle Database Server

Create or replace function functionname (parameter)

Return returntype is

declare variables;

begin

statements;

end;

Note

• By default parameter of function is IN and IN is not allowed to specify IN parameter.

• For a function return type is mandatory.

• Within the body of function statement is mandatory.

The following function is created for finding the sum of salaries of employees working in a given department, by accepting depno as input.

sql>ed demo3

create or replace function sum_dept(dno number)

return is

temp number(q)

begin

select sum(sql) into temp from employee where depno=dno

end;

sql>@demo3

Function created

• The following JDBC program is calling the above function using CallableStatement.

Example

import java.sql.*;

import java.util.*;

public class CallableTest3

{

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

              {

                   //load the type-4 driver

                   Class.forName(“oracle.jdbc.driver.OracleDriver”);

                   //get connection

                   Connection cn=DriverManager.getConnection (“jdbc:oracle:thin:@locdlhost:1521:XE”,”system”,”manager”);

                   //create CallableStatememt object

                   CallableStatement cs=cn.prepareCall (“{? =call sum_dept(?) }”);

                   // register OUT parameter

                   cs.registerOutParameter(1,Types.INTEGER);

                   Scanner sc=new Scanner (System.in) ;

                   System.out.println(“Enter deptno”);

                   int dno=sc.nextInt ();

                   //set IN parameter

                   cs.setInt(2,dno) ;

                   //call the procedure

                   cs.execute ( ) ;

                   //read OUT parameter

                   int k=cs.getInt (1) ;

                   System.out.println(“sal sum is ” +k) ;

                   cs.close() ;

                   cn.close() ;

               }

}

Output:

Enter depno

30

Sum of sal=2000

To overcome the above limitation, use precompiled SQL query with the help of PreparedStatement object. The JDBC PreparedStatement object deals with precompiled SQL query. The query that comes to database software from Java application without values only once becomes parsed query in database software only once and allows client application(Java application) to set the values for query for the multiple times, with same or different values to gather the results for multiple times. This is called precompiled SQL query. [Read more…] about How to Working With Prepared Statement

The Statement object executes a normal SQL statement to update or query the database. The java.sql.Statement interface is a part of JDBC API that describes a standard abstraction for statement object which is implemented as part of JDBC driver. [Read more…] about How to Work with Statement Interface?

A JDBC statement object is used to execute an SQL statement. JDBC API supports three types of JDBC statement object to work with the SQL statements.

In JDBC we get three types of statements:

i  Statement Interface

ii PreparedStatement Interface

iii CallableStatement Interface

   There is a relationship between above three statements:

The above three Statement Interfaces are given in  java.sql package. PreparedStatement Interface extends Statement. It means PreparedStatement interface has more features than that statement interface, Callablestatement (interface) extends Prepared Statement.

Statement

It executes normal SQL statements with no IN and OUT parameters.

PreparedStatement

It executes parameterized SQL statement that supports IN parameter.

CallableStatement

It executes parameterized SQL statement that invokes database procedure or function or supports IN and OUT parameter.

The save point is a logical position in a transaction up to which we can rollback the transaction. When the save point is placed in the middle of the transaction, the logics placed before the save point will be committed and the logics placed after the save point will be rolled back. There are two types of save points:

1. Named save point

2. Unnamed save point

The save point object is the object of a class given by JDBC driver implementing java.sql.Savepoint interface.

When the save point is placed in the transaction we cannot reference it from connection.commit (), but it can be referenced from connection.rollback (). That means we cannot commit up to save point position, but we can roll back up to save point position.

Source codes for save point testing program.

SavepointDemo.java

import java.sql.*;

public class SavepointDemo 

{

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

          {

                  Class.forName (“oracle.jdbc.driver.oracleDriver”);

                  Connection con = DriverManager.getConnection

                  (“jdbc:oracle:thin:@localhost:1521:orcl”, “scott”, “tiger”);

                  Statement st = con.createStatement ();

                  con.setAutoCommit (false);

                  st.executeUpdate (“insert into college values (‘olamipa’, ‘mumbai’, 1009)”);   

                  Savepoint svpt = con.setSavepoint (“mysp”);

                  st.executeUpdate (“update college set cname= ‘xavier’ where code=1001”);

                 st.executeUpdate (“insert into college values (‘KIIT’, ‘Bhubaneswar’, 1008)”);

                 con.rollback (svpt);

                 con.commit ();

           }

}

Savepoint svpt=con.setSavepoint (“mysp”): After execution of some database operation the save point is created at this position, and any name can be passed as string.

con.rollback (svpt): When we call rollback on save point instance, the transaction before the save point is saved but the transaction after the save point is not performed.

Running the Program

We can confirm from the database table.

Join is a keyword in SQL that is used to retrieve data from two or more table based on some relationship between two columns.

Type of Joins

EQUI join: It returns all the matched records from the table based on the condition.

Query: Select empno, ename, job, dname, loc from emp1,deptl where  emp1.deptno=deptl.deptno

Left outer join: It returns all the records from left-side table and only matched records from right-side based on the joining condition.

Query: Select empno, ename, job, dname, loc from emp1,deptl where emp1.deptno=dept1.deptno(+)

Right outer join: It returns all the records from right-side table and only matched records from left-side table based on the joining condition.

Query: Select empno, ename, job, dname, loc from emp1,deptl where  emp1.deptno(+)=dept1.deptno

Full join: It returns all the records from the two or more tables based on joining condition.

Query: Select empno, ename, job, dname, loc from emp1full outer join deptl on (emp1.deptno=dept1.deptno)

Example of showing EQUI join

Source code for the program:

join.java

import java.sql.*;

public class join

{

       public static void main (String arg [])

         {

                try

                 {

                      Class.forName (“oracle.jdbc.driver.oracleDriver”);

                      Connection con = DriverManager.getConnection (“jdbc:oracle:thin:@localhost:1521:orcl”, “scott”, “tiger”);

                      Statement st = con.createStatement ();

                      String query = “select empno, ename, job, dname, loc from emp1, dept1 where emp1.deptno=dept1.deptno”;

                      ResultSet rs = st.executeQuery (query);

                      System.out.println (“empno\t ename \t job dname \t \t loc”);

                      while (rs.next ())

                        {

                              int empno = rs.getInt (1);

                              String ename = rs.getString (2);

                              String job = rs.getString (3);

                              String dname = rs.getString (4);

                              String loc = rs.getString (5);

                              System.out.println (empno+ ” “+ename+ ” “+job+ ” “+dname+ ” “+ loc);

                        }

                        st.close ();

                        con.close ();

                 }

                catch (Exception ee)          

                 {

                      System.out.println(ee);

                 }

        }

}

Based on joining condition all records have been displayed here.

Example on Left outer join

Outer.java

import java.sql.*;

public class outer

{

        //code same as the above application

        ……………………………………..

        ……………………………………..

        only change this line of code

        String query = “select empno, ename, job, dname, loc from emp1, dept1 where

        emp1.deptno=dept1.deptno (+)”;

        ……………………………………..

}

 Right outer join

 Full join

JDBC connection pool is divided into two parts:

1. JDBC Driver Managed/Stand Alone JDBC Connection Pool (Resides Outside The Server)

2. Web Server Managed JDBC Connection Pool

While developing small-scale applications like standalone desktop applications we can use driver-managed JDBC connection pool. While developing large-scale applications like web sites and enterprise application, we can use server managed JDBC connection pool. JDBC connection pool for Oracle means all JDBC connection pool represents connectivity with Oracle database software. Oracle thin driver/OCI driver gives one built-in JDBC connection pool for oracle. JDBC data source object represents JDBC connection pool so our application should always get JDBC connection object from connection pool through data source object.

JDBC data source object is the object of a Java class that implements javax.sql. DataSource (I). The JDBC connection object that is created by the programmer manually is called the Direct JDBC Connection Object. The JDBC connection object that is collected from JDBC connection pool is called Pooled JDBC Connection Object.

Example application on Oracle thin Driver-Managed JDBC Connection Pool

ConnectionPoolTest.java

import java.sql.*;

import javax.sql.*;//for pooledConnection (I)

import oracle.jdbc.pool.*;// (for oracle Connection pool DataSource (c)) 

public class ConnPoolTest

{

      public void main (String args []) throws Exception

        {

                /*create JDBC DataSource object representing empty driver managed

                connection pool of oracle*/

                OracleConnectionPoolDataSource ds = new OracleConnectionPoolDataSource

                ();

                //gives details to create JDBC connection objects in the Connection pool

                ds.setDriverType (“thin”);

               ds.setPortNumber (1521) ;

               ds.setServiceName (“Orcl”) ;

               ds.setUser (“Scott”) ;

               ds.setPassword (“tiger”);

               /*get access to JDBC connection pool for oracle (this connection pool will be

               created based on the details)*/

               PooledConnection con = pcon.getConnection ();

               //Write JDBC based on the above details

               Statement st = con.createStatement ();

               ResultSet rs = st.executeQuery (“select * from student”);

               while (rs.next())

                {

                      System.out.println (“rs.getInt (1) + ” ” + rs.getInt (2) + ” ” + rs.getInt (3)”);

                }

                rs.close ();

                st.close (); //releases Connection object back to JDBC connection pool

         }

}

Limitations of ResultSet object

Objects become serializable object only when its class implements java.io.5erializable

interface. We can send only Serializable objects over the network.

i. ResultSet object cannot be send over the network because it is not Serializable object.

ii. ResultSet object does not support beans style programming.

RowSet

RowSet object means it is the object of driver supplied Java class that implements javax.sql.RowSet interface. This RowSet interface is a sub interface of java.sql.ResultSet interface. We have three types of RowSets.

1. Cached RowSet

It is a disconnected RowSet. Behavior wise, it is like insensitive ResultSet object.

2. JDBC RowSet

It is connected RowSet. Behavior wise, it is like sensitive ResultSet object.

3. Web RowSet

It is a connected RowSet. It collects data from xml files.

All RowSet objects are Serializable objects and they support bean style programming (getter and setter method-based programming).

Oracle thin/OCI driver give support for RowSets by supplying the following three

classes in ojdbc14.jar (oracle 10g), orcrs12.jar(oracle 9i), ojdbc6.jaroracle 11g) files.

The classes are

i. oracle.jdbc.rowset.OracleCachedRowSet

ii. oracle.jdbc.rowset.OracleJDBCRowSet

iii. oracle.jdbc.rowset.OracleWebRowSet

Advantages with RowSets

i. Gives simplified programming (allows single object based setter and getter methods oriented programming).

ii. RowSets are serializable objects so they can be sent over the network.

Disadvantages with RowSets

i. Very few drivers support Rowsets.

ii. Allows only select operations.

iii. These are not industry standard.

Example on CachedRowSet

Source code for CachedRowSet:

CachedRowSetDemo.java

import java.sql.*;

import javax.sql.*;

public class CachedRowSetDemo

{

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

         {

                  //instance is created for OracleRowSet class

                  oracle.jdbc.rowset.OracleCachedRowSet crs = new oracle.jdbc.rowset.OracleCachedRowSet ();

                  //setter methods

                  crs.setUrl (“jdbc:oracle:thin:@Jocalhost:1521:orcl”);

                  crs.setUsername (“scott”);

                  crs.setPqssword (“tiger”);

                  crs.setCommand (“select* from college”);

                  crs.execute () ;

                  while (crs.next ())

                     {

                            System.out.println (crs.getString (1) +” “+crs.getString (2) + ” “+crs.getInt(3)); 

                    }

                    crs.close ();

         }

}

crs.setCommand (“select * from college”): Is used to set the command for select query.

crs.execute (): is used to execute all above settled commands.

Example on JdbcRowSet

Source code for the program:

JdbcRowSetDemo.java

import java.sql.*;

import javax.sql.*;

import oracle.jdbc.rowset.*;

public class JdbcRowSetDemo

{

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

         {

               try

                {     

                     //instance created for OraCleJDBCRowSet and referenced by RowSet

                     RowSet jrs = new OracleJDBCRowSet ();

                     jrs.setUrl (“jdbc:oracle:thin@localhost:1521:orcl”);

                     jrs.setUsername (“scott”);

                     jrs.setpassword (“tiger”);

                     jrs.setCommand (“select * from college”);

                     jrs.execute ();

                     while (jrs.next ())

                       {

                               System.out.println (jrs.getString (1) +” “+jrs.getString (2) + ” “+jrs.getInt (3));

                       }

                       jrs.close ();

                }

                catch (Exception e){}

         }

} 

Network round trips (query gone for execution to database and result comes to the screen, i.e., one network roundtrip) increases while sending multiple queries as individual queries to the database. It is recommended to combine them into a single unit/batch and send that batch to database only once for execution. This is called batch processing and this reduces network round trips between Java application and the database software. [Read more…] about Batch Processing in JDBC

A transaction is a group of operations used to perform a particular task. In a transaction if one operation fails then all operations of the transaction gets cancelled. Finally, the transaction status fails.

If all operations in a transaction are successfully executed, then only the transaction status is successful. A transaction follows the principle of –All or Nothing. For example, booking a movie ticket online is a transaction.

Transactions are of two types:

1. Local transaction

2. Distributed transaction (Global Transaction)

Local Transaction

Local transaction means all operations are executed on a single database. In Java, local transaction can be done by using JDBC, hibernate, Enterprise JavaBeans (EJB), spring framework.

Distributed Transaction

Distributed transaction means the operations are executed on more than one database.

In Java, distributed transaction can be done either using EJB technology or by using spring framework.

Transaction property

1. Atomicity

Every transaction is an atomic operation. It means “follow all or nothing principle”. For example, in a transfer of money operation-in a bank, if an amount is withdrawn from account 1 and it is deposited to account 2. Here either both operations are done or both are cancelled. So it is an atomic operation.

2. Consistency

After the transaction is completed successfully or fails, the data left in the database, should be reliable. Reliable data is called consistent data.

3. Isolation

If two transaction are working on the same data then one transaction does not clash (collide) with the other. It means transaction is isolated.

4. Durability

The data generated after completion of transaction will be long-lasting data, until another transaction is done on that data. We call these data durable.

Transaction management method in JDBC

The following are the three methods given by Connection interface in JDBC to perform transaction management.

i.  setAutoCommit (false)

ii. commit ()

iii. rollback ()

From a Java program if any SQL operation is executed, it will be permanently executed on database by default. If operations are permanently executed in database then we cannot cancel them. So transaction management is not possible.

If we want to perform transaction management in JDBC program, we should execute operation on database as temporarily. Later, we can either commit or cancel the operation.

If we want to execute SQL operation temporarily from a Java program onto database, then first of all, we need to disable auto commit mode on JDBC program. To disable auto commit mode we need to call setAutocommit (-) method.

If execution occurs while executing database operation, then we need to cancel the transaction by calling rollback (-) method.

If all operations are successfully executed, then we need to commit the transaction by calling commit (-) method.

Need for try catch in transaction management

In JDBC program we need to put the operation of transaction within try block, because Java program knows whether an error has occurred in transaction or not by observing the control in the catch block.

If the control entereds the catch block, it means an error has occurred. So, we will rollback the transaction.

If the control does not enter the catch block, it means no exception has occurred. So we commit the transaction.

This account_balance is created by the following columns and data types on which

we are going to perform transaction management:

The source code for the transaction management:

BankingTest.java

import java.sql.*;

import java.util.Scanner;

public class BankingTest

{

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

       {

             //to take the input from keyboard

             Scanner s = new Scanner (System.in);

             System.out.println (“Enter the Source account number :” );

             int saccno = s.nextInt ();

             System.out.println (“Enter the Destination account number :”);  

             int daccno = s.nextInt ();

            System.out.println (“Enter the amount to transfer :”);

            int amnt = s.nextInt ();

            Class.forName (“oracle.jdbc.driver.OracleDriver :”);

            Connection cn = DriverManager.getConnection

            (“jdbc:oracle:thin:@localhost:1521:orcl”, “scott”, “tiger”);

            Statement st = cn.createStatement ();

            cn.setAutoCommit (false);

            ResultSet rs = st.executeQuery (“select avail_balance from account_balance where account_number=”+saccno);

            rs.next ();

            int abal = rs.getInt (1);

            //System.out.print (abal);

            if (abal>amnt)

            {

                 int up = st.executeUpdate (“update account_balance set avail_balance =avail_balance-“+amnt+ “where account_number=”+saccno);

                 int up1 = st.executeUpdate (“update account_balance set avail_balance = avail_balance-“+amnt+ “where account_number=”+daccno);

                 //System.out.print (up+” “+up1);

                 if (up==1 && up1==1)

                 {

                      cn.commit ();

                      System.out.println (“*******: “+amnt+” balance is successfully Transferred:*******”);

                 }

                 else

                  {

                      cn.rollback ();

                      System.out.println (“rollback”);

                 }

            }

            else

              {

                    System.out.println (“You does not have sufficient balance !!! please deposit in your account.”);

              }

      }

}

In the above program:

cn.setAutoCommit (false): Used to disable auto commit mode. Auto commit mode is disabled before any transaction happens, so the any data cannot be stored automatically.

cn.commit () It commits the transaction after the successful execution of the transaction.

cn.rollback ()  If some problem occurs in the transaction, it cancels the entire transaction.

Transaction is performed by running the program. Here, transaction is successful. You can check it from the database table.

This program also shows error message when the transaction has more than the available balance.