JDBC Interview Questions

1. What is JDBC?

Ans. JDBC is a Java API that is used to connect and execute query to the database. JDBC API uses jdbc drivers to connect to the database.

2. What is JDBC Driver?

Ans. JDBC Driver is a software component that enables java application to interact with the database.

There are 4 types of JDBC drivers:

JDBC-ODBC bridge driver

Native-API driver (partially java driver)

Network Protocol driver (fully java driver)

Thin driver (fully java driver)

3. What are the steps to connect to database in java?

Ans. Register the driver class

Creating connection

Creating statement

Executing queries

Closing connection

4. What is the difference between Statement and PreparedStatement interface?

Ans. In case of Statement, query is complied each time whereas in case of PreparedStatement, query is complied only once. So performance of PreparedStatement is better than Statement.

5. How can we execute stored procedures and functions?

Ans. By using CallableStatement interface, we can execute procedures and functions. 

6. how can we store and retrieve images from the database?

Ans. By using PreparedStaement interface, we can store and retrieve images.

import java.sql.*;

import java.io.*;

public class InsertImage {

public static void main(String[] args) {

try{

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection con=DriverManager.getConnection(

"jdbc:oracle:thin:@localhost:1521:xe","system","oracle");

                                   

PreparedStatement ps=con.prepareStatement("insert into imgtable values(?,?)");

                       

FileInputStream fin=new FileInputStream("d:\\g.jpg");

                       

ps.setString(1,"sonoo");

ps.setBinaryStream(2,fin,fin.available());

int i=ps.executeUpdate();

System.out.println(i+" records affected");

                       

con.close();

                                   

}catch (Exception e) {e.printStackTrace();}

}

}

7. What is JDBC Driver interface?

Ans. The JDBC Driver interface provides vendor-specific implementations of the abstract classes provided by the JDBC API. Each vendors driver must provide implementations of the java.sql.Connection,Statement,PreparedStatement, CallableStatement, ResultSet and Driver.

8. What Class.forName will do while loading drivers?

Ans. It is used to create an instance of a driver and register it with the DriverManager. When you have loaded a driver, it is available for making a connection with a DBMS.

9. How can you retrieve data from the ResultSet?

Ans. First JDBC returns results in a ResultSet object, so we need to declare an instance of the class ResultSet to hold our results. The following code demonstrates declaring the ResultSet object rs.

E.g.

ResultSet rs = stmt.executeQuery(”SELECT COF_NAME, PRICE FROM COFFEES”);

Second:

String s = rs.getString(”COF_NAME”);

The method getString is invoked on the ResultSet object rs , so getString will retrieve (get) the value stored in the column COF_NAME in the current row of rs.

10. What are the different types of Statements?
Ans.
1.Statement (use createStatement method)
2. Prepared Statement (Use prepareStatement method) and
3. Callable Statement (Use prepareCall)

11. How can you use PreparedStatement?

Ans. This special type of statement is derived from the more general class, Statement. If you want to execute a Statement object many times, it will normally reduce execution time to use a PreparedStatement object instead. The advantage to this is that in most cases, this SQL statement will be sent to the DBMS right away, where it will be compiled. As a result, the PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement ’s SQL statement without having to compile it first.

E.g.

PreparedStatement updateSales = con.prepareStatement(”UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?”);

12. How to call a Stored Procedure from JDBC?

Ans. The first step is to create a CallableStatement object. As with Statement and PreparedStatement objects, this is done with an open Connection object. A CallableStatement object contains a call to a stored procedure;

E.g.

CallableStatement cs = con.prepareCall(”{call SHOW_SUPPLIERS}”);

ResultSet rs = cs.executeQuery();

13. How to Retrieve Warnings?

Ans. SQLWarning objects are a subclass of SQLException that deal with database access warnings. Warnings do not stop the execution of an application, as exceptions do; they simply alert the user that something did not happen as planned. A warning can be reported on a Connection object, a Statement object (including PreparedStatement and CallableStatement objects), or a ResultSet object. Each of these classes has a getWarnings method, which you must invoke in order to see the first warning reported on the calling object

E.g.

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

        }

    }

14. How to Make Updates to Updatable Result Sets?

Ans. Another new feature in the JDBC 2.0 API is the ability to update rows in a result set using methods in the Java programming language rather than having to send an SQL command. But before you can take advantage of this capability, you need to create a ResultSet object that is updatable. In order to do this, you supply the ResultSet constant CONCUR_UPDATABLE to the createStatement method.

E.g.

Connection con = DriverManager.getConnection(”jdbc:mySubprotocol:mySubName”);

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,

ResultSet.CONCUR_UPDATABLE);

ResultSet uprs = (”SELECT COF_NAME, PRICE FROM COFFEES”);

15. What is new in JDBC 2.0?

Ans. With the JDBC 2.0 API, you will be able to do the following:

* Scroll forward and backward in a result set or move to a specific row (TYPE_SCROLL_SENSITIVE,previous(), last(), absolute(), relative(), etc.)

