JDBC Interview Questions with Answers Page III

From freshersonline.com

Interview Question Home

1. What isolation level is used by the DBMS when inserting, updating and selecting rows from a database?

The answer depends on both your code and the DBMS. If the program does not explicitly set the isolation level, the DBMS default is used. You can determine

the default using DatabaseMetaData.getDefaultTransactionIsolation() and the level for the current Connection with Connection.getTransactionIsolation(). If

the default is not appropriate for your transaction, change it with Connection.setTransactionIsolation(int level).

2. How can I determine the isolation levels supported by my DBMS?

Use DatabaseMetaData.supportsTransactionIsolationLevel(int level).

3. Connecting to a database through the Proxy I want to connect to remote database using a program that is running in the local network behind the

proxy. Is that possible?

I assume that your proxy is set to accept http requests only on port 80. If you want to have a local class behind the proxy connect to the database for

you, then you need a servlet/JSP to receive an HTTP request and use the local class to connect to the database and send the response back to the client.

You could also use RMI where your remote computer class that connects to the database acts as a remote server that talks RMI with the clients. if you

implement this, then you will need to tunnel RMI through HTTP which is not that hard.In summary, either have a servlet/JSP take HTTP requests, nstantiate

a class that handles database connections and send HTTP response back to the client or have the local class deployed as RMI server and send requests to

it using RMI.

4. How do I receive a ResultSet from a stored procedure?

Stored procedures can return a result parameter, which can be a result set. For a discussion of standard JDBC syntax for dealing with result, IN, IN/OUT

and OUT parameters, see Stored Procedures.

5. How can I write to the log used by DriverManager and JDBC drivers?

The simplest method is to use DriverManager.println(String message), which will write to the current log.

6. How can I get or redirect the log used by DriverManager and JDBC drivers?

As of JDBC 2.0, use DriverManager.getLogWriter() and DriverManager.setLogWriter(PrintWriter out). Prior to JDBC 2.0, the DriverManager methods

getLogStream() and setLogStream(PrintStream out) were used. These are now deprecated.

7. What does it mean to "materialize" data?

This term generally refers to Array, Blob and Clob data which is referred to in the database via SQL locators "Materializing" the data means to return

the actual data pointed to by the Locator.

For Arrays, use the various forms of getArray() and getResultSet().

For Blobs, use getBinaryStream() or getBytes(long pos, int length).

For Clobs, use getAsciiStream() or getCharacterStream().

8. Why do I have to reaccess the database for Array, Blob, and Clob data?

Most DBMS vendors have implemented these types via the SQL3 Locator type

Some rationales for using Locators rather than directly returning the data can be seen most clearly with the Blob type. By definition, a Blob is an

arbitrary set of binary data. It could be anything; the DBMS has no knowledge of what the data represents. Notice that this effectively demolishes data

independence, because applications must now be aware of what the Blob data actually represents. Let's assume an employee table that includes employee

images as Blobs.Say we have an inquiry program that presents multiple employees with department and identification information. To see all of the data

for a specific employee, including the image, the summary row is selected and another screen appears. It is only at this pont that the application needs

the specific image. It would be very wasteful and time consuming to bring down an entire employee page of images when only a few would ever be selected

in a given run.Now assume a general interactive SQL application. A query is issued against the employee table. Because the image is a Blob, the

application has no idea what to do with the data, so why bring it down, killing performance along the way, in a long running operation? Clearly this is

not helpful in those applications that need the data everytime, but these and other considerations have made the most general sense to DBMS vendors

9. What is an SQL Locator?

A Locator is an SQL3 data type that acts as a logical pointer to data that resides on a database server. Read "logical pointer" here as an identifier the

DBMS can use to locate and manipulate the data. A Locator allows some manipulation of the data on the server. While the JDBC specification does not

directly address Locators, JDBC drivers typically use Locators under the covers to handle Array, Blob, and Clob data types.

10. How do I set properties for a JDBC driver and where are the properties stored?

A JDBC driver may accept any number of properties to tune or optimize performance for the specific driver. There is no standard, other than user and

