Jdbc in Java, Top 15 Important Interview Questions

JDBC in Java is an API(Application programming interface) used in java programming to interact with databases. We have shortlisted the interview questions on JDBC. which might help you when you are preparing for an interview.

1. What are the different types of JDBC ResultSet?

When creating a Statement, you can specify the different types of ResultSet that you receive.

Three types of ResultSet objects

• ResultSet.TYPE_FORWARD_ONLY:
  • the default type. Supports cursor movement only in the forward direction.
• ResultSet.TYPE_SCROLL_INSENSITIVE:
  • Bi-directional cursor. The object is not sensitive to the changes that occurred with the table after the result was obtained.
• ResultSet.TYPE_SCROLL_SENSITIVE:
  • Bi-directional cursor. The object is sensitive to changes that occurred with the database after the ResultSet object was created.

Two types of thread-safe objects are ResultSet

• ResultSet.CONCUR_READ_ONLY:
  • Supports read-only. By default, it is used.
• ResultSet.CONCUR_UPDATABLE:
  • Supports the ResultSet update method to update rows in the data table.

2. How  setFetchSize() and SetMaxRows() methods used in Statement?

To limit the number of rows that a query can return, the setMaxRows (int i) method is used. Of course, this result can be obtained using a SQL query (for example, for MySQL, the LIMIT command exists).

To understand the setFetchSize() method, you need to understand the work of Statement and ResultSet. When a database query is executed, the result is processed and stored in the database cache and returned as a ResultSet.

ResultSet is the cursor that references the result in the database. Now suppose we have a query that returns 100 rows and we set setFetchSize (10). Now for every database access, only 10 rows are allocated and 10 requests will be needed to get all the data.

Choosing the optimal amount of fetch size () can improve the performance of executing a large number of calls to each row and in the case of a large number of rows in the output result.

The value of fetchSize can be specified inside the Statement object, but it can be overridden in the ResultSet object with setFetchSize ().

3. What is JDBC Batch Processing, and its advantages?

Sometimes it is necessary to perform a group of similar requests at once, for example, when loading data from CSV files of a relational database.

You can do this simply by using Statement or PreparedStatement to step through these queries. In the JDBC API, there is another option that provides the ability to execute a query group at a time. This type of task is performed using the JDBC API Batch Processing.

The JDBC API supports batch processing using the addBatch () and executeBatch () methods of Statement and PreparedStatement. The advantages of this approach include faster work because calls to the database can be significantly fewer.

4. Transaction Management in JDBC in Java, why it is needed?

Auto-commit mode is selected by default when you create a connection to the database. This means that each time the query is executed, it will be confirmed automatically when completed.

Each SQL query is transactional and executing any DML or DDL requests to complete the change will be accepted (saved) by the database. If we need to refuse to save the execution of any query (or query groups) in case something went wrong, then we can use transaction support in the JDBC API.

Using the setAutoCommit (boolean flag) method, you can disable auto-commit in a specific connection. It should be noted that if you disable auto-commit, not one change will be stored in the database until the commit () method is called and you should monitor it.

Disabling AutoCommit might be dangerous sometimes, so you have to be careful about this.

The database server will block the necessary part of the database before the transaction is confirmed, and since this is a resource-intensive task, the transaction must be confirmed immediately after the task is completed.

5. What is JDBC Savepoint and how is it used?

JDBC Savepoint allows you to create “checkpoints” in a transaction with which we can roll back not the whole transaction, but only a part to the save point.

Any savepoint is automatically released and becomes unavailable after the transaction is confirmed or its rollback. A rollback to the save point makes all subsequent saves unavailable and can no longer be returned.

6. Tell us about the JDBC DataSource. What advantages does it give?

JDBC DataSource is the interface of the javax.sql package and is more advanced than the DriverManager to connect to the database.

We can use DataSource to create a connection to the database and implement a driver class that will perform all the work to maintain the connection. In addition to connecting through the Database, DataSource provides the following additional features:

• Caching PreparedStatement for faster query processing
• Connection timeout settings
• Logging capabilities
• Maximum ResultSet Threshold
• Support for Connection Pooling in a servlet container that uses JNDI support.

7. What is a JDBC Statement?

The JDBC Statement API is used to execute SQL queries against the database. You can get the Statement object using the Connection.getStatement () method. Calling the execute (), executeQuery (), executeUpdate (), and other methods, you can execute various static SQL queries.

In the case of dynamically created SQL queries inside the java program, when the user input can be unverified, you can use SQL injection.

By default, only one ResultSet for each Statement can be opened at the same time. Thus, if it is necessary to work with several ResultSet objects at the same time, we must use different Statement objects. All execute () methods in the Statement interface will close the currently open ResultSet object when executed.

8. What are the differences between executing, executeQuery, and executeUpdate?

There are several ways to execute SQL queries, depending on the type of this query. For this, the Statement interface has three different methods: executeQuery () , executeUpdate () , and execute () . Let’s consider them separately.

