JDBC Interview Questions with Answers Page II

From freshersonline.com

Interview Question Home

1. Why can't I invoke the ResultSet methods afterLast and beforeFirst when the method next works?

You are probably using a driver implemented for the JDBC 1.0 API. You need to upgrade to a JDBC 2.0 driver that implements scrollable result sets. Also be

sure that your code has created scrollable result sets and that the DBMS you are using supports them.

2. 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.We are studying this issue to see if it is an area in which the

JDBC API should be improved. Stay tuned, and please send us any comments you have on this question.

3. How many types of JDBC Drivers are present and what are they?

There are 4 types of JDBC Drivers

Type 1: JDBC-ODBC Bridge Driver

Type 2: Native API Partly Java Driver

Type 3: Network protocol Driver

Type 4: JDBC Net pure Java Driver

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

In general, all things being equal, you can assume that the more your request and response change hands, the slower it will be. This means that Type 1

and Type 3 drivers will be slower than Type 2 drivers (the database calls are make at least three translations versus two), and Type 4 drivers are the

fastest (only one translation).

5. There is a method getColumnCount in the JDBC API. Is there a similar method to find the number of rows in a result set?

No, but it is easy to find the number of rows. If you are using a scrollable result set, rs, you can call the methods rs.last and then rs.getRow to find

out how many rows rs has. If the result is not scrollable, you can either count the rows by iterating through the result set or get the number of rows by

submitting a query with a COUNT column in the SELECT clause.

6. I would like to download the JDBC-ODBC Bridge for the Java 2 SDK, Standard Edition (formerly JDK 1.2). I'm a beginner with the JDBC API, and I

would like to start with the Bridge. How do I do it?

The JDBC-ODBC Bridge is bundled with the Java 2 SDK, Standard Edition, so there is no need to download it separately.

7. If I use the JDBC API, do I have to use ODBC underneath?

No, this is just one of many possible solutions. We recommend using a pure Java JDBC technology-enabled driver, type 3 or 4, in order to get all of the

benefits of the Java programming language and the JDBC API.

8. Once I have the Java 2 SDK, Standard Edition, from Sun, what else do I need to connect to a database?

You still need to get and install a JDBC technology-enabled driver that supports the database that you are using. There are many drivers available from a

variety of sources. You can also try using the JDBC-ODBC Bridge if you have ODBC connectivity set up already. The Bridge comes with the Java 2 SDK,

Standard Edition, and Enterprise Edition, and it doesn't require any extra setup itself. The Bridge is a normal ODBC client. Note, however, that you

should use the JDBC-ODBC Bridge only for experimental prototyping or when you have no other driver available.

9. What is the best way to generate a universally unique object ID? Do I need to use an external resource like a file or database, or can I do it

all in memory?

1: Unique down to the millisecond. Digits 1-8 are are the hex encoded lower 32 bits of the System.currentTimeMillis() call.

2: Unique across a cluster. Digits 9-16 are the encoded representation of the 32 bit integer of the underlying IP address.

3: Unique down to the object in a JVM. Digits 17-24 are the hex representation of the call to System.identityHashCode(), which is guaranteed to return

distinct integers for distinct objects within a JVM.

4: Unique within an object within a millisecond. Finally digits 25-32 represent a random 32 bit integer generated on every method call using the

cryptographically strong java.security.SecureRandom class.

Answer1

There are two reasons to use the random number instead of incrementing your last. 1. The number would be predictable and, depending on what this is used

for, you could be opening up a potential security issue. This is why ProcessIDs are randomized on some OSes (AIX for one). 2. You must synchronize on

that counter to guarantee that your number isn't reused. Your random number generator need not be synchronized, (though its implementation may be).

Answer2

1) If your using Oracle You can create a sequence ,by which you can generate unique primary key or universal primary key. 2) you can generate by using

random numbers but you may have to check the range and check for unique id. ie random number generate 0.0 to 1.0 u may have to make some logic which

suits your unique id 3) Set the maximum value into an XML file and read that file at the time of loading your application from xml .

10. Whan happens when I close a Connection application obtained from a connection Pool? How does a connection pool maintain the Connections that I

had closed through the application?

Answer1

It is the magic of polymorphism, and of Java interface vs. implementation types. Two objects can both be "instanceof" the same interface type, even

though they are not of the same implementation type.

