JDBC Interview Questions and Answers

What is JDBC?

JDBC refers to the Java Database Connectivity. It provides java API that allows Java programs to access database management systems (relational database). The JDBC API consists of a set of interfaces and classes which enables java programs to execute SQL statements. Interfaces and classes in JDBC API are written in java.
See more at: JDBC Overview.

Explain JDBC core components.

The JDBC API consists of the following core components:
1. JDBC Drivers
2. Connections
3. Statements
4. ResultSets
See more at: JDBC Components.

What are different types of JDBC Drivers?

JDBC driver:
A driver is a software component that provides the facility to interact java application with the database.
Types of JDBC drivers:
1. JDBC-ODBC bridge driver.
2. Native-API driver.
3. Network-Protocol driver.
4. Thin driver.
See more at: JDBC Driver.

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

Steps to connect database in java using JDBC are given below:
1. Load the JDBC driver.
2. connection.
3. statement.
4. Execute statement.
5. Close database connection.

What is JDBC Statement?

The JDBC statement is used to execute queries against the database. Statement is an interface which provides the methods to execute queries. We can get a statement object by invoking the createStatement() method of Connection interface.
Syntax: Statement stmt=conn.createStatement();

What is the difference between execute, executequery, executeupdate?

executeQuery()—for getting the data from database.
executeUpdate()—for insert,update,delete.
execute()—any kind of operations.
boolean execute(): Executes the SQL statement in this Prepared Statement object, which may be any kind of SQL statement.
ResultSet executeQuery(): Executes the SQL query in this Prepared Statement object and returns the ResultSet object generated by the query.
int executeUpdate(): Executes the SQL statement in this Prepared Statement object, which must be an SQL INSERT, UPDATE or DELETE statement; or an SQL statement that returns nothing, such as a DDL statement.

What is JDBC PreparedStatement?

The JDBC PreparedStatement is used to execute parameterized queries against the database. PreparedStatement is an interface which provides the methods to execute parameterized queries. A parameter is represented by ? symbol in JDBC. PreparedStatement extends the Statement interface. We can get a PreparedStatement object by invoking the prepareStatement() method of Connection interface.
Syntax: PreparedStatement pstmt = conn.prepareStatement(SQL);

What is JDBC CallableStatement?

The JDBC CallableStatement is used to execute the store procedure and functions. CallableStatement interface provides the methods to execute the store procedure and functions. We can get a statement object by invoking the prepareCall() method of Connection interface.
Syntax: CallableStatement callableStatement = conn.prepareCall(“{call procedurename(?,?…?)}”);

What is the difference between Statement and PreparedStatement in jdbc?

StatementPreparedStatement
  1. 1. Statement not executes the parameterized query.
  2. 2. Relational DB uses following 4 step to execute a query:
    a. Parse the query.
    b. Compile the query.
    c. Optimize/Plan the query.
    d. Execute the query.
    A statement always executes the all four steps.
  3. 3. No database statement caching in case of statement.
  1. 1. PreparedStatement can execute the parameterized query.
  2. 2. Relational DB uses following 4 step to execute a query:
    a. Parse the query.
    b. Compile the query.
    c. Optimize/Plan the query.
    d. Execute the query.
    PreparedStatement pre-executes first three steps in the execution.
  3. 3. It provides the database statement caching the execution plans of previously executed statements. Hence database engine can reuse the plans for statements that have been executed previously.

Explain JDBC batch processing?

The JDBC batch processing provides the facility to execute a group of related queries. A group of related queries is known as a batch. It reduces the amount of communication overhead and hence improves the performance.

How can we execute stored procedures and functions?

We can execute stored procedures and functions by using Callable statement interface.

Explain JDBC ResultSetMetaData interface.

The metadata refers to the data about data. The ResultSetMetaData interface provides the facility to get the information like table name, total number of column, column name and column type etc. We can get the object of ResultSetMetaData by calling getMetaData() method of ResultSet interface.

Explain JDBC DatabaseMetaData interface.

The metadata refers to the data about data. The DatabaseMetaData interface provides the facility to get the information like driver name, total number of tables and driver version etc. We can get the object of DatabaseMetaData by calling getMetaData() method of Connection interface.

How to rollback a JDBC transaction?

We can rollback the transaction by using rollback() method of Connection object. It will rollback all the changes made by the transaction and release any database locks currently held by this Connection object.

Explain JDBC Savepoint.

A savepoint represents a point that the current transaction can roll back to. Instead of rolling all of its changes back, it can choose to roll back only some of them. For example, suppose you
• start a transaction.
• insert 20 rows into a table.
• set a savepoint.
• insert another 10 rows.
• rollback to the savepoint.
• commit the transaction.
After doing this, the table will contain the first 20 rows you inserted. The other 10 rows will have been deleted by the rollback. A savepoint is just a marker that the current transaction can roll back to.

What is the use of blob and clob datatypes in JDBC?

The blob and clob datatypes in JDBC are used to store large amount of data into database like images, movie etc which are extremely large in size.

What is Connection Pooling?

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 every time to interact with the database.

How do you implement connection pooling?

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.

What is 2 phase commit?

When we work in distributed systems where multiple databases are involved, we are required to use 2 phase commit protocol. 2 phase commit protocol is an atomic commitment protocol for distributed systems.
In the first phase, transaction manager sends commit-request to all the transaction resources. If all the transaction resources are OK, then transaction manager commits the transaction changes for all the resources. If any of the transaction resource responds as Abort, then the transaction manager can rollback all the transaction changes.

What are the different types of locking in JDBC?

Optimistic Locking: Optimistic locking lock the record only when update take place. Optimistic locking does not use exclusive locks when reading.
Pessimistic locking: Record are locked as it selects the row to update.

What is a “dirty read”?

In typical database transactions, say one transaction reads and changes the value while the second transaction reads the value before committing or rolling back by the first transaction. This reading process is called as ‘dirty read’.

No comments: