JDBC Interview Questions

1. What is JDBC?

JDBC is java database connectivity as name implies it’s a java API for communicating to relational database, API has java classes and interfaces using that developer can easily interact with database. For this we need database specific JDBC drivers.

2. Different Types of JDBC Drivers in Java?

Type 1 JDBC Driver is called JDBC-ODBC Bridge driver (bridge driver)

JDBC-ODBC driver translates JDBC calls into ODBC callas and send them to ODBC driver for passing to database. Since type 1 driver act as bridge between JDBC and ODBC and that's why its called JDBC-ODBC bridge driver. This driver is not fast and good for production use mainly because of several layer of translation on back and fourth database traffic but it has advantage in terms of of availability and can be your last choice.

Type 2 JDBC Driver is referred as Native-API/partly Java driver (native driver)

This is also called type 2 driver and its slightly better than type 1 JDBC driver. type 2 JDBC driver convert JDBC calls into database calls by using native API provided by database. This driver is database specific so once you switch from one database to another you need to change type 2 JDBC driver. performance is better than JDBC-ODBC bridge driver since communication layer is reduced. type 2 JDBC driver requires database native library to be available on client but it poses several version and compatibility issue. This was liked by Database vendors though because they can reuse there existing native libraries.

Type 3 JDBC Driver is called AllJava/Net-protocol driver (middleware driver)

both type 1 and type 2 JDBC drivers were not written in Java so there was need for pure Java JDBC driver to resolve portability issue. type 3 JDBC driver comes with pure java implementation (that's why All Java word ) but it uses 3 tier architecture where you have a Java client and Java Server which talk with Net protocol and Server speaking to database. type 3 JDBC driver never get popular among database vendors as it was costly for them to rewrite there existing native database library which was mainly on C and C++.

Type 4 JDBC Driver is called All Java/Native-protocol driver (Pure java driver)

type 4 JDBC driver is most popular among all four types of JDBC driver. it has not only implemented in Java but also incorporates all database call in single driver. It was pretty easy to use and deploy as well just include driver's jar in classpath and you are ready. It also removes 3 tier architecture of type 3 JDBC driver which makes it faster than type 3. Major development happens on type 4 JDBC driver when database upgrade themselves, though some of them still upgrade native database library or type 2 driver.

3. What are the main steps in java to make JDBC connectivity?

1. Load the Driver: First step is to load the database specific driver which communicates with database.
2. Make Connection: Next step is get connection from the database using connection object, which is used to send SQL statement also and get result back from the database.
3. Get Statement object: From connection object we can get statement object which is used to query the database
4. Execute the Query:Using statement object we execute the SQL or database query and get result set from the query.
5. Close the connection:After getting resultset and all required operation performed the last step should be closing the database connection.

4. What is the mean of “dirty read“ in database?

As the name it self convey the meaning of dirty read “read the value which may or may not be correct”. in database when one transaction is executing and changing some field value same time some another transaction comes and read the change field value before first transaction commit or rollback the value ,which cause invalid value for that field, this scenario is known as dirty read.

5. What are different types of Statement?

1. Statement: it’s a commonly used for getting data from database useful when we are using static SQL statement at runtime. it will not accept any parameter.

      Statement   stmt = conn.createStatement( );
      ResultSet rs = stmt.executeQuery();
      

2. PreparedStatement: when we are using same SQL statement multiple time its is useful and it will accept parameter at runtime.

      String SQL = "Update stock SET limit = ? WHERE stockType = ?";
      PreparedStatement  pstmt = conn.prepareStatement(SQL);
      ResultSet rs = pstmt.executeQuery();
      

3. Callable Statement: when we want to access stored procedures then callable statement are useful and they also accept runtime parameter. It is called like this

    
      CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}"); 
      ResultSet rs = cs.executeQuery();
  

6. What is connection pooling?

In this mechanism client are not required every time make new connection and then interact with database instead of that connection objects are stored in connection pool and client will get it from there. so it’s a best way to share a server resources among the client and enhance the application performance.

Spring Example JDBC Database Connection Pool

Spring framework provides convenient JdbcTemplate class for performing all Database related operation. if you are not using Hibernate than using Spring's JdbcTemplate is good option. JdbcTemplate requires a DataSource which is javax.sql.DataSource implementation and you can get this directly using spring bean configuration or by using JNDI if you are using J2EE web server or application server for managing Connection Pool. See How to setup JDBC connection Pool in tomcat and Spring for JNDI based connection pooling for more details. In order to setup Data source you will require following configuration in your applicationContext.xml (spring configuration) file:

7. What do you mean by cold backup, hot backup?

Cold back is the backup techniques in which backup of files are taken before the database restarted. In hot backup backup of files and table is taken at the same time when database is running. A warm is a recovery technique where all the tables are locked and users cannot access at the time of backing up data.

8. What are the locking system in JDBC?

There are 2 types of locking in JDBC by which we can handle multiple user issue using the record. if two user are reading the same record then there is no issue but what if users are updating the record , in this case changes done by first user is gone by second user if he also update the same record .so we need some type of locking so no lost update.

Optimistic Locking: optimistic locking lock the record only when update take place. Optimistic locking does not use exclusive locks when reading

Pessimistic locking: in this record are locked as it selects the row to update

9. Difference between java.util.Date and java.sql.Date in Java?