The most basic executeQuery () method is required for queries that result in a single set of values, such as SELECT queries. Returns a ResultSet, which cannot be null even if the query result did not contain values.

The method executes () is used when SQL statements return more than one set of data, more than one update counter, or both. The method returns true if the result is ResultSet, as in the SELECT query. Returns false if the ResultSet is not present, for example, for requests of the type Insert, Update. Using the getResultSet () methods, we can get ResultSet, and getUpdateCount () is the number of updated records.

9. What is JDBC PreparedStatement?

The PreparedStatement object is used to perform precompiled SQL queries with or without input (IN) parameters. We can use setters to set values in a query. Because PreparedStatement is precompiled, then it can be effectively used many times. PreparedStatement is considered a better choice than Statement, because it automatically processes special characters, and also prevents the so-called SQL injection attack (when you can substitute your code into the query).

10. How to set NULL values in JDBC PreparedStatement?

Using the setNull () method to set the null variable as a parameter. This method takes an index and an SQL type as arguments:  s.setNull (10, java.sql.Types.INTEGER);

How is the getGeneratedKeys () method used in the Statement?

If the table uses crucial automatic generation, then use the Statement getGeneratedKeys () method to get them, which will return the generated key.

11. What are the advantages of using PreparedStatement on a Statement?

PreparedStatement allows you to prevent SQL injection attacks. It automatically escapes special characters.

PreparedStatement allows you to use dynamic queries with the implementation of parameters.

PreparedStatement is faster than the Statement. This is especially noticeable with the frequent use of PreparedStatement or when using it to query a group of queries.

PreparedStatement allows you to write object-oriented code using setters/getters. At that time, when using a Statement, you must use string concatenation to create a query. For large questions, the concatenation looks, at least, large, and also carries a high risk of error in the query.

12. What are the limitations of PreparedStatement and how to overcome them?

In PreparedStatement you cannot use direct queries with IN (input) parameters. There are some workarounds:

• Run Single Queries – low performance and generally not recommended to do.
• Use Stored Procedures (stored procedures) – are specific to a specific database and therefore are bad for applications with the ability to connect to different databases.

Creating a PreparedStatement Query dynamically is a good solution, but with the loss of PreparedStatement caching.

Using NULL in PreparedStatement Query is a good solution if you know the maximum number of IN variables. You can expand to use an unlimited number of parameters by splitting them into parts.

13. What is the JDBC API and when is it used?

JDBC is the standard of application interaction with various DBMSs. JDBC is based on the concept of drivers, which allows you to get a connection to the database using a special URL. The JDBC APIs are in the packages java.sql and javax.sql. Using the JDBC API, you can create database connections, execute SQL queries, store stored procedures, and process results. The JDBC API makes it easy to work with databases from Java programs.

14. What is a JDBC Driver and what are the different types of JDBC drivers you know?

JDBC is based on the concept of so-called drivers that allow you to connect to a database using a specially described URL. Drivers can be loaded dynamically (while the program is running). Once loaded, the driver registers itself and is called automatically when the program requires a URL containing the protocol for which the driver responds.

There are four types of drivers. Java program works with a DB in two parts. The first part is the JDBC API, and the second is the driver, which does all the work. Each type defines the implementation of the JDBC driver by the increasing degree of platform independence, performance, and ease of administration. These four types are:

• Type 1: JDBC-ODBC bridge (JDBC-ODBC Bridge plus ODBC Driver) – translates JDBC into ODBC and uses the ODBC driver to interact with the database. Sun included one such driver in the JDK, the JDBC / ODBC bridge. Now there are more successful implementations
• Type 2: Native API / partially Java driver (JAPS) – translates JDBC calls into database-specific calls such as SQL Server, Informix, Oracle, or Sybase. The driver of the second type communicates directly with the database server; hence it requires that some binary code is on the client machine side.
• Type 3: The network protocol / “pure” Java driver (Pure Java Driver for Database Middleware)- uses a three-tier architecture, where JDBC calls are sent to the intermediate so-called application server, then this server translates calls (explicitly or indirectly) to calls of the DBMS-specific native interface for further access to the database. If the middle layer server is written in Java, then it can use 1 and 2-type drivers for JDBC broadcasting.
• Type 4: The native protocol (“pure” Java driver) – converts JDBC calls to a specific database vendor protocol, so that client applications can directly access the database server. Drivers of the 4th type are fully implemented in Java with the goal of achieving platform independence and eliminating administration and deployment problems.

15. How does the JDBC API help achieve a weak link between the Java program and the JDBC Drivers API?

The JDBC API uses reflection in java to achieve a weak connection between the Java program and the JDBC drivers. The driver is loaded once using Class.forName (), and then the JDBC API in Java is used. So, we write the code without really thinking about what database we will be working with. If necessary, just specify another driver and do not overwrite a large amount of code.