JDBC Interview Questions

JDBC Questions

Q) What Class.forName will do while loading drivers?

A) Will create an instance of the driver and register with the DriverManager.

Q) JDBC 3.0 new features?

A) 1. Transaction Savepoint support: - Added the Savepoint interface, which contains new methods to set, release, or roll back a transaction to designated savepoints.

2. Reuse of prepared statements by connection pools: - to control how prepared statements are pooled and reused by connections.

3. Connection pool configuration :- Defined a number of properties for the ConnectionPoolDataSource interface.

These properties can be used to describe how PooledConnection objects created by DataSource objects should be pooled.

4. Retrieval of parameter metadata: - Added the interface ParameterMetaData, which describes the number, type

and properties of parameters to prepared statements.

5. Retrieval of auto-generated keys: - Added a means of retrieving values from columns containing automatically

generated values.

6. Multiple open ResultSet objects: - Added the new method getMoreResults(int).

7. Passing parameters to CallableStatement objects by name: - Added methods to allow a string to identify the parameter to be set for a CallableStatement object.

8. Holdable cursor support: - Added the ability to specify the of holdability of a ResultSet object.

9. BOOLEAN data type: - Added the data type java.sql.Types.BOOLEAN. BOOLEAN is logically equivalent to BIT.

10. Making internal updates to the data in Blob and Clob objects: - Added methods to allow the data contained in Blob and Clob objects to be altered.

11. Retrieving and updating the object referenced by a Ref object: - Added methods to retrieve the object referenced by a Ref object. Also added the ability to update a referenced object through the Ref object.

12. Updating of columns containing BLOB, CLOB, ARRAY and REF types: - Added of the updateBlob, updateClob, updateArray, and updateRef methods to the ResultSet interface.

Q) JDBC Drivers

• JDBC-ODBC Bridge Driver
• Native API - Partly Java Driver
• Network protocol - All Java Driver
• Native Protocol - Pure Java Driver

Tier	Driver mechanism	Description
Two	JDBC-ODBC	JDBC access via most ODBC drivers, some ODBC binary code and client code must be loaded on each client machine. This driver is commonly used for prototyping. The JDBC-ODBC Bridge is JDBC driver which implements JDBC operations by translating them to ODBC operations.
Two	Native API - Partly Java driver	This driver converts JDBC calls to database specific native calls. Client requires database specific libraries.
Three	Network protocol - All Java Driver	This driver converts JDBC calls into DBMS independent network protocol that is sent to the middleware server. This will translate this DBMS independent network protocol into DBMS specific protocol, which is sent to a particular database. The results are again rooted back to middleware server and sent back to client.
Two	Native protocol - All - Java driver	They are pure java driver, they communicate directly with the vendor database.

Q) JDBC connection

import java.sql.*;

public class JDBCSample {

  public static void main(java.lang.String[] args) {

    try {

      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

    } catch (ClassNotFoundException e) {

      System.out.println("Unable to load Driver Class");

      return;

    }

    try {

      Connection con = DriverManager.getConnection("jdbc:odbc:companydb","uname", "pwd");

      Statement stmt = con.createStatement();

      ResultSet rs = stmt.executeQuery("SELECT FIRST_NAME FROM EMPLOYEES");

      while(rs.next()) {

      System.out.println(rs.getString("FIRST_NAME"));

      }

      rs.close();

      stmt.close();

      con.close();

    }

    catch (SQLException se) {

      System.out.println("SQL Exception: " + se.getMessage());

    }

  }

}

Q) 4^th type driver

class.forName(“oracle.jdbcdriver.oracledriver”);

connection con = driverManager.getConnection(“JDBC:oracle:thin:@hostname:portno:oracleservice”,”uid”, “pwd”);

Q) Steps to connect to JDBC?

A) 1. First thing is using jdbc you have to establish a connection to the data base this is 2 steps process (i) you must load the jdbc driver (ii) then make a connection, to do this we can call the getConnection() method of driver manager class.

2. To execute any sql commands using jdbc connection you must first create a statement object to create this call statement st = con.createSteatement().

This is done by calling the createStatement() method in connection interface. Once the statement is created you can executed it by calling execute() method of the statement interface.

Q) Resultset Types

  rs.beforeFirst() -- goto 1^st record

  rs.afterLast() -- goto last record

  isFirst() / isLast()

  res.absolute(4) -- will got 4^th record in result set.

  rs.deleteRow()

  rs.updateRow(3,88) -- value in column 3 of resultset is set to 88.

  rs.updateFloat()

  rs.relative(2)

Q) Transactional  Savepoints

  Statement stmt = conn.createStatement ();

  Int rowcount = stmt.executeUpdate ("insert into etable (event) values ('TMM')"); 

  Int rowcount = stmt.executeUpdate ("insert into costs (cost) values (45.0)");

  Savepoint sv1 = conn.setSavePoint ("svpoint1"); // create save point for inserts

  Int rowcount = stmt.executeUpdate ("delete from employees");

  Conn.rollback (sv1); // discard the delete statement but keep the inserts

  Conn.commit; // inserts are now permanent