password, for what these properties should be. Therefore, the developer is dependent on the driver documentation to automatically pass properties. For a

standard dynamic method that can be used to solicit user input for properties, see What properties should I supply to a database driver in order to

connect to a database?In addition, a driver may specify its own method of accepting properties. Many do this via appending the property to the JDBC

Database URL. However, a JDBC Compliant driver should implement the connect(String url, Properties info) method. This is generally invoked through

DriverManager.getConnection(String url, Properties info).

11. What is the JDBC syntax for using a literal or variable in a standard Statement?

First, it should be pointed out that PreparedStatement handles many issues for the developer and normally should be preferred over a standard Statement.

Otherwise, the JDBC syntax is really the same as SQL syntax. One problem that often affects newbies ( and others ) is that SQL, like many languages,

requires quotes around character ( read "String" for Java ) values to distinguish from numerics. So the clause:

"WHERE myCol = " + myVal

is perfectly valid and works for numerics, but will fail when myVal is a String. Instead use:

"WHERE myCol = '" + myVal + "'"

if myVal equals "stringValue", the clause works out to:

WHERE myCol = 'stringValue'

You can still encounter problems when quotes are embedded in the value, which, again, a PreparedStatement will handle for you.

The passed properties are ( probably ) stored in variables in the Driver instance. This, again, is up to the driver, but unless there is some sort of

driver setup, which is unusual, only default values are remembered over multiple instantiations.

12. 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.

13. 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.

14. DB2 Universal claims to support JDBC 2.0, But I can only get JDBC 1.0 functionality. What can I do?

DB2 Universal defaults to the 1.0 driver. You have to run a special program to enable the 2.0 driver and JDK support. For detailed information, see

Setting the Environment in Building Java Applets and Applications. The page includes instructions for most supported platforms.

15. How to get a field's value with ResultSet.getxxx when it is a NULL? I have tried to execute a typical SQL statement:

select * from T-name where (clause);

But an error gets thrown because there are some NULL fields in the table.

You should not get an error/exception just because of null values in various columns. This sounds like a driver specific problem and you should first

check the original and any chained exceptions to determine if another problem exists. In general, one may retrieve one of three values for a column that

is null, depending on the data type. For methods that return objects, null will be returned; for numerics ( get Byte(), getShort(), getInt(), getLong(),

getFloat(), and getDouble() ) zero will be returned; for getBoolean() false will be returned. To find out if the value was actually NULL, use

ResultSet.wasNull() before invoking another getXXX method.

16. How do I insert/update records with some of the columns having NULL value?

Use either of the following PreparedStatement methods:

public void setNull(int parameterIndex, int sqlType) throws SQLException

public void setNull(int paramIndex, int sqlType, String typeName) throws SQLException

These methods assume that the columns are nullable. In this case, you can also just omit the columns in an INSERT statement; they will be automatically

assigned null values.

17. How do I disallow NULL values in a table?

Null capability is a column integrity constraint, normally aplied at table creation time. Note that some databases won't allow the constraint to be

applied after table creation. Most databases allow a default value for the column as well. The following SQL statement displays the NOT NULL constraint:

