Clouds
Home

J2EE Overview

J2EE Servlet 1

J2EE Servlet 2

J2EE JSP

J2EE JDBC

J2EE JNDI LDAP

J2EE RMI

J2EE EJB

J2EE JMS

J2EE XML

J2EE STRUTS

J2EE SERVERS

J2EE Best Practices

J2EE Logging

J2EE Testing

J2EE Deployment

J2EE Development Process

JDBC Interview Questions


What is JDBC ? How do you connect to a database ? Have you used a Data Access Object (i.e. DAO) pattern ?

JDBC stands for Java Database Connectivity. It is an API which provides easy connection to a wide range of databases. To connect to a database we need to load the appropriate driver and then request for a connection object. The Class.forName(..) will load the driver and register it with the DriverManager

Class.forName("oracle.jdbc.driver.OracleDriver"); //dynamic class loading
String url = jdbc:oracle:thin:@hostname:1526:myDB;
Connection myConnection = DriverManager.getConnection(url, "username", "password");


The DataSource interface provides an alternative to the DriverManager for making a connection. DataSource makes the code more portable than DriverManager because it works with JNDI and it is created, deployed and managed separately from the application that uses it. If the DataSource location changes, then there is no need to change the code but change the configuration properties in the server. This makes your application code easier to maintain. DataSource allows the use of connection pooling and support for distributed transactions. A DataSource is not only a database but also can be a file or a spreadsheet. A DataSource object can be bound to JNDI and an application can retrieve and use it to make a connection to the database. J2EE application servers provide tools to define your DataSource with a JNDI name. When the server starts it loads all the DataSources into the application server’s JNDI service.
DataSource configuration properties are shown below:
  • JNDI Name -> jdbc/myDataSource
  • URL -> jdbc:oracle:thin:@hostname:1526:myDB
  • UserName, Password
  • Implementation classname -> oracle.jdbc.pool.OracleConnectionPoolDataSource
  • Classpath -> ora_jdbc.jar
  • Connection pooling settings like -> minimum pool size, maximum pool size, connection timeout, statement cache size etc.
Once the DataSource has been set up, then you can get the connection object as follows:

Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("jdbc/myDataSource");
Connection myConnection = ds.getConnection("username","password");


Why should you prefer using DataSource ?

In a basic implementation a Connection obtained from a DataSource and a DriverManager are identical. But the J2EE best practice is to use DataSource because of its portability, better performance due to pooling of valuable resources and the J2EE standard requires that applications use the container’s resource management facilities to obtain connections to resources. Every major web application container provides pooled database connection management as part of its resource management framework. JDBC architecture decouples an abstraction from its implementation so that the implementation can vary independent of the abstraction. This is an example of the bridge design pattern. The JDBC API provides the abstraction and the JDBC drivers provide the implementation. New drivers can be plugged-in to the JDBC API without changing the client code.

Have you used a Data Access Object (DAO) pattern? Why is it a best practice to use a DAO pattern Design Pattern ?

A DAO class provides access to a particular data resource in the data tier (e.g. relational database, XML , mainframe etc) without coupling the resource’s API to the business logic in the business tier. For example you may have a EmployeeBO business object class access all of its employees in the database using a DAO interface EmployeeDAO. If your data resource change from a database to a Mainframe system, then reimplementing EmployeeDAO for a different data access mechanism (to use a mainframe Connector) would have little or no impact on any classes like EmployeeBO that uses EmployeeDAO because only the implementation (e.g. EmployeeDAOImpl) would change but the interface remains the same. All the classes that use the DAO should code to interface not implementation. If you happen to use the popular Spring framework, then you can inject your DAO classes into your Business Object classes. Spring framework promotes the design principle of "code to interface not to implementation".