When you call "getConnection()" on a pooled connection cache manager object, you get a "logical" connection, something which implements the

java.sql.Connection interface.

But it is not the same implementation type as you would get for your Connection, if you directly called getConnection() from a (non-pooled/non-cached)

datasource.

So the "close()" that you invoke on the "logical" Connection is not the same "close()" method as the one on the actual underlying "physical" connection

hidden by the pool cache manager.

The close() method of the "logical" connection object, while it satisfies the method signature of close() in the java.sql.Connection interface, does not

actually close the underlying physical connection.

Answer2

Typically a connection pool keeps the active/in-use connections in a hashtable or other Collection mechanism. I've seen some that use one stack for

ready-for-use, one stack for in-use.

When close() is called, whatever the mechanism for indicating inuse/ready-for-use, that connection is either returned to the pool for ready-for-use or

else physically closed. Connections pools should have a minimum number of connections open. Any that are closing where the minimum are already available

should be physically closed.

Some connection pools periodically test their connections to see if queries work on the ready-for-use connections or they may test that on the close()

method before returning to the ready-for-use pool.

11. How can I know when I reach the last record in a table, since JDBC doesn't provide an EOF method?

Answer1

You can use last() method of java.sql.ResultSet, if you make it scrollable.

You can also use isLast() as you are reading the ResultSet.

One thing to keep in mind, though, is that both methods tell you that you have reached the end of the current ResultSet, not necessarily the end of the

table. SQL and RDBMSes make no guarantees about the order of rows, even from sequential SELECTs, unless you specifically use ORDER BY. Even then, that

doesn't necessarily tell you the order of data in the table.

Answer2

Assuming you mean ResultSet instead of Table, the usual idiom for iterating over a forward only resultset is:

ResultSet rs=statement.executeQuery(...);

while (rs.next()) {

// Manipulate row here

}

12. Where can I find info, frameworks and example source for writing a JDBC driver?

There a several drivers with source available, like MM.MySQL, SimpleText Database, FreeTDS, and RmiJdbc. There is at least one free framework, the

jxDBCon-Open Source JDBC driver framework. Any driver writer should also review For Driver Writers.

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

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

15. How do I implement a RowSetReader? I want to populate a CachedRowSet myself and the documents specify that a RowSetReader should be used. The

single method accepts a RowSetInternal caller and returns void. What can I do in the readData method?

"It can be implemented in a wide variety of ways..." and is pretty vague about what can actually be done. In general, readData() would obtain or create

the data to be loaded, then use CachedRowSet methods to do the actual loading. This would usually mean inserting rows, so the code would move to the

insert row, set the column data and insert rows. Then the cursor must be set to to the appropriate position.

16. Can I set up a connection pool with multiple user IDs? The single ID we are forced to use causes probelems when debugging the DBMS.

Since the Connection interface ( and the underlying DBMS ) requires a specific user and password, there's not much of a way around this in a pool. While

you could create a different Connection for each user, most of the rationale for a pool would then be gone. Debugging is only one of several issues that

arise when using pools.However, for debugging, at least a couple of other methods come to mind. One is to log executed statements and times, which should

allow you to backtrack to the user. Another method that also maintains a trail of modifications is to include user and timestamp as standard columns in

your tables. In this last case, you would collect a separate user value in your program.

17. How can I protect my database password ? I'm writing a client-side java application that will access a database over the internet. I have

concerns about the security of the database passwords. The client will have access in one way or another to the class files, where the connection string

to the database, including user and password, is stored in as plain text. What can I do to protect my passwords?

This is a very common question.

Conclusion: JAD decompiles things easily and obfuscation would not help you. But you'd have the same problem with C/C++ because the connect string would

still be visible in the executable.

SSL JDBC network drivers fix the password sniffing problem (in MySQL 4.0), but not the decompile problem. If you have a servlet container on the web

server, I would go that route (see other discussion above) then you could at least keep people from reading/destroying your mysql database.

Make sure you use database security to limit that app user to the minimum tables that they need, then at least hackers will not be able to reconfigure

your DBMS engine.

Aside from encryption issues over the internet, it seems to me that it is bad practise to embed user ID and password into program code. One could

generally see the text even without decompilation in almost any language. This would be appropriate only to a read-only database meant to be open to the

world. Normally one would either force the user to enter the information or keep it in a properties file.

18. Detecting Duplicate Keys I have a program that inserts rows in a table ...

