JDBC Interview Questions and Answers

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Page Numbers : 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Have a Question ? post your questions here. It will be answered as soon as possible.

Check Java Interview Questions for more Java Interview Questions with answers

Check Structs Interview Questions for more Structs Interview Questions with answers

Check Servlet Interview Questions for more Servlet Interview Questions with answers