Q) What is JDBC?
A) JDBC may stand for Java Database Connectivity. It is also a trade mark. JDBC is a layer of abstraction that allows users to choose between databases. It allows you to change to a different database engine and to write to a single API. JDBC allows you to write database applications in Java without having to concern yourself with the underlying details of a particular database.
This allows Java programs to interact with any SQL-compliant database. Since nearly all relational database management systems (DBMSs) support SQL, and because Java itself runs on most platforms, JDBC makes it possible to write a single database application that can run on different platforms and interact with different DBMSs.
JDBC is similar to ODBC, but is designed specifically for Java programs, whereas ODBC is language-independent.

Q) What is new in JDBC 2.0?
A) With the JDBC 2.0 API, you will be able to do the following:
*Scroll forward and backward in a result set or move to a specific row (TYPE_SCROLL_SENSITIVE,previous(), last(), absolute(), relative(), etc.)
*Make updates to database tables using methods in the Java programming language instead of using SQL commands.(updateRow(), insertRow(), deleteRow(), etc.)
*Send multiple SQL statements to the database as a unit, or batch (addBatch(), executeBatch())
*Use the new SQL3 datatypes as column values like Blob, Clob, Array, Struct, Ref.

Q) How do I start debugging problems related to the JDBC API?
A) A good way to find out what JDBC calls are doing is to enable JDBC tracing. The JDBC trace contains a detailed listing of the activity occurring in the system that is related to JDBC operations.If you use the DriverManager facility to establish your database connection, you use the DriverManager.setLogWriter method to enable tracing of JDBC operations. If you use a DataSource object to get a connection, you use the DataSource.setLogWriter method to enable tracing. (For pooled connections, you use the ConnectionPoolDataSource.setLogWriter method, and for connections that can participate in distributed transactions, you use the XADataSource.setLogWriter method.)

Q) Does the JDBC-ODBC Bridge support the new features in the JDBC 3.0 API?
A) The JDBC-ODBC Bridge provides a limited subset of the JDBC 3.0 API.

Q) What's the JDBC 3.0 API?
A) The JDBC 3.0 API is the latest update of the JDBC API. It contains many features, including scrollable result sets and the SQL:1999 data types. JDBC (Java Database Connectivity) is the standard for communication between a Java application and a relational database. The JDBC API is released in two versions; JDBC version 1.22 (released with JDK 1.1.X in package java.sql) and version 2.0 (released with Java platform 2 in packages java.sql and javax.sql). It is a simple and powerful largely database-independent way of extracting and inserting data to or from any database.

Q) Can the JDBC-ODBC Bridge be used with applets?
A) Use of the JDBC-ODBC bridge from an untrusted applet running in a browser, such as Netscape Navigator, isn't allowed. The JDBC-ODBC bridge doesn't allow untrusted code to call it for security reasons. This is good because it means that an untrusted applet that is downloaded by the browser can't circumvent Java security by calling ODBC. Remember that ODBC is native code, so once ODBC is called the Java programming language can't guarantee that a security violation won't occur. On the other hand, Pure Java JDBC drivers work well with applets. They are fully downloadable and do not require any client-side configuration.
Finally, we would like to note that it is possible to use the JDBC-ODBC bridge with applets that will be run in appletviewer since appletviewer assumes that applets are trusted. In general, it is dangerous to turn applet security off, but it may be appropriate in certain controlled situations, such as for applets that will only be used in a secure intranet environment. Remember to exercise caution if you choose this option, and use an all-Java JDBC driver whenever possible to avoid security problems.

Q) How to update a resultset programmatically? (new feature in JDBC 2.0)
A) a. create a scrollable and updatable ResultSet object.
Statement stmt = con.createStatement
(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet uprs = stmt.executeQuery("SELECT COLUMN_1, COLUMN_2 FROM TABLE_NAME");
b. move the cursor to the specific position and use related method to update data and then, call updateRow() method.
uprs.last();
uprs.updateFloat("COLUMN_2", 25.55);//update last row's data
uprs.updateRow();//don't miss this method, otherwise,
// the data will be lost.

Q) Are there any ODBC drivers that do not work with the JDBC-ODBC Bridge?
A) Most ODBC 2.0 drivers should work with the Bridge. Since there is some variation in functionality between ODBC drivers, the functionality of the bridge may be affected. The bridge works with popular PC databases, such as Microsoft Access and FoxPro.

Q) How can I use the JDBC API to access a desktop database like Microsoft Access over the network?
A) Most desktop databases currently require a JDBC solution that uses ODBC underneath. This is because the vendors of these database products haven't implemented all-Java JDBC drivers. The best approach is to use a commercial JDBC driver that supports ODBC and the database you want to use. See the JDBC drivers page for a list of available JDBC drivers.
The JDBC-ODBC bridge from Sun's Java Software does not provide network access to desktop databases by itself. The JDBC-ODBC bridge loads ODBC as a local DLL, and typical ODBC drivers for desktop databases like Access aren't networked. The JDBC-ODBC bridge can be used together with the RMI-JDBC bridge, however, to access a desktop database like Access over the net. This RMI-JDBC-ODBC solution is free.

Q) How to move the cursor in scrollable resultsets?(new feature in JDBC 2.0)
A) a. create a scrollable ResultSet object.
Statement stmt = con.createStatement
ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery("SELECT COLUMN_1,COLUMN_2 FROM TABLE_NAME");
b. use a built in methods like afterLast(), previous(), beforeFirst(), etc. to scroll the resultset.
srs.afterLast();
while (srs.previous()) {
String name = srs.getString("COLUMN_1");
float salary = srs.getFloat("COLUMN_2");
//...
c. to find a specific row, use absolute(), relative() methods.
srs.absolute(4); // cursor is on the fourth row
int rowNum = srs.getRow(); // rowNum should be 4
srs.relative(-3);
int rowNum = srs.getRow(); // rowNum should be 1
srs.relative(2);
int rowNum = srs.getRow(); // rowNum should be 3
d. use isFirst(), isLast(), isBeforeFirst(), isAfterLast() methods to check boundary status.

Q) What causes the "No suitable driver" error?
A) "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.

Q) Why isn't the java.sql.DriverManager class being found?
A) This problem can be caused by running a JDBC applet in a browser that supports the JDK 1.0.2, such as Netscape Navigator 3.0. The JDK 1.0.2 does not contain the JDBC API, so the DriverManager class typically isn't found by the Java virtual machine running in the browser. Here's a solution that doesn't require any additional configuration of your web clients. Remember that classes in the java.* packages cannot be downloaded by most browsers for security reasons. Because of this, many vendors of all-Java JDBC drivers supply versions of the java.sql.* classes that have been renamed to jdbc.sql.*, along with a version of their driver that uses these modified classes. If you import jdbc.sql.* in your applet code instead of java.sql.*, and add the jdbc.sql.* classes provided by your JDBC driver vendor to your applet's codebase, then all of the JDBC classes needed by the applet can be downloaded by the browser at run time, including the DriverManager class.
This solution will allow your applet to work in any client browser that supports the JDK 1.0.2. Your applet will also work in browsers that support the JDK 1.1, although you may want to switch to the JDK 1.1 classes for performance reasons. Also, keep in mind that the solution outlined here is just an example and that other solutions are possible.

Q) What is JDBC Driver interface?
A) The JDBC Driver interface provides vendor-specific implementations of the abstract classes provided by the JDBC API. Each vendor driver must provide implementations of the java.sql.Connection,Statement,PreparedStatement, CallableStatement, ResultSet and Driver.

Q) What are the common tasks of JDBC?
A) 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

Q) How do I retrieve a whole row of data at once, instead of calling an individual ResultSet.getXXX method for each column?
A) 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.

Q) Why does the ODBC driver manager return 'Data source name not found and no default driver specified Vendor: 0'
A) This type of error occurs during an attempt to connect to a database with the bridge. First, note that the error is coming from the ODBC driver manager. This indicates that the bridge-which is a normal ODBC client-has successfully called ODBC, so the problem isn't due to native libraries not being present. In this case, it appears that the error is due to the fact that an ODBC DSN (data source name) needs to be configured on the client machine. Developers often forget to do this, thinking that the bridge will magically find the DSN they configured on their remote server machine

Q) What are four types of JDBC driver?
A)
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

Q) Which type of JDBC driver is the fastest one?
A) 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.

Q) Are all the required JDBC drivers to establish connectivity to my database part of the JDK?
A) No. There aren't any JDBC technology-enabled drivers bundled with the JDK 1.1.x or Java 2 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. We are considering bundling JDBC technology- enabled drivers in the future.

Q) Is the JDBC-ODBC Bridge multi-threaded?
A) No. The JDBC-ODBC Bridge does not support concurrent access from different threads. 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. In addition, deadlocks can occur between locks held in the database and the semaphore used by the Bridge. We are thinking about removing the synchronized methods in the future. They were added originally to make things simple for folks writing Java programs that use a single-threaded ODBC driver.

Q) Does the JDBC-ODBC Bridge support multiple concurrent open statements per connection?
A) No. You can open only one Statement object per connection when you are using the JDBC-ODBC Bridge.

Q) What is the query used to display all tables names in SQL Server (Query analyzer)?
A) select * from information_schema.tables

Q) Why can't I invoke the ResultSet methods afterLast and beforeFirst when the method next works?
A) 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.

Q) How can I retrieve a String or other object type without creating a new object each time?
A) 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.

Q) Where can I find info, frameworks and example source for writing a JDBC driver?
A) 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.

Q) How can I create a custom RowSetMetaData object from scratch?
A) 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.

Q) How does a custom RowSetReader get called from a CachedRowSet?
A) 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.

Q) Once I have the Java 2 SDK, Standard Edition, from Sun, what else do I need to connect to a database?
A) 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.

Q) What is the fastest type of JDBC driver?
A) 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).

Q) There is a method getColumnCount in the JDBC API. Is there a similar method to find the number of rows in a result set?
A) 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.

Q) 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?
A) The JDBC-ODBC Bridge is bundled with the Java 2 SDK, Standard Edition, so there is no need to download it separately.

