JDBC Interview Questions

What is JDBC?

Short for Java Database Connectivity, a Java API that enables Java programs to execute SQL statements. This allows Java programs to interact with any SQL-compliant database. Since nearly all relational database management systems (DBMSs) support SQL, and because Java itself runs on most platforms, JDBC makes it possible to write a single database application that can run on different platforms and interact with different DBMSs. JDBC is similar to ODBC, but is designed specifically for Java programs, whereas ODBC is language-independent.

What are the components of JDBC

JDBC Components—Connection Pools, Data Sources, and MultiPools

How to you load the drivers

Class.forName() method is used in JDBC to load the JDBC drivers dynamically

What does Class.forname() do

Class.forName() method is used in JDBC to load the JDBC drivers dynamically

What are the different types of JDBC drivers

A JDBC-ODBC bridge provides JDBC API access via one or more ODBC drivers. Note that some ODBC native code and in many cases native database client code must be loaded on each client machine that uses this type of driver. Hence, this kind of driver is generally most appropriate when automatic installation and downloading of a Java technology application is not important.  A native-API partly Java technology-enabled driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this style of driver requires that some binary code be loaded on each client machine. A net-protocol fully Java technology-enabled driver translates JDBC API calls into a DBMS-independent net protocol which is then translated to a DBMS protocol by a server. This net server middleware is able to connect all of its Java technology-based clients to many different databases. The specific protocol used depends on the vendor. In general, this is the most flexible JDBC API alternative. It is likely that all vendors of this solution will provide products suitable for Intranet use. In order for these products to also support Internet access they must handle the additional requirements for security, access through firewalls, etc., that the Web imposes. Several vendors are adding JDBC technology-based drivers to their existing database middleware products. A native-protocol fully Java technology-enabled driver converts JDBC technology calls into the network protocol used by DBMSs directly. This allows a direct call from the client machine to the DBMS server and is a practical solution for Intranet access. Since many of these protocols are proprietary the database vendors themselves will be the primary source for this style of driver. Several database vendors have these in progress.

What are the pros and cons of all the 4 drivers

Type 1: JDBC-ODBC Bridge The type 1 driver, JDBC-ODBC Bridge, translates all JDBC calls into ODBC (Open DataBase Connectivity) calls and sends them to the ODBC driver. As such, the ODBC driver, as well as, in many cases, the client database code, must be present on the client machine. Figure 1 shows a typical JDBC-ODBC Bridge environment. Figure 1. Type 1: JDBC-ODBC Bridge Pros The JDBC-ODBC Bridge allows access to almost any database, since the database's ODBC drivers are already available. Type 1 drivers may be useful for those companies that have an ODBC driver already installed on client machines. Cons The performance is degraded since the JDBC call goes through the bridge to the ODBC driver, then to the native database connectivity interface. The result comes back through the reverse process. Considering the performance issue, type 1 drivers may not be suitable for large-scale applications. The ODBC driver and native connectivity interface must already be installed on the client machine. Thus any advantage of using Java applets in an intranet environment is lost, since the deployment problems of traditional applications remain. Type 2: Native-API/partly Java driver JDBC driver type 2 -- the native-API/partly Java driver -- converts JDBC calls into database-specific calls for databases such as SQL Server, Informix, Oracle, or Sybase. The type 2 driver communicates directly with the database server; therefore it requires that some binary code be present on the client machine. Figure 2. Type 2: Native-API/partly Java driver Pros Type 2 drivers typically offer significantly better performance than the JDBC-ODBC Bridge. Cons The vendor database library needs to be loaded on each client machine. Consequently, type 2 drivers cannot be used for the Internet. Type 2 drivers show lower performance than type 3 and type 4 drivers. Type 3: Net-protocol/all-Java driver JDBC driver type 3 -- the net-protocol/all-Java driver -- follows a three-tiered approach whereby the JDBC database requests are passed through the network to the middle-tier server. The middle-tier server then translates the request (directly or indirectly) to the database-specific native-connectivity interface to further the request to the database server. If the middle-tier server is written in Java, it can use a type 1 or type 2 JDBC driver to do this. Figure 3. Type 3: Net-protocol/all-Java driver Pros The net-protocol/all-Java driver is server-based, so there is no need for any vendor database library to be present on client machines. Further, there are many opportunities to optimize portability, performance, and scalability. Moreover, the net protocol can be designed to make the client JDBC driver very small and fast to load. Additionally, a type 3 driver typically provides support for features such as caching (connections, query results, and so on), load balancing, and advanced system administration such as logging and auditing. Cons Type 3 drivers require database-specific coding to be done in the middle tier. Additionally, traversing the recordset may take longer, since the data comes through the backend server. Type 4: Native-protocol/all-Java driver The native-protocol/all-Java driver (JDBC driver type 4) converts JDBC calls into the vendor-specific database management system (DBMS) protocol so that client applications can communicate directly with the database server. Level 4 drivers are completely implemented in Java to achieve platform independence and eliminate deployment administration issues. Figure 4. Type 4: Native-protocol/all-Java driver Pros Since type 4 JDBC drivers don't have to translate database requests to ODBC or a native connectivity interface or to pass the request on to another server, performance is typically quite good. Moreover, the native-protocol/all-Java driver boasts better performance than types 1 and 2. Also, there's no need to install special software on the client or server. Further, these drivers can be downloaded dynamically. Cons With type 4 drivers, the user needs a different driver for each database.