1. As per Javadoc java.sql.Date is a thin wrapper around millisecond value which is used by JDBC to identify an SQL DATE type.
2. java.sql.Date just represent DATE without time information while java.util.Date represent both Date and Time information. This is the major differences why java.util.Date can not directly map to java.sql.Date.
3. In order to suppress time information and to confirm with definition of ANSI SQL DATE type, the millisecond values used in java.sql.Date instance must be "normalized by setting the hours, minutes, seconds and milliseconds to zero in the timezone with with DATE instance is associated. In other words all time related information is removed from java.sql.Date class.

10. Why use PreparedStatement in Java JDBC?

1. PreparedStatement allows you to write dynamic and parametric query.

By using PreparedStatement in Java you can write parametrized sql queries and send different parameters by using same sql queries which is lot better than creating different queries. Here is an example of parametric query written using PreparedStatement in java:

select interest_rate from loan where loan_type=?

Now you can run this query for any loan type e.g. "personal loan”, "home loan" or "gold loan". This example of SELECT query is called parametric or parametrized query because it can be invoked with different parameter. Here “?” is used as place holder for parameter.

2. PreparedStatement is faster than Statement in Java

One of the major benefits of using PreparedStatement is better performance. PreparedStatement gets pre compiled In database and there access plan is also cached in database, which allows database to execute parametric query written using prepared statement much faster than normal query because it has less work to do. You should always try to use PreparedStatement in production JDBC code to reduce load on database. In order to get performance benefit its worth noting to use only parametrized version of sql query and not with string concatenation. Out of following two examples of SELECT queries, first example of SELECT query will not offer any performance benefit:

SQL Query 1: PreparedStatement with String concatenation

String loanType = getLoanType();
PreparedStatement prestmt = conn.prepareStatement("select banks from loan where loan_type=" + loanType);

SQL Query 2: Parameterized query using PreparedStatement

PreparedStatement prestmt = conn.prepareStatement("select banks from loan where loan_type=?");
prestmt.setString(1,loanType);

Second SQL query is correct use of PreparedStatement in Java and give better performance than SQL query1.

3. PreparedStatement prevents SQL Injection attacks in Java

If you have been working in Java web application you must be familiar with infamous SQL Injection attacks, last year Sony got victim of SQL injection and compromised several Sony play station user data. In SQL Injection attack, malicious user pass SQL meta-data combined with input which allowed them to execute sql query of there choice, If not validated or prevented before sending query to database. By using parametric queries and PreparedStatement you prevent many forms of SQL injection because all the parameters passed as part of place-holder will be escaped automatically by JDBC Driver. Though It’s worth remembering that in above example of two PreparedStatement only second example will prevent SQL injection attacks and first example is not secure with SQL injection.

4. At last PreparedStatement queries are more readable and secure than cluttered string concatenated queries.

11. How to check if ResultSet is empty in JDBC?

if (rs.next() == false) { 
 System.out.println("ResultSet in empty in Java"); 
} else { 
 do { 
  String data = rs.getString("emp_name"); 
  System.out.println(data); 
 } while (rs.next()); 
}

12. Why Timestamp cannot be used in place of Date in Java?

Timestamp is not exactly Date. It's a composite type of java.util.Date and an additional nanosecond value which is fitted there to confirm database DATETIME data type, which supports nanosecond precision. If you look at the implementation of java.sql.Timestamp class, you will find that the long value supplied by Date is stored separately then this nanosecond value.

You cannot even compare Date to Timestamp class in Java. The Timestamp.equals(Object) method returns false when you pass an instance of java.util.Date because the nanos component of a date is unknown. As a result, the Timestamp.equals(Object) method is not symmetric with respect to the java.util.Date.equals(Object) method. Also, the hashCode method uses the underlying java.util.Date implementation and therefore does not include nanos in its computation.

13. Difference between java.sql.Time, java.sql.Timestamp and java.sql.Date?

1. First difference on java.sql.Time vs java.sql.Timestamp vs java.sql.Date is about information they represent :
   JDBC TIME or java.sql.Time represent only time information e.g. hours, minutes and seconds without any date information.
   JDBC DATE or java.sql.Date represent only date information e.g. year, month and day without any time information.
   JDBC TIMESTAMP or java.sql.Timestamp represent both date and time information including nanosecond details.
2. java.sql.Time and java.sql.Timestamp extends java.util.Date class but java.sql.Date is independent.
3. Time information from java.sql.Date and Date information from java.sql.Time is normalized and may set to zero in order to confirm ANSI SQL DATE and TIME types.

So difference between Time, Timestamp and Date of SQL package is clear in terms of what they represent. On contrary java.util.Date also represent Date and time information but without nanosecond details and that's why many people prefer to store date as long value (millisecond passed from epoch January 1, 1970 00:00:00.000 GMT). If you compare to java.sql.Timestamp with equals() method it will return false as value of nanosecond is unknown.

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

Date now = new Date();
java.sql.Date sqlDate = new java.sql.Date(now.getTime());

Things to Remember

1. java.sql.Date mapped to DATE datatype on JDBC i.e. Type.Date, which corresponds to date equivalent in DB side. In SQLSERVER till 2008 it maps to DATETIME and afterward maps to DATE data type.
2. java.sql.Date extends java.util.Date but voilates Liskov Substituion Principle.
3. You can convert between java.util.Date and java.sql.Date by using getTime() method.

15. How to convert java.util.Date to java.sql.Timestamp in Java?

it's easy to convert a java.util.Date object to java.sql.Timestamp in Java, all you need to do is call the getTime() method to get the long value from java.util.Date object and pass it to java.sql.Timestamp constructor, as shown below:

public Timestamp getTimestamp(java.util.Date date){
  return date == null ? null : new java.sql.Timestamp(date.getTime());
}