Q) If I use the JDBC API, do I have to use ODBC underneath?
A) 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.

Q) 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?
A) "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.

Q) Can I set up a conection pool with multiple user IDs? The single ID we are forced to use causes probelems when debugging the DBMS.?
A) 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.

Q) What driver should I use for scalable Oracle JDBC applications?
A) 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.

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

Q) is it possible to open a connection to a database with exclusive mode with JDBC?
A) 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().

Q) What are the standard isolation levels defined by JDBC?
A) 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

Q) How can I determine the isolation levels supported by my DBMS?
A) Use DatabaseMetaData.supportsTransactionIsolationLevel(int level).

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

Q) 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?
A) 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.

Q) 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?
A) 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.

Q) How do I receive a ResultSet from a stored procedure?
A) 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.

Q) How can I write to the log used by DriverManager and JDBC drivers?
A) The simplest method is to use DriverManager.println(String message), which will write to the current log.

Q) How can I get or redirect the log used by DriverManager and JDBC drivers?
A) 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.

Q) What does it mean to "materialize" data?
A) 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().

Q) How do I check in my code whether a maximum limit of database connections have been reached?
A) 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.

Q) Why do I get UnsatisfiedLinkError when I try to use my JDBC driver?
A) 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.

Q) 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?
A) 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, instantiate 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.

Q) How to insert and delete a row programmatically? (new feature in JDBC 2.0)
A) Make sure the resultset is updatable.
1. move the cursor to the specific position.
uprs.moveToCurrentRow();
2. set value for each column.
uprs.moveToInsertRow();//to set up for insert
uprs.updateString("col1" "strvalue");
uprs.updateInt("col2", 5);
...
3. call inserRow() method to finish
the row insert process.
uprs.insertRow();
To delete a row: move to the specific
position and call deleteRow() method:
uprs.absolute(5);
uprs.deleteRow();//delete row 5
To see the changes call refreshRow();
uprs.refreshRow();

Q) Can I use JDBC to execute non-standard features that my DBMS provides?
A) 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.

Q) What are the two major components of JDBC?
A) One implementation interface for database manufacturers, the other implementation interface for application and applet writers.

Q) How do I set properties for a JDBC driver and where are the properties stored?
A) 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). 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

Q) What is the JDBC syntax for using a literal or variable in a standard Statement?
A) 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.

Q) DB2 Universal claims to support JDBC 2.0, But I can only get JDBC 1.0 functionality. What can I do?
A) 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.

Q) What is the significance of DataBaseMetaData.tableIndexStatistics? How to obtain and use it?
A) To answer the second question first, the tableIndexStatistic constant in the TYPE column will identify one of the rows in the ResultSet returned when DatabaseMetaData.getIndexInfo() is invoked. If you analyze the wordy API, a tableIndexStatistic row will contain the number of rows in the table in the CARDINALITY column and the number of pages used for the table in the PAGES column.

Q) What types of DataSource objects are specified in the Optional Package?
A) * Basic - Provides a standard Connection object.
* Pooled - Provides a Connection pool and returns a Connection that is controlled by the pool.
* Distributed - Provides a Connection that can participate in distributed transactions ( more than one DBMS is involved). It is anticipated, but not enforced, that a distributed DataSource will also provide pooling.
However, there are no standard methods available in the DataSource class to determine if one has obtained a pooled and/or distributed Connection.

Q) What is a JDBC 2.0 DataSource?
A) The DataSource class was introduced in the JDBC 2.0 Optional Package as an easier, more generic means of obtaining a Connection. The actual driver providing services is defined to the DataSource outside the application ( Of course, a production quality app can and should provide this information outside the app anyway, usually with properties files or ResourceBundles ). The documentation expresses the view that DataSource will replace the common DriverManager method.

Q) Does the database server have to be running Java or have Java support in order for my remote JDBC client app to access the database?
A) The answer should always be no. The two critical requirements are LAN/internet connectivity and an appropriate JDBC driver. Connectivity is usually via TCP/IP, but other communication protocols are possible. Unspoken, but assumed here is that the DBMS has been started to listen on a communications port. It is the JDBC driver's job to convert the SQL statements and JDBC calls to the DBMS' native protocol. From the server's point of view, it's just another data request coming into the port, the programming language used to send the data is irrelevant at that point.

Q) Which Java and java.sql data types map to my specific database types?
A) JDBC is, of necessity, reliant on the driver and underlying DBMS. These do not always adhere to standards as closely as we would like, including differing names for standard Java types. To deal with this, first, there are a number of tables available in the JDK JDBC documentation dealing with types.

Q) What is optimistic concurrency?
A) An optimistic approach dispenses with locks ( except during the actual update ) and usually involves comparison of timestamps, or generations of data to ensure that data hasn't changed between access and update times. It's generally explained that the term optimistic is used because the expectation is that a clash between multiple updates to the same data will seldom occur.

Q) What is pessimistic concurrency?
A) With a pessimistic approach, locks are used to ensure that no users, other than the one who holds the lock, can update data. It's generally explained that the term pessimistic is used because the expectation is that many users will try to update the same data, so one is pessimistic that an update will be able to complete properly. Locks may be acquired, depending on the DBMS vendor, automatically via the selected Isolation Level. Some vendors also implement 'Select... for Update', which explicitly acquires a lock.

Q) When an SQL select statement doesn't return any rows, is an SQLException thrown?
A) No. If you want to throw an exception, you could wrap your SQL related code in a custom class and throw something like ObjectNotFoundException when the returned ResultSet is empty.

Q) Can I get information about a ResultSet's associated Statement and Connection in a method without having or adding specific arguments for the Statement and Connection?
A) Yes. Use ResultSet.getStatement(). From the resulting Statement you can use Statement.getConnection().

Q) I need to have result set on a page where the user can sort on the column headers. Any ideas?
A) One possibility: Have an optional field in your form or GET url called (appropriately) ORDER with a default value of either "no order" or whatever you want your default ordering to be (i.e. timestamp, username, whatever). When you get your request, see what the value of the ORDER element is. If it's null or blank, use the default. Use that value to build your SQL query, and display the results to the page. If you're caching data in your servlet, you can use the Collection framework to sort your data (see java.util.Collections) if you can get it into a List format. Then, you can create a Collator which can impose a total ordering on your results.

Q) What are the components of the JDBC URL for Oracle's "thin" driver and how do I use them?
A) Briefly: jdbc:oracle:thin:@hostname:port:oracle-sid
1. in green the Oracle sub-protocol (can be oracle:oci7:@, oracle:oci8:@, racle:thin:@, etc...) is related on the driver you are unsign and the protocol to communicate with server.
2. in red the network machine name, or its ip address, to locate the server where oracle is running.
3. in blue the port (it is complementary to the address to select the specific oracle service)
4. in magenta the sid, select on which database you want to connect.
example:
jdbc:oracle:thin:@MyOracleHost:1521:MyDB
IHere's an example:
jdbc:oracle:thin:scott/tiger@MyOracleHost:1521:MyDB
where user=scott and pass=tiger.

Q) Why doesn't JDBC accept URLs instead of a URL string?
A) In order for something to be a java.net.URL, a protocol handler needs to be installed. Since there is no one universal protocol for databases behind JDBC, the URLs are treated as strings. In Java 1.4, these URL strings have a class called java.net.URI. However, you still can't use a URI to load a JDBC driver, without converting it to a string.

Q) What JDBC objects generate SQLWarnings?
A) Connections, Statements and ResultSets all have a getWarnings method that allows retrieval. Keep in mind that prior ResultSet warnings are cleared on each new read and prior Statement warnings are cleared with each new execution. getWarnings() itself does not clear existing warnings, but each object has a clearWarnings method.

Q) What's the fastest way to normalize a Time object?
A) Of the two recommended ways when using a Calendar( see How do I create a java.sql.Time object? ), in my tests, this code ( where
c is a Calendar and t is a Time ):
c.set( Calendar.YEAR, 1970 );
c.set( Calendar.MONTH, Calendar.JANUARY );
c.set( Calendar.DATE, 1 );
c.set( Calendar.MILLISECOND, 0 );
t = new java.sql.Time( c.getTime().getTime() );
was always at least twice as fast as:
t = java.sql.Time.valueOf(
c.get(Calendar.HOUR_OF_DAY) + ":" +
c.get(Calendar.MINUTE) + ":" +
c.get(Calendar.SECOND) );
When the argument sent to valueOf() was hardcoded ( i.e. valueOf( "13:50:10" ), the time difference over 1000 iterations was negligible.

Q) How can I tell if my JDBC driver normalizes java.sql.Date and java.sql.Time objects?
A) To actually determine the values, the objects must be converted to a java.util.Date and examined.Notice that even a debugger will not show whether these objects have been normalized, since the getXXX methods in java.sql.Date for time elements and in java.sql.Time for date elements throw an exception.
So, while a java.sql.Date may show 2001-07-26, it's normalized only if the java.util.Date value is:
Thu Jul 26 00:00:00 EDT 2001
and while a java.sql.Time may show 14:01:00, it's normalized only if the java.util.Date value is:
Thu Jan 01 14:01:00 EST 1970

Q) What scalar functions can I expect to be supported by JDBC?
A) JDBC supports numeric, string, time, date, system, and conversion functions on scalar values. For a list of those supported and additional information, see section A.1.4 Support Scalar Functions in the JDBC Data Access API For Driver Writers. Note that drivers are only expected to support those scalar functions that are supported by the underlying DB engine.

Q) Are the code examples from the JDBC API Tutorial and Reference, Second Edition available online?
A) Yes.

