Jdbc Faqs-->5

JDBC interview questions and answers

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?

• 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)
{
;                    }
            }
           

31: how to update the database through PreparedStatement object?

import java.sql.*;
public class PreparedUpdateEx
{
            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(“update emp1 set esal = esal+? Where esal<?”);
Pst.setInt(1,500);
Pst.setFloat(2,10000.0f);
Int count = pst.executeUpdate();
System.out.println(“no. of records updated:”+count);
                        }
            }

32:how to fetch the data from database through PreparedStatement object?

import java.sql.*;
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”);
PreparedStatement pst = con.prepareStatement(“select * from emp1 where esal<=?”);
Pst.setFloat(1,10000.0f);
ResultSet rs = pst.executeQuery();
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));
                                    }
                        }
            }

33:What is meant by Transaction? How it is possible to maintain Transactions in JDBC applications?

• Transaction is nothing but an unit of work performed by the applications.
• Every transaction should have the following properties.
• Atomicity
• Consistency
• Isolation
• Durability

• Where atomicity is nothing but perform all the operations or not to perform all the operations in a transaction. That is every transaction must be in either success state or failure state.
• As part of the jdbc applications when we establish a connection automatically the connection should have a default nature called as “auto commit”.
• Auto commit in the sense when we send an sql query to the connection then connection will carry that to the DBE and make the DBE to execute provided sql query and store the results on the database permanently.
• The connections default auto commit nature violates the transactions atomicity property.
• To preserve transactions atomicity property we should change the connections auto commit nature to non-auto commit nature, for this we will use the following method.

Public void setAutoCommit(Boolean b)
Where b=true    connection is in auto commit
And b=false      connection not in auto commit.

• If we use connections non auto commit nature in our jdbc applications then we must use either commit or rollback operations explicitily as part of the transactions.

Public void commit()
Public void rollback()

The following example demonstrates how to maintain the transactions with atomicity property in the jdbc applications.

import java.sql.*;
public class TransactionEx
{
      public static void main(String[] args)throws Exception
      {
                  Connection con = null;
                  try
                  {
                              Class.forName(“sun.jdbc.odbd.JdbcOdbcDriver”);
Con = DriverManager.getConnection(“jdbc:odbc:nag”,”system”,”durga”);
con.setAutoCommit(“false”);
Statement st = con.createStatement();
st.executeUpdate(“insert into emp1 values(888,’fff’,8000,’hhh’)”);
st.executeUpdate(“update emp1 set esal = esal-500 where esal>= ‘abc’ “);
st.executeUpdate(“delete emp1 where esal<7000”);
con.commit();
                        }
                        catch(Exception e)
                        {
                                    con.rollback();
                                    System.out.println(e);
                        }
            }
      }

34:What is meant by SavePoint?How to use Savepoints in JDBC applications?

• Save point is a concept introduced by jdbc 3.0 which can be used to block a set of instructions execution in the transactions committing operation.
• To set a save point we will use the following method.

public SavePoint setSavePoint()

• To block a set of sql queries execution prior to the save point we will use the following method.

public void rollback(savepoint s)

• To release a savepoint we will use the following method

public void releaseSavePoint();

• SavePoint concept could not be supported be type1 driver, it could be supported by type4 driver.
• Even type 4 driver is supporting up to setSavePoint() and rollback() , not releaseSavepoint();

  Eg:
      import java.sql.*;
public class SavePointEx
{
      public static void main(String[] args)throws Exception
      {
                  Connection con = null;
                  try
                  {
                              Class.forName(“oracle.jdbc.driver.OracleDriver”);
con = DriverManager.getConnection(“jdbc:oracle:thin:@locajhost:1521:xe”,”system”,”durga”);
con.setAutoCommit(“false”);
Statement st = con.createStatement();
st.executeUpdate(“insert into emp1 values(111,’fff’,8000,’hhh’)”);
savepoint sp= con.Savepoint();
st.executeUpdate(“insert into emp1 values(222,’ggg’,7000,’iii’) “);
con.rollback(sp);
st.executeUpdate(“insert into emp1 values(333,’hhh’,9000,’jjj’)”);
con.commit();
                        }
                        catch(Exception e)
                        {
                                    con.rollback();
                                    System.out.println(e);
                        }
            }
      }