Q) Updating BLOB & CLOB Data Types

  rs.next();

  Blob data = rs.getClob (1);

  Rs.close();

  // now let's insert this history into another table

  stmt.setClob (1, data); // data is the Clob object we retrieved from the history table

  int InsertCount = stmt.executeUpdate("insert into EscalatedIncidents (IncidentID, CaseHistory, Owner)"

+ " Values (71164, ?, 'Goodson') ");

Q Retreiving / Storing / Updating Array of Objects

     Array a = rs.getArray(1);

     Pstmt.setArray(2, member_array);

     Rs.updateArray(“last_num”,num);

Q) How to execute no of queries at one go?

A) By using a batchUpdate's (i.e. throw addBatch() and executeBatch()) in java.sql.Statement interface or by using procedures.

Q) Batch Updates        

CallableStatement stmt = con.prepareCall(“{call employeeInfo (?)}”);

stmt.addBatch("INSERT INTO employees VALUES (1000, 'Joe Jones')");

stmt.addBatch("INSERT INTO departments VALUES (260, 'Shoe')");

// submit a batch of update commands for execution

int[] updateCounts = stmt.executeBatch();

Q) Multiple Resultset

A) The methods getMoreResults, getUpdateCount, and getResultSet can be used to retrieve all the results.

CallableStatement cstmt = connection.prepareCall(procCall);

boolean retval = cstmt.execute();

if (retval == false) {

} else {

    ResultSet rs1 = cstmt.getResultSet();

    retval = cstmt.getMoreResults(Statement.KEEP_CURRENT_RESULT);

    if (retval == true) {

        ResultSet rs2 = cstmt.getResultSet();

        rs2.next();

        rs1.next();

    }

}

CLOSE_ALL_RESULTS	All previously opened ResultSet objects should be closed when calling getMoreResults().
CLOSE_CURRENT_RESULT	The current ResultSet object should be closed when calling getMoreResults().
KEEP_CURRENT_RESULT	The current ResultSet object should not be closed when calling getMoreResults().

Q) Diff execute() ,executeUpdate() and executeQuery() ?

A) execute() returns a boolean value, which may return multiple results.

    executeUpdate() is used for nonfetching queries, which returns int value and tell how many rows will be affected.

    executeQuery() is used for fetching queries, which returns single ResulSet object and never return Null value.

Q) How to move the cursor in scrollable resultset?

   Type of a ResultSet object:-

   TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, TYPE_SCROLL_SENSITIVE,         CONCUR_READ_ONLY and CONCUR_UPDATABLE.

    Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                                                                                ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = stmt.executeQuery("SELECT COLUMN_1, COLUMN_2 FROM TABLE_NAME");
    rs.afterLast();
    while (srs.previous()) {
       String name = rs.getString("COLUMN_1");
       float salary = rs.getFloat("COLUMN_2");
       rs.absolute(4); // cursor is on the fourth row
       int rowNum = rs.getRow(); // rowNum should be 4
       rs.relative(-3);
       int rowNum = rs.getRow(); // rowNum should be 1
       rs.relative(2);
       int rowNum = rs.getRow(); // rowNum should be 3
        //...
     }

Q) How to “Update” & “Delete” a resultset programmatically?

Update: -

   Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                                                                             ResultSet.CONCUR_UPDATABLE);
   ResultSet uprs = stmt.executeQuery("SELECT COLUMN_1, COLUMN_2 FROM TABLE_NAME");
   uprs.last();
   uprs.updateFloat("COLUMN_2", 25.55);//update last row's data
   uprs.updateRow();//don't miss this method, otherwise, the data will be lost.
 

Delete: -

   uprs.absolute(5);

   uprs.deleteRow(); // will delete row 5.

Q) JDBC connection pool

            When you are going to caret a pool of connection to the database. This will give access to a collection of already opened data base connections, which will reduce the time it takes to service the request and you can service “n” number of request at once.

Q) Why you need JDBC if ODBC is available?

A) ODBC is purely written in “c” so we cannot directly connect with java. JDBC is a low level pure java API used to execute SQL statements. (i) ODBC is not appropriate for direct use from java because it uses “c” interfaces. Calls from java to native “c” code has number of drawbacks in the security, implementation and robustness.

Q) Can we establish the connection with ODBC itself?

A) Yes, using java native classes we have to write a program.

Q) What is necessity of JDBC in JDBC-ODBC bridge?

A) The purpose of JDBC is to link java API to the ODBC, ODBC return high level “c” API so the JDBC converts “c” level API to java API.

Q) Does the JDBC-ODBC Bridge support multiple concurrent open statements per connection?

A) No. You can open only one Statement object per connection when you are using the JDBC-ODBC Bridge.

Q) Is the JDBC-ODBC Bridge multi-threaded?

A) No. The JDBC-ODBC Bridge does not support concurrent access from different threads. The JDBC-ODBC Bridge uses synchronized methods to serialize all of the calls that it makes to ODBC

