JDBC interview questions

Q: What is JDBC?

A: JDBC stands for Java Database Connectivity, which is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases.

Q: What is a JDBC DriverManager?

A: JDBC DriverManager is a class that manages a list of database drivers. It matches connection requests from the java application with the proper database driver using communication subprotocol.

Q: What is a JDBC Driver?

A: JDBC driver is an interface enabling a Java application to interact with a database. To connect with individual databases, JDBC requires drivers for each database. The JDBC driver gives out the connection to the database and implements the protocol for transferring the query and result between client and database

Q: What is a connection?

A: Connection interface consists of methods for contacting a database. The connection object represents communication context.

Q: What is a statement?

A: Statement encapsulates an SQL statement which is passed to the database to be parsed, compiled, planned and executed.

---

Q: What is a ResultSet?

A: These objects hold data retrieved from a database after you execute an SQL query using Statement objects. It acts as an iterator to allow you to move through its data. The java.sql.ResultSet interface represents the result set of a database query.

Q: What are types of ResultSet?

A: There are three constants which when defined in result set can move cursor in resultset backward, forward and also in a particular row.

ResultSet.TYPE_FORWARD_ONLY: The cursor can only move forward in the result set.

ResultSet.TYPE_SCROLL_INSENSITIVE: The cursor can scroll forwards and backwards, and the result set is not sensitive to changes made by others to the database that occur after the result set was created.

ResultSet.TYPE_SCROLL_SENSITIVE: The cursor can scroll forwards and backwards, and the result set is sensitive to changes made by others to the database that occur after the result set was created.

Q: What are the basic steps to create a JDBC application?

A: Following are the basic steps to create a JDBC application:

Import packages containing the JDBC classes needed for database programming.

Register the JDBC driver, so that you can open a communications channel with the database.

Open a connection using the DriverManager.getConnection () method.

Execute a query using an object of type Statement.

Extract data from result set using the appropriate ResultSet.getXXX () method.

Clean up the environment by closing all database resources relying on the JVM's garbage collection.

Q: What are JDBC driver types?

A: There are four types of JDBC drivers:

JDBC-ODBC Bridge plus ODBC driver, also called Type 1: calls native code of the locally available ODBC driver.

Native-API, partly Java driver, also called Type 2: calls database vendor native library on a client side. This code then talks to database over network.

JDBC-Net, pure Java driver, also called Type 3 : the pure-java driver that talks with the server-side middleware that then talks to database.

Native-protocol, pure Java driver, also called Type 4: the pure-java driver that uses database native protocol.

Q: What are the different types of JDBC Statements?

A: Types of statements are:

Statement (regular SQL statement)

PreparedStatement (more efficient than statement due to pre-compilation of SQL)

CallableStatement (to call stored procedures on the database)

Q: What is difference between statement and prepared statement?

Statement	PreparedStatement
A standard Statement is used to create a Java representation of a literal SQL statement and execute it on the database.	A PreparedStatement is a precompiled  statement. This means that when the PreparedStatement is executed, the RDBMS can just run the PreparedStatement SQL statement without having to compile it first.
Statement has to verify its metadata against the database every time.	While a prepared statement has to verify its metadata against the database only once.
If you want to execute the SQL statement once go for STATEMENT	If you want to execute a single SQL statement multiple number of times, then go for PREPAREDSTATEMENT. PreparedStatement objects can be reused with passing different values to the queries

Q: How do you register a driver?

A: There are 2 approaches for registering the Driver:

Class.forName(): This method dynamically loads the driver's class file into memory, which automatically registers it. This method is preferable because it allows you to make the driver registration configurable and portable.

DriverManager.registerDriver(): This static method is used in case you are using a non-JDK compliant JVM, such as the one provided by Microsoft.

Q: How do you create a connection object?

A: There are 3 overloaded DriverManager.getConnection() methods to create a connection object:

getConnection(String url, String user, String password):Using a database URL with a username and password. For example:

String URL = "jdbc:oracle:thin:@amrood:1521:EMP";

