JDBC Interview Questions with Answers Page IV

From freshersonline.com

Interview Question Home

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

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

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

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

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

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

7. When an SQL select statement doesn't return any rows, is an SQLException thrown?

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.

8. Why should I consider optimistic versus pessimistic approaches to database updates?

In a modern database, possibly the two most important issues are data integrity and concurrency ( multiple users have access to and can update the data

). Either approach can be appropriate, depending on the application, but it is important to be aware of possible consequences to avoid being blindsided.

A pessimistic approach, with locks, is usually seen as good for data integrity, although it can be bad for concurrency, especially the longer a lock is

held. In particular, it guarantees against 'lost updates' - defined as an update performed by one process between the time of access and update by

another process, which overwrites the interim update. However, other users are blocked from updating the data and possibly reading it as well if the read

access also tries to acquire a lock. A notorious problem can arise when a user accesses data for update and then doesn't act on it for a period of time.

Another situation that occurred with one of my clients is that a batch ( non-interactive ) process may need to update data while an interactive user has

an update lock on the same data. In that case, data integrity goes out the window and, depending on how the application is written, more problems may be

introduced. ( No, we did not write the interactive update program and yes, we had recovery procedures in place. )An optimstic approach can alleviate lock

concurrency problems, but requires more code and care for integrity. The "optimistic" definition usually says that expectations of update clashes are

rare, but I view them as normal occurrances in a heavily used database. The basics are that any changes between time of access and time of update must be

detected and taken into account. This is often done by comparing timestamps, but one must be sure that the timestamp is always changed for an update and,

of course, that the table contains a timestamp column. A more involved, but more complete method involves saving the original columns and using them in

the 'Where' clause of the Update statement. If the update fails, the data has changed and the latest data should be reaccessed.

9. What is optimistic concurrency?

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.

10. What is pessimistic concurrency?

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.

11. 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?

Yes. Use ResultSet.getStatement(). From the resulting Statement you can use Statement.getConnection().

12. What is the most efficient method of replicating data between databases using JDBC?

Within Java, the most efficient method would be, opening connections using the JDBC and inserting or updating the records from one database to the other

database, but it depends upon the databases being replicated. If you are using Oracle databases, it has standard methods for replication, and you do not

need the JDBC for the replication. Use snapshots like updateable and read-only.

There are different kind of replication. Let us consider the most widely used ones:

A) One Master - One slave

I) If there is not a significant difference between the structure of the database tables, the following method would be useful.

FromDatabase=A; ToDatabase=B

1) Open JDBC connections between the databases A and B.

2) Read a record (RA ) from A using an SQL query.

3) Store the values in the local variables in the Java program.

4) Insert the record in B if PK does not exist for the record RA in B.

5) If the PK exists in B, update the record in B.

6) Repeat the steps 2-5 'til all the records are read by the query.

7) If there are multiple tables to be replicated, repeat steps 2-7 using the different queries.

II)If there is significant difference between the structure of the database tables, the following method would be useful.

FromDatabase=A; ToDatabase=B

1) Open the JDBC connections to the databases A.

2) Read a record ( RA ) from A using an SQL query.

3) Write the output to an XML file-XMLA, according to the DTD for the records for the database A structure.

4) Repeat steps 2 & 3 'til all the records are written to XMLA.

5) If there are more queries, repeat steps repeat steps from 2-4 and write the records to the different entities in the XML file.

6) Transform the XMLA file using the XSL and XSLT to the format useful for the database B and write to the XML file-XMLB.

7) Open the second JDBC connection to the Database B.

8) Read the XMLB file, one record at a time.

9) Insert the record in B if PK does not exist for the record RA in B.

10) If the PK exists in B, update the record in B.

B) One Master - Multiple slaves

The difference here is to open multiple JDBC connections to write to the different databases one record at a time.

C) Multiple Masters:

For multiple masters, use timestamps to compare the times of the records to find out which is the latest record when a record is found in all the master

databases. Alternatively, create a column to store the time and date a record is inserted or updated. When records are deleted, record the event in a log

file along with the PK.

Prepared statements and batch updates should be used wherever possible in this scenario.

13. How can I tell if my JDBC driver normalizes java.sql.Date and java.sql.Time objects?

To actually determine the values, the objects must be converted to a java.util.Date and examined. See What does normalization mean for java.sql.Date and

java.sql.Time? for the definition of normalization. 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

14. What is the difference between setMaxRows(int) and SetFetchSize(int)? Can either reduce processing time?

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.Since we're talking about interfaces, be careful because the implementation of drivers is often different from database to database and, in some

