Some fairly good JDBC Questions 1-12

I am quite excited with the response from my servlet questions series [Questions 1-25, Questions 26-50, Questions 51-75 ].

So I thought of writing another one on JDBC and here we go ..

Q1. What are four types of JDBC driver?

Type 1 Drivers : Bridge drivers such as the jdbc-odbc bridge. They rely on an intermediary such as ODBC to transfer the SQL calls to the database and also often rely on native code. It is not a serious solution for an application

Type 2 Drivers : Use the existing database API to communicate with the database on the client. Faster than Type 1, but need native code and require additional permissions to work in an applet. Client machine requires software to run.

Type 3 Drivers : JDBC-Net pure Java driver. It translates JDBC calls to a DBMS-independent network protocol, which is then translated to a DBMS protocol by a server. Flexible. Pure Java and no native code.

Type 4 Drivers : Native-protocol pure Java driver. It converts JDBC calls directly into the network protocol used by DBMSs. This allows a direct call from the client machine to the DBMS server. It doesn't need any special native code on the client machine.
Recommended by Sun's tutorial, driver type 1 and 2 are interim solutions where direct pure Java drivers are not yet available. Driver type 3 and 4 are the preferred way to access databases using the JDBC API, because they offer all the advantages of Java technology, including automatic installation. For more info, visit Sun JDBC page

Q2. Which type of JDBC driver is the fastest one?

JDBC Net pure Java driver(Type IV) is the fastest driver because it converts the jdbc calls into vendor specific protocol calls and it directly interacts with the database.

Q3. Are all the required JDBC drivers to establish connectivity to my database part of the JDK?

No. There aren't any JDBC technology-enabled drivers bundled with the Java Platform releases other than the JDBC-ODBC Bridge. So, developers need to get a driver and install it before they can connect to a database.

Q4. Is the JDBC-ODBC Bridge multi-threaded?

No.

Q5. 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).

Q6. What causes the "No suitable driver" error?

"No suitable driver" is an error that usually occurs during a call to the DriverManager.getConnection method. The cause can be failing to load the appropriate JDBC drivers before calling the getConnection method, or it can be specifying an invalid JDBC URL--one that isn't recognized by your JDBC driver. Your best bet is to check the documentation for your JDBC driver or contact your JDBC driver vendor if you suspect that the URL you are specifying is not being recognized by your JDBC driver.
In addition, when you are using the JDBC-ODBC Bridge, this error can occur if one or more the the shared libraries needed by the Bridge cannot be loaded. If you think this is the cause, check your configuration to be sure that the shared libraries are accessible to the Bridge.

Q7. Why isn't the java.sql.DriverManager class being found?
Some one may ask you questions to confuse you also :) simple answer is the classpath does not contain the necessary jdk jars.

Q8. What are the common steps to execute a Query in JDBC?

Create an instance of a JDBC driver or load JDBC drivers through jdbc.drivers
Register a driver
Specify a database
Open a database connection
Submit a query
Receive results
Process results

Q9. What are the steps involved in establishing a JDBC connection?
This action involves two steps: loading the JDBC driver and making the connection.

Q10. How can you load the drivers?
Loading the driver or drivers you want to use is very simple and involves just one line of code. If, for example, you want to use the JDBC-ODBC Bridge driver, the following code will load it:

Class.forName(”sun.jdbc.odbc.JdbcOdbcDriver”);

Your driver documentation will give you the class name to use. For instance, if the class name is jdbc.DriverXYZ, you would load the driver with the following line of code:

Class.forName(”jdbc.DriverXYZ”);

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

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.

Q12. How can you make the connection?

To establish a connection you need to have the appropriate driver connect to the DBMS.
The following line of code illustrates the general idea:

String url = “jdbc:odbc:Fred”;
Connection con = DriverManager.getConnection(url, “Fernanda”, “J8?);