JDBC Interview Questions & Answers
JDBC Interview Questions and Answers
Dear Readers, Welcome to JDBC Interview Questions and Answers have been designed specially to get you acquainted with the nature of questions you may encounter during your Job interview for the subject of JDBC. These JDBC Questions are very important for campus placement test and job interviews. As per my experience good interviewers hardly plan to ask any particular questions during your Job interview and these model questions are asked in the online technical test and interview of many IT companies.
1. What is the JDBC?
Java Database Connectivity (JDBC) is a standard Java API to interact with relational databases form Java. JDBC has set of classes and interfaces which can use from Java application and talk to database without learning RDBMS details and using Database Specific JDBC Drivers.
2. What are the new features added to JDBC 4.0?
The major features added in JDBC 4.0 include :
• Auto-loading of JDBC driver class
• Connection management enhancements
• Support for RowId SQL type
• DataSet implementation of SQL using Annotations
• SQL exception handling enhancements
• SQL XML support
3. Explain Basic Steps in writing a Java program using JDBC?
JDBC makes the interaction with RDBMS simple and intuitive.
When a Java application needs to access database :
• Load the RDBMS specific JDBC driver because this driver actually communicates with the database (Incase of JDBC 4.0 this is automatically loaded).
• Open the connection to database which is then used to send SQL statements and get results back.
• Create JDBC Statement object. This object contains SQL query.
• Execute statement which returns resultset(s). ResultSet contains the tuples of database table as a result of SQL query.
• Process the result set.
• Close the connection.
4. Exaplain the JDBC Architecture.
The JDBC Architecture consists of two layers:
• The JDBC API, which provides the application-to-JDBC Manager connection.
• The JDBC Driver API, which supports the JDBC Manager-to-Driver Connection.
The JDBC API uses a driver manager and database-specific drivers to provide transparent connectivity to heterogeneous databases. The JDBC driver manager ensures that the correct driver is used to access each data source. The driver manager is capable of supporting multiple concurrent drivers connected to multiple heterogeneous databases. The location of the driver manager with respect to the JDBC drivers and the Java application is shown in Figure 1.
5. What are the main components of JDBC ?
The life cycle of a servlet consists of the following phases:
• 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 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.The connection object represents communication context, i.e., all communication with database is through connection object only.
• Statement : Encapsulates an SQL statement which is passed to the database to be parsed, compiled, planned and executed.
• ResultSet: The ResultSet represents set of rows retrieved due to query execution.
6. How the JDBC application works?
A JDBC application can be logically divided into two layers:
1. Driver layer
2. Application layer
• Driver layer consists of DriverManager class and the available JDBC drivers.
• The application begins with requesting the DriverManager for the connection.
• An appropriate driver is choosen and is used for establishing the connection. This connection is given to the application which falls under the application layer.
• The application uses this connection to create Statement kind of objects, through which SQL commands are sent to backend and obtain the results.
7. How do I load a database driver with JDBC 4.0 / Java 6?
Provided the JAR file containing the driver is properly configured, just place the JAR file in the classpath. Java developers NO longer need to explicitly load JDBC drivers using code like Class.forName() to register a JDBC driver.The DriverManager class takes care of this by automatically locating a suitable driver when the DriverManager.getConnection() method is called. This feature is backward-compatible, so no changes are needed to the existing JDBC code.
8. What is JDBC Driver interface?
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.
9. What does the connection object represents?
The connection object represents communication context, i.e., all communication with database is through connection object only.
10. Can the JDBC-ODBC Bridge be used with applets?
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.
11. How do I start debugging problems related to the JDBC API?
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.)
12. What is new in JDBC 2.0?
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.
13. How to move the cursor in scrollable resultset ?
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.
14. How to update a resultset programmatically?
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.
15. How can I use the JDBC API to access a desktop database like Microsoft Access over the network?
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.
16. Are there any ODBC drivers that do not work with the JDBC-ODBC Bridge?
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.
17. What causes the "No suitable driver" error?
"No suitable driver" is an error that usually occurs during a call to the DriverManager.getConnection method. The cause can be failing to load the appropriate JDBC drivers before calling the getConnection method, or it can be specifying an invalid JDBC URL--one that isn't recognized by your JDBC driver. Your best bet is to check the documentation for your JDBC driver or contact your JDBC driver vendor if you suspect that the URL you are specifying is not being recognized by your JDBC driver.
In addition, when you are using the JDBC-ODBC Bridge, this error can occur if one or more the the shared libraries needed by the Bridge cannot be loaded. If you think this is the cause, check your configuration to be sure that the shared libraries are accessible to the Bridge.
18. Why isn't the java.sql.DriverManager class being found?
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.
How to insert and delete a row programmatically? (new feature in JDBC 2.0)
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();
19. What are the two major components of JDBC?
One implementation interface for database manufacturers, the other implementation interface for application and applet writers.
20. What is JDBC Driver interface?
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.
21. How do I retrieve a whole row of data at once, instead of calling an individual ResultSet.getXXX method for each column?
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.
22. What are the common tasks of JDBC?
Create an instance of a JDBC driver or load JDBC drivers through jdbc.drivers
Register a driver
Specify a database
Open a database connection
Submit a query
Receive results
Process results
Why does the ODBC driver manager return 'Data source name not found and no default driver specified Vendor: 0'
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
23. How to use JDBC to connect Microsoft Access?
There is a specific tutorial at javacamp.org. Check it out.
24. What are four types of JDBC driver?
Type 1 Drivers
Bridge drivers such as the jdbc-odbc bridge. They rely on an intermediary such as ODBC to transfer the SQL calls to the database and also often rely on native code. It is not a serious solution for an application
Type 2 Drivers
Use the existing database API to communicate with the database on the client. Faster than Type 1, but need native code and require additional permissions to work in an applet. Client machine requires software to run.
Type 3 Drivers
JDBC-Net pure Java driver. It translates JDBC calls to a DBMS-independent network protocol, which is then translated to a DBMS protocol by a server. Flexible. Pure Java and no native code.
Type 4 Drivers
Native-protocol pure Java driver. It converts JDBC calls directly into the network protocol used by DBMSs. This allows a direct call from the client machine to the DBMS server. It doesn't need any special native code on the client machine.
Recommended by Sun's tutorial, driver type 1 and 2 are interim solutions where direct pure Java drivers are not yet available. Driver type 3 and 4 are the preferred way to access databases using the JDBC API, because they offer all the advantages of Java technology, including automatic installation. For more info, visit Sun JDBC page
25. Which type of JDBC driver is the fastest one?
JDBC Net pure Java driver(Type IV) is the fastest driver because it converts the jdbc calls into vendor specific protocol calls and it directly interacts with the database.
26. Are all the required JDBC drivers to establish connectivity to my database part of the JDK?
No. There aren't any JDBC technology-enabled drivers bundled with the 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.
27. Is the JDBC-ODBC Bridge multi-threaded?
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.
28. Does the JDBC-ODBC Bridge support multiple concurrent open statements per connection?
No. You can open only one Statement object per connection when you are using the JDBC-ODBC Bridge.
29. What is the query used to display all tables names in SQL Server (Query analyzer)?
select * from information_schema.tables
30. Why can't I invoke the ResultSet methods afterLast and beforeFirst when the method next works?
You are probably using a driver implemented for the JDBC 1.0 API. You need to upgrade to a JDBC 2.0 driver that implements scrollable result sets. Also be sure that your code has created scrollable result sets and that the DBMS you are using supports them.
31. How can I retrieve a String or other object type without creating a new object each time?
Creating and garbage collecting potentially large numbers of objects (millions) unnecessarily can really hurt performance. It may be better to provide a way to retrieve data like strings using the JDBC API without always allocating a new object.
We are studying this issue to see if it is an area in which the JDBC API should be improved. Stay tuned, and please send us any comments you have on this question.
32. How many types of JDBC Drivers are present and what are they?
There are 4 types of JDBC Drivers
Type 1: JDBC-ODBC Bridge Driver
Type 2: Native API Partly Java Driver
Type 3: Network protocol Driver
Type 4: JDBC Net pure Java Driver
33. What is the fastest type of JDBC driver?
JDBC driver performance will depend on a number of issues:
(a) the quality of the driver code,
(b) the size of the driver code,
(c) the database server and its load,
(d) network topology,
(e) the number of times your request is translated to a different API.
In general, all things being equal, you can assume that the more your request and response change hands, the slower it will be. This means that Type 1 and Type 3 drivers will be slower than Type 2 drivers (the database calls are make at least three translations versus two), and Type 4 drivers are the fastest (only one translation).
34. There is a method getColumnCount in the JDBC API. Is there a similar method to find the number of rows in a result set?
No, but it is easy to find the number of rows. If you are using a scrollable result set, rs, you can call the methods rs.last and then rs.getRow to find out how many rows rs has. If the result is not scrollable, you can either count the rows by iterating through the result set or get the number of rows by submitting a query with a COUNT column in the SELECT clause.
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
35. the Bridge. How do I do it?
The JDBC-ODBC Bridge is bundled with the Java 2 SDK, Standard Edition, so there is no need to download it separately.
36. If I use the JDBC API, do I have to use ODBC underneath?
No, this is just one of many possible solutions. We recommend using a pure Java JDBC technology-enabled driver, type 3 or 4, in order to get all of the benefits of the Java programming language and the JDBC API.
37. Once I have the Java 2 SDK, Standard Edition, from Sun, what else do I need to connect to a database?
You still need to get and install a JDBC technology-enabled driver that supports the database that you are using. There are many drivers available from a variety of sources. You can also try using the JDBC-ODBC Bridge if you have ODBC connectivity set up already. The Bridge comes with the Java 2 SDK, Standard Edition, and Enterprise Edition, and it doesn't require any extra setup itself. The Bridge is a normal ODBC client. Note, however, that you should use the JDBC-ODBC Bridge only for experimental prototyping or when you have no other driver available.
38. How can I know when I reach the last record in a table, since JDBC doesn't provide an EOF method?
Answer1
You can use last() method of java.sql.ResultSet, if you make it scrollable.
You can also use isLast() as you are reading the ResultSet.
One thing to keep in mind, though, is that both methods tell you that you have reached the end of the current ResultSet, not necessarily the end of the table. SQL and RDBMSes make no guarantees about the order of rows, even from sequential SELECTs, unless you specifically use ORDER BY. Even then, that doesn't necessarily tell you the order of data in the table.
Answer2
Assuming you mean ResultSet instead of Table, the usual idiom for iterating over a forward only resultset is:
ResultSet rs=statement.executeQuery(...);
while (rs.next()) {
// Manipulate row here
}
39. Where can I find info, frameworks and example source for writing a JDBC driver?
There a several drivers with source available, like MM.MySQL, SimpleText Database, FreeTDS, and RmiJdbc. There is at least one free framework, the jxDBCon-Open Source JDBC driver framework. Any driver writer should also review For Driver Writers.
40. How can I create a custom RowSetMetaData object from scratch?
One unfortunate aspect of RowSetMetaData for custom versions is that it is an interface. This means that implementations almost have to be proprietary. The JDBC RowSet package is the most commonly available and offers the sun.jdbc.rowset.RowSetMetaDataImpl class. After instantiation, any of the RowSetMetaData setter methods may be used. The bare minimum needed for a RowSet to function is to set the Column Count for a row and the Column Types for each column in the row. For a working code example that includes a custom RowSetMetaData,
41. How does a custom RowSetReader get called from a CachedRowSet?
The Reader must be registered with the CachedRowSet using CachedRowSet.setReader(javax.sql.RowSetReader reader). Once that is done, a call to CachedRowSet.execute() will, among other things, invoke the readData method.
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
42. RowSetInternal caller and returns void. What can I do in the readData method?
"It can be implemented in a wide variety of ways..." and is pretty vague about what can actually be done. In general, readData() would obtain or create the data to be loaded, then use CachedRowSet methods to do the actual loading. This would usually mean inserting rows, so the code would move to the insert row, set the column data and insert rows. Then the cursor must be set to to the appropriate position.
43. How can I instantiate and load a new CachedRowSet object from a non-JDBC source?
The basics are:
* Create an object that implements javax.sql.RowSetReader, which loads the data.
* Instantiate a CachedRowset object.
* Set the CachedRowset's reader to the reader object previously created.
* Invoke CachedRowset.execute().
Note that a RowSetMetaData object must be created, set up with a description of the data, and attached to the CachedRowset before loading the actual data.
The following code works with the Early Access JDBC RowSet download available from the Java Developer Connection and is an expansion of one of the examples:
// Independent data source CachedRowSet Example
import java.sql.*;
import javax.sql.*;
import sun.jdbc.rowset.*;
public class RowSetEx1 implements RowSetReader
{
CachedRowSet crs;
int iCol2;
RowSetMetaDataImpl rsmdi;
String sCol1,
sCol3;
public RowSetEx1()
{
try
{
crs = new CachedRowSet();
crs.setReader(this);
crs.execute(); // load from reader
System.out.println(
"Fetching from RowSet...");
while(crs.next())
{
showTheData();
} // end while next
if(crs.isAfterLast() == true)
{
System.out.println(
"We have reached the end");
System.out.println("crs row: " +
crs.getRow());
}
System.out.println(
"And now backwards...");
while(crs.previous())
{
showTheData();
} // end while previous
if(crs.isBeforeFirst() == true)
{ System.out.println(
"We have reached the start");
}
crs.first();
if(crs.isFirst() == true)
{ System.out.println(
"We have moved to first");
}
System.out.println("crs row: " +
crs.getRow());
if(crs.isBeforeFirst() == false)
{ System.out.println(
"We aren't before the first row."); }
crs.last();
if(crs.isLast() == true)
{ System.out.println(
"...and now we have moved to the last");
}
System.out.println("crs row: " +
crs.getRow());
if(crs.isAfterLast() == false)
{
System.out.println(
"we aren't after the last.");
}
} // end try
catch (SQLException ex)
{
System.err.println("SQLException: " +
ex.getMessage());
}
} // end constructor
public void showTheData() throws SQLException
{
sCol1 = crs.getString(1);
if(crs.wasNull() == false)
{ System.out.println("sCol1: " + sCol1); }
else { System.out.println("sCol1 is null"); }
iCol2 = crs.getInt(2);
if (crs.wasNull() == false)
{ System.out.println("iCol2: " + iCol2); }
else { System.out.println("iCol2 is null"); }
sCol3 = crs.getString(3);
if (crs.wasNull() == false)
{
System.out.println("sCol3: " +
sCol3 + "\n" );
}
else
{ System.out.println("sCol3 is null\n"); }
} // end showTheData
// RowSetReader implementation
public void readData(RowSetInternal caller)
throws SQLException
{
rsmdi = new RowSetMetaDataImpl();
rsmdi.setColumnCount(3);
rsmdi.setColumnType(1, Types.VARCHAR);
rsmdi.setColumnType(2, Types.INTEGER);
rsmdi.setColumnType(3, Types.VARCHAR);
crs.setMetaData( rsmdi );
crs.moveToInsertRow();
crs.updateString( 1, "StringCol11" );
crs.updateInt( 2, 1 );
crs.updateString( 3, "StringCol31" );
crs.insertRow();
crs.updateString( 1, "StringCol12" );
crs.updateInt( 2, 2 );
crs.updateString( 3, "StringCol32" );
crs.insertRow();
crs.moveToCurrentRow();
crs.beforeFirst();
} // end readData
public static void main(String args[])
{
new RowSetEx1();
}
} // end class RowSetEx1
44. Can I set up a connection pool with multiple user IDs? The single ID we are forced to use causes problems when debugging the DBMS.
Since the Connection interface ( and the underlying DBMS ) requires a specific user and password, there's not much of a way around this in a pool. While you could create a different Connection for each user, most of the rationale for a pool would then be gone. Debugging is only one of several issues that arise when using pools.
However, for debugging, at least a couple of other methods come to mind. One is to log executed statements and times, which should allow you to backtrack to the user. Another method that also maintains a trail of modifications is to include user and timestamp as standard columns in your tables. In this last case, you would collect a separate user value in your program.
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
45. password, is stored in as plain text. What can I do to protect my passwords?
This is a very common question.
Conclusion: JAD decompiles things easily and obfuscation would not help you. But you'd have the same problem with C/C++ because the connect string would still be visible in the executable.
SSL JDBC network drivers fix the password sniffing problem (in MySQL 4.0), but not the decompile problem. If you have a servlet container on the web server, I would go that route (see other discussion above) then you could at least keep people from reading/destroying your mysql database.
Make sure you use database security to limit that app user to the minimum tables that they need, then at least hackers will not be able to reconfigure your DBMS engine.
Aside from encryption issues over the internet, it seems to me that it is bad practice 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.
Detecting Duplicate Keys I have a program that inserts rows in a table. My table has a column 'Name' that has a unique constraint. If the user attempts to insert a duplicate name into the table, I want to display an error message by processing the error code from the database. How can I capture this error code in a Java program?
A solution that is perfectly portable to all databases, is to execute a query for checking if that unique value is present before inserting the row. The big advantage is that you can handle your error message in a very simple way, and the obvious downside is that you are going to use more time for inserting the record, but since you're working on a PK field, performance should not be so bad.
You can also get this information in a portable way, and potentially avoid another database access, by capturing SQLState messages. Some databases get more specific than others, but the general code portion is 23 - "Constraint Violations". UDB2, for example, gives a specific such as 23505, while others will only give 23000.
46. What driver should I use for scalable Oracle JDBC applications?
Sun recommends using the thin ( type 4 ) driver.
* On single processor machines to avoid JNI overhead.
* On multiple processor machines, especially running Solaris, to avoid synchronization bottlenecks.
48. How do I write Greek ( or other non-ASCII/8859-1 ) characters to a database?
From the standard JDBC perspective, there is no difference between ASCII/8859-1 characters and those above 255 ( hex FF ). The reason for that is that all Java characters are in Unicode ( unless you perform/request special encoding ). Implicit in that statement is the presumption that the data store can handle characters outside the hex FF range or interprets different character sets appropriately. That means either:
* The OS, application and database use the same code page and character set. For example, a Greek version of NT with the DBMS set to the default OS encoding.
* The DBMS has I18N support for Greek ( or other language ), regardless of OS encoding. This has been the most common for production quality databases, although support varies. Particular DBMSes may allow setting the encoding/code page/CCSID at the database, table or even column level. There is no particular standard for provided support or methods of setting the encoding. You have to check the DBMS documentation and set up the table properly.
* The DBMS has I18N support in the form of Unicode capability. This would handle any Unicode characters and therefore any language defined in the Unicode standard. Again, set up is proprietary.
49. How can I insert images into a Mysql database?
This code snippet shows the basics:
File file = new File(fPICTURE);
FileInputStream fis = new FileInputStream(file);
PreparedStatement ps =
ConrsIn.prepareStatement("insert into dbPICTURE values (?,?)");
// ***use as many ??? as you need to insert in the exact order***
ps.setString(1,file.getName());
ps.setBinaryStream(2,fis,(int)file.length());
ps.close();
fis.close();
50. Is possible to open a connection to a database with exclusive mode with JDBC?
I think you mean "lock a table in exclusive mode". You cannot open a connection with exclusive mode. Depending on your database engine, you can lock tables or rows in exclusive mode.
In Oracle you would create a statement st and run
st.execute("lock table mytable in exclusive mode");
Then when you are finished with the table, execute the commit to unlock the table. Mysql, Informix and SQLServer all have a slightly different syntax for this function, so you'll have to change it depending on your database. But they can all be done with execute().
51. What are the standard isolation levels defined by JDBC?
The values are defined in the class java.sql.Connection and are:
* TRANSACTION_NONE
* TRANSACTION_READ_COMMITTED
* TRANSACTION_READ_UNCOMMITTED
* TRANSACTION_REPEATABLE_READ
* TRANSACTION_SERIALIZABLE
Update fails without blank padding. Although 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 (e.g. if the key column is 20 characters long, and key is msgID, length 6, I pad it with 14 spaces),
52. the update then works!!! Is there any solution to this problem without padding?
In the SQL standard, CHAR is a fixed length data type. In many DBMSes ( but not all), that means that for a WHERE clause to match, every character must match, including size and trailing blanks. As Alessandro indicates, defining CHAR columns to be VARCHAR is the most general answer.
53. What isolation level is used by the DBMS when inserting, updating and selecting rows from a database?
The answer depends on both your code and the DBMS. If the program does not explicitly set the isolation level, the DBMS default is used. You can determine the default using DatabaseMetaData.getDefaultTransactionIsolation() and the level for the current Connection with Connection.getTransactionIsolation(). If the default is not appropriate for your transaction, change it with Connection.setTransactionIsolation(int level).
54. How can I determine the isolation levels supported by my DBMS?
Use DatabaseMetaData.supportsTransactionIsolationLevel(int level).
Connecting to a database through the Proxy I want to connect to remote database using a program that is running in the local network behind the proxy. Is that possible?
I assume that your proxy is set to accept http requests only on port 80. If you want to have a local class behind the proxy connect to the database for you, then you need a servlet/JSP to receive an HTTP request and use the local class to connect to the database and send the response back to the client.
You could also use RMI where your remote computer class that connects to the database acts as a remote server that talks RMI with the clients. if you implement this, then you will need to tunnel RMI through HTTP which is not that hard.
In summary, either have a servlet/JSP take HTTP requests, 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.
55. How do I receive a ResultSet from a stored procedure?
Stored procedures can return a result parameter, which can be a result set. For a discussion of standard JDBC syntax for dealing with result, IN, IN/OUT and OUT parameters, see Stored Procedures.
56. How can I write to the log used by DriverManager and JDBC drivers?
The simplest method is to use DriverManager.println(String message), which will write to the current log.
57. How can I get or redirect the log used by DriverManager and JDBC drivers?
As of JDBC 2.0, use DriverManager.getLogWriter() and DriverManager.setLogWriter(PrintWriter out). Prior to JDBC 2.0, the DriverManager methods getLogStream() and setLogStream(PrintStream out) were used. These are now deprecated.
58. What does it mean to "materialize" data?
This term generally refers to Array, Blob and Clob data which is referred to in the database via SQL locators "Materializing" the data means to return the actual data pointed to by the Locator.
For Arrays, use the various forms of getArray() and getResultSet().
For Blobs, use getBinaryStream() or getBytes(long pos, int length).
For Clobs, use getAsciiStream() or getCharacterStream().
59. Why do I have to reaccess the database for Array, Blob, and Clob data?
Most DBMS vendors have implemented these types via the SQL3 Locator type
Some rationales for using Locators rather than directly returning the data can be seen most clearly with the Blob type. By definition, a Blob is an arbitrary set of binary data. It could be anything; the DBMS has no knowledge of what the data represents. Notice that this effectively demolishes data independence, because applications must now be aware of what the Blob data actually represents. Let's assume an employee table that includes employee images as Blobs.
Say we have an inquiry program that presents multiple employees with department and identification information. To see all of the data for a specific employee, including the image, the summary row is selected and another screen appears. It is only at this pont that the application needs the specific image. It would be very wasteful and time consuming to bring down an entire employee page of images when only a few would ever be selected in a given run.
Now assume a general interactive SQL application. A query is issued against the employee table. Because the image is a Blob, the application has no idea what to do with the data, so why bring it down, killing performance along the way, in a long running operation?
Clearly this is not helpful in those applications that need the data everytime, but these and other considerations have made the most general sense to DBMS vendors.
60. What is an SQL Locator?
A Locator is an SQL3 data type that acts as a logical pointer to data that resides on a database server. Read "logical pointer" here as an identifier the DBMS can use to locate and manipulate the data. A Locator allows some manipulation of the data on the server. While the JDBC specification does not directly address Locators, JDBC drivers typically use Locators under the covers to handle Array, Blob, and Clob data types.
61. How do I set properties for a JDBC driver and where are the properties stored?
A JDBC driver may accept any number of properties to tune or optimize performance for the specific driver. There is no standard, other than user and password, for what these properties should be. Therefore, the developer is dependent on the driver documentation to automatically pass properties. For a standard dynamic method that can be used to solicit user input for properties, see What properties should I supply to a database driver in order to connect to a database?
In addition, a driver may specify its own method of accepting properties. Many do this via appending the property to the JDBC Database URL. However, a JDBC Compliant driver should implement the connect(String url, Properties info) method. This is generally invoked through DriverManager.getConnection(String url, Properties info).
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.
62. What is the JDBC syntax for using a literal or variable in a standard Statement?
First, it should be pointed out that PreparedStatement handles many issues for the developer and normally should be preferred over a standard Statement.
Otherwise, the JDBC syntax is really the same as SQL syntax. One problem that often affects newbies ( and others ) is that SQL, like many languages, requires quotes around character ( read "String" for Java ) values to distinguish from numerics. So the clause:
"WHERE myCol = " + myVal
is perfectly valid and works for numerics, but will fail when myVal is a String. Instead use:
"WHERE myCol = '" + myVal + "'"
if myVal equals "stringValue", the clause works out to:
WHERE myCol = 'stringValue'
You can still encounter problems when quotes are embedded in the value, which, again, a PreparedStatement will handle for you.
63. How do I check in my code whether a maximum limit of database connections have been reached?
Use DatabaseMetaData.getMaxConnections() and compare to the number of connections currently open. Note that a return value of zero can mean unlimited or, unfortunately, unknown. Of course, driverManager.getConnection() will throw an exception if a Connection can not be obtained.
64. Why do I get UnsatisfiedLinkError when I try to use my JDBC driver?
The first thing is to be sure that this does not occur when running non-JDBC apps. If so, there is a faulty JDK/JRE installation. If it happens only when using JDBC, then it's time to check the documentation that came with the driver or the driver/DBMS support. JDBC driver types 1 through 3 have some native code aspect and typically require some sort of client install. Along with the install, various environment variables and path or classpath settings must be in place. Because the requirements and installation procedures vary with the provider, there is no reasonable way to provide details here. A type 4 driver, on the other hand, is pure Java and should never exhibit this problem. The trade off is that a type 4 driver is usually slower.
Many connections from an Oracle8i pooled connection returns statement closed. I am using import oracle.jdbc.pool.* with thin driver. If I test with many simultaneous connections, I get an SQLException that the statement is closed.
ere is an example of concurrent operation of pooled connections from the OracleConnectionPoolDataSource. There is an executable for kicking off threads, a DataSource, and the workerThread.
The Executable Member
package package6;
/**
* package6.executableTester
*
*/
public class executableTester {
protected static myConnectionPoolDataSource dataSource = null;
static int i = 0;
/**
* Constructor
*/
public executableTester() throws java.sql.SQLException
{
}
/**
* main
* @param args
*/
public static void main(String[] args) {
try{
dataSource = new myConnectionPoolDataSource();
}
catch ( Exception ex ){
ex.printStackTrace();
}
while ( i++ < 10 ) {
try{
workerClass worker = new workerClass();
worker.setThreadNumber( i );
worker.setConnectionPoolDataSource
( dataSource.getConnectionPoolDataSource() );
worker.start();
System.out.println( "Started Thread#"+i );
}
catch ( Exception ex ){
ex.printStackTrace();
}
}
}
}
The DataSource Member
package package6;
import oracle.jdbc.pool.*;
/**
* package6.myConnectionPoolDataSource.
*
*/
public class myConnectionPoolDataSource extends Object {
protected OracleConnectionPoolDataSource ocpds = null;
/**
* Constructor
*/
public myConnectionPoolDataSource()
throws java.sql.SQLException {
// Create a OracleConnectionPoolDataSource instance
ocpds = new OracleConnectionPoolDataSource();
// Set connection parameters
ocpds.setURL("jdbc:oracle:oci8:@mydb");
ocpds.setUser("scott");
ocpds.setPassword("tiger");
}
public OracleConnectionPoolDataSource
getConnectionPoolDataSource() {
return ocpds;
}
}
The Worker Thread Member
package package6;
import oracle.jdbc.pool.*;
import java.sql.*;
import javax.sql.*;
/**
* package6.workerClass .
*
*/
public class workerClass extends Thread {
protected OracleConnectionPoolDataSource ocpds = null;
protected PooledConnection pc = null;
protected Connection conn = null;
protected int threadNumber = 0;
/**
* Constructor
*/
public workerClass() {
}
public void doWork( ) throws SQLException {
// Create a pooled connection
pc = ocpds.getPooledConnection();
// Get a Logical connection
conn = pc.getConnection();
// Create a Statement
Statement stmt = conn.createStatement ();
// Select the ENAME column from the EMP table
ResultSet rset = stmt.executeQuery ("select ename from emp");
// Iterate through the result and print the employee names
while (rset.next ())
// System.out.println (rset.getString (1));
;
// Close the RseultSet
rset.close();
rset = null;
// Close the Statement
stmt.close();
stmt = null;
// Close the logical connection
conn.close();
conn = null;
// Close the pooled connection
pc.close();
pc = null;
System.out.println( "workerClass.thread#
"+threadNumber+" completed..");
}
public void setThreadNumber( int assignment ){
threadNumber = assignment;
}
public void setConnectionPoolDataSource
(OracleConnectionPoolDataSource x){
ocpds = x;
}
public void run() {
try{
doWork();
}
catch ( Exception ex ){
ex.printStackTrace();
}
}
}
The OutPut Produced
Started Thread#1
Started Thread#2
Started Thread#3
Started Thread#4
Started Thread#5
Started Thread#6
Started Thread#7
Started Thread#8
Started Thread#9
Started Thread#10
workerClass.thread# 1 completed..
workerClass.thread# 10 completed..
workerClass.thread# 3 completed..
workerClass.thread# 8 completed..
workerClass.thread# 2 completed..
workerClass.thread# 9 completed..
workerClass.thread# 5 completed..
workerClass.thread# 7 completed..
workerClass.thread# 6 completed..
workerClass.thread# 4 completed..
The oracle.jdbc.pool.OracleConnectionCacheImpl class is another subclass of the oracle.jdbc.pool.OracleDataSource which should also be looked over, that is what you really what to use. Here is a similar example that uses the oracle.jdbc.pool.OracleConnectionCacheImpl. The general construct is the same as the first example but note the differences in workerClass1 where some statements have been commented ( basically a clone of workerClass from previous example ).
The Executable Member
package package6;
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.pool.*;
/**
* package6.executableTester2
*
*/
public class executableTester2 {
static int i = 0;
protected static myOracleConnectCache
connectionCache = null;
/**
* Constructor
*/
public executableTester2() throws SQLException
{
}
/**
* main
* @param args
*/
public static void main(String[] args) {
OracleConnectionPoolDataSource dataSource = null;
try{
dataSource = new OracleConnectionPoolDataSource() ;
connectionCache = new myOracleConnectCache( dataSource );
}
catch ( Exception ex ){
ex.printStackTrace();
}
while ( i++ < 10 ) {
try{
workerClass1 worker = new workerClass1();
worker.setThreadNumber( i );
worker.setConnection( connectionCache.getConnection() );
worker.start();
System.out.println( "Started Thread#"+i );
}
catch ( Exception ex ){
ex.printStackTrace();
}
}
}
protected void finalize(){
try{
connectionCache.close();
} catch ( SQLException x) {
x.printStackTrace();
}
this.finalize();
}
}
The ConnectCacheImpl Member
package package6;
import javax.sql.ConnectionPoolDataSource;
import oracle.jdbc.pool.*;
import oracle.jdbc.driver.*;
import java.sql.*;
import java.sql.SQLException;
/**
* package6.myOracleConnectCache
*
*/
public class myOracleConnectCache extends
OracleConnectionCacheImpl {
/**
* Constructor
*/
public myOracleConnectCache( ConnectionPoolDataSource x)
throws SQLException {
initialize();
}
public void initialize() throws SQLException {
setURL("jdbc:oracle:oci8:@myDB");
setUser("scott");
setPassword("tiger");
//
// prefab 2 connection and only grow to 4 , setting these
// to various values will demo the behavior
//clearly, if it is not
// obvious already
//
setMinLimit(2);
setMaxLimit(4);
}
}
The Worker Thread Member
package package6;
import oracle.jdbc.pool.*;
import java.sql.*;
import javax.sql.*;
/**
* package6.workerClass1
*
*/
public class workerClass1 extends Thread {
// protected OracleConnectionPoolDataSource
ocpds = null;
// protected PooledConnection pc = null;
protected Connection conn = null;
protected int threadNumber = 0;
/**
* Constructor
*/
public workerClass1() {
}
public void doWork( ) throws SQLException {
// Create a pooled connection
// pc = ocpds.getPooledConnection();
// Get a Logical connection
// conn = pc.getConnection();
// Create a Statement
Statement stmt = conn.createStatement ();
// Select the ENAME column from the EMP table
ResultSet rset = stmt.executeQuery
("select ename from EMP");
// Iterate through the result
// and print the employee names
while (rset.next ())
// System.out.println (rset.getString (1));
;
// Close the RseultSet
rset.close();
rset = null;
// Close the Statement
stmt.close();
stmt = null;
// Close the logical connection
conn.close();
conn = null;
// Close the pooled connection
// pc.close();
// pc = null;
System.out.println( "workerClass1.thread#
"+threadNumber+" completed..");
}
public void setThreadNumber( int assignment ){
threadNumber = assignment;
}
// public void setConnectionPoolDataSource
(OracleConnectionPoolDataSource x){
// ocpds = x;
// }
public void setConnection( Connection assignment ){
conn = assignment;
}
public void run() {
try{
doWork();
}
catch ( Exception ex ){
ex.printStackTrace();
}
}
}
The OutPut Produced
Started Thread#1
Started Thread#2
workerClass1.thread# 1 completed..
workerClass1.thread# 2 completed..
Started Thread#3
Started Thread#4
Started Thread#5
workerClass1.thread# 5 completed..
workerClass1.thread# 4 completed..
workerClass1.thread# 3 completed..
Started Thread#6
Started Thread#7
Started Thread#8
Started Thread#9
workerClass1.thread# 8 completed..
workerClass1.thread# 9 completed..
workerClass1.thread# 6 completed..
workerClass1.thread# 7 completed..
Started Thread#10
workerClass1.thread# 10 completed..
65. DB2 Universal claims to support JDBC 2.0, But I can only get JDBC 1.0 functionality. What can I do?
DB2 Universal defaults to the 1.0 driver. You have to run a special program to enable the 2.0 driver and JDK support. For detailed information, see Setting the Environment in Building Java Applets and Applications. The page includes instructions for most supported platforms.
66. How do I disallow NULL values in a table?
Null capability is a column integrity constraint, normally applied 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
)
67. How to get a field's value with ResultSet.getxxx when it is a NULL? I have tried to execute a typical SQL statement:
select * from T-name where (clause);
But an error gets thrown because there are some NULL fields in the table.
You should not get an error/exception just because of null values in various columns. This sounds like a driver specific problem and you should first check the original and any chained exceptions to determine if another problem exists. In general, one may retrieve one of three values for a column that is null, depending on the data type. For methods that return objects, null will be returned; for numeric ( get Byte(), getShort(), getInt(), getLong(), getFloat(), and getDouble() ) zero will be returned; for getBoolean() false will be returned. To find out if the value was actually NULL, use ResultSet.wasNull() before invoking another getXXX method.
68. How do I insert/update records with some of the columns having NULL value?
Use either of the following PreparedStatement methods:
public void setNull(int parameterIndex, int sqlType) throws SQLException
public void setNull(int paramIndex, int sqlType, String typeName) throws SQLException
These methods assume that the columns are nullable. In this case, you can also just omit the columns in an INSERT statement; they will be automatically assigned null values.
Is there a way to find the primary key(s) for an Access Database table? Sun's JDBC-ODBC driver does not implement the getPrimaryKeys() method for the DatabaseMetaData Objects.
// Use meta.getIndexInfo() will
//get you the PK index. Once
// you know the index, retrieve its column name
DatabaseMetaData meta = con.getMetaData();
String key_colname = null;
// get the primary key information
rset = meta.getIndexInfo(null,null, table_name, true,true);
while( rset.next())
{
String idx = rset.getString(6);
if( idx != null)
{
//Note: index "PrimaryKey" is Access DB specific
// other db server has diff. index syntax.
if( idx.equalsIgnoreCase("PrimaryKey"))
{
key_colname = rset.getString(9);
setPrimaryKey( key_colname );
}
}
}
69. Why can't Tomcat find my Oracle JDBC drivers in classes111.zip?
TOMCAT 4.0.1 on NT4 throws the following exception when I try to connect to Oracle DB from JSP.
javax.servlet.ServletException : oracle.jdbc.driver.OracleDriver
java.lang.ClassNotFoundException: oracle:jdbc:driver:OracleDriver
But, the Oracle JDBC driver ZIP file (classes111.zip)is available in the system classpath.
Copied the Oracle Driver class file (classes111.zip) in %TOMCAT_Home - Home%\lib directory and renamed it to classess111.jar.
Able to connect to Oracle DB from TOMCAT 4.01 via Oracle JDBC-Thin Driver.
I have an application that queries a database and retrieves the results into a JTable. This is the code in the model that seems to be taken forever to execute, especially for a large result set:
while ( myRs.next() ) {
Vector newRow =new Vector();
for ( int i=1;i<=numOfCols;i++ )
{
newRow.addElement(myRs.getObject(i));
}
allRows.addElement(newRow);
}
fireTableChanged(null);
newRow stores each row of the resultset and allRows stores all the rows.
Are the vectors here the problem?
70. Is there another way of dealing with the result set that could execute faster?
java.util.Vector is largely thread safe, which means that there is a greater overhead in calling addElement() as it is a synchronized method. If your result set is very large, and threading is not an issue, you could use one of the thread-unsafe collections in Java 2 to save some time. java.util.ArrayList is the likeliest candidate here.
Do not use a DefaultTableModel as it loads all of your data into memory at once, which will obviously cause a large overhead - instead, use an AbstractTableModel and provide an implementation which only loads data on demand, i.e. when (if) the user scrolls down through the table.
How does one get column names for rows returned in a ResultSet?
ResultSet rs = ...
...
ResultSetMetaData rsmd = rs.getMetaData();
int numCols = rsmd.getColumnCount();
for (int i = 1; i <= numCols; i++)
{
System.out.println("[" + i + "]" +
rsmd.getColumnName(i) + " {" +
rsmd.getColumnTypeName(i) + "}");
}
71. What are the considerations for deciding on transaction boundaries?
Transaction processing should always deal with more than one statement and a transaction is often described as a Logical Unit of Work ( LUW ). The rationale for transactions is that you want to know definitively that all or none of the LUW completed successfully. Note that this automatically gives you restart capability. Typically, there are two conditions under which you would want to use transactions:
* Multiple statements involving a single file - An example would be inserting all of a group of rows or all price updates for a given date. You want all of these to take effect at the same time; inserting or changing some subset is not acceptable.
* Multiple statements involving multiple files - The classic example is transferring money from one account to another or double entry accounting; you don't want the debit to succeed and the credit to fail because money or important records will be lost. Another example is a master/detail relationship, where, say, the master contains a total column. If the entire LUW, writing the detail row and updating the master row, is not completed successfully, you A) want to know that the transaction was unsuccessful and B) that a portion of the transaction was not lost or dangling.
Therefore, determining what completes the transaction or LUW should be the deciding factor for transaction boundaries.
72. How can I determine where a given table is referenced via foreign keys?
DatabaseMetaData.getExportedKeys() returns a ResultSet with data similar to that returned by DatabaseMetaData.getImportedKeys(), except that the information relates to other tables that reference the given table as a foreign key container.
73. How can I get information about foreign keys used in a table?
DatabaseMetaData.getImportedKeys() returns a ResultSet with data about foreign key columns, tables, sequence and update and delete rules.
74. Can I use JDBC to execute non-standard features that my DBMS provides?
The answer is a qualified yes. As discussed under SQL Conformance: "One way the JDBC API deals with this problem is to allow any query string to be passed through to an underlying DBMS driver. This means that an application is free to use as much SQL functionality as desired, but it runs the risk of receiving an error on some DBMSs. In fact, an application query may be something other than SQL, or it may be a specialized derivative of SQL designed for specific DBMSs (for document or image queries, for example)."
Clearly this means either giving up portability or checking the DBMS currently used before invoking specific operations.
75. What is DML?
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.
76. What is the significance of DataBaseMetaData.tableIndexStatistics? How to obtain and use it?
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.
77. What types of DataSource objects are specified in the Optional Package?
* 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.
78. What is a JDBC 2.0 DataSource?
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.
79. 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?
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.
80. Which Java and java.sql data types map to my specific database types?
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.