JDBC Interview Questions - FAQs

JDBC - Frequently Asked Questions or Interview Questions(FAQs)

21. What are the different types of JDBC drivers?

JDBC technology drivers fit into one of four categories:

1. 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, see JDBC-ODBC Bridge Driver.

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

22. Are there recommended programming practices for using JDBC connections?

The general rule is to get the connection from the connection pool as late as possible and give it back to the connection pool as soon as possible. Ensure that the connection is a method variable, and get and release the connection within the same method as where you use it (each thread should have its own connection). The cost of getting the connection is small, the prepared statement cache will reduce the preparation time, the set statements are small, the execute needs to be done no matter what the usage, and the close is small. It is not recommended to

create the connection at ejbCreate/activate and close it on ejbRemove/pasivate.

23. Why should I not use DriverManager.getConnection?

DriverManager.getConnection can cause a deadlock. In the server, all DriverManager calls are class-synchronized including many frequent calls that all drivers make, and JDBC drivers do a lot of synchronization internally. One long-waiting call can stop all JDBC work in the whole JVM and cause deadlocks. Also, you should not reregister the driver repeatedly. Regardless of the DBMS state, the one driver that is initially loaded at startup will always work.

24. Can I use a prepared statement across multiple transactions?

Yes. Every transaction uses a dedicated JDBC connection, and all database interaction needs to use this connection object in order to participate in the transaction. So a prepared statement is tied to a particular connection and can't be shared with other connections. But a prepared statement can span transactions.

25. Why do I get a java.lang.AbstractMethodError when calling a method on a driver?

This usually indicates that the driver has not implemented the method. For instance, you might be calling a JDBC 3.0 method on a driver that has only implemented the JDBC 2.0 methods.

26. Why do I get "ResourceException: No Resource Available"?

One common reason is that you have too many consumers (connection users) for the number of configured JDBC connections in the connection pool or execute threads on the server.

Another reason may be that the refresh testing process has reserved one or more connections for testing so these connections are briefly unavailable.

27. How do I ensure that a new database connection is created each time an EJB's container-managed transaction is started (so that I get a new authentication/authorization each time)?

The EJB should be tx-requires, which means it will start a transaction when called if one is not underway already, or will join the transaction in progress if there is one. Your code will use the standard JTS/JTA API to obtain and start a UserTransaction. Then you should obtain your JDBC connection from a tx data source, and it will be included in the transaction. To get a new connection each time, you could use the dynamic pool API to make a one-connection pool. We suggest configuring the server to have a one-connection pool and a tx data source for it at startup. Then when you want to do a transaction in an external client, you would destroy the initial pool and recreate it with the DBMS user you want. This will allow you to use the tx data source to get a connection, which if obtained in the context of a running UserTransaction, will get automatically included in the tx.

28. If it is a WebLogic DataSource, then you get a stub for the Connection instance, not a connection pool in the local process.

If a distributed transaction involves JMS and JDBC, how do I ensure that the JDBC update is available when the JMS message is processed?

The problem is that an application can receive the JMS message from the destination before the associated JDBC data is in the database.

Distributed transactions guarantee all involved changes will either succeed or fail as a unit, but cannot guarantee that they will happen exactly simultaneously (the transaction manager instructs all resource managers to commit but cannot control the timing of the completion of that operation).

For the WebLogic transaction manager, if the JDBC connection pool and the JMS server are both on the same server, and the transaction starts on the same server, the changes are committed in the order in which they were asked for by the transaction. This is not supported behavior, it just happens to be the current behavior. So if you can co-locate JMS and the JDBC connection pool, then you may have a chance.

You could send the JMS message with a delayed birth-time, and hope that this is good enough.

If the receiver fails to find the associated JDBC record, it could rollback/recover the message. You could use the WebLogic JMS redelivery delay feature to prevent the message from being redelivered instantly.

29. If an application calls DataSource.getConnection multiple times in the same thread and transaction, will WebLogic Server handle giving me the same connection and transaction?

A common scenario might be to have multiple methods that are called within a transaction (begin/commit) that do something like the following:

Context ctx = new InitialContext();
DataSource ds = (javax.sql.DataSource) ctx.lookup("connpoll");
// work using Connection

In this case, all of the work will be done within the transaction and the same underlying JDBC connection will be used as long as the DataSource ds is a tx data source.

30. Why do I get a SystemException failure when trying to enlist my XAResource in a client?

WebLogic Server does not allow you to register or enlist an XA resource on a client. The reason for this restriction is that a client is deemed to be less reliable than a server in terms of availability. This is also why a client is not allowed to act as a transaction coordinator and register Synchronization objects.

Your client could invoke a remote object on a server that accesses the resource within a transaction. If it's a JDBC resource, then you can configure a JDBCConnectionPool and JDBCTxDataSource using an Oracle XA driver (Oracle thin or WebLogic Type 4 driver for Oracle) and obtain a connection from the data source. Or the client could look up the data source using JNDI and retrieve and manipulate a connection in a transaction. Transaction enlistment is performed automatically.

Source : www.javabeat.net