Q) What does setFetchSize() really do?
A) The API documentation explains it pretty well, but a number of programmers seem to have a misconception of its functionality. The first thing to note is that it may do nothing at all; it is only a hint, even to a JDBC Compliant driver. setFetchSize() is really a request for a certain sized blocking factor, that is, how much data to send at a time.
Because trips to the server are expensive, sending a larger number of rows can be more efficient. It may be more efficient on the server side as well, depending on the particular SQL statement and the DB engine. That would be true if the data could be read straight off an index and the DB engine paid attention to the fetch size. In that case, the DB engine could return only enough data per request to match the fetch size. Don't count on that behavior. In general, the fetch size will be transparent to your program and only determines how often requests are sent to the server as you traverse the data. Also, both Statement and ResultSet have setFetchSize methods. If used with a Statement, all ResultSets returned by that Statement will have the same fetch size. The method can be used at any time to change the fetch size for a given ResultSet. To determine the current or default size, use the getFetchSize methods.

Q) Is there a practical limit for the number of SQL statements that can be added to an instance of a Statement object ?
A) While the specification makes no mention of any size limitation for Statement.addBatch(), this seems to be dependent, as usual, on the driver. Among other things, it depends on the type of container/collection used. I know of at least one driver that uses a Vector and grows as needed. I've seen questions about another driver that appears to peak somewhere between 500 and 1000 statements. Unfortunately, there doesn't appear to be any metadata information regarding possible limits. Of course, in a production quality driver, one would expect an exception from an addBatch() invocation that went beyond the command list's limits.

Q) How can I determine whether a Statement and its ResultSet will be closed on a commit or rollback?
A) Use the DatabaseMetaData methods supportsOpenStatementsAcrossCommit() and supportsOpenStatementsAcrossRollback().

Q) How do I get runtime information about the JDBC Driver?
A) Use the following DatabaseMetaData methods:
getDriverMajorVersion()
getDriverMinorVersion()
getDriverName()
getDriverVersion()

Q) How do I create an updatable ResultSet?
A) Just as is required with a scrollable ResultSet, the Statement must be capable of returning an updatable ResultSet. This is accomplished by asking the Connection to return the appropriate type of Statement using Connection.createStatement(int resultSetType, int resultSetConcurrency). The resultSetConcurrency parameter must be ResultSet.CONCUR_UPDATABLE. The actual code would look like this:
Statement stmt = con.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE );
Note that the spec allows a driver to return a different type of Statement/ResultSet than that requested, depending on capabilities and circumstances, so the actual type returned should be checked with ResultSet.getConcurrency().

Q) How can I connect to an Oracle database not on the web server from an untrusted applet?
A) You can use the thin ORACLE JDBC driver in an applet (with some extra parameters on the JDBC URL). Then, if you have NET8, you can use the connection manager of NET8 on the web server to proxy the connection request to the database server.

Q) How can I convert a java array to a java.sql.Array?
A) A Java array is a first class object and all of the references basically use PreparedStatement.setObject() or ResultSet.updateObject() methods for putting the array to an ARRAY in the database. Here's a basic example:
String[] as = { "One", "Two", "Three" };
...
PreparedStatement ps = con.prepareStatement("UPDATE MYTABLE SET ArrayNums = ? WHERE MyKey = ?" );
...
ps.setObject( 1, as );

Q) What is the difference between setMaxRows(int) and SetFetchSize(int)? Can either reduce processing time?
A) setFetchSize(int) defines the number of rows that will be read from the database when the ResultSet needs more rows. The method in the java.sql.Statement interface will set the 'default' value for all the ResultSet derived from that Statement; the method in the java.sql.ResultSet interface will override that value for a specific ResultSet. Since database fetches can be expensive in a networked environment, fetch size has an impact on performance.
setMaxRows(int) sets the limit of the maximum nuber of rows in a ResultSet object. If this limit is exceeded, the excess rows are "silently dropped". That's all the API says, so the setMaxRows method may not help performance at all other than to decrease memory usage. A value of 0 (default) means no limit.

Q) How can I connect from an applet to a database on the server?
A) There are two ways of connecting to a database on the server side.
1. The hard way. Untrusted applets cannot touch the hard disk of a computer. Thus, your applet cannot use native or other local files (such as JDBC database drivers) on your hard drive. The first alternative solution is to create a digitally signed applet which may use locally installed JDBC drivers, able to connect directly to the database on the server side.
2. The easy way. Untrusted applets may only open a network connection to the server from which they were downloaded. Thus, you must place a database listener (either the database itself, or a middleware server) on the server node from which the applet was downloaded. The applet would open a socket connection to the middleware server, located on the same computer node as the webserver from which the applet was downloaded. The middleware server is used as a mediator, connecting to and extract data from the database.

Q) How can resultset records be restricted to certain rows?
A) The easy answer is "Use a JDBC 2.0 compliant driver".
With a 2.0 driver, you can use the setFetchSize() method within a Statement or a ResultSet object.
For example,
Statement stmt = con.createStatement();
stmt.setFetchSize(400);
ResultSet rs = stmt.executeQuery("select * from customers");
will change the default fetch size to 400.
You can also control the direction in which the rows are processed. For instance:
stmt.setFetchDirection(ResultSet.FETCH_REVERSE)
will process the rows from bottom up.
The driver manager usually defaults to the most efficient fetch size...so you may try experimenting with different value for optimal performance.

Q) Can I use the JDBC-ODBC bridge driver in an applet?
A) Short answer: No.
Longer answer: You may create a digitally signed applet using a Certicate to circumvent the security sandbox of the browser.

Q) How do I extract the SQL statements required to move all tables and views from an existing database to another database?
A) The operation is performed in 9 steps:
1. Open a connection to the source database. Use the DriverManager class.
2. Find the entire physical layout of the current database. Use the DatabaseMetaData interface.
3. Create DDL SQL statements for re-creating the current database structure. Use the DatabaseMetaData interface.
4. Build a dependency tree, to determine the order in which tables must be setup. Use the DatabaseMetaData interface.
5. Open a connection to the target database. Use the DriverManager class.
6. Execute all DDL SQL statements from (3) in the order given by (4) in the target database to setup the table and view structure. Use the PreparedStatement interface.
7. If (6) threw exceptions, abort the entire process.
8. Loop over all tables in the physical structure to generate DML SQL statements for re-creating the data inside the table. Use the ResultSetMetaData interface.
9. Execute all DML SQL statements from (8) in the target database.

Q) What is the advantage of using a PreparedStatement?
A) For SQL statements that are executed repeatedly, using a PreparedStatement object would almost always be faster than using a Statement object. This is because creating a PreparedStatement object by explicitly giving the SQL statement causes the statement to be precompiled within the database immediately. Thus, when the PreparedStatement is later executed, the DBMS does not have to recompile the SQL statement and prepared an execution plan - it simply runs the statement. Typically, PreparedStatement objects are used for SQL statements that take parameters. However, they can also be used with repeatedly executed SQL statements that do not accept parameters.

Q) When we will Denormalize data?
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. What is the advantage of using PreparedStatement? - If we are using PreparedStatement the execution time will be less. The PreparedStatement object contains not just an SQL statement, but the SQL statement that has been precompiled. This means that when the PreparedStatement is executed,the RDBMS can just run the PreparedStatement's Sql statement without having to compile it first.

Q) How does the Java Database Connectivity (JDBC) work?
A) The JDBC is used whenever a Java application should communicate with a relational database for which a JDBC driver exists. JDBC is part of the Java platform standard; all visible classes used in the Java/database communication are placed in package java.sql. Main JDBC classes:
* DriverManager. Manages a list of database drivers. Matches connection requests from the java application with the proper database driver using communication subprotocol. The first driver that recognizes a certain subprotocol under jdbc (such as odbc or dbAnywhere/dbaw) will be used to establish a database Connection.
* Driver. The database communications link, handling all communication with the database. Normally, once the driver is loaded, the developer need not call it explicitly.
* Connection. Interface with all methods for contacting a database
* Statement. Encapsulates an SQL statement which is passed to the database to be parsed, compiled, planned and executed.
* ResultSet. The answer/result from a statement. A ResultSet is a fancy 2D list which encapsulates all outgoing results from a given SQL query.

Q) What are collection pools? What are the advantages?
A) A connection pool is a cache of database connections that is maintained in memory, so that the connections may be reused

Q) How do I check what table types exist in a database?
A) Use the getTableTypes method of interface java.sql.DatabaseMetaData to probe the database for table types. The exact usage is described in the code below.
public static void main(String[] args) throws Exception{
// Load the database driver - in this case, we use the Jdbc/Odbc bridge driver.
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Open a connection to the database
Connection conn = DriverManager.getConnection("[jdbcURL]", "[login]", "[passwd]");
// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs = dbmd.getTableTypes();
while(rs.next()){
System.out.println("Type: " + rs.getString(1));
}
// Close database resources
rs.close();
conn.close();
}

Q) Could we get sample code for retrieving more than one parameter from a stored procedure?
A) Assume we have a stored procedure with this signature:
MultiSP (IN I1 INTEGER, OUT O1 INTEGER, INOUT IO1 INTEGER)
The code snippet to retrieve the OUT and INOUT parameters follows:
CallableStatement cs = connection.prepareCall( "(CALL MultiSP(?, ?, ?))" );
cs.setInt(1, 1); // set the IN parm I1 to 1
cs.setInt(3, 3); // set the INOUT parm IO1 to 3
cs.registerOutParameter(2, Types.INTEGER); // register the OUT parm O1
cs.registerOutParameter(3, Types.INTEGER); // register the INOUT parm IO1
cs.execute();
int iParm2 = cs.getInt(2);
int iParm3 = cs.getInt(3);
cs.close();
The code really is just additive; be sure that for each IN parameter that setXXX() is called and that for each INOUT and OUT parameter that registerOutParameter() is called.

Q) What is a ResultSet ?
A) 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. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set.

Q) What is Connection?
A) Connection class represents a connection (session) with a specific database. SQL statements are executed and results are returned within the context of a connection. A Connection object's database is able to provide information describing its tables, its supported SQL grammar, its stored procedures, the capabilities of this connection, and so on. This information is obtained with the getMetaData method.