What are the best practices relating to exception handling to make your DAOs more robust and maintainable ?

  • If you catch an exception in your DAO code, never ignore it or swallow it because ignored exceptions are hard to troubleshoot. DAO class methods should throw checked exceptions only if the caller can reasonably recover from the exception or reasonably handle it If the caller cannot handle the exception in a meaningful way, consider throwing a runtime (i.e. unchecked) exception. For example Hibernate 3 exceptions are all runtime exceptions.
  • DAO methods should not throw low level JDBC exceptions like java.sql.SQLException. A DAO should encapsulate JDBC rather than expose it to rest of the application. Use chained exceptions to translate low-level exceptions into high-level checked exceptions or runtime exceptions. DAO methods should not throw java.lang.Exception because it is too generic and does not convey any underlying problem.
  • Log your exceptions, configuration information, query parameters etc.

What are JDBC Statements ? What are different types of statements ? How can you create them ?

A statement object is responsible for sending the SQL statements to the Database. Statement objects are created from the connection object and then executed.

Statement stmt = myConnection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT id, name FROM myTable where id =1245"); //to read
or
stmt.executeUpdate("INSERT INTO (field1,field2) values (1,3)");//to insert/update/delete/create


The types of statements are:
  • Statement (regular statement as shown above)
  • PreparedStatement (more efficient than statement due to pre-compilation of SQL)
  • CallableStatement (to call stored procedures on the database)
To use prepared statement:

PreparedStatement prepStmt = myConnection.prepareStatement("SELECT id, name FROM myTable where id = ? ");
prepStmt.setInt(1, 1245);


Callable statements are used for calling stored procedures.

CallableStatement calStmt = myConnection.prepareCall("{call PROC_SHOWMYBOOKS}");
ResultSet rs = cs.executeQuery();


What is a Transaction ?

A transaction is a set of operations that should be completed as a unit. If one operation fails then all the other operations fail as well.
For example if you transfer funds between two accounts there will be two operations in the set
  • Withdraw money from one account.
  • Deposit money into other account.
These two operations should be completed as a single unit. Otherwise your money will get lost if the withdrawal is successful and the deposit fails. There are four characteristics (ACID properties) for a Transaction.
  • Atomicity: All the individual operations should either complete or fail.
  • Consistency: The design of the transaction should update the database correctly.
  • Isolation: Prevents data being corrupted by concurrent access by two different sources. It keeps transactions isolated or separated from each other until they are finished.
  • Durability: Ensures that the database is definitely updated once the Transaction is completed.
Transactions maintain data integrity. A transaction has a beginning and an end like everything else in life. The setAutocommit(...), commit() and rollback() are used for marking the transactions (known as transaction demarcation). 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 immediately after it is executed. The way to allow two or more statements to be grouped into a transaction is to disable auto-commit mode:

try{
    Connection myConnection = dataSource.getConnection();
    // set autoCommit to false
    myConnection.setAutoCommit(false);
    withdrawMoneyFromFirstAccount(.............); //operation 1
    depositMoneyIntoSecondAccount(.............); //operation 2
    myConnection .commit();
}
catch(Exception sqle){
    try{
    myConnection .rollback();
    }catch( Exception e){}
}
finally{
    try{if( conn != null) {conn.close();}} catch( Exception e) {}
}


The above code ensures that both operation 1 and operation 2 succeed or fail as an atomic unit and consequently leaves the database in a consistent state. Also turning auto-commit off will provide better performance.

What is transaction demarcation? What are the different ways of defining transactional boundaries ?

Data Access Objects (DAO) are transactional objects. Each operation associated with CRUD operations like Create, Update and/or Delete operations should be associated with transactions. Transaction demarcation is the manner in which transaction boundaries are defined. There are two approaches for transaction demarcation.

Declarative transaction demarcation

The programmer declaratively specifies the transaction boundaries using transaction attributes for an EJB via ejb-jar.xml deployment descriptor.
Note: Spring framework has support for declarative transaction demarcation by specifying transaction attributes via Spring config files. If you choose Spring framework to mark the transaction boundaries then you need to turn off transaction demarcation in your EJB by:

<trans-attribute>NotSupported</trans-attribute>

Programmatic transaction demarcation

