Search your topic in Java2share
Home > FAQs
eXTReMe Tracker


JDBC FAQs


21:  What is the difference between ScrollSensitive ResultSet  and ScrollInsensitive  ResultSets?

Ans:  Scroll sensitive ResultSet is a ResultSet object, which will allow the later updations from database automatically after creating it. To refer this ResultSet we will use the following constant.
            public static final int TYPE_SCROLL_SENSITIVE;

            Scroll insensitive ResultSet is a ResultSet object, which will not allow later updations from database after creating it. To refer this ResultSet we will use the following constant from ResultSet interface.

  • public static final int TYPE_SCROLL_INSENSITIVE;

22:What is the default ResultSet type in JDBC application and How it is possible to create a specific type of ResultSet object?

  • The default ResultSet type in the jdbc applications is Read only and forward only.
  • In jdbc applications we are able to specify the following types of the ResultSet combination to any particular ResultSet.

 

    • read-only, forward only
    • read-only, scroll sensitive
    • read-only, scroll insensitive
    • updatable, forward only
    • updatable, scroll sensitive
    • updatable, scroll insensitive
  • if we want to specity a particular type to the ResultSet object then we should use either of the above constants combination as a parameter to createStatement() method, for this we will use the following method.

 

public Statement createStatement(int forward / ScrollSensitive / ScrollInsensitive, int  readonly / updatable)