Q) Which is the preferred collection class to use for storing database result sets?
A) When retrieving database results, the best collection implementation to use is the LinkedList. The benefits include: * Retains the original retrieval order
* Has quick insertion at the head/tail
* Doesn't have an internal size limitation like a Vector where when the size is exceeded a new internal structure is created (or you have to find out size beforehand to size properly)
* Permits user-controlled synchronization unlike the pre-Collections Vector which is always synchronized
Basically:
ResultSet result = stmt.executeQuery("...");
List list = new LinkedList();
while(result.next()) {
list.add(result.getString("col"));
}
If there are multiple columns in the result set, you'll have to combine them into their own data structure for each row. Arrays work well for that as you know the size, though a custom class might be best so you can convert the contents to the proper type when extracting from databse, instead of later.

Q) How can I make batch updates using JDBC?
A) One of the more advanced features of JDBC 2.0 is the ability to submit multiple update statements to the database for processing as a single unit. This batch updating can be significantly more efficient compared to JDBC 1.0, where each update statement has to be executed separately.
Consider the following code segment demonstrating a batch update:
try {
dbCon.setAutoCommit(false);
Statement stmt= dbCon.createStatement();
stmt.addBatch("INSERT INTO bugs "+ "VALUES (1007, 'Server stack overflow', 1,2,{d '1999-01-01'})");
stmt.addBatch("INSERT INTO bugs "+ "VALUES (1008,'Cannot load DLL', 3,1,{d '1999-01-01'})");
stmt.addBatch("INSERT INTO bugs "+ "VALUES (1009,'Applet locks up',2,2,{d '1999-01-01'})");
int[] updCnt = stmt.executeBatch();
dbCon.commit();
} catch (BatchUpdateException be) {
//handle batch update exception
int[] counts = be.getUpdateCounts();
for (int i=0; I counts.length; i++) {
System.out.println("Statement["+i+"] :"+counts[i]);
}
dbCon.rollback();
}
catch (SQLException e) {
//handle SQL exception
dbCon.rollback();
}
Before carrying out a batch update, it is important to disable the auto-commit mode by calling setAutoCommit(false). This way, you will be able to rollback the batch transaction in case one of the updates fail for any reason. When the Statement object is created, it is automatically associated a "command list", which is initially empty. We then add our SQL update statements to this command list, by making successive calls to the addBatch() method. On calling executeBatch(), the entire command list is sent over to the database, and are then executed in the order they were added to the list. If all the commands in the list are executed successfully, their corresponding update counts are returned as an array of integers. Please note that you always have to clear the existing batch by calling clearBatch() before creating a new one.
If any of the updates fail to execute within the database, a BatchUpdateException is thrown in response to it. In case there is a problem in returning the update counts of each SQL statement, a SQLException will be thrown to indicate the error.

Q) What is JDBC Driver ?
A) The JDBC Driver provides vendor-specific implementations of the abstract classes provided by the JDBC API. This driver is used to connect to the database.

Q) What are the steps required to execute a query in JDBC?
A) First we need to create an instance of a JDBC driver or load JDBC drivers, then we need to register this driver with DriverManager class. Then we can open a connection. By using this connection , we can create a statement object and this object will help us to execute the query.

Q) How do I extract SQL table column type information?
A) Use the getColumns method of the java.sql.DatabaseMetaData interface to investigate the column type information of a particular table. Note that most arguments to the getColumns method (pinpointing the column in question) may be null, to broaden the search criteria. A code sample can be seen below:
public static void main(String[] args) throws Exception
{ // Load the database driver - in this case, we use the Jdbc/Odbc bridge driver.
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Open a connection to the database
Connection conn = DriverManager.getConnection("[jdbcURL]", "[login]", "[passwd]");
// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
// Get all column types for the table "sysforeignkeys", in schema "dbo" and catalog "test".
ResultSet rs = dbmd.getColumns("test", "dbo", "sysforeignkeys", "%");
while(rs.next()){
// Get dbObject metadata
String dbObjectCatalog = rs.getString(1);
String dbObjectSchema = rs.getString(2);
String dbObjectName = rs.getString(3);
String dbColumnName = rs.getString(4);
String dbColumnTypeName = rs.getString(6);
int dbColumnSize = rs.getInt(7);
int dbDecimalDigits = rs.getInt(9);
String dbColumnDefault = rs.getString(13);
int dbOrdinalPosition = rs.getInt(17);
String dbColumnIsNullable = rs.getString(18);
System.out.println("Col(" + dbOrdinalPosition + "): " + dbColumnName + " (" + dbColumnTypeName +")");
System.out.println(" Nullable: " + dbColumnIsNullable + ", Size: " + dbColumnSize);
System.out.println(" Position in table: " + dbOrdinalPosition + ", Decimal digits: " + dbDecimalDigits);
}
// Free database resources
rs.close();
conn.close();
}

Q) How do I check what table-like database objects (table, view, temporary table, alias) are present in a particular database?
A) Use java.sql.DatabaseMetaData to probe the database for metadata. Use the getTables method to retrieve information about all database objects (i.e. tables, views, system tables, temporary global or local tables or aliases). The exact usage is described in the code below.
NOTE! Certain JDBC drivers throw IllegalCursorStateExceptions when you try to access fields in the ResultSet in the wrong order (i.e. not consecutively). Thus, you should not change the order in which you retrieve the metadata from the ResultSet.
public static void main(String[] args) throws Exception{
// Load the database driver - in this case, we use the Jdbc/Odbc bridge driver.
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Open a connection to the database
Connection conn = DriverManager.getConnection("[jdbcURL]","[login]", "[passwd]");
// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
// Get all dbObjects. Replace the last argument in the getTables method with objectCategories below to obtain only database tables. (Sending in null retrievs all dbObjects).
String[] objectCategories = {"TABLE"};
ResultSet rs = dbmd.getTables(null, null, "%", null);
while(rs.next()){
// Get dbObject metadata
String dbObjectCatalog = rs.getString(1);
String dbObjectSchema = rs.getString(2);
String dbObjectName = rs.getString(3);
String dbObjectType = rs.getString(4);
System.out.println("" + dbObjectType + ": " + dbObjectName);
System.out.println(" Catalog: " + dbObjectCatalog);
System.out.println(" Schema: " + dbObjectSchema);
}
// Close database resources
rs.close();
conn.close();
}

Q) How do you get Column names only for a table (SQL Server)?
A) Write the Query.select name from syscolumns where id=(select id from sysobjects where name='user_hdr') order by colid --user_hdr is the table name.

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

Q) How do I find all database stored procedures in a database?
A) Use the getProcedures method of interface java.sql.DatabaseMetaData to probe the database for stored procedures. The exact usage is described in the code below.
public static void main(String[] args) throws Exception{
// Load the database driver - in this case, we use the Jdbc/Odbc bridge driver.
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Open a connection to the database
Connection conn = DriverManager.getConnection("[jdbcURL]", "[login]", "[passwd]");
// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
// Get all procedures.
System.out.println("Procedures are called '" + dbmd.getProcedureTerm() +"' in the DBMS.");
ResultSet rs = dbmd.getProcedures(null, null, "%");
while(rs.next()){
// Get procedure metadata
String dbProcedureCatalog = rs.getString(1);
String dbProcedureSchema = rs.getString(2);
String dbProcedureName = rs.getString(3);
String dbProcedureRemarks = rs.getString(7);
short dbProcedureType = rs.getShort(8);
// Make result readable for humans
String procReturn = (dbProcedureType == DatabaseMetaData.procedureNoResult ? "No Result" : "Result");
System.out.println("Procedure: " + dbProcedureName + ", returns: " + procReturn);
System.out.println(" [Catalog | Schema]: [" + dbProcedureCatalog + " | " + dbProcedureSchema + "]");
System.out.println(" Comments: " + dbProcedureRemarks);
}
rs.close();
conn.close();
}

Q) How can I investigate the physical structure of a database?
A) The JDBC view of a database internal structure can be seen in the image below.
* Several database objects (tables, views, procedures etc.) are contained within a Schema.
* Several schema (user namespaces) are contained within a catalog.
* Several catalogs (database partitions; databases) are contained within a DB server (such as Oracle, MS SQL
The DatabaseMetaData interface has methods for discovering all the Catalogs, Schemas, Tables and Stored Procedures in the database server. The methods are pretty intuitive, returning a ResultSet with a single String column; use them as indicated in the code below:
public static void main(String[] args) throws Exception{
// Load the database driver - in this case, we use the Jdbc/Odbc bridge driver.
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Open a connection to the database
Connection conn = DriverManager.getConnection("[jdbcURL]", "[login]", "[passwd]");
// Get DatabaseMetaData
DatabaseMetaData dbmd = conn.getMetaData();
// Get all Catalogs
System.out.println("\nCatalogs are called '" + dbmd.getCatalogTerm() + "' in this RDBMS.");
processResultSet(dbmd.getCatalogTerm(), dbmd.getCatalogs());
// Get all Schemas
System.out.println("\nSchemas are called '" + dbmd.getSchemaTerm()+ "' in this RDBMS.");
processResultSet(dbmd.getSchemaTerm(), dbmd.getSchemas());
// Get all Table-like types
System.out.println("\nAll table types supported in this RDBMS:");
processResultSet("Table type", dbmd.getTableTypes());
conn.close();
}
public static void processResultSet(String preamble, ResultSet rs) throws SQLException{
while(rs.next()){
System.out.println(preamble + ": " + rs.getString(1));
}
rs.close();
}

Q) What is 2 phase commit?
A) A 2-phase commit is an algorithm used to ensure the integrity of a committing transaction. In Phase 1, the transaction coordinator contacts potential participants in the transaction. The participants all agree to make the results of the transaction permanent but do not do so immediately. The participants log information to disk to ensure they can complete In phase 2 f all the participants agree to commit, the coordinator logs that agreement and the outcome is decided. The recording of this agreement in the log ends in Phase 2, the coordinator informs each participant of the decision, and they permanently update their resources.

Q) What does Class.forName return?
A) A class as loaded by the classloader.

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

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

Q) What are the components of JDBC
A) JDBC Components-Connection Pools, Data Sources, and MultiPools

Q) How to you load the drivers
A) Class.forName() method is used in JDBC to load the JDBC drivers dynamically

Q) What does Class.forname() do
A) Class.forName() method is used in JDBC to load the JDBC drivers dynamically