The programmer is responsible for coding transaction logic as shown above. The application controls the transaction via an API like JDBC API, JTA API, Hibernate API etc. JDBC transactions are controlled using the java.sql.Connection object. There are two modes: auto-commit and manual commit. Following methods are provided in the JDBC API via non-XA java.sql.Connection class for programmatically controlling transactions:

public void setAutoCommit(booleanmode);
public boolean getAutoCommit();
public void commit();
public void rollback();


For XA-Connections use the following methods on javax.transaction.UserTransaction.

public void begin();
public void commit();
public void rollback();
public int getStatus();
public void setRollbackOnly();
public void setTransactionTimeOut(int)


How are these declarative transactions know when to rollback ?

  • EJBs: When the EJB container manages the transaction, it is automatically rolled back when a System Exception occurs. This is possible because the container can intercept "SystemException". However when an Application Exception occurs, the container does not intercept it and therefore leaves it to the code to roll back using ctx.setRollbackOnly().
  • Spring Framework: Transaction declaration format is: PROPAGATION_NAME,ISOLATION_NAME,readOnly,timeout_NNNN,+CheckedException1, -CheckedException2
By default transactions are rolled-back on java.lang.RuntimeException. You can control when transactions are committed and rolled back with the "+" or "-" prefixes in the exception declaration. "+" means commit on exception (You can even force it on RuntimeException) and "-" means rollback on exception. You can specify multiple rules for rollback as "," separated.
For example: Following declaration will rollback transactions on RunTime exceptions and MyCheckedException, which is a checked exception. PROPAGATION_REQUIRED,-MyCheckedException

What is a distributed (aka JTA/XA) transaction ? How does it differ from a local transaction ?

There are two types of transactions:

Local transaction:

Transaction is within the same database. As we have seen above, with JDBC transaction demarcation, you can combine multiple SQL statements into a single transaction, but the transactional scope is limited to a single database connection. A JDBC transaction cannot span multiple databases.

Distributed Transaction (aka Global Transaction, JTA/XA transaction):

The transactions that constitute a distributed transaction might be in the same database, but more typically are in different databases and often in different locations. For example A distributed transaction might consist of money being transferred from an account in one bank to an account in another bank. You would not want either transaction committed without assurance that both will complete successfully. The Java Transaction API (JTA) and its sibling Java Transaction Service (JTS), provide distributed transaction services for the J2EE platform. A distributed transaction (aka JTA/XA transaction) involves a transaction manager and one or more resource managers. A resource manager represents any kind of data store. The transaction manager is responsible for coordinating communication between your application and all the resource managers. A transaction manager decides whether to commit or rollback at the end of the transaction in a distributed system. A resource manager is responsible for controlling of accessing the common resources in the distributed system.

What is two-phase commit ?

A two-phase commit is an approach for committing a distributed transaction in 2 phases.

What do you understand by JTA and JTS ?

JTA is a high level transaction interface which allows transaction demarcation in a manner that is independent of the transaction manager implementation. JTS specifies the implementation of a Transaction Manager which supports the JTA. The code developed by developers does not call the JTS methods directly, but only invokes the JTA methods. The JTA internally invokes the JTS routines.

What is a XA resource ?

The XA specification defines how an application program uses a transaction manager to coordinate distributed transactions across multiple resource managers. Any resource manager that adheres to XA specification can participate in a transaction coordinated by an XA-compliant transaction manager. JTA transaction demarcation requires a JDBC driver that implements XA interfaces like javax.sql.-XADatasource, javax.sql.XAConnection and javax.sql.XAResource. A driver that implements these interfaces will be able to participate in JTA transactions. You will also require to set up the XADatasource using your application server specific configuration files, but once you get a handle on the DataSource via JNDI lookup, you can get a XA connection via javax.sql.DataSource.getConnection() in a similar manner you get a non-XA connections. XA connections are different from non-XA connections and do not support JDBC’s auto-commit feature. You cannot also use the commit(), rollback() methods on the java.sql.Connection class for the XA connections. A J2EE component can begin a transaction programmatically using javax.transaction.UserTransaction interface or it can also be started declaratively by the EJB container if an EJB bean uses container managed transaction. For explicit (i.e. programmatic) JTA/XA transaction you should use the UserTransaction.begin(), UserTransaction.commit() and UserTransaction.rollback() methods. For example:

// programmatic JTA transaction
InitialContext ctx = new InitialContext();
UserTransaction utx = (UserTransaction)ctx.lookup("java:comp/UserTransaction");
try {
    //...
    utx.begin();
    //...
    DataSource ds = getXADatasource();
    Connection con = ds.getConnection(); // get a XAconnection.
    PreparedStatement pstmt = con.prepareStatement("UPDATE Employee emp where emp.id =?");
    pstmt.setInt(1, 12456);
    pstmt.executeUpdate();
    utx.commit();//transaction manager uses two-phase commit protocol to end transaction
}
catch(SQLException sqle){
    utx.rollback();
    throw new RuntimeException(sqle);
}
// for bean-managed EJB transaction demarcation
UserTransaction ut = ejbContext.getUserTransaction();


Why JTA transactions are more powerful than JDBC transactions ?

JTA transactions are more powerful than JDBC transaction because a JDBC transaction is limited to a single database whereas a JTA transaction can have multiple participants like:
  • JDBC connections.
  • JMS queues/topics.
  • Enterprise JavaBeans (EJBs).
  • Resource adapters that comply with J2EE Connector Architecture (JCA) specification.

What is J2EE Connector architecture (JCA) ? How does it differ from JDBC ?

JCA is a Java based technology solution for connecting application servers and Enterprise Information Systems (EIS) like Enterprise Resource Planning (ERP) systems, Customer Relationship Management) (CRM) systems etc as part of Enterprise Application Integration (EAI). The JCA API is used by J2EE tool developers and system integrators to create resource adapters While JDBC is specifically used to connect J2EE applications to databases, JCA is a more generic architecture for connecting to legacy systems (including databases).

How would you send a JMS message to a JMS queue/topic and update a database table within the same transaction ?

Using JTA/XA transaction. A J2EE application using EJB containers can send or receive messages from one or more JMS destinations and update data in one or more databases in a single transaction. The J2EE architecture allows updates of data at multiple sites (i.e. more than one application servers) to be performed in a single transaction.

What are the considerations for a programmatic transaction control within a Servlet/JSP? Can a transaction span across multiple web requests ?

Web components like Servlets/JSPs may only start a transaction in its service() method and a transaction started in its service method must be completed before the service() method completes. A transaction cannot span across multiple web requests. Some of the considerations are as follows:
  • JTA transactions should start and complete within the thread in which service() method is called and any additional threads created in the servlet should not try to start any JTA transaction.
  • JDBC connection objects should not be stored in static fields or instance fields (for multi-threaded model). JDBC connection objects should be acquired and released within the same invocation of the service() method.

    How will you control two concurrent transactions accessing a database ?

    You can use isolation levels. An isolation level defines how concurrent transactions accessing a relational database are isolated from each other for read purpose. Refer Q72 in Enterprise section. These isolation levels can prevent one or more of the phenomena that happen during concurrent transactions:
  • Dirty reads: A transaction reads uncommitted changes from another transaction.
  • Nonrepeatable reads: A transaction reads a row in a database table, a second transaction changes the same row and the first transaction re-reads the row and gets a different value.
  • Phantom reads: A transaction executes a query, returning a set of rows that satisfies a search condition and a second transaction inserts another row and the first re-executes the same query and get an additional record returned.

    What is the difference between JDBC-1.0 and JDBC-2.0 ?

    JDBC 1.0

    • With JDBC-1.0 the ResultSet functionality was limited. There was no support for updates of any kind and scrolling through the ResultSets was forward only (no going back)
    • With JDBC-1.0 the statement objects submits updates to the database individually within same or separate transactions. This is very inefficient when large amounts of data need to be updated.

    JDBC 2.0

    • With JDBC 2.0 ResultSets are updateable and also you can move forward and backward. Example This example creates an updateable and scroll-sensitive ResultSet

      Statement stmt = myConnection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATEABLE)

    • With JDBC-2.0 statement objects can be grouped into a batch and executed at once. You call addBatch() multiple times to create your batch and then you call executeBatch() to send the SQL statements off to database to be executed as a batch (this minimizes the network overhead).
      Example

      Statement stmt = myConnection.createStatement();
      stmt.addBatch("INSERT INTO myTable1 VALUES (1,"ABC")");
      stmt.addBatch("INSERT INTO myTable1 VALUES (2,"DEF")");
      stmt.addBatch("INSERT INTO myTable1 VALUES (3,"XYZ")");
      ...
      int[] countInserts = stmt.executeBatch();


    • The JDBC-2.0 optional package provides a RowSet interface, which extends the ResultSet. One of the implementations of the RowSet is the CachedRowSet, which can be considered as a disconnected ResultSet.

    How to avoid the "running out of cursors" problem ?

    A database can run out of cursors if the connection is not closed properly or the DBA has not allocated enough cursors. In a Java code it is essential that we close all the valuable resources in a try{} and finally{} block. The finally{} block is always executed even if there is an exception thrown from the catch {} block. So the resources like connections and statements should be closed in a finally {} block.

    What is the difference between statements and prepared statements ?

  • Prepared statements offer better performance, as they are pre-compiled. Prepared statements reuse the same execution plan for different arguments rather than creating a new execution plan every time. Prepared statements use bind arguments, which are sent to the database engine. This allows mapping different requests with same prepared statement but different arguments to execute the same execution plan.
  • Prepared statements are more secure because they use bind variables, which can prevent SQL injection attack. The most common type of SQL injection attack is SQL manipulation. The attacker attempts to modify the SQL statement by adding elements to the WHERE clause or extending the SQL with the set operators like UNION, INTERSECT etc. Example Let us look at the following SQL: SELECT * FROM users where username='bob' AND password='xyfdsw'; The attacker can manipulate the SQL as follows SELECT * FROM users where username='bob' AND password='xyfdsw' OR 'a' = 'a' ; The above "WHERE" clause is always true because of the operator precedence. The PreparedStatement can prevent this by using bind variables:

    String strSQL = SELECT * FROM users where username=? AND password=?);
    PreparedStatement pstmt = myConnection.prepareStatement(strSQL);
    pstmt.setString(1,"bob");
    pstmt.setString(2,"xyfdsw");
    pstmt.execute();


    Explain differences among java.util.Date, java.sql.Date, java.sql.Time, and java.sql.Timestamp ?

    As shown below all the sql Date classes extend the util Date class.
    • java.util.Date - class supports both the Date (i.e. year/month/date etc) and the Time (hour, minute, second, and millisecond) components.
    • java.sql.Date - class supports only the Date (i.e. year/month/date etc) component. The hours, minutes, seconds and milliseconds of the Time component will be set to zero in the particular time zone with which the instance is associated.
    • java.sql.Time - class supports only Time (i.e. hour, minute, second, and millisecond) component. The date components should be set to the "zero epoch" value of January 1, 1970 and should not be accessed.
    • java.sql.TimeStamp : class supports both Date (i.e. year/month/date etc) and the Time (hour, minute, second, millisecond and nanosecond) components.
    Note: the subtle difference between java.util.Date and java.sql.Date. The java.sql.Date does not have a time component. If you need both date and time, then should use either java.util.Date or java.sql.TimeStamp. To keep track of time Java counts the number of milliseconds from January 1, 1970 and stores it as a long value in java.util.Date class. The GregorianCalendar class provides us a way to represent an arbitrary date. The GregorianCalendar class also provides methods for manipulating dates (date arithmetic, date comparisons etc).

    JNDI and LDAP Interview Questions >>>




    Home Clouds