Saturday, December 3, 2011

JDBC Interview Questions & Answers for Java Developers – Part 2


This post is an extension to my previous post JDBC Interview Questions & Answers for Java Developers – Part 1. We have compiled few more JDBC questions. Read these interview questions.

What's the difference between a primary key and a unique key?

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

How to make a query?

Create a Statement object and calls the Statement.executeQuery method to select data from the database. The result of the query are returned in a ResultSet object.

Statement stmt = con.createStatement();
ResultSet result = stmt.executeQuery("SELECT data FROM table");

How can you retrieve data from the ResultSet?

Use get methods to retrieve data from returned ResultSet object.

Statement stmt = null;
String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES";
try {
       stmt = con.createStatement();
       ResultSet rs = stmt.executeQuery(query);
       while (rs.next()) {
        // fetch data from resultset using get methods
        String coffeeName = rs.getString("COF_NAME");
       int supplierID = rs.getInt("SUP_ID");
        float price = rs.getFloat("PRICE");
        int sales = rs.getInt("SALES");
        int total = rs.getInt("TOTAL");
       }
 } catch (SQLException e ) {
      JDBCTutorialUtilities.printSQLException(e);
 } finally {
      if (stmt != null) { stmt.close(); }
 }

The method getString is invoked on the ResultSet object rs , so getString will retrieve (get) the value stored in the column COF_NAME in the current row of rs.

How to navigate the ResultSet?

By default the result set cursor points to the row before the first row of the result set. A call to next() retrieves the first result set row. The cursor can also be moved by calling one of the following ResultSet methods:

beforeFirst(): Puts cursor before the first row of the result set.

first(): Puts cursor on the first row of the result set.

last(): Puts cursor before the last row of the result set.

afterLast(): Puts cursor beyond last row of the result set.

absolute(pos): Puts cursor at the row number position where absolute(1) is the first row and absolute(-1) is the last row.

relative(pos): Puts cursor at a row relative to its current position where relative(1) moves row cursor one row forward.

How to update a ResultSet?

An updatable result set allows modification to data in a table through the result set.  You can update a value in a result set by calling the ResultSet Update Methods on the row where the cursor is positioned. The type value here is the same used when retrieving a value from the result set, for example, updateString updates a String value and updateDouble updates a double value in the result set.

rs.updateDouble("balance", rs.getDouble("balance") - 5.00);

The update applies only to the result set until the call to rs.updateRow(), which updates the underlying database.

To delete the current row, use rs.deleteRow().
To insert a new row, use rs.moveToInsertRow().

What are the Large object types supported by Oracle?

Blob and Clob.

How can you use PreparedStatement?

The advantage to this is that in most cases, this SQL statement will be sent to the DBMS right away, where it will be compiled. As a result, the PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement 's SQL statement without having to compile it first.

PreparedStatement updateSales = con.prepareStatement( "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");

Use PreparedStatement object setter methods to set the values for place holders.

How to set a scroll type?

Both Statements and PreparedStatements have an additional constructor that accepts a scroll type and an update type parameter. The scroll type value can be one of the following values:

ResultSet.TYPE_FORWARD_ONLY:  Default behavior in JDBC 1.0, application can only call next() on the result set.

ResultSet.SCROLL_SENSITIVE: ResultSet is fully navigable and updates are reflected in the result set as they occur.

ResultSet.SCROLL_INSENSITIVE: Result set is fully navigable, but updates are only visible after the result set is closed. You need to create a new result set to see the results.

How to set update type parameter?

To set update type parameter in the constructors of Statements and PreparedStatements, you may use

ResultSet.CONCUR_READ_ONLY: The result set is read only.

ResultSet.CONCUR_UPDATABLE: The result set can be updated.

How to do a batch job?

By default, every JDBC statement is sent to the database individually. To send multiple statements at one time, use addBatch() method to append statements to the original statement and call executeBatch() method to submit entire statement.