Q) What are the different types of JDBC drivers
A)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.
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.

Q) How do I create a database connection?
A) The database connection is created in 3 steps:
1. Find a proper database URL (see FAQ on JDBC URL)
2. Load the database driver
3. Ask the Java DriverManager class to open a connection to your database
In java code, the steps are realized in code as follows:
1. Create a properly formatted JDBR URL for your database. (See FAQ on JDBC URL for more information). A JDBC URL has the form jdbc:someSubProtocol://myDatabaseServer/theDatabaseName
2.try {
Class.forName("my.database.driver");
}
catch(Exception ex){
System.err.println("Could not load database driver: " + ex);
}
3. Connection conn = DriverManager.getConnection("a.JDBC.URL", "databaseLogin", "databasePassword");

Q) What is Metadata and why should I use it?
A) Metadata ('data about data') is information about one of two things:
1. Database information (java.sql.DatabaseMetaData), or
2. Information about a specific ResultSet (java.sql.ResultSetMetaData).
Use DatabaseMetaData to find information about your database, such as its capabilities and structure. Use ResultSetMetaData to find information about the results of an SQL query, such as size and types of columns.

Q) How to you establish a connection
A) Loading Drivers
Class.forName("Driver");
Getting connection
Connection con = DriverManager.getConnection(url,"myLogin", "myPassword");

Q) What are different types of statements in JDBC
A) java.sql.Statement - Top most interface which provides basic methods useful for executing SELECT, INSERT, UPDATE and DELETE SQL statements.
java.sql.PreparedStatement - An enhanced verion of java.sql.Statement which allows precompiled queries with parameters. It is more efficient to use java.sql.PreparedStatement if you have to specify parameters to your SQL queries.
java.sql.CallableStatement - Allows you to execute stored procedures within a RDBMS which supports stored procedures (MySQL doesn't support stored procedures at the moment).

Q) When do we used prepared statements?
A) If you want to execute a Statement object many times, it will normally reduce execution time to use a PreparedStatement object instead.
The main feature of a PreparedStatement object is that, unlike a Statement object, it is given an SQL statement when it is created. The advantage to this is that in most cases, this SQL statement will be sent to the DBMS right away, where it will be compiled. As a result, the PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement 's SQL statement without having to compile it first.
Although PreparedStatement objects can be used for SQL statements with no parameters, you will probably use them most often for SQL statements that take parameters. The advantage of using SQL statements that take parameters is that you can use the same statement and supply it with different values each time you execute it. You will see an example of this in the following sections.

Q) How do you create JDBC statements?
A) Connection con = null;
Statement st = null;
// Obtain connection here
st = con.createStatement();
ResultSet rs = null;
rs = st.executeQuery("SELECT * FROM users");
int recordsUpdated;
recordsUpdated = st.executeUpdate("DELETE FROM users WHERE user_id = 1");

Q) How do you retrieve data from a result set
A) Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(SELECT COF_NAME, PRICE FROM COFFEES");
while (rs .next() ) {
//Iam assuming there are 3 columns in the table.
System.out.println ( rs.getString(1));
System.out.println(rs.getString(2));
System.out.println(rs.getString(3));
}
//don't forget to close the resultset, statement & connection
rs.close(); //First
stmt.close(); //Second
con.close(); //Last
System.out.println("You are done");

Q) What is a stored procedure
A) A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. Stored procedures are used to encapsulate a set of operations or queries to execute on a database server. For example, operations on an employee database (hire, fire, promote, lookup) could be coded as stored procedures executed by application code. Stored procedures can be compiled and executed with different parameters and results, and they may have any combination of input, output, and input/output parameters.

Q) What do you mean by batch updates
A) if you want to execute a set of statements, i.e. SQL statements at a time then we use batch update statement. resultset=pst.batchUpdate();

Q) What are the tasks of JDBC
A) Following are the tasks of JDBC
* Load the JDBC drivers
* Register the drivers
* Specify a database
* Open a connection to database
* Submit a query to database
* Gets the results

Q) Why do we need batch updates
A) Let's say there are 100 records need to be insert. If we execute normal statemets the no of transactions will be 100 (in terms of connection making to DB). using batch updates we can add 100 rec to batch and the no of transactions will be only one in this case. This will reduce the burdon on db, which is very costly in terms of resources.

Q) What packages are being used by JDBC
A) Following packages are used in JDBC
java.sql
javax.sql

Q) Difference between Resultset and Rowset
A) RowSet
The interface that adds support to the JDBC API for the JavaBeansTM component model. A rowset, which can be used as a JavaBeans component in a visual Bean development environment, can be created and configured at design time and executed at run time.
The RowSet interface provides a set of JavaBeans properties that allow a RowSet instance to be configured to connect to a JDBC data source and read some data from the data source. A group of setter methods (setInt, setBytes, setString, and so on) provide a way to pass input parameters to a rowset's command property. This command is the SQL query the rowset uses when it gets its data from a relational database, which is generally the case.
The RowSet interface supports JavaBeans events, allowing other components in an application to be notified when an event occurs on a rowset, such as a change in its value.
The RowSet interface is unique in that it is intended to be implemented using the rest of the JDBC API. In other words, a RowSet implementation is a layer of software that executes "on top" of a JDBC driver. Implementations of the RowSet interface can be provided by anyone, including JDBC driver vendors who want to provide a RowSet implementation as part of their JDBC products.
A RowSet object may make a connection with a data source and maintain that connection throughout its life cycle, in which case it is called a connected rowset. A rowset may also make a connection with a data source, get data from it, and then close the connection. Such a rowset is called a disconnected rowset. A disconnected rowset may make changes to its data while it is disconnected and then send the changes back to the original source of the data, but it must reestablish a connection to do so.
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. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set.
A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable and/or updatable. The following code fragment, in which con is a valid Connection object, illustrates how to make a result set that is scrollable and insensitive to updates by others, and that is updatable. See ResultSet fields for other options.

Q) How do we retrieve warning
A) SQLWarning objects are a subclass of SQLException that deal with database access warnings. Warnings do not stop the execution of an application, as exceptions do.
They simply alert the user that something did not happen as planned. A warning can be reported on a Connection object, a Statement object (including PreparedStatement and CallableStatement objects), or a ResultSet object. Each of these classes has a getWarnings method, which you must invoke in order to see the first warning reported on the calling object.
E.g.
SQLWarning warning = stmt.getWarnings();
if (warning != null) {
while (warning != null) {
System.out.println("Message: " + warning.getMessage());
System.out.println("SQLState: " + warning.getSQLState());
System.out.print("Vendor error code: ");
System.out.println(warning.getErrorCode());
warning = warning.getNextWarning();
}
}

Q) What do we use setAutoCommit() for
A) The DML operations by default are committed. If we wish to avoid the commit by default, setAutoCommit(false) has to be called on the Connection object.Once the statements are executed, commit() has to be called on the Connection object explicitly.

Q) How do you insert images in Database using JDBC
A) We can store images in the databse using the BLOB datatype where in the image is stored as a byte stream

Q) How many statements can we create with one connection
A) There is no such limit on number of statements to be created

Q) What is Metadata
A) It is information about one of two things: Database information (java.sql.DatabaseMetaData), or Information about a specific ResultSet (java.sql.ResultSetMetaData). Use DatabaseMetaData to find information about your database, such as its capabilities and structure. Use ResultSetMetaData to find information about the results of an SQL query, such as size and types of columns

Q) What is a data source
A) A DataSource class brings another level of abstraction than directly using a connection object. Data source can be referenced by JNDI. Data Source may point to RDBMS, file System , any DBMS etc.

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

Q) What is the advantage of using PreparedStatement?
A) If we are using PreparedStatement the execution time will be less. The PreparedStatement object contains not just an SQL statement, but the SQL statement that has been precompiled. This means that when the PreparedStatement is executed,the RDBMS can just run the PreparedStatement's Sql statement without having to compile it first.

Q) What is a "dirty read"?
A) Quite often in database processing, we come across the situation wherein one transaction can change a value, and a second transaction can read this value before the original change has been committed or rolled back. This is known as a dirty read scenario because there is always the possibility that the first transaction may rollback the change, resulting in the second transaction having read an invalid value. While you can easily command a database to disallow dirty reads, this usually degrades the performance of your application due to the increased locking overhead. Disallowing dirty reads also leads to decreased system concurrency.

Q) Different types of Transaction Isolation Levels?
A) The isolation level describes the degree to which the data being updated is visible to other transactions. This is important when two transactions are trying to read the same row of a table. Imagine two transactions: A and B. Here three types of inconsistencies can occur:
o Dirty-read: A has changed a row, but has not committed the changes. B reads the uncommitted data but his view of the data may be wrong if A rolls back his changes and updates his own changes to the database.
o Non-repeatable read: B performs a read, but A modifies or deletes that data later. If B reads the same row again, he will get different data.
o Phantoms: A does a query on a set of rows to perform an operation. B modifies the table such that a query of A would have given a different result. The table may be inconsistent.
TRANSACTION_READ_UNCOMMITTED : DIRTY READS, NON-REPEATABLE READ AND PHANTOMS CAN OCCUR.
TRANSACTION_READ_COMMITTED : DIRTY READS ARE PREVENTED, NON-REPEATABLE READ AND PHANTOMS CAN OCCUR.
TRANSACTION_REPEATABLE_READ : DIRTY READS , NON-REPEATABLE READ ARE PREVENTED AND PHANTOMS CAN OCCUR.
TRANSACTION_SERIALIZABLE : DIRTY READS, NON-REPEATABLE READ AND PHANTOMS ARE PREVENTED.

Q) What is the normal procedure followed by a java client to access the db.?
A) The database connection is created in 3 steps:
1. Find a proper database URL
2. Load the database driver
3. Ask the Java DriverManager class to open a connection to your database
In java code, the steps are realized in code as follows:
4. Create a properly formatted JDBR URL for your database. A JDBC URL has the form jdbc:someSubProtocol://myDatabaseServer/theDatabaseName
5. Class.forName("my.database.driver");
6. Connection conn = DriverManager.getConnection("a.JDBC.URL", "databaseLogin","databasePassword");