* Make updates to database tables using methods in the Java programming language instead of using SQL commands.(updateRow(), insertRow(), deleteRow(), etc.)

* Send multiple SQL statements to the database as a unit, or batch (addBatch(), executeBatch())

* Use the new SQL3 datatypes as column values like Blob, Clob, Array, Struct, Ref.

16. How to move the cursor in scrollable resultsets?(new feature in JDBC 2.0)

Ans.

a. create a scrollable ResultSet object.

    Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,

         ResultSet.CONCUR_READ_ONLY);

    ResultSet srs = stmt.executeQuery("SELECT COLUMN_1,

 COLUMN_2 FROM TABLE_NAME");

b. Use a built in methods like afterLast(), previous(), beforeFirst(), etc. to scroll the resultset.

   srs.afterLast();

    while (srs.previous()) {

        String name = srs.getString("COLUMN_1");

        float salary = srs.getFloat("COLUMN_2");

        //...

c. to find a specific row, use absolute(), relative() methods.

       srs.absolute(4); // cursor is on the fourth row

       int rowNum = srs.getRow(); // rowNum should be 4

       srs.relative(-3);

       int rowNum = srs.getRow(); // rowNum should be 1

       srs.relative(2);

       int rowNum = srs.getRow(); // rowNum should be 3

d. use isFirst(), isLast(), isBeforeFirst(), isAfterLast() methods to check boundary status.

17. How to update a resultset programmatically? (new feature in JDBC 2.0)

 Ans.

a. create a scrollable and updatable ResultSet object.

Statement stmt = con.createStatement

         (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

   ResultSet uprs = stmt.executeQuery("SELECT COLUMN_1,

         COLUMN_2 FROM TABLE_NAME");

b. move the cursor to the specific position and use related method to update data and then, call updateRow() method.

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.

18. How to insert and delete a row programmatically? (new feature in JDBC 2.0)

Make sure the resultset is updatable.

1. move the cursor to the specific position.

uprs.moveToCurrentRow();

2. set value for each column.

   uprs.moveToInsertRow();//to set up for insert

   uprs.updateString("col1" "strvalue");

   uprs.updateInt("col2", 5);

   ...

3. call insertRow() method to finish the row insert process.

uprs.insertRow();

To delete a row: move to the specific position and call deleteRow() method:

   uprs.absolute(5);

   uprs.deleteRow();//delete row 5

To see the changes call refreshRow();

uprs.refreshRow();

   

19. How does one use the Oracle JDBC Thin Driver?
20. How does one use the Oracle OCI Drivers?
21. What is JSQL and what is it good for?
22. Where can I find a list of the latest JDBC drivers ?
23. Can I connect to my database from Microsoft Access or Visual Basic via ODBC?
24. Why do I have more than one database connection?

25. How do I pool my database connections?
26. Indirect Database Access from Applets
27. How do I determine the number of columns in a ResultSet?
28. What is a database URL?
29. How do I create a database connection?
30. What is Metadata and why should I use it?
31. What is the advantage of using a PreparedStatement?
32. What is a "dirty read"?

33. How do I check what table types exist in a database?
34. How do I extract SQL table column type information?
35. How do I find all database stored procedures in a database?
36. How can I investigate the parameters to send into and receive from a database stored procedure?
37. What properties should I supply to a database driver in order to connect to a database?
38. Which is the preferred collection class to use for storing database result sets?
39. Do I need to commit after an INSERT call in JDBC or does JDBC do it automatically in the DB?
40. How can I retrieve only the first n rows, second n rows of a database using a particular WHERE clause?

41. For example, if a SELECT typically returns a 1000 rows, how do first retrieve the 100 rows, then go back and retrieve the next 100 rows and so on ?

42. What does ResultSet actually contain? Is it the actual data of the result or some links to
43. databases? If it is the actual data then why can't we access it after connection is closed?
44. How can I make batch updates using JDBC?
45. Do I need to commit after an INSERT call in JDBC or does JDBC do it automatically in the DB?
46. How can I manage special characters (for example: " _ ' % ) when I execute an INSERT query?
47. the java.sql package contains mostly interfaces. When and how are these interfaces implemented while connecting to database?

48. How can I connect from an applet to a database on the server?
49. How do i insert an image file (or other raw data) into a database?
50. How can resultset records be restricted to certain rows?
51. How can I pool my database connections so I don't have to keep reconnecting to the database?
52. In distributed architecture (typical three tier consisting of thin client, middleware & database)  which type of JDBC driver should be used and why?

53. How do I pass a Timestamp from JDBC to a database? Give me some database specific examples (MS   Access etc.).
54. Will a call to PreparedStatement.executeQuery() always close the ResultSet from the previous executeQuery()?
55. How do you find the number of records returned using the JDBC API? Is there a direct function call(like in other languages)?
56. I have stored image files in a database. Is there any way to display that image in a web browser by querying the database?
57. How can I get data from multiple ResultSets.
58. What is JDBC and what is it good for?