JDBC Interview Questions

1 - What is JDBC?
JDBC stands for java database connectivity, It was developed by JavaSoft, a subsidiary of Sun Microsystems. it is a java api that is use to connect with database and execute sql statement. It is an application programming interface that defines how a java programmer can access the database from Java code.
2 - What are the new features added to JDBC 4.0?
Features added in jdbc 4.0 are:
  • Connection management enhancements
  • Support for RowId SQL type
  • SQL exception handling enhancements
  • Auto-loading of JDBC driver class
  • SQL XML support
  • DataSet implementation of SQL using annotations
3 - What are the main steps in java to make JDBC connectivity
There are several steps for jdbc connectivity:
  • Load the driver using Class.forName(driver name); as it communicate with the database.
  • Create Connection object which use to send sql statement and get results from database.
  • Create Statement object as this contain SQL query.
  • Execute Statement that return resultSet.
  • Process data in ResultSet.
  • Close the connections.
4 - What is the mean of "dirty read" in database?
As name suggest "reading data which may or may not be correct". when one transaction is executing and on other hand changing some field value at same time, some another transaction comes and read the change field value before first transaction commit or rollback the value, which cause unnecessary value for that field, this scenario is known as dirty read.
5 - What is two phase commit?
Two phase commit is used in distributed environment where multiple process take part in distributed transaction process. In other words if any transaction is executing and it will effect multiple database then two phase commit will be used to make all database synchronized with each other.
In two phase commit, commit and rollback is done in two phases
  • Commit request phase - In this phase main process take feedback of other processes, if all process complete successfully then they go to next phase and if any process is incomplete then rollback is performed.
  • Commit phase - if all the votes are yes then commit is done.
6 - What are different types of Statement?
Statement object is used to send SQL query to database and get result from database, there are 3 type of statement:
  • Statement - It is common and used for getting data from database, when using static SQL statement.
    Syntax:
    Statement statement = connection.createStatement( );
    ResultSet resultSet = statement.executeQuery();
  • PreparedStatement - It is another useful statement to get data from database, you can use placeholder in place of data in sql query
    Syntax:
    PreparedStatement pstmt = conn.prepareStatement("insert into table_name(name,age) values(?,?)");
    pstmt.setString(1,"abc");
    pstmt.setString(2,"xyz");
    int count = pstmt.executeUpdate();
  • Callable Statement - to access stored procedure use callable statement.
    Syntax:
    CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");
    ResultSet rs = cs.executeQuery();
7 - What is connection pooling?
Connection pooling mechanism is used to reuse the resource like Connection object. In this mechanism Connection object is stored in connection pool so that no need to create Connection object every time while integrating with database, instead of that you can use connection object stored in connection pool. This technique increase the application performance.
8 - What are the locking system in JDBC
If multiple user reading records simultaneously then there is no problem but if two or more user updating records simultaneously, in this case records will conflict and you need some locking technique that prevent from this situation. There are two type of locking in jdbc.
  • Optimistic Locking - it locks the record only when updates take place. Optimistic locking does not use lock while reading
  • Pessimistic locking - In this record are locked as you selects the row to update.
9 - Does the JDBC-ODBC Bridge support multiple concurrent open statements per connection?
No, You can open only one Statement object per connection while you are using the JDBC-ODBC Bridge.
10 - What are stored procedures? How is it useful?
Stored procedure is a set of SQL statement . It contain repeated code that increase reusability, reduce code complexity, it increase application security from sql injection and hacking. Maintenance work become easy using stored procedure.
Syntax:
CREATE PROCEDURE procedure_name
@param data_type = default_value,
@param data_type = default_value,
@param data_type = default_value
AS

BEGIN
DELETE FROM Employees
WHERE EmployeeId = @EmployeeId;
END
11 - What is a Transaction?
Transaction is a logical unit of work. It is used to provide data integrity, correct application semantics, and a consistent view of data during concurrent access. By default setAutoCommit() is true, for transaction management in java you need to do con.setAutoCommit() false because if one of the steps to the unit of work files fails, all the work done as part of that logical unit of work can be undone and the database can return to its previous state from where transaction began.
12 - What are the different types of ResultSet?
ResultSet contain result of SQl query. There are three type of ResultSet
  • Forward-only - This type only move forward and non-scrollable.
  • Scroll-insensitive - This is scrollable that mean that cursor can move in any direction and insensitive implies that any change to database will not show in the resultset while it open.
  • Scroll-sensitive - This allows cursor to move in any direction and sensitive implies that any changes to database will show in resultset.