Q) How do you handle your own transaction ?
A) Connection Object has a method called setAutocommit ( boolean flag) . For handling our own transaction we can set the parameter to false and begin your transaction . Finally commit the transaction by calling the commit method.

Q) I have the choice of manipulating database data using a String or a java.sql.Clob. Which has best performance?
A) java.sql.Clob, since it does not extract any data from the database until you explicitly ask it to. The Java platform 2 type Clob wraps a database locator (which is essentially a pointer to char). That pointer is a rather large number (between 32 and 256 bits in size) - but the effort to extract it from the database is insignificant next to extracting the full Clob content. For insertion into the database, you should use a String since data need not been downloaded from the database. Thus, use the Clob class only for extraction. Conclusion: Unless you always intend to extract the full textual data stored in the particular table cell, use the java.sql.Clob class for extraction whenever you can.

Q) I have the choice of manipulating database data using a byte[] or a java.sql.Blob. Which has best performance?
A) java.sql.Blob, since it does not extract any data from the database until you explicitly ask it to. The Java platform 2 type Blob wraps a database locator (which is essentially a pointer to byte). That pointer is a rather large number (between 32 and 256 bits in size) - but the effort to extract it from the database is insignificant next to extracting the full blob content. For insertion into the database, you should use a byte[] since data has not been uploaded to the database yet. Thus, use the Blob class only for extraction. Conclusion: use the java.sql.Blob class for extraction whenever you can.

Q) Do I need to commit after an INSERT call in JDBC or does JDBC do it automatically in the DB?
A) If your autoCommit flag (managed by the Connection.setAutoCommit method) is false, you are required to call the commit() method - and vice versa.

Q) How can I retrieve only the first n rows, second n rows of a database using a particular WHERE clause ? For example, if a SELECT typically returns a 1000 rows, how do first retrieve the 100 rows, then go back and retrieve the next 100 rows and so on ?
A) Use the Statement.setFetchSize method to indicate the size of each database fetch. Note that this method is only available in the Java 2 platform. For Jdk 1.1.X and Jdk 1.0.X, no standardized way of setting the fetch size exists. Please consult the Db driver manual.

Q) What does ResultSet actually contain? Is it the actual data of the result or some links to databases? If it is the actual data then why can't we access it after connection is closed?
A) A ResultSet is an interface. Its implementation depends on the driver and hence ,what it "contains" depends partially on the driver and what the query returns. For example with the Odbc bridge what the underlying implementation layer contains is an ODBC result set. A Type 4 driver executing a stored procedure that returns a cursor - on an oracle database it actually returns a cursor in the databse. The oracle cursor can however be processed like a ResultSet would be from the client. Closing a connection closes all interaction with the database and releases any locks that might have been obtained in the process.

Q) What are SQL3 data types?
A) The next version of the ANSI/ISO SQL standard defines some new datatypes, commonly referred to as the SQL3 types. The primary SQL3 types are:
STRUCT: This is the default mapping for any SQL structured type, and is manifest by the java.sql.Struct type.
REF: Serves as a reference to SQL data within the database. Can be passed as a parameter to a SQL statement. Mapped to the java.sql.Ref type.
BLOB: Holds binary large objects. Mapped to the java.sql.Blob type.
CLOB: Contains character large objects. Mapped to the java.sql.Clob type.
ARRAY: Can store values of a specified type. Mapped to the java.sql.Array type.
You can retrieve, store and update SQL3 types using the corresponding getXXX(), setXXX(), and updateXXX() methods defined in ResultSet interface

Q) What is SQLJ and why would I want to use it instead of JDBC?
A) SQL/J is a technology, originally developed by Oracle Corporation, that enables you to embed SQL statements in Java. The purpose of the SQLJ API is to simplify the development requirements of the JDBC API while doing the same thing. Some major databases (Oracle, Sybase) support SQLJ, but others do not. Currently, SQLJ has not been accepted as a standard, so if you have to learn one of the two technologies, I recommend JDBC.

Q) Will a call to PreparedStatement.executeQuery() always close the ResultSet from the previous executeQuery()?
A) A ResultSet is automatically closed by the Statement that generated it when that Statement is closed, re-executed, or is used to retrieve the next result from a sequence of multiple results.

Q) How can I manage special characters (for example: " _ ' % ) when I execute an INSERT query? If I don't filter the quoting marks or the apostrophe, for example, the SQL string will cause an error.
A) The characters "%" and "_" have special meaning in SQL LIKE clauses (to match zero or more characters, or exactly one character, respectively). In order to interpret them literally, they can be preceded with a special escape character in strings, e.g. "\". In order to specify the escape character used to quote these characters, include the following syntax on the end of the query:
{escape 'escape-character'}
For example, the query
SELECT NAME FROM IDENTIFIERS WHERE ID LIKE '\_%' {escape '\'}finds identifier names that begin with an underbar.

Q) How do I insert an image file (or other raw data) into a database?
A) All raw data types (including binary documents or images) should be read and uploaded to the database as an array of bytes, byte[]. Originating from a binary file,
1. Read all data from the file using a FileInputStream.
2. Create a byte array from the read data.
3. Use method setBytes(int index, byte[] data); of java.sql.PreparedStatement to upload the data.

Q) How can I pool my database connections so I don't have to keep reconnecting to the database?
A) * you gets a reference to the pool
* you gets a free connection from the pool
* you performs your different tasks
* you frees the connection to the pool
Since your application retrieves a pooled connection, you don't consume your time to connect / disconnect from your data source.

Q) What is the difference between client and server database cursors?
A) What you see on the client side is the current row of the cursor which called a Result (ODBC) or ResultSet (JDBC). The cursor is a server-side entity only and remains on the server side.

Q) Are prepared statements faster because they are compiled? if so, where and when are they compiled?
A)Prepared Statements aren't actually compiled, but they are bound by the JDBC driver. Depending on the driver, Prepared Statements can be a lot faster - if you re-use them. Some drivers bind the columns you request in the SQL statement. When you execute Connection.prepareStatement(), all the columns bindings take place, so the binding overhead does not occur each time you run the Prepared Statement.

Q) How do I upload SQL3 BLOB & CLOB data to a database?
A) Although one may simply extract BLOB & CLOB data from the database using the methods of the java.sql.CLOB and java.sql.BLOB, one must upload the data as normal java datatypes. The example below inserts a BLOB in the form of a byte[] and a CLOB in the form of a String into the database
Inserting SQL3 type data [BLOB & CLOB]
private void runInsert() {
try {
// Log
this.log("Inserting values ... ");
// Open a new Statement
PreparedStatement stmnt = conn.prepareStatement("insert Lobtest (image, name) values (?, ?)");
// Create a timestamp to measure the insert time
Date before = new java.util.Date();
for(int i = 0; i < 500; i++) {
// Set parameters
stmnt.setBytes(1, blobData);
stmnt.setString(2, "i: " + i + ";" + clobData);
// Perform insert
int rowsAffected = stmnt.executeUpdate();
}
// Get another timestamp to complete the time measurement
Date after = new java.util.Date();
this.log(" ... Done!");
log("Total run time: " + (after.getTime() - before.getTime()));
// Close database resources
stmnt.close();
} catch(SQLException ex) {
this.log("Hmm... " + ex);
}
}

Q) What is JDO?
A) JDO provides for the transparent persistence of data in a data store agnostic manner, supporting object, hierarchical, as well as relational stores.

Q) Why do I get an UnsupportedOperationException?
A) JDBC 2.0, introduced with the 1.2 version of Java, added several capabilities to JDBC. Instead of completely invalidating all the older JDBC 1.x drivers, when you try to perform a 2.0 task with a 1.x driver, an UnsupportedOperationException will be thrown. You need to update your driver if you wish to use the new capabilities.

Q) Is it possible to connect to multiple databases simultaneously? Can one extract/update data from multiple databases with a single statement?
A) In general, subject, as usual, to the capabilities of the specific driver implementation, one can connect to multiple databases at the same time. At least one driver ( and probably others ) will also handle commits across multiple connections. Obviously one should check the driver documentation rather than assuming these capabilities. As to the second part of the question, one needs special middleware to deal with multiple databases in a single statement or to effectively treat them as one database. DRDA ( Distributed Relational Database Architecture -- I, at least, make it rhyme with "Gerta" ) is probably most commonly used to accomplish this. Oracle has a product called Oracle Transparent Gateway for IBM DRDA and IBM has a product called DataJoiner that make multiple databases appear as one to your application . No doubt there are other products available. XOpen also has papers available regarding DRDA.

Q) What advantage is there to using prepared statements if I am using connection pooling or closing the connection frequently to avoid resource/connection/cursor limitations?
A) The ability to choose the 'best' efficiency ( or evaluate tradeoffs, if you prefer, ) is, at times, the most important piece of a mature developer's skillset. This is YAA ( Yet Another Area, ) where that maxim applies. Apparently there is an effort to allow prepared statements to work 'better' with connection pools in JDBC 3.0, but for now, one loses most of the original benefit of prepared statements when the connection is closed. A prepared statement obviously fits best when a statement differing only in variable criteria is executed over and over without closing the statement. However, depending on the DB engine, the SQL may be cached and reused even for a different prepared statement and most of the work is done by the DB engine rather than the driver. In addition, prepared statements deal with data conversions that can be error prone in straight ahead, built on the fly SQL; handling quotes and dates in a manner transparent to the developer, for example.

Q) Can I reuse a Statement or must I create a new one for each query?
A) When using a JDBC compliant driver, you can use the same Statement for any number of queries. However, some older drivers did not always "respect the spec." Also note that a Statement SHOULD automatically close the current ResultSet before executing a new query, so be sure you are done with it before re-querying using the same Statement.

Q) What is a three-tier architecture?
A) A three-tier architecture is any system which enforces a general separation between the following three parts:
1. Client Tier or user interface
2. Middle Tier or business logic
3. Data Storage Tier
Applied to web applications and distributed programming, the three logical tiers usually correspond to the physical separation between three types of devices or hosts:

Q) What separates one tier from another in the context of n-tiered architecture?
A) It depends on the application.
In a web application, for example, where tier 1 is a web-server, it may communicate with a tier 2 Application Server using RMI over IIOP, and subsequently tier 2 may communicate with tier 3 (data storage) using JDBC, etc.
Each of these tiers may be on separate physical machines or they may share the same box.
The important thing is the functionality at each tier.
* Tier 1 - Presentation - should be concerned mainly with display of user interfaces and/or data to the client browser or client system.
* Tier 2 - Application - should be concerned with business logic
Tier 3+ - Storage/Enterprise Systems - should be focused on data persistence and/or communication with other Enterprise Systems.

Q) How can I insert multiple rows into a database in a single transaction?
A) //turn off the implicit commit
Connection.setAutoCommit(false);
//..your insert/update/delete goes here
Connection.Commit();
a new transaction is implicitly started.
JDBC 2.0 provides a set of methods for executing a batch of database commands. Specifically, the java.sql.Statement interface provides three methods: addBatch(), clearBatch() and executeBatch(). Their documentation is pretty straight forward. The implementation of these methods is optional, so be sure that your driver supports these.

Q) How do I convert a java.sql.Timestamp to a java.util.Date?
A)While Timesteamp extends Date, it stores the fractional part of the time within itself instead of within the Date superclass. If you need the partial seconds, you have to add them back in.
Date date = new Date(ts.getTime() + (ts.getNanos() / 1000000 ));

Q) Is Class.forName(Drivername) the only way to load a driver? Can I instantiate the Driver and use the object of the driver?
A) Yes, you can use the driver directly. Create an instance of the driver and use the connect method from the Driver interface. Note that there may actually be two instances created, due to the expected standard behavior of drivers when the class is loaded.

Q) What's new in JDBC 3.0?
A) Probably the new features of most interest are:
* Savepoint support
* Reuse of prepared statements by connection pools
* Retrieval of auto-generated keys
* Ability to have multiple open ResultSet objects
* Ability to make internal updates to the data in Blob and Clob objects
* Ability to Update columns containing BLOB, CLOB, ARRAY and REF types
* Both java.sql and javax.sql ( JDBC 2.0 Optional Package ) are expected to be included with J2SE 1.4.

Q) Why do I get the message "No Suitable Driver"?
A) Often the answer is given that the correct driver is not loaded. This may be the case, but more typically, the JDBC database URL passed is not properly constructed. When a Connection request is issued, the DriverManager asks each loaded driver if it understands the URL sent. If no driver responds that it understands the URL, then the "No Suitable Driver" message is returned.

Q) When I create multiple Statements on my Connection, only the current Statement appears to be executed. What's the problem?
A) All JDBC objects are required to be threadsafe. Some drivers, unfortunately, implement this requirement by processing Statements serially. This means that additional Statements are not executed until the preceding Statement is completed.

Q) Can a single thread open up mutliple connections simultaneously for the same database and for same table?
A) The general answer to this is yes. If that were not true, connection pools, for example, would not be possible. As always, however, this is completely dependent on the JDBC driver. You can find out the theoretical maximum number of active Connections that your driver can obtain via the DatabaseMetaData.getMaxConnections method.

Q) Can I ensure that my app has the latest data?
A) Typically an application retrieves multiple rows of data, providing a snapshot at an instant of time. Before a particular row is operated upon, the actual data may have been modified by another program. When it is essential that the most recent data is provided, a JDBC 2.0 driver provides the ResultSet.refreshRow method.

Q) What does normalization mean for java.sql.Date and java.sql.Time?
A) These classes are thin wrappers extending java.util.Date, which has both date and time components. java.sql.Date should carry only date information and a normalized instance has the time information set to zeros. java.sql.Time should carry only time information and a normalized instance has the date set to the Java epoch ( January 1, 1970 ) and the milliseconds portion set to zero.

Q) What's the best way, in terms of performance, to do multiple insert/update statements, a PreparedStatement or Batch Updates?
A) Because PreparedStatement objects are precompiled, their execution can be faster than that of Statement objects. Consequently, an SQL statement that is executed many times is often created as a PreparedStatement object to increase efficiency.
A CallableStatement object provides a way to call stored procedures in a standard manner for all DBMSes. Their execution can be faster than that of PreparedStatement object.
Batch updates are used when you want to execute multiple statements together. Actually, there is no conflict here. While it depends on the driver/DBMS engine as to whether or not you will get an actual performance benefit from batch updates, Statement, PreparedStatement, and CallableStatement can all execute the addBatch() method.

Q) What is DML?
A) DML is an abbreviation for Data Manipulation Language. This portion of the SQL standard is concerned with manipulating the data in a database as opposed to the structure of a database. The core verbs for DML are SELECT, INSERT, DELETE, UPDATE, COMMIT and ROLLBACK.

Q) What is DDL?
A) DDL is an abbreviation for Data Definition Language. This portion of the SQL standard is concerned with the creation, deletion and modification of database objects like tables, indexes and views. The core verbs for DDL are CREATE, ALTER and DROP. While most DBMS engines allow DDL to be used dynamically ( and available to JDBC ), it is often not supported in transactions.

Q) How can I get information about foreign keys used in a table?
A) DatabaseMetaData.getImportedKeys() returns a ResultSet with data about foreign key columns, tables, sequence and update and delete rules.

Q) How do I disallow NULL values in a table?
A) 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 )

Q) What isolation level is used by the DBMS when inserting, updating and selecting rows from a database?
A) 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).

Q) What areas should I focus on for the best performance in a JDBC application?
A) These are few points to consider:
* Use a connection pool mechanism whenever possible.
* Use prepared statements. These can be beneficial, for example with DB specific escaping, even when used only once.
* Use stored procedures when they can be created in a standard manner. Do watch out for DB specific SP definitions that can cause migration headaches.
* Even though the jdbc promotes portability, true portability comes from NOT depending on any database specific data types, functions and so on.
* Select only required columns rather than using select * from Tablexyz.
* Always close Statement and ResultSet objects as soon as possible.
* Write modular classes to handle database interaction specifics.
* Work with DatabaseMetaData to get information about database functionality.
* Softcode database specific parameters with, for example, properties files.
* Always catch AND handle database warnings and exceptions. Be sure to check for additional pending exceptions.
* Test your code with debug statements to determine the time it takes to execute your query and so on to help in tuning your code. Also use query plan functionality if available.
* Use proper ( and a single standard if possible ) formats, especially for dates.
* Use proper data types for specific kind of data. For example, store birthdate as a date type rather than, say, varchar.

Q) Explain how to get the resultset of Stored procedure
A) CallableStatement cstmt;
ResultSet rs;
int i;
String s;
...
cstmt.execute();// Call the stored procedure 1
rs = cstmt.getResultSet();// Get the first result set 2
while (rs.next()) { // Position the cursor 3
i = rs.getInt(1); // Retrieve current result set value
System.out.println("Value from first result set = " + i); // Print the value
} cstmt.getMoreResults(); // Point to the second result set 4a and close the first result set
rs = cstmt.getResultSet(); // Get the second result set 4b
while (rs.next()) { // Position the cursor 4c
s = rs.getString(1); // Retrieve current result set value
System.out.println("Value from second result set = " + s);
}
rs.close(); // Close the result set
cstmt.close(); // Close the statement

Q) How to Make Updates to Updatable Result Sets?
A) Another new feature in the JDBC 2.0 API is the ability to update rows in a result set using methods in the Java programming language rather than having to send an SQL command. But before you can take advantage of this capability, you need to create a ResultSet object that is updatable. In order to do this, you supply the ResultSet constant CONCUR_UPDATABLE to the createStatement method.
Connection con =DriverManager.getConnection("jdbc:mySubprotocol:mySubName");
Statement stmt =con.createStatement
( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet uprs =stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");

Q) How do you call a stored procedure from JDBC?
A) The first step is to create a CallableStatement object. As with Statement an and PreparedStatement objects, this is done with an open Connection object. A CallableStatement object contains a call to a stored procedure.
CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");
ResultSet rs = cs.executeQuery();

Q) How can you load the drivers?
A) 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");

Q) What does setAutoCommit do?
A) When a connection is created, it is in auto-commit mode. This means that each individual SQL statement is treated as a transaction and will be automatically committed right after it is executed. The way to allow two or more statements to be grouped into a transaction is to disable auto-commit mode:
con.setAutoCommit(false);
Once auto-commit mode is disabled, no SQL statements will be committed until you call the method commit explicitly.
con.setAutoCommit(false);
PreparedStatement updateSales = con.prepareStatement( "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");
updateSales.setInt(1, 50); updateSales.setString(2, "Colombian");
updateSales.executeUpdate();
PreparedStatement updateTotal = con.prepareStatement("UPDATE COFFEES SET TOTAL = TOTAL + ? WHERE COF_NAME LIKE ?");
updateTotal.setInt(1, 50);
updateTotal.setString(2, "Colombian");
updateTotal.executeUpdate();
con.commit();
con.setAutoCommit(true);

Q) 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?
A) 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.

Q) What's the difference between TYPE_SCROLL_INSENSITIVE , and TYPE_SCROLL_SENSITIVE?
A) You will get a scrollable ResultSet object if you specify one of these ResultSet constants.The difference between the two has to do with whether a result set reflects changes that are made to it while it is open and whether certain methods can be called to detect these changes. Generally speaking, a result set that is TYPE_SCROLL_INSENSITIVE does not reflect changes made while it is still open and one that is TYPE_SCROLL_SENSITIVE does. All three types of result sets will make changes visible if they are closed and then reopened:
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");
srs.afterLast();
while (srs.previous()) {
String name = srs.getString("COF_NAME");
float price = srs.getFloat("PRICE");
System.out.println(name + " " + price);
}

Q) How can I know when I reach the last record in a table, since JDBC doesn't provide an EOF method?
A) 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.

