JDBC interview questions & answers

Back
What is a ResultSet ?
A table of data representing a database result set, which is usually generated by executing a statement that queries the database.
A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row.

What are the different types of Statements?
Regular statement (use createStatement method), prepared statement (use prepareStatement method) and callable statement (use prepareCall)

What is DriverManager ?
A DriverManager is a class in java.sql package. It is the basic service for managing a set of JDBC drivers.

How can you retrieve data from the ResultSet?
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.
ResultSet rs = stmt.executeQuery(”SELECT COF_NAME, PRICE FROM COFFEES”);
String s = rs.getString(”COF_NAME”);

What is the advantage of denormalization?
A Data denormalization is reverse procedure, carried out purely for reasons of improving performance. It maybe efficient for a high-throughput system to replicate data for certain data.

How does a custom RowSetReader get called from a CachedRowSet?
The Reader must be registered with the CachedRowSet using CachedRowSet.setReader(javax.sql.RowSetReader reader). Once that is done, a call to CachedRowSet.execute() will, among other things, invoke the readData method.

What is Connection pooling?
A Connection pooling is a technique used for sharing server resources among requesting clients. Connection pooling increases the performance of Web applications by reusing active database connections instead of creating a new connection with every request. Connection pool manager maintains a pool of open database connections.

What are the different JDB drivers available?
1: JDBC-ODBC Bridge Driver – A JDBC-ODBC bridge provides JDBC API access via one or more ODBC drivers. Note that some ODBC native code and in many cases native database client code must be loaded on each client machine that uses this type of driver. Hence, this kind of driver is generally most appropriate when automatic installation and downloading of a Java technology application is not important. For information on the JDBC-ODBC bridge driver provided by Sun.

2: Native API Partly Java Driver- A native-API partly Java technology-enabled driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this style of driver requires that some binary code be loaded on each client machine.

3: Network protocol Driver- A net-protocol fully Java technology-enabled driver translates JDBC API calls into a DBMS-independent net protocol which is then translated to a DBMS protocol by a server. This net server middleware is able to connect all of its Java technology-based clients to many different databases. The specific protocol used depends on the vendor. In general, this is the most flexible JDBC API alternative. It is likely that all vendors of this solution will provide products suitable for Intranet use. In order for these products to also support Internet access they must handle the additional requirements for security, access through firewalls, etc., that the Web imposes. Several vendors are adding JDBC technology-based drivers to their existing database middleware products.

4: JDBC Net pure Java Driver – A native-protocol fully Java technology-enabled driver converts JDBC technology calls into the network protocol used by DBMSs directly. This allows a direct call from the client machine to the DBMS server and is a practical solution for Intranet access. Since many of these protocols are proprietary the database vendors themselves will be the primary source for this style of driver. Several database vendors have these in progress.

What is the fastest type of JDBC driver?
JDBC driver performance will depend on a number of issues:
(A) the quality of the driver code,
(B) the size of the driver code,
(C) the database server and its load,
(D) network topology,
(E) the number of times your request is translated to a different API.

What is cold backup, hot backup recovery?
A Cold backup means all these files must be backed up at the same time, before the database is restarted.
Hot backup (online backup) is a backup taken of each tablespace while the database is running and is being accessed by the users

Is the JDBC-ODBC Bridge multi-threaded?
A No. The JDBC-ODBC Bridge does not support multi threading. The JDBC-ODBC Bridge uses synchronized methods to serialize all of the calls that it makes to ODBC. Multi-threaded Java programs may use the Bridge, but they won’t get the advantages of multi-threading.

How can I create a custom RowSetMetaData object from scratch?
One unfortunate aspect of RowSetMetaData for custom versions is that it is an interface. This means that implementations almost have to be proprietary. The JDBC RowSet package is the most commonly available and offers the sun.jdbc.rowset.RowSetMetaDataImpl class. After instantiation, any of the RowSetMetaData setter methods may be used. The bare minimum needed for a RowSet to function is to set the Column Count for a row and the Column Types for each column in the row. For a working code example that includes a custom RowSetMetaData.


How do I check in my code whether a maximum limit of database connections have been reached?
Use DatabaseMetaData.getMaxConnections() and compare to the number of connections currently open. Note that a return value of zero can mean unlimited or, unfortunately, unknown. Of course, driverManager.getConnection() will throw an exception if a Connection can not be obtained.
Why do I get UnsatisfiedLinkError when I try to use my JDBC driver?
The first thing is to be sure that this does not occur when running non-JDBC apps. If so, there is a faulty JDK/JRE installation. If it happens only when using JDBC, then it’s time to check the documentation that came with the driver or the driver/DBMS support. JDBC driver types 1 through 3 have some native code aspect and typically require some sort of client install. Along with the install, various environment variables and path or classpath settings must be in place. Because the requirements and installation procedures vary with the provider, there is no reasonable way to provide details here. A type 4 driver, on the other hand, is pure Java and should never exhibit this problem. The trade off is that a type 4 driver is usually slower.

How can I create a custom RowSetMetaData object from scratch?
One unfortunate aspect of RowSetMetaData for custom versions is that it is an interface. This means that implementations almost have to be proprietary. The JDBC RowSet package is the most commonly available and offers the sun.jdbc.rowset.RowSetMetaDataImpl class. After instantiation, any of the RowSetMetaData setter methods may be used. The bare minimum needed for a RowSet to function is to set the Column Count for a row and the Column Types for each column in the row. For a working code example that includes a custom RowSetMetaData.

How do I retrieve a whole row of data at once, instead of calling an individual ResultSet.getXXX method for each column
The ResultSet.getXXX methods are the only way to retrieve data from a ResultSet object, which means that you have to make a method call for each column of a row. It is unlikely that this is the cause of a performance problem, however, because it is difficult to see how a column could be fetched without at least the cost of a function call in any scenario. We welcome input from developers on this issue.

How can I retrieve a String or other object type without creating a new object each time?
Creating and garbage collecting potentially large numbers of objects (millions) unnecessarily can really hurt performance. It may be better to provide a way to retrieve data like strings using the JDBC API without always allocating a new object.


Share your requirement with me and give your business a much needed thrust.
CONTACT HERE