JDBC

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

 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.



What is DDL? 



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 DBMSengines allow DDL to be used dynamically ( and available to JDBC ), it is often not supported in transactions.

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.

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

 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.

 Can I ensure that my app has the latest data?

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.

 Can a single thread open up mutliple connections simultaneously for the same database and for same table?

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.

 When I create multiple Statements on my Connection, only the current Statement appears to be executed. What's the problem? 

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.

How do I convert a java.sql.Timestamp to a java.util.Date?

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 )); 

What areas should I focus on for the best performance in a JDBC application? 

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.

Can I reuse a Statement or must I create a new one for each query? 

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.

Are prepared statements faster because they are compiled? if so, where and when are they compiled? 

Prepared Statements aren't actually compiled, but they are bound by theJDBC 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. For additional information on Prepared Statement performance and binding see JDBC Performance Tips on IBM'swebsite.

 Q What is cold backup, hot backup, warm backup recovery?

A : Cold backup means all these files must be backed up at the same time, before the database is 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

How many Rowset Interfaces  there in Jdbc?


Jdbc provided five types of RowSet interfaces
They are

• JdbcRowSet
• CachedRowSet
• WebRowSet
• JoinRowSet
• FilteredRowSet

Kinds of RowSet Objects

A RowSet object is considered either connected or disconnected. A connected RowSet object uses a JDBC driver to make a connection to a relational database and maintains that connection throughout its life span. A disconnected RowSet object makes a connection to a data source only to read in data from a ResultSet object or to write data back to the data source. After reading data from or writing data to its data source, the RowSet object disconnects from it, thus becoming "disconnected." During much of its life span, a disconnected RowSet object has no connection to its data source and operates independently. The next two sections tell you what being connected or disconnected means in terms of what a RowSet object can do.

Connected RowSet Objects

Only one of the standard RowSet implementations is a connected RowSet object: JdbcRowSet. Always being connected to a database, a JdbcRowSet object is most similar to a ResultSet object and is often used as a wrapper to make an otherwise non-scrollable and read-only ResultSet object scrollable and updatable.
As a JavaBeans component, a JdbcRowSet object can be used, for example, in a GUI tool to select a JDBC driver. A JdbcRowSet object can be used this way because it is effectively a wrapper for the driver that obtained its connection to the database.

Disconnected RowSet Objects

The other four implementations are disconnected RowSet implementations. Disconnected RowSet objects have all the capabilities of connected RowSet objects plus they have the additional capabilities available only to disconnected RowSet objects. For example, not having to maintain a connection to a data source makes disconnected RowSetobjects far more lightweight than a JdbcRowSet object or a ResultSet object. Disconnected RowSet objects are also serializable, and the combination of being both serializable and lightweight makes them ideal for sending data over a network. They can even be used for sending data to thin clients such as PDAs and mobile phones.
The CachedRowSet interface defines the basic capabilities available to all disconnected RowSet objects. The other three are extensions of the CachedRowSet interface, which provide more specialized capabilities. The following information shows how they are related:
A CachedRowSet object has all the capabilities of a JdbcRowSet object plus it can also do the following:

• Obtain a connection to a data source and execute a query
• Read the data from the resulting ResultSet object and populate itself with that data
• Manipulate data and make changes to data while it is disconnected
• Reconnect to the data source to write changes back to it
• Check for conflicts with the data source and resolve those conflicts

A WebRowSet object has all the capabilities of a CachedRowSet object plus it can also do the following:

• Write itself as an XML document
• Read an XML document that describes a WebRowSet object

A JoinRowSet object has all the capabilities of a WebRowSet object (and therefore also those of a CachedRowSet object) plus it can also do the following:

• Form the equivalent of a SQL JOIN without having to connect to a data source

A FilteredRowSet object likewise has all the capabilities of a WebRowSet object (and therefore also a CachedRowSet object) plus it can also do the following:

• Apply filtering criteria so that only selected data is visible. This is equivalent to executing a query on a RowSet object without having to use a query language or connect to a data source.