13 - How can you retrieve data from the ResultSet?
  • Create a ResultSet Object that will contain all data of SQl query.
    Syntax:
    ResultSet rs = stmt.executeQuery("select * from emp");
  • retrive each row from rs.next() method.
  • Get the data from the row using column index or column name.
    Syntax:
    rs.getString(1); or rs.getString("column-name");
14 - What is diference between PreparedStatement and Statement?
PreparedStatement
Statement
It has ability to create an incomplete query and
supply parameter values at execution time. 		It supply complete query with parameters.
The query execution include 4 steps: parsing
query,compile query,optimized query and executing
query.In PreparedStatement first 3 steps perform only
once when query is submitted initially, only the
last step is performed each time at the time of query submitted.		 Statement perform 4 steps each time when query is executing.
PreparedStatement is faster than Statement. Statement is relatively slow.
15 - What are the different type of RowSet?
A RowSet is an object that encapsulates a set of rows from either JDBC result sets or tabular data sources. RowSets were introduced in JDBC 2.0 as a optional packages. Their are five type of RowSet:
  • CachedRowSet - A CachedRowSet is a RowSet in which the rows are cached and the RowSet is disconnected, that is, it does not maintain an active connection to the database.
  • JdbcRowSet - A JdbcRowSet is a RowSet that wraps around a ResultSet object. It is a connected RowSet that provides JDBC interfaces in the form of a JavaBean interface.
  • WebRowSet - A WebRowSet is an extension to CachedRowSet. It represents a set of fetched rows or tabular data that can be passed between tiers and components in such a way that no active connections with the data source need to be maintained.
  • FilteredRowSet - A FilteredRowSet is an extension to WebRowSet that provides programmatic support for filtering its content. This enables you to avoid the overhead of supplying a query and the processing involved.
  • JoinRowSet - A JoinRowSet is an extension to WebRowSet that consists of related data from different RowSets. There is no standard way to establish a SQL JOIN between disconnected RowSets without connecting to the data source.
16 - What is DAO? Why is it a best practice to use a DAO Design Pattern?
DAO stands for Data Access Object. Use a DAO to abstract and encapsulate all access to the data source, DAO manages the connection with the data source to obtain and store data. DAO is used when underlying data source need to change acccording to requirement.
17 - What is the difference between JDBC-1.0 and JDBC-2.0?
  • Scrollable ResultSets - In JDBC-1.0 you have only one way to move the cursor by call to next() method. But in JDBC-2.0 you also have previous with next to move cursor backward.
  • Updateable ResultSets - JDBC-2.0 introduce updatable ResultSet that can insert a new row, delete a existing row and modify the column value in ResultSet object, that is not in JDBC-1.0
  • Batch updates - In JDBC-1.0 Statement objects submit updates to the database individually with executeUpdate() method. Multiple executeUpdate statements can be sent in the same transaction, but even though they are committed or rolled back as a unit, they are still processed individually. whereas in in JDBC-2.0 Statement, PreparedStatement, and CallableStatement objects have the ability to maintain a list of sql statement that execute as a batch. addBatch() method add sql command to list, clearBatch() method empty the list and executeBatch() method send all command in list to database.
  • The JDBC 2.0 provides interfaces that represent the mapping to new SQL3 datatypes into the Java programming language.
18 - What is Meta Data? how you can get it?
Data about data is called meta data. Database metadata is information about a database. JDBC provide 4 type of interface to deal with metadata:
  • java.sql.DatabaseMetaData provides the metadata about the database like table names, table indexes, database name and version.
    Syntax: DatabaseMetaData dbmd = connection.getMetaData();
  • java.sql.ResultSetMetaData provides the types and properties of the columns in a ResultSet object.
    Syntax: ResultSetMetaData rsmd = resultSet.getMetaData();
  • java.sql.ParameterMetaData provides the types and properties of the parameters in a PreparedStatement object.
  • javax.sql.RowSetMetaData provides data about the columns in a RowSet object.
19 - What is Use of Callable Statement ?
A CallableStatement object provides a way to call stored procedures in a standard way for all DBMSs. The call to the stored procedure is what a CallableStatement object contains. Syntax to create CallableStatement object and call stored procedure
CallableStatement cstmt = con.prepareCall("{call getTestData(?, ?)}");
20 - How will you control two concurrent transactions accessing a database?
If two concurrent transactions accessing a database for reading data then there is no issue but problem arise when two concurrent transactions update the same column value at same time, in this situation you need some locking technique that prevent from this situation.