Eg: Statement st = con. createSensitive(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = con.executeQuery(….);
 

23:How to iterate the data from Scrollable ResultSet objuect in both forward and backword direction?

  • to iterate the data in forward direction from a ResultSet object we will use the following 2 methods.

public Boolean next()
public xxx getXxx(int fieldno.)
            Where xxx may be byte, short, char, int, long, float, double.

  • To iterate the data in backward direction from Scrollable ResultSet object we will use the following 2 methods.

 

public Boolean previous()
public xxx getXxx(int fieldno)
            Where previous() is a Boolean method, which can be used to check whether the previous record is available or not, if it is available then cursor will be moved to previous record position.

 

The following example demonstrates how to iterate the data in both forward and backward direction from the ResultSet object

import java.sql.*;
public class ScrollResEx
{
            public static void main(String[] args)throws Exception
            {
                        Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection con = DriverManager.getConnection(“jdbc:odbc:nag”,”system”,”durga”);
Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSEITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs = st.executeQuery(“select * from emp1”);
System.out.println(“data in forward direction”);
System.out.println(“ENO       ENAME       ESAL       EADDR”);
System.out.println(“**********************************”);
While(rs.next())
{
System.out.println(rs.getInt(1)+”    ”+rs.getString(2)+”    ”+rs.getFloat(3)+”    ”+rs.getString(4));

}
System.in.read();
System.out.println(“data in backward direction”);
System.out.println(“ENO        ENAME        ESAL        EADDR”);
System.out.println(“***********************************”);
While(rs.previous())
{
System.out.println(rs.getInt(1)+”    ”+rs.getString(2)+”    ”+rs.getFloat(3)+”    ”+rs.getString(4));

}
}
}

24:   how to generate ScrollSensitive Result Set and how to reflect the later updations from database automatically to the ResultSet object?

import java.sql.*;
public class Test
{
            Public static void main(String[] args)throws Exception
            {
                        Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection con = DriverManager.getConnection(“jdbc:odbc:nag”,”system”,”durga”);
Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSEITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs = st.executeQuery(“select * from emp1”);
rs.next();
System.out.println(“old salary emp111…….”+rs.getFloat(3));
System.in.read();//application is in pause, perform database updations
Rs.refreshRow();
System.out.println(“new salary of emp111……..”+rs.getFloat(3));
}
}

            Where refreshRow() is a method from Scrollable ResultSet object, which can be used to refresh the current row in the ResultSet object to allow the later updations from database. Prototype of this method is
            public void refreshRow()

25:  How to insert records into Database throws Updatable ResultSet?

If we want to insert a new record on to the database through Updatable ResultSet object, we will use the following steps.
Step1: Get the Updatable ResultSet object with fetched data.
Step2:  Move ResultSet cursor to the end of the ResultSet object, where we need to take a buffer to hold new records data temporarily, for this we use the following method from updatable ResultSet object.
                       
                        public void moveToInsertRow()

Step3:   Insert new records data on to the buffer temporarily at Updatable ResultSet object for this we will use the following method format.

            public void updateXxx(int fieldno,xxx value)

            Where xxx may be byte, short, int, char, double, float, long.
Step4:  Make the temporary insertion as the permanent insertion in Updatable ResultSet object as will as in database, for this we will use the following method.
public void insertRow()
The following example demonstrates how to insert no. of records onto the database through Updatable ResultSet objects.

 

import java.sql.*;
import java.io.*;
public class UpdateResEx
{
            public static void main(String[] args)throws Exception
            {
                        Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection con = DriverManager.getConnection(“jdbc:odbc:nag”,”system”,”durga”);
Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSEITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs = st.executeQuery(“select * from emp1”);
BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
rs.moveToInsertRow();
                        while(true)
                        {
                                    System.out.println(“enter employee number”);
                                    int eno = Integer.parseInt(br.readLine());
                                    System.out.println(“enter employee name”);
                                    String ename = br.readLine();
                                    System.out.println(“enter employee salary”);
                                    float esal = Float.parseFloat(br.readLine());
                                    System.out.println(“enter employee address”);
                                    String eaddr = br.readLine();
                                    rs.updateInt(1,eno);
                                    rs.updateString(2,ename);
                                    rs.updateFloat(3,esal);
                                    rs.updateString(4,eaddr);
                                    rs.insertRow();
                                    System.out.println(“record successfully inserted”);
                                    System.out.println(“one more record[y/n]);
                                    String option = br.readLine();
                                    if(option.equals(“n”))
                                                break;
}
}

 26:  How to perform  updations on Database throws Updatable ResultSet?

By using Updatable ResulSet object we are able to perform some updations on to the database. To perform updations on to the database through Updatable ResultSet object we will use the following steps.
Step1:  Get the Updatable ResultSet objectd with the fetched data.
Step2:  Move ResultSet cursor to a record where we want to perform updations, for this we will use the following method.
                        public void absolute(int recordno.)
Step3:  Perform Temporary updations on to the particular record, for this we will use following method.
                        public void updateXxx(int fieldno,xxx value)
Step4:  Make the temporary updation as a parmenent updation on to the Updatable ResultSet object as well as to the database. For this we will use the following method.
                        public void updateRow()

The following example demonstrates how to perform updations on to the database through Updatable ResultSet object.

import java.sql.*;
public class UpdateResEx1
{
            public static void main(String[] args)throws Exception
            {
                        Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection con = DriverManager.getConnection(“jdbc:odbc:nag”,”system”,”durga”);
Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSEITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs = st.executeQuery(“select * from emp1”);
rs.absolute(3);
float newsal = rs.getFloat(3)+500;
rs.updateFloat(3,newsal);
rs.updateRow();
                         }
              }

27:what is meant by ResultSetMetaData ?How to get The ResultSet metadata of a ResultSet object?

Data about the data is called as Metadata. Similarily data about the data available in ResultSet object called as “ResultSet Metadata”.

 

  • ResultSet Metadata includes the number of columns of a table in ResultSet object, all the column names, column datatypes and the column display sizes.
  • To get the ResultSet Metadata object we will use the following method from ResultSet object.

public ResultSetMetaData getMetaData()

  • To get the number of columns available in ResultSet object we will use the following method from ResultSetMetaData object.

 

public int getColumnCount()

  • To get the name of a particular column, we will use the following method.

 

public String getColumnName(int fieldno)

  • To get the column datatype of a particular column, we will use the following method

 

public String getColumnTypeName(int fieldno)

  • To get the column display size of a particular column we will use the following method.

public int getColumnDisplaySize(int fieldno)

The following example demonstrates how to get ResultSetMetaData from a ResultSet object

import java.sql.*;
public class ResultSetMD
{
            public static void main(String[] args)throws Exception
            {
                        Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection con = DriverManager.getConnection(“jdbc:odbc:nag”,”system”,”durga”);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(“select * from emp1”);
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();
System.out.println(“number of columns......”+count);
for(int i=1;i<=count;i++)
{
System.out.println(rsmd.getColumnName(i)+”  “+rsmd.getColumnTypeName(i)+”  “+rsmd.getColumnDisplaySize(i));
System.out.println()
                                    }
                        }
            }

28: how to display the data with the respective field names

import java.sql.*;
public class RSMD1
{
            public static void main(String[] args)throws Exception
            {
                        Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection con = DriverManager.getConnection(“jdbc:odbc:nag”,”system”,”durga”);
Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSEITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs = st.executeQuery(“select * from emp1”);
                                    ResultSetMetaData rsmd = rs.getMetaData();
System.out.println(rsmd.getColumnName(1)+”     “+rsmd.getColumnName(2)+”   “+rsmd.getColumnName(3)+”       “+rsmd.getColumnName(4));
System.out.println(“********************************”);
while(rs.next())
{
System.out.println(rs.getInt(1)+”    “+rs.getString(2)+”    “rs.getFloat(3)+”    “+rs.getString(4));
                                    }
                        }
            }

29:  What are the differences between Statement and PreparedStatement?
(or)
Tell me the situations where we should go for PreparedStatement over Statement object.

Ans:

  • When we have a requirement to execute same kind of sql query in the next sequence then we should go for PreparedStatement over Statement object.
  • For the above requirement if we use Statement object, every time execution of the same sql query DBE must perform query tokenization, query parsing, query optimization and query execution.
  • This approach will increase burden to the DBE. To reduce burden to the DBE we should go for an alternative. That is PreparedStatement over Statement object.
  • For the same requirement if we use PreparedStatement object then for our complete requirement DBE will go for only one time query parsing (tokenization, parsing, optimization and execution);

If we want to use PreparedStatement object for the above requirement then
we will use following steps.
Step1:  Prepare  PrepareStatement object by providing generalized sql query format with the required number of parameters, for this we will use the following method from Statement object.

            public PreparedStatement prepareStatement(String  sqlqueryformat)

Eg:  PreparedStatement pst = con.prepareStatement(“insert into emp1        values(?,?,?,?)”);

            When JVM encounters above instruction jvm will pickup specified generalized sql query format and send to the DBE, here DBE will process query format only one time and prepare a Buffer with the specified parameters, called as “query plan”. As a result PreparedStatement object will be created with the parameters at java side.

Step2:   Set the values to parameters in PreparedStatement object. After getting PreparedStatement object with parameters, we need to set some values to perform an operation, for this we will use the following method.

            public void setXxx(int parano,xxx value)

            where xxx may be byte, short, char, int, long, float, double.
Eg:  pst.setInt(1,111);
        pst.setString(2,”abc”);
            When  JVM  encounters the above method then jvm will set the specified values to the specified parameters at the PreparedStatement object, intern that parameter values could be reflected to query plan.

Step3: Given an intimation to DBE to perform the respective operation. After setting the values to the parameters we should give an intimation to the DBE explicitly pickup the values from query plan and perform the operation specified in generalized sql query format, for this we will use the following methods.

  • If the generalized sql query belongs to selection group then we will use following method from PreparedStatement object

public ResultSet executeQuery(…)

  • If the generalized sql query belongs to updation group then we will use the following method.

public int executeUpdate(…)

30:  Hhow to insert number of records into a table through Prepared Statement object.

import java.sql.*;
import java.io.*;
public class PreparedInsertEx
{
            public static void main(String[] args)throws Exception
            {
                        Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection con = DriverManager.getConnection(“jdbc:odbc:nag”,”system”,”durga”);
PreparedStatement pst= con.prepareStatement(“insert into emp1 values(?,?,?,?)”);
BufferedReader br= new BufferedReader(new InputStreamReader(System.in));
while(true)
{
;                    }
            }