Detecting Duplicate Keys I have a program that inserts rows in a table. My table has a column 'Name' that has a unique constraint. If the user attempts

to insert a duplicate name into the table, I want to display an error message by processing the error code from the database. How can I capture this

error code in a Java program?

A solution that is perfectly portable to all databases, is to execute a query for checking if that unique value is present before inserting the row. The

big advantage is that you can handle your error message in a very simple way, and the obvious downside is that you are going to use more time for

inserting the record, but since you're working on a PK field, performance should not be so bad.

You can also get this information in a portable way, and potentially avoid another database access, by capturing SQLState messages. Some databases get

more specific than others, but the general code portion is 23 - "Constraint Violations". UDB2, for example, gives a specific such as 23505, while others

will only give 23000.

19. What driver should I use for scalable Oracle JDBC applications?

Sun recommends using the thin ( type 4 ) driver.

• On single processor machines to avoid JNI overhead.

• On multiple processor machines, especially running Solaris, to avoid synchronization bottlenecks.

20. Can you scroll a result set returned from a stored procedure?...

Can you scroll a result set returned from a stored procedure? I am returning a result set from a stored procedure with type SQLRPGLE but once I reach the

end of the result set it does not allow repositioning. Is it possible to scroll this result set?

A CallableStatement is no different than other Statements in regard to whether related ResultSets are scrollable. You should create the CallableStatement

using Connection.prepareCall(String sql, int resultSetType, int resultSetConcurrency).

21. How do I write Greek ( or other non-ASCII/8859-1 ) characters to a database?

From the standard JDBC perspective, there is no difference between ASCII/8859-1 characters and those above 255 ( hex FF ). The reason for that is that

all Java characters are in Unicode ( unless you perform/request special encoding ). Implicit in that statement is the presumption that the datastore can

handle characters outside the hex FF range or interprets different character sets appropriately. That means either:

• The OS, application and database use the same code page and character set. For example, a Greek version of NT with the DBMS set to the default OS

encoding.

• The DBMS has I18N support for Greek ( or other language ), regardless of OS encoding. This has been the most common for production quality databases,

although support varies. Particular DBMSes may allow setting the encoding/code page/CCSID at the database, table or even column level. There is no

particular standard for provided support or methods of setting the encoding. You have to check the DBMS documentation and set up the table properly.

• The DBMS has I18N support in the form of Unicode capability. This would handle any Unicode characters and therefore any language defined in the Unicode

standard. Again, set up is proprietary.

22. How can I insert images into a Mysql database?

This code snippet shows the basics:

File file = new File(fPICTURE);

FileInputStream fis = new FileInputStream(file);

PreparedStatement ps =

ConrsIn.prepareStatement("insert into dbPICTURE values (?,?)");

// ***use as many ??? as you need to insert in the exact order***

ps.setString(1,file.getName());

ps.setBinaryStream(2,fis,(int)file.length());

ps.close();

fis.close();

23. Is is possible to open a connection to a database with exclusive mode with JDBC?

I think you mean "lock a table in exclusive mode". You cannot open a connection with exclusive mode. Depending on your database engine, you can lock

tables or rows in exclusive mode.

In Oracle you would create a statement st and run

st.execute("lock table mytable in exclusive mode");

Then when you are finished with the table, execute the commit to unlock the table. Mysql, informix and SQLServer all have a slightly different syntax for

this function, so you'll have to change it depending on your database. But they can all be done with execute().

24. Update fails without blank padding. Alhough a particular row is present in the database for a given key, executeUpdate() shows 0 rows updated

and, in fact, the table is not updated. If I pad the Key with spaces for the column length (eg if the key column is 20 characters long, and key is msgID,

length 6, I pad it with 14 spaces), the update then works!!! Is there any solution to this problem without padding?

In the SQL standard, CHAR is a fixed length data type. In many DBMSes ( but not all), that means that for a WHERE clause to match, every character must

match, including size and trailing blanks. As Alessandro indicates, defining CHAR columns to be VARCHAR is the most general answer.

25. What are the standard isolation levels defined by JDBC?

The values are defined in the class java.sql.Connection and are:

• TRANSACTION_NONE

• TRANSACTION_READ_COMMITTED

• TRANSACTION_READ_UNCOMMITTED

• TRANSACTION_REPEATABLE_READ

• TRANSACTION_SERIALIZABLE