cases, may not be implemented or have a null implementation. Always refer to the driver documentation.

15. What is JDO?

JDO provides for the transparent persistence of data in a data store agnostic manner, supporting object, hierarchical, as well as relational stores.

16. When I intersperse table creation or other DDL statements with DML statements ....

When I intersperse table creation or other DDL statements with DML statements, I have a problem with a transaction being commited before I want it to be.

Everything ( commit and rollback ) works fine as long as I don't create another table. How can I resolve the issue?

While the questioner found a partially workable method for his particular DBMS, as mentioned in the section on transactions in my JDBC 2.0 Fundamentals

Short Course:

DDL statements in a transaction may be ignored or may cause a commit to occur. The behavior is DBMS dependent and can be discovered by use of

DatabaseMetaData.dataDefinitionCausesTransactionCommit() and DatabaseMetaData.dataDefinitionIgnoredInTransactions(). One way to avoid unexpected results

is to separate DML and DDL transactions.The only generally effective way to "rollback" table creation is to delete the table.

17. What's the best way, in terms of performance, to do multiple insert/update statements, a PreparedStatement or Batch Updates?

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.

18. I need to have result set on a page where the user can sort on the column headers. Any ideas?

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.

19. What are the components of the JDBC URL for Oracle's "thin" driver and how do I use them?

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.

20. Why doesn't JDBC accept URLs instead of a URL string?

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.

21. What JDBC objects generate SQLWarnings?

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.

22. What's the fastest way to normalize a Time object?

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.

23. What does normalization mean for java.sql.Date and java.sql.Time?

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.

24. How do I create a java.sql.Date object?

java.sql.Date descends from java.util.Date, but uses only the year, month and day values. There are two methods to create a Date object. The first uses a

Calendar object, setting the year, month and day portions to the desired values. The hour, minute, second and millisecond values must be set to zero. At

that point, Calendar.getTime().getTime() is invoked to get the java.util.Date milliseconds. That value is then passed to a java.sql.Date constructor:

Calendar cal = Calendar.getInstance();

// set Date portion to January 1, 1970

cal.set( cal.YEAR, 1970 );

cal.set( cal.MONTH, cal.JANUARY );

cal.set( cal.DATE, 1 );

cal.set( cal.HOUR_OF_DAY, 0 );

cal.set( cal.MINUTE, 0 );

cal.set( cal.SECOND, 0 );

cal.set( cal.MILLISECOND, 0 );

java.sql.Date jsqlD =

new java.sql.Date( cal.getTime().getTime() );

The second method is java.sql.Date's valueOf method. valueOf() accepts a String, which must be the date in JDBC time escape format - "yyyy-mm-dd". For

example,

25. How do I create a java.sql.Time object?

java.sql.Time descends from java.util.Date, but uses only the hour, minute and second values. There are two methods to create a Time object. The first

uses a Calendar object, setting the year, month and day portions to January 1, 1970, which is Java's zero epoch. The millisecond value must also be set

to zero. At that point, Calendar.getTime().getTime() is invoked to get the time in milliseconds. That value is then passed to a Time constructor:

Calendar cal = Calendar.getInstance();

// set Date portion to January 1, 1970

cal.set( cal.YEAR, 1970 );

cal.set( cal.MONTH, cal.JANUARY );

cal.set( cal.DATE, 1 );

cal.set( cal.MILLISECOND, 0 );

java.sql.Time jsqlT =

new java.sql.Time( cal.getTime().getTime() );

The second method is Time's valueOf method. valueOf() accepts a String, which must be the time in JDBC time escape format - "hh:mm:ss". For example,

java.sql.Time jsqlT = java.sql.Time.valueOf( "18:05:00" );

creates a Time object representing 6:05 p.m. To use this method with a Calendar object, use:

java.sql.Time jsqlT = java.sql.Time.valueOf(

cal.get(cal.HOUR_OF_DAY) + ":" +

cal.get(cal.MINUTE) + ":" +

cal.get(cal.SECOND) );

which produces a Time object with the same value as the first example.

java.sql.Date jsqlD = java.sql.Date.valueOf( "2010-01-31" );

creates a Date object representing January 31, 2010. To use this method with a Calendar object, use:

java.sql.Date jsqlD = java.sql.Date.valueOf(

cal.get(cal.YEAR) + ":" +

cal.get(cal.MONTH) + ":" +

cal.get(cal.DATE) );

which produces a Date object with the same value as the first example.