String USER = "username";

String PASS = "password"

Connection conn = DriverManager.getConnection(URL, USER, PASS);

getConnection(String url):Using only a database URL. For example:

String URL = "jdbc:oracle:thin:username/password@amrood:1521:EMP";

Connection conn = DriverManager.getConnection(URL);

getConnection(String url, Properties prop):Using a database URL and a Properties object. For example:

String URL = "jdbc:oracle:thin:@amrood:1521:EMP";

Properties info = new Properties( );

info.put( "user", "username" );

info.put( "password", "password" );

Q: What is a Stored Procedure and how do you call it in JDBC?

A: A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. 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 called using CallableStatement class in JDBC API. For example the following code demonstrates this:

CallableStatement cs = con.prepareCall("{call MY_SAMPLE_STORED_PROC}");

ResultSet rs = cs.executeQuery();

Q: What is JDBC SQL escape syntax?

A: The escape syntax gives you the flexibility to use database specific features unavailable to you by using standard JDBC methods and properties.

The general SQL escape syntax format is as follows:

{keyword 'parameters'}.

JDBC defines escape sequences that contain the standard syntax for the following language features:

Date, time, and timestamp literals (d, t, ts Keywords).

Scalar functions such as numeric, string, and data type conversion functions(fn Keyword).

Outer joins(oj Keyword)

Escape characters for wildcards used in LIKE clauses(escape Keyword).

Procedure calls(call Keyword).

Q: What is the difference between execute, executeQuery, executeUpdate?

A: boolean execute(): Executes the any kind of SQL statement

ResultSet executeQuery(): This is used generally for reading the content of the database. The output will be in the form of ResultSet. Generally SELECT statement is used.

int executeUpdate(): This is generally used for altering the databases. Generally DROP TABLE or DATABASE, INSERT into TABLE, UPDATE TABLE, DELETE from TABLE statements will be used in this. The output will be in the form of int which denotes the number of rows affected by the query.

Q: Why do you have to close database connections in Java?

A: You need to close the resultset, the statement and the connection. If the connection has come from a pool, closing it actually sends it back to the pool for reuse. We can do this in the finally{} block, such that if an exception is thrown, you still get the chance to close this.

Q: What is the use of blob, clob datatypes in JDBC?

A: These are used to store large amount of data into database like images, movie etc which are extremely large in size.

Q: What is Connection Pooling ?

A: Connection Pooling is a technique used for reuse of physical connections and reduced overhead for your application. Connection pooling functionality minimizes expensive operations in the creation and closing of sessions.Database vendor's help multiple clients to share a cached set of connection objects that provides access to a database. Clients need not create a new connection everytime to interact with the database.

Q: How do you implement connection pooling

A: If you use an application server like WebLogic, WebSphere, jBoss, Tomcat. , then your application server provides the facilities to configure for connection pooling. If you are not using an application server then components like Apache Commons DBCP Component can be used.

Q: How to Connect to an Excel Spreadsheet using JDBC in Java ?

A: Follow the steps below:

First setup the new ODBC datasource. Goto Administrative Tools->Data Sources (ODBC)->System DSN tab->Add->Driver do Microsoft Excel(*.xls)->Finish. Now give the Data Source Name (SampleExcel) & Description. Next, click Select Workbook and point to your excel sheet.

In the code make to following code additions:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection conn=DriverManager.getConnection("jdbc:odbc:SampleExcel","","");

stmt=conn.createStatement();

sql="select * from [Sheet1$]";

rs=stmt.executeQuery(sql);

Where Sheet1 is the excel sheet name.

Q: What is difference between JDBC, JNDI and Hibernate?

A: Hibernate is an Object-Relational Mapping tool. It maps Objects to relational data.

The Java Naming and Directory Interface (JNDI) is an API to access different naming and directory services. You use it to access something stored in a directory or naming service without haveing to code specifically to that naming or directory service.

Java DataBase Connectivity (JDBC) API is an API to access different relational databases. You use it to access relational databases without embedding a dependency on a specific database type in your code.