Q) Dynamically creating Tables

            Statement st = con.cretaeStatement();

            Int n = st.executeUpdate(“create table “ + uname+ “(sno int, sentby varchar(10), subject varchar(15)”);

Q) Statements in JDBC

Statement                -- Does not take any arguments, In this statement it will check syntax error and execute it every time (it will parse every time).

Prepare statement -- P.S are precompiled statements once we compile the statements and send it to the server for later use. P.S are partially compiled statements placed at server side with placeholders. Before execution of these statements user has to supply values for place holders, it will increase performance of application.

PreparedStatement pst = con.prepareStatement("SELECT * FROM EMP WHERE deptno=?");

DataInputStream dis = new DataInputStream(“System.in”);

Int dno = Integer.ParseInt(dis.readLine());

pst.setInt(1, dno);

ResultSet rs = pst.executeQuery();

Callable statement -- C.S used to retrieve data by invoking stored procedures, stored procedure are program units placed at data base server side for reusability. These are used by n-number of clients. Stored procedure is precompiled in RDBMS, so they can run faster than the dynamic sql.

Callable statement will call a single stored procedure, they perform multiple queries and updates without network traffic.

callableStatement cst = con.prepareCall(“{CALL procedure-name(??)} ”);

DataInputStream dis = new DataInputStream(“System.in”);

Int enum = Integer.ParseInt(dis.readLine());

cst.setInt(1, enum);

cst.registerOutParameter(2, types.VARCHAR)

resultset rs = cst.execute();

In       -- used to send information to the procedure.

Out    -- used to retrieve information from data base.

InOut -- both.

Q) In which interface the methods commit() & rollback() savepoint() defined ?

A) java.sql.Connection interface

Q) Retrieving very large values from database?

A) getASSCIISteram() -- read values which are character in nature.

     GetBinaryStream()  -- used to read images.

Q) ResultSetMetaData

It is used to find out the information of a table in a data base.

      ResultSet rs = stmt.executeQuery("SELECT * FROM "+ table);

      ResultSetMetaData rsmd = rs.getMetaData();

Methods -- getColumnCount(), getColumnName(), getColumnLabel(), getColumnType(), getTableName(),

Q) Database MetaData

You need some information about the “data base” & “dictionary” we use this .To find out tables, stored procedure names, columns in a table, primary key of a table we use this, this is the largest interface in java.sql package

Connection con = DriverManager.getConnection(jdbcURL, "", "");

DatabaseMetaData dbmd = con.getMetaData();

ResultSet rs= dbmd.getxxx();

Methods -- getColumns(), getTableTypes(), getTables(), getDriverName(), getMajorVersion(), get MinorVersion(), getProcedures(), getProcedureColumns(), getTables().

Q) SQL Warnings

Warnings may be retrieved from Connection, Statement, and ResultSet objects. Trying to retrieve a warning on a connection after it has been closed will cause an exception to be thrown. Similarly, trying to retrieve a warning on a statement after it has been closed or on a result set after it has been closed will cause an exception to be thrown. Note that closing a statement also closes a result set that it might have produced.

Connection.getWarnings()

Statement.getWarnings(),

ResultSet.getWarnings(), Serialized Form

SQLWarning warning = stmt.getWarnings();

                if (warning != null)

                {

                                while (warning != null)

                                {

                                                System.out.println("Message: " + warning.getMessage());

                                                System.out.println("SQLState: " + warning.getSQLState());

                                                System.out.print("Vendor error code: ");

                                                System.out.println(warning.getErrorCode());

                                                warning = warning.getNextWarning();

                                }

                }

Q) Procedure

            Procedure is a subprogram will perform some specific action, sub programs are name PL/SQL blocks that can take parameters to be invoked.

create (or) replace procedure procedure-name (id IN INTEGER , bal IN OUT FLOAT) IS

  BEGIN

select balance into bal from accounts where account_id = id;

             Bal: = bal + bal * 0.03;

             Update accounts set balance = bal where account_id = id;

  END;

Q) Trigger

            Trigger is a stored PL/SQL block associated with a specific database table. Oracle executes triggers automatically when ever a given SQL operation effects the table, we can associate 12 data base triggers with in a given table.

Create/Replace trigger before Insert (or) Delete (or) Update on emp for each row

Begin

 Insert into table-name values(:empno; :name)

end

Q) Stored Images into a table

Public class img

{

   Public static void main(String args[]){

   Class.forName();

   Connection con = DriverManager.getConnection();

   Preparestatement pst = con.prepareStatement(“insert into image value(?));

   FileInputStream fis = new FileInputStream(“a.gif”);

   Pst.setBinaryStream(1, fis, fis.available);

   Int I = pst.executeUpadate();

}

Retrieve Image

Statement st = con.CreateStatement();

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

Rs.next();

InputStream is = rs.getBinaryStream(1);

FileOutPutStream fos = new FileOutPutStream(“g2.gif”);

Int ch;

While((ch=is.read(1))!=!-1)

{

            fos.write(ch);

}