How to you establish a connection

Loading Drivers Class.forName("Driver"); Getting connection Connection con = DriverManager.getConnection(url,                      "myLogin", "myPassword");

What are different types of statements in JDBC

java.sql.Statement - Top most interface which provides basic methods useful for executing SELECT, INSERT, UPDATE and DELETE SQL statements. java.sql.PreparedStatement - An enhanced verion of java.sql.Statement which allows precompiled queries with parameters. It is more efficient to use java.sql.PreparedStatement if you have to specify parameters to your SQL queries. java.sql.CallableStatement - Allows you to execute stored procedures within a RDBMS which supports stored procedures (MySQL doesn't support stored procedures at the moment).

When do we used prepared statements

If you want to execute a Statement object many times, it will normally reduce execution time to use a PreparedStatement object instead. The main feature of a PreparedStatement object is that, unlike a Statement object, it is given an SQL statement when it is created. 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. Although PreparedStatement objects can be used for SQL statements with no parameters, you will probably use them most often for SQL statements that take parameters. The advantage of using SQL statements that take parameters is that you can use the same statement and supply it with different values each time you execute it. You will see an example of this in the following sections.

How do you create JDBC statements

Connection con = null; Statement st = null; // Obtain connection here st = con.createStatement(); ResultSet rs = null; rs = st.executeQuery("SELECT * FROM users"); int recordsUpdated; recordsUpdated = st.executeUpdate("DELETE FROM users WHERE user_id = 1");

How do you retrieve data from a result set

Example: Statement stmt = conn.createStatement();  ResultSet rs = stmt.executeQuery(SELECT COF_NAME, PRICE FROM COFFEES”);  while (rs .next() )  {  //Iam assuming there are 3 columns in the table.  System.out.println ( rs.getString(1));  System.out.println(rs.getString(2));  System.out.println(rs.getString(3));  }  //don’t forget to close the resultset, statement & connection  rs.close(); //First   stmt.close(); //Second  con.close(); //Last   System.out.println(”You are done”);

What is a stored procedure

A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. Stored procedures are used to encapsulate a set of operations or queries to execute on a database server. For example, operations on an employee database (hire, fire, promote, lookup) could be coded as stored procedures executed by application code. Stored procedures can be compiled and executed with different parameters and results, and they may have any combination of input, output, and input/output parameters.

What are the tasks of JDBC

Following are the tasks of JDBC Load the JDBC drivers Register the drivers Specify a database Open a connection to database Submit a query to database Gets the results

What do you mean by batch updates

if you want to execute a set of statements, i.e. SQL statements at a time then we use batch update statement. resultset=pst.batchUpdate();

Why do we need batch updates

Let's say there are 100 records need to be insert. If we execute normal statemets the no of transactions will be 100 (in terms of connection making to DB). using batch updates we can add 100 rec to batch and the no of transactions will be only one in this case. This will reduce the burdon on db, which is very costly in terms of resources.

How do you call a stored procedure from java

You can call a stored procedure using Callable statements CallableStatement cs = con.prepareCall("{call StoredProc}"); ResultSet rs = cs.executeQuery();

What packages are being used by JDBC

Following packages are used in JDBC java.sql javax.sql

Explain how to get the resultset of Stored procedure

CallableStatement cstmt; ResultSet rs; int i; String s; ... cstmt.execute();// Call the stored procedure  1  rs = cstmt.getResultSet();// Get the first result set  2  while (rs.next()) {               // Position the cursor  3          i = rs.getInt(1);          // Retrieve current result set value        System.out.println("Value from first result set = " + i);   // Print the value } cstmt.getMoreResults();  // Point to the second result set  4a                                             // and close the first result set rs = cstmt.getResultSet(); // Get the second result set  4b  while (rs.next()) {               // Position the cursor  4c          s = rs.getString(1);   // Retrieve current result set value         System.out.println("Value from second result set = " + s);                                            // Print the value } rs.close();                          // Close the result set cstmt.close();                    // Close the statement

What do we use setAutoCommit() for

The DML operations by default are committed. If we wish to avoid the commit by default, setAutoCommit(false) has to be called on the Connection object. Once the statements are executed, commit() has to be called on the Connection object explicitly.

Difference between Resultset and Rowset

RowSet   The interface that adds support to the JDBC API for the JavaBeansTM component model. A rowset, which can be used as a JavaBeans component in a visual Bean development environment, can be created and configured at design time and executed at run time. The RowSet interface provides a set of JavaBeans properties that allow a RowSet instance to be configured to connect to a JDBC data source and read some data from the data source. A group of setter methods (setInt, setBytes, setString, and so on) provide a way to pass input parameters to a rowset's command property. This command is the SQL query the rowset uses when it gets its data from a relational database, which is generally the case. The RowSet interface supports JavaBeans events, allowing other components in an application to be notified when an event occurs on a rowset, such as a change in its value. The RowSet interface is unique in that it is intended to be implemented using the rest of the JDBC API. In other words, a RowSet implementation is a layer of software that executes "on top" of a JDBC driver. Implementations of the RowSet interface can be provided by anyone, including JDBC driver vendors who want to provide a RowSet implementation as part of their JDBC products. A RowSet object may make a connection with a data source and maintain that connection throughout its life cycle, in which case it is called a connected rowset. A rowset may also make a connection with a data source, get data from it, and then close the connection. Such a rowset is called a disconnected rowset. A disconnected rowset may make changes to its data while it is disconnected and then send the changes back to the original source of the data, but it must reestablish a connection to do so. ResultSet A table of data representing a database result set, which is usually generated by executing a statement that queries the database. A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set. A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable and/or updatable. The following code fragment, in which con is a valid Connection object, illustrates how to make a result set that is scrollable and insensitive to updates by others, and that is updatable. See ResultSet fields for other options.

How do we retrieve warning

SQLWarning objects are a subclass of SQLException that deal with database access warnings. Warnings do not stop the execution of an application, as exceptions do. They simply alert the user that something did not happen as planned. A warning can be reported on a Connection object, a Statement object (including PreparedStatement and CallableStatement objects), or a ResultSet object. Each of these classes has a getWarnings method, which you must invoke in order to see the first warning reported on the calling object. E.g. SQLWarning warning = stmt.getWarnings(); if (warning != null) { while (warning != null) { System.out.println("Message: " + warning.getMessage()); System.out.println("SQLState: " + warning.getSQLState()); System.out.print("Vendor error code: "); System.out.println(warning.getErrorCode()); warning = warning.getNextWarning(); } }

How many statements can we create with one connection

There is no such limit on number of statements to be created

How to Make Updates to Updatable Result Sets

Usinf JDBC 2.0 API we have the ability to update rows in a result set. For this we need to create a ResultSet object that is updatable. For this, we pass the ResultSet constant CONCUR_UPDATABLE to the createStatement method. Connection con =     DriverManager.getConnection(url, "myLogin", "myPassword"); Statement stmt =     con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet uprs =     stmt.executeQuery("SELECT NAME, SALARY FROM EMPLOYEES");

How can you move the cursor in scrollable result sets

In JDBC 2.0 API we have the ability to move a result set’s cursor backward as well as forward. We can also move the cursor to a particular row and check the position of the cursor. Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet srs = stmt.executeQuery(”SELECT NAME, SALARY FROM EMPLOYEES”); The first argument is one of three constants added to the ResultSet API to indicate the type of a ResultSet object: TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE , and TYPE_SCROLL_SENSITIVE. The second argument is one of two ResultSet constants for specifying whether a result set is read-only or updatable: CONCUR_READ_ONLY and CONCUR_UPDATABLE. Make sure that when you specify a type, you must also specify whether it is read-only or updatable. Specifying the constant TYPE_FORWARD_ONLY creates a nonscrollable result set, that is, one in which the cursor moves only forward. If you do not specify any constants for the type and updatability of a ResultSet object, you will automatically get one that is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY.

What’s the difference between TYPE_SCROLL_INSENSITIVE , and TYPE_SCROLL_SENSITIVE

You will get a scrollable ResultSet object if you specify one of these ResultSet constants.The difference between the two has to do with whether a result set reflects changes that are made to it while it is open and whether certain methods can be called to detect these changes. Generally speaking, a result set that is TYPE_SCROLL_INSENSITIVE does not reflect changes made while it is still open and one that is TYPE_SCROLL_SENSITIVE does. All three types of result sets will make changes visible if they are closed and then reopened: Statement stmt =     con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet srs =     stmt.executeQuery("SELECT NAME, SALARY FROM PERSON"); srs.afterLast(); while (srs.previous()) {     String name = srs.getString("NAME");     float salary = srs.getFloat("SALARY");     System.out.println(name + " " + salary); }

How do you insert images in Database using JDBC

We can store images in the databse using the BLOB datatype where in the image is stored as a byte stream

What is Metadata

It is information about one of two things: Database information (java.sql.DatabaseMetaData), or Information about a specific ResultSet (java.sql.ResultSetMetaData). Use DatabaseMetaData to find information about your database, such as its capabilities and structure. Use ResultSetMetaData to find information about the results of an SQL query, such as size and types of columns

What is a data source

A DataSource class brings another level of abstraction than directly using a connection object. Data source can be referenced by JNDI. Data Source may point to RDBMS, file System , any DBMS etc.