CREATE TABLE CoffeeTable (

Type VARCHAR(25) NOT NULL,

Pounds INTEGER NOT NULL,

Price NUMERIC(5, 2) NOT NULL

18. Why can't Tomcat find my Oracle JDBC drivers in classes111.zip?

TOMCAT 4.0.1 on NT4 throws the following exception when I try to connect to Oracle DB from JSP.

javax.servlet.ServletException : oracle.jdbc.driver.OracleDriver

java.lang.ClassNotFoundException: oracle:jdbc:driver:OracleDriver

But, the Oracle JDBC driver ZIP file (classes111.zip)is available in the system classpath.

Copied the Oracle Driver class file (classes111.zip) in %TOMCAT_HOME%\lib directory and renamed it to classess111.jar.

Able to connect to Oracle DB from TOMCAT 4.01 via Oracle JDBC-Thin Driver.

19. I have an application that queries a database and retreives the results into a JTable ...

I have an application that queries a database and retreives the results into a JTable. This is the code in the model that seems to be taken forever to

execute, especially for a large result set:

while ( myRs.next() ) {

Vector newRow =new Vector();

for ( int i=1;i<=numOfCols;i++ )

{

newRow.addElement(myRs.getObject(i));

}

allRows.addElement(newRow);

}

fireTableChanged(null);

newRow stores each row of the resultset and allRows stores all the rows. Are the vectors here the problem?

Is there another way of dealing with the result set that could execute faster?

java.util.Vector is largely thread safe, which means that there is a greater overhead in calling addElement() as it is a synchronized method. If your

result set is very large, and threading is not an issue, you could use one of the thread-unsafe collections in Java 2 to save some time.

java.util.ArrayList is the likeliest candidate here.

Do not use a DefaultTableModel as it loads all of your data into memory at once, which will obviously cause a large overhead - instead, use an

AbstractTableModel and provide an implementation which only loads data on demand, i.e. when (if) the user scrolls down through the table.

20. Is there a way to find the primary key(s) for an Access Database table? Sun's JDBC-ODBC driver does not implement the getPrimaryKeys() method

for the DatabaseMetaData Objects.

// Use meta.getIndexInfo() will

//get you the PK index. Once

// you know the index, retrieve its column name

DatabaseMetaData meta = con.getMetaData();

String key_colname = null;

// get the primary key information

rset = meta.getIndexInfo(null,null, table_name, true,true);

while( rset.next())

{

String idx = rset.getString(6);

if( idx != null)

{

//Note: index "PrimaryKey" is Access DB specific

// other db server has diff. index syntax.

if( idx.equalsIgnoreCase("PrimaryKey")) {

key_colname = rset.getString(9);

setPrimaryKey( key_colname ); }

}

}

21. How does one get column names for rows returned in a ResultSet?

ResultSet rs = ...

ResultSetMetaData rsmd = rs.getMetaData();

int numCols = rsmd.getColumnCount();

for (int i = 1; i <= numCols; i++)

{

System.out.println("[" + i + "]" +

rsmd.getColumnName(i) + " {" +

rsmd.getColumnTypeName(i) + "}");

}

22. What are the considerations for deciding on transaction boundaries?

Transaction processing should always deal with more than one statement and a transaction is often described as a Logical Unit of Work ( LUW ). The

rationale for transactions is that you want to know definitively that all or none of the LUW completed successfully. Note that this automatically gives

you restart capability. Typically, there are two conditions under which you would want to use transactions:

• Multiple statements involving a single file - An example would be inserting all of a group of rows or all price updates for a given date. You want all

of these to take effect at the same time; inserting or changing some subset is not acceptable.

• Multiple statements involving multiple files - The classic example is transferring money from one account to another or double entry accounting; you

don't want the debit to succeed and the credit to fail because money or important records will be lost. Another example is a master/detail relationship,

where, say, the master contains a total column. If the entire LUW, writing the detail row and updating the master row, is not completed successfully, you

A) want to know that the transaction was unsuccessful and B) that a portion of the transaction was not lost or dangling.

Therefore, determining what completes the transaction or LUW should be the deciding factor for transaction boundaries.

23. How can I determine where a given table is referenced via foreign keys?

DatabaseMetaData.getExportedKeys() returns a ResultSet with data similar to that returned by DatabaseMetaData.getImportedKeys(), except that the

information relates to other tables that reference the given table as a foreign key container.

24. How can I get information about foreign keys used in a table?

DatabaseMetaData.getImportedKeys() returns a ResultSet with data about foreign key columns, tables, sequence and update and delete rules.

25. Can I use JDBC to execute non-standard features that my DBMS provides?

The answer is a qualified yes. As discussed under SQL Conformance: "One way the JDBC API deals with this problem is to allow any query string to be

passed through to an underlying DBMS driver. This means that an application is free to use as much SQL functionality as desired, but it runs the risk of

receiving an error on some DBMSs. In fact, an application query may be something other than SQL, or it may be a specialized derivative of SQL designed

for specific DBMSs (for document or image queries, for example)." Clearly this means either giving up portability or checking the DBMS curently used

before invoking specific operations.