Statement stmt = con.createStatement();
stmt.addBatch("update registration set balance=balance-5.00 where theuser="+theuser);
stmt.addBatch("insert into auctionitems(description, startprice) " +
                        "values("+description+","+startprice+")");
...
int[] results = stmt.executeBatch();

The return result of the addBatch() method is an array of row counts affected for each statement executed in the batch job. If a problem occurred, a java.sql.BatchUpdateException is thrown. An incomplete array of row counts can be obtained from BatchUpdateException by calling its getUpdateCounts() method.

How to use meta data to check a column type?

Use getMetaData().getColumnType() method to check data type. For example to retrieve an Integer, you may check it first:
 
int count=0;
Connection con=getConnection();
Statement stmt= con.createStatement();
stmt.executeQuery("select counter from aTable");
ResultSet rs = stmt.getResultSet();
if(rs.next()) {
    if(rs.getMetaData().getColumnType(1) == Types.INTEGER) {
    Integer i = (Integer)rs.getObject(1);
    count = i.intValue();
    }
rs.close();

Why cannot java.util.Date match with java.sql.Date?

Because java.util.Date represents both date and time. SQL has three types to represent date and time.
java.sql.Date -- (00/00/00)
java.sql.Time -- (00:00:00)
java.sql.Timestamp -- in nanoseconds
Note that they are subclasses of java.util.Date.

How to convert java.util.Date value to java.sql.Date?

Use the code below:

Calendar currenttime=Calendar.getInstance();
java.sql.Date startdate= new java.sql.Date((currenttime.getTime()).getTime());

OR

SimpleDateFormat template = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date enddate = new java.util.Date("10/31/99");
java.sql.Date sqlDate = java.sql.Date.valueOf(template.format(enddate));

What does setAutoCommit do?

When a connection is created, it is in auto-commit mode. This means that each individual SQL statement is treated as a transaction and will be automatically committed right after it is executed. The way to allow two or more statements to be grouped into a transaction is to disable auto-commit mode:

con.setAutoCommit(false);

Once auto-commit mode is disabled, no SQL statements will be committed until you call the method commit explicitly.

con.setAutoCommit(false);
PreparedStatement updateSales =
con.prepareStatement( "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");
updateSales.setInt(1, 50);  updateSales.setString(2, "Colombian");
updateSales.executeUpdate();
PreparedStatement updateTotal =
con.prepareStatement("UPDATE COFFEES SET TOTAL=TOTAL + ? WHERE COF_NAME LIKE ?");
updateTotal.setInt(1, 50);
updateTotal.setString(2, "Colombian"); updateTotal.executeUpdate();
con.commit();
con.setAutoCommit(true);

What is the difference among "dropping a table", "truncating a table" and "deleting all records" from a table?

Dropping:  Table structure + Data are deleted. Invalidates the dependent objects and drops the indexes

Truncating:  Data alone deleted. Performs an automatic commit and faster than delete.

Delete: Data alone deleted. Doesn’t perform automatic commit

What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?

Cursors allow row-by-row prcessing of the resultsets.

Types of cursors: Static, Dynamic, Forward-only, Keyset-driven.

Disadvantages of cursors:

A) In cursors each time you fetch a new row it does a round trip with the database thereby increase network use and time.

B) There is also restriction on select statements that can be used in cursors

C) It also uses more resources and temporary memory

Cursors can be avoided with the help of careful implementation of while loop and other set based operations.

What are triggers? How to invoke a trigger on demand?

Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.

Triggers can't be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.

Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.

That's all folks. Let me know your comments. Continue Reading !


1 Responses to “JDBC Interview Questions & Answers for Java Developers – Part 2”

Javin @ Struts interview questions said...
December 14, 2011 at 5:07 PM

Part 2 is even better than part 1 , keep it up. I have also shared some Interview questions on my blog you may find useful:

Top 20 core java interview questions and answers
3 ways to resolve NoClassDefFoundError in Java


Post a Comment

© 2011 a2ztechguide.com • All rights reserved.
Blogger Template by Bloggermint