JDBC Interview Questions-4

31) What separates one tier from another in the context of n-tiered architecture?

It depends on the application.

In a web application, for example, where tier 1 is a web-server, it may communicate with a tier 2 Application Server using RMI over IIOP, and subsequently tier 2 may communicate with tier 3 (data storage) using JDBC, etc.

Each of these tiers may be on separate physical machines or they may share the same box.

The important thing is the functionality at each tier.

• Tier 1 - Presentation - should be concerned mainly with display of user interfaces and/or data to the client browser or client system.
• Tier 2 - Application - should be concerned with business logic

Tier 3+ - Storage/Enterprise Systems - should be focused on data persistence and/or communication with other Enterprise Systems.

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

33) How can I insert multiple rows into a database in a single transaction?

//turn off the implicit commit

Connection.setAutoCommit(false);

//..your insert/update/delete goes here

Connection.Commit();

a new transaction is implicitly started.

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

35) What is SQL?

SQL is a standardized language used to create, manipulate, examine, and manage relational databases.

36) Is Class.forName(Drivername) the only way to load a driver? Can I instantiate the Driver and use the object of the driver?

Yes, you can use the driver directly. Create an instance of the driver and use the connect method from the Driver interface. Note that there may actually be two instances created, due to the expected standard behavior of drivers when the class is loaded.

37) What's new in JDBC 3.0?

Probably the new features of most interest are:

• Savepoint support
• Reuse of prepared statements by connection pools
• Retrieval of auto-generated keys
• Ability to have multiple open ResultSet objects
• Ability to make internal updates to the data in Blob and Clob objects
• Ability to Update columns containing BLOB, CLOB, ARRAY and REF types
• Both java.sql and javax.sql ( JDBC 2.0 Optional Package ) are expected to be included with J2SE 1.4.

38) Why do I get the message "No Suitable Driver"?

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

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

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