Q) How can you move the cursor in scrollable result sets?
A) One of the new features in the JDBC 2.0 API is the ability to move a result set's cursor backward as well as forward. There are also methods that let you move the cursor to a particular row and check the position of the cursor.
Statement stmt = con.createStatement
(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");
The first argument is one of three constants added to the ResultSet API to indicate the type of a ResultSet object: TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE , and TYPE_SCROLL_SENSITIVE. The second argument is one of two ResultSet constants for specifying whether a result set is read-only or updatable: CONCUR_READ_ONLY and CONCUR_UPDATABLE. The point to remember here is that if you specify a type, you must also specify whether it is read-only or updatable. Also, you must specify the type first, and because both parameters are of type int , the compiler will not complain if you switch the order. Specifying the constant TYPE_FORWARD_ONLY creates a nonscrollable result set, that is, one in which the cursor moves only forward. If you do not specify any constants for the type and updatability of a ResultSet object, you will automatically get one that is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY.

Q) Can ResultSets be passed between methods of a class? Are there any special usage
A) Yes. There is no reason that a ResultSet can't be used as a method parameter just like any other object reference. You must ensure that access to the ResultSet is synchronized. This should not be a problem is the ResultSet is a method variable passed as a method parameter - the ResultSet will have method scope and multi-thread access would not be an issue.
As an example, say you have several methods that obtain a ResultSet from the same table(s) and same columns, but use different queries. If you want these ResultSets to be processed the same way, you would have another method for that. This could look something like:
public List getStudentsByLastName(String lastName) {
ResultSet rs = ... (JDBC code to retrieve students by last name);
return processResultSet(rs);
}
public List getStudentsByFirstName(String firstName) {
ResultSet rs = ... (JDBC code to retrieve students by first name);
return processResultSet(rs);
}
private List processResultSet(ResultSet rs) {
List l = ... (code that iterates through ResultSet to build a List of Student objects);
return l;
}
Since the ResultSet always has method scope - sychronization is never an issue.
1. There is only one ResultSet. Dont assume that the ResultSet is at the start (or in any good state...) just because you received it as a parameter. Previous operations involving the ResultSet will have had the side-effect of changing its state.
2. You will need to be careful about the order in which you close the ResultSet and CallableStatement/PreparedStatement/etc
From my own experience using the Oracle JDBC drivers and CallableStatements the following statements are true:
* If you close the CallableStatement the ResultSet retrieved from that CallableStatement immediately goes out-of-scope.
* If you close the ResultSet without reading it fully, you must close the CallableStatement or risk leaking a cursor on the database server.
* If you close the CallableStatement without reading it's associated ResultSet fully, you risk leaking a cursor on the database server.
No doubt, these observations are valid only for Oracle drivers. Perhaps only for some versions of Oracle drivers.
The recommended sequence seems to be:
* Open the statement
* Retrieve the ResultSet from the statement
* Read what you need from the ResultSet
* Close the ResultSet
* Close the Statement

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

Q) How do I retrieve warnings?
A) SQLWarning objects are a subclass of SQLException that deal with database access warnings. Warnings do not stop the execution of an application, as exceptions do; they simply alert the user that something did not happen as planned. A warning can be reported on a Connection object, a Statement object (including PreparedStatement and CallableStatement objects), or a ResultSet object. Each of these classes has a getWarnings method, which you must invoke in order to see the first warning reported on the calling object:
SQLWarning warning = stmt.getWarnings();
if (warning != null){
System.out.println("n---Warning---n");
while (warning != null) {
System.out.println("Message: " + warning.getMessage());
System.out.println("SQLState: " + warning.getSQLState());
System.out.print("Vendor error code: ");
System.out.println(warning.getErrorCode());
System.out.println("");
warning = warning.getNextWarning();
}
}

Q) How can you use PreparedStatement?
A) This special type of statement is derived from class Statement.If you need a Statement object to execute many times, it will normally make sense to use a PreparedStatement object instead. The advantage to this is that in most cases, this SQL statement will be sent to the DBMS right away, where it will be compiled. As a result, the PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement's SQL statement without having to compile it first.
PreparedStatement updateSales = con.prepareStatement("UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");

Q) How can you retrieve data from the ResultSet?
A) 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");
The method getString is invoked on the ResultSet object rs, so getString() will retrieve (get) the value stored in the column COF_NAME in the current row of rs.

Q) The java.sql package contains mostly interfaces. When and how are these interfaces implemented while connecting to database?
A) The implementation of these interfaces is all part of the driver. A JDBC driver is not just one class - it is a complete set of database-specific implementations for the interfaces defined by the JDBC.
These driver classes come into being through a bootstrap process. This is best shown by stepping through the process of using JDBC to connect to a database, using Oracle's type 4 JDBC driver as an example:
* First, the main driver class must be loaded into the VM:
Class.forName("oracle.jdbc.driver.OracleDriver");
The specified driver must implement the Driver interface. A class initializer (static code block) within the OracleDriver class registers the driver with the DriverManager.
* Next, we need to obtain a connection to the database:
String jdbcURL = "jdbc:oracle:thin:@www.jguru.com:1521:ORCL";
Connection connection = DriverManager.getConnection(jdbcURL);
DriverManager determines which registered driver to use by invoking the acceptsURL(String url) method of each driver, passing each the JDBC URL. The first driver to return "true" in response will be used for this connection. In this example, OracleDriver will return "true", so DriverManager then invokes the connect() method of OracleDriver to obtain an instance of OracleConnection. It is this database-specific connection instance implementing the Connection interface that is passed back from the DriverManager.getConnection() call.
* The bootstrap process continues when you create a statement:
Statement statement = connection.createStatement();
The connection reference points to an instance of OracleConnection. This database-specific implementation of Connection returns a database-specific implementation of Statement, namely OracleStatement
* Invoking the execute() method of this statement object will execute the database-specific code necessary to issue an SQL statement and retrieve the results:
ResultSet result = statement.executeQuery("SELECT * FROM TABLE");
Again, what is actually returned is an instance of OracleResultSet, which is an Oracle-specific implementation of the ResultSet interface. So the purpose of a JDBC driver is to provide these implementations that hide all the database-specific details behind standard Java interfaces.

Q) How can you create JDBC statements and what are they?
A) Statement object is what sends your SQL statement to the DBMS. You simply create a Statement object and then execute it, supplying the appropriate execute method with the SQL statement you want to send. For a SELECT statement, the method to use is executeQuery. For statements that create or modify tables, the method to use is executeUpdate. It takes an instance of an active connection to create a Statement object. In the following example, we use our Connection object con to create the Statement object Statement stmt = con.createStatement();

Q) What will Class.forName do while loading drivers?
A) 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.

Q) What are the pros and cons of all the Type1 (JDBC-ODBC Bridge) driver
A)Type 1: JDBC-ODBC Bridge
The type 1 driver, JDBC-ODBC Bridge, translates all JDBC calls into ODBC (Open DataBase Connectivity) calls and sends them to the ODBC driver. As such, the ODBC driver, as well as, in many cases, the client database code, must be present on the client machine. Figure 1 shows a typical JDBC-ODBC Bridge environment.

   

Pros :
The JDBC-ODBC Bridge allows access to almost any database, since the database's ODBC drivers are already available. Type 1 drivers may be useful for those companies that have an ODBC driver already installed on client machines.
Cons :
The performance is degraded since the JDBC call goes through the bridge to the ODBC driver, then to the native database connectivity interface. The result comes back through the reverse process. Considering the performance issue, type 1 drivers may not be suitable for large-scale applications. The ODBC driver and native connectivity interface must already be installed on the client machine. Thus any advantage of using Java applets in an intranet environment is lost, since the deployment problems of traditional applications remain.

Q) What are the pros and cons of all the Type2 (Native-API/partly Java driver) driver
A)Type 2: Native-API/partly Java driver
JDBC driver type 2 -- the native-API/partly Java driver -- converts JDBC calls into database-specific calls for databases such as SQL Server, Informix, Oracle, or Sybase. The type 2 driver communicates directly with the database server; therefore it requires that some binary code be present on the client machine.

   

Pros :
Type 2 drivers typically offer significantly better performance than the JDBC-ODBC Bridge.
Cons :
The vendor database library needs to be loaded on each client machine. Consequently, type 2 drivers cannot be used for the Internet. Type 2 drivers show lower performance than type 3 and type 4 drivers.

Q) What are the pros and cons of all the Type3 (Net-protocol/all-Java driver) driver
A) Type 3: Net-protocol/all-Java driver
JDBC driver type 3 -- the net-protocol/all-Java driver -- follows a three-tiered approach whereby the JDBC database requests are passed through the network to the middle-tier server. The middle-tier server then translates the request (directly or indirectly) to the database-specific native-connectivity interface to further the request to the database server. If the middle-tier server is written in Java, it can use a type 1 or type 2 JDBC driver to do this.

   

Pros :
The net-protocol/all-Java driver is server-based, so there is no need for any vendor database library to be present on client machines. Further, there are many opportunities to optimize portability, performance, and scalability. Moreover, the net protocol can be designed to make the client JDBC driver very small and fast to load. Additionally, a type 3 driver typically provides support for features such as caching (connections, query results, and so on), load balancing, and advanced system administration such as logging and auditing.
Cons :
Type 3 drivers require database-specific coding to be done in the middle tier. Additionally, traversing the recordset may take longer, since the data comes through the backend server.

Q) What are the pros and cons of all the Type4 (Native-protocol/all-Java driver) driver
A) Type 4: Native-protocol/all-Java driver The native-protocol/all-Java driver (JDBC driver type 4) converts JDBC calls into the vendor-specific database management system (DBMS) protocol so that client applications can communicate directly with the database server. Level 4 drivers are completely implemented in Java to achieve platform independence and eliminate deployment administration issues.

   

Pros :
Since type 4 JDBC drivers don't have to translate database requests to ODBC or a native connectivity interface or to pass the request on to another server, performance is typically quite good. Moreover, the native-protocol/all-Java driver boasts better performance than types 1 and 2. Also, there's no need to install special software on the client or server. Further, these drivers can be downloaded dynamically.
Cons :
With type 4 drivers, the user needs a different driver for each database.