47 Advanced JDBC Interview Questions & Answers For Experienced

47 JDBC Interview Questions And Answers – Java JDBC Interview Questions For Experienced from Codingcompiler. Test your Java JDBC knowledge by answering these tricky interview questions on JDBC. Let’s start learning Java JDBC interview questions and prepare for Java interviews. All the best for your future and happy learning.

Table of Contents

JDBC Interview Questions

ANSI SQL
The main elements of databases are tables, procedures, functions, collections, etc.
How do you understand null in databases?
Aggregate functions, how they work with null. Do not forget about group by and having
How is it better to add a large number of records to a table?
What is the first normal form and normalization process? What are the normal forms?
What is the meaning of the DBMS index, how are they arranged, how are they stored? How would you implement the same functionality?
What is the JDBC API and when is it used?
What is the JDBC Driver and what different types of JDBC drivers do you know?
How does the JDBC API help achieve weak communication between the Java program and the JDBC Drivers API?
What is JDBC Connection? Show the steps to connect the program to the database.
How is the JDBC DriverManager class used?
How to get information about the database server from a java program?
What is the JDBC Statement?
What are the differences between execute, executeQuery, executeUpdate?
What is JDBC PreparedStatement?
How to set NULL values in JDBC PreparedStatement?
How is the getGeneratedKeys () method used in the Statement?
What are the advantages in using PreparedStatement over Statement?
What are the limitations of PreparedStatement and how to overcome them?
What is JDBC ResultSet?
What are the different types of JDBC ResultSet?
How are the setFetchSize () and SetMaxRows () methods used in the Statement?
How to call Stored Procedures using JDBC API?
What is JDBC Batch Processing and what are its benefits?
What is JDBC Transaction Management and why is it needed?
How to roll back a JDBC transaction?
What is JDBC Savepoint and how is it used?
Tell us about the JDBC DataSource. What advantages does it give?
How to create a JDBC connection pool using JDBC DataSource and JNDI in Apache Tomcat Server?
Tell us about the Apache DBCP API.
What are the isolation levels of connections in JDBC?
What do you know about JDBC RowSet? What are the different types of RowSet?
What is the difference between ResultSet and RowSet?
Give an example of the most common exceptions in JDBC.
Tell us about the data types CLOB and BLOB in JDBC.
What do you know about dirty read in JDBC? What isolation level does this type of reading prevent?
What are the two commit phases?
Give an example of the different types of blocking in JDBC.
How do you understand DDL and DML expressions?
What is the difference between java.util.Date and java.sql.Date?
How to insert an image or raw data into a database?
What can you tell about phantom reading? What level of insulation prevents it?
What is SQL Warning? How to return SQL warnings in a JDBC program?
How to run Oracle Stored Procedure with IN / OUT database objects?
Give an example of java.sql.SQLException occurrence: No suitable driver found.
Best Practices in JDBC.

JDBC Interview Questions And Answers

1. What is ANSI SQL?

SQL (structured query language) is a formal non-procedural programming language used to create, modify, and manage data in an arbitrary relational database controlled by an appropriate database management system (DBMS). SQL is based on the calculation of tuples. The SQL standard is defined using ANSI code.

* The question “tell me about SQL” is very broad and does not fit into the scope of this article. By reading any info from the Internet, for example:

2. The main elements of the database are tables, procedures, functions, constraints, etc.

A field is a minimal element of a database containing one indivisible quantum of information. Each field is characterized by the name and type of data stored in it.

A record is a collection of several heterogeneous fields, describing some essence of a subject domain.

A database table is a collection of uniform records.

A stored procedure is a database object, which is a set of SQL statements that is compiled once and stored on the server. Stored procedures are very similar to ordinary procedures of high-level languages, they can have input and output parameters and local variables, they can be used for numerical calculations and operations on character data, the results of which can be assigned to variables and parameters.

In stored procedures, standard database operations (both DDL and DML) can be performed. In addition, cycles and branches are possible in stored procedures, that is, they can use instructions to control the execution process.

CREATE [OR REPLACE] PROCEDURE procedure_name

[ (parameter [, parameter, …]) ] IS

BEGIN

executable sentences

[EXCEPTION

exception handlers]

END [procedure_name];

A function is a subroutine that calculates a value. There are a large number of built-in functions (they can vary for different databases).

CREATE [OR REPLACE] FUNCTION function_name

[ (parameter [, parameter, …]) ]

RETURN datatype IS | AS

BEGIN

executable sentences

[EXCEPTION

exception handlers]

END [function_name];

Constraints (constraints) – declaration of rules (restrictions), which must correspond to the data in the table.

CREATE TABLE table_name

(

column_name1 data_type(size) constraint_name,

column_name2 data_type(size) constraint_name,

column_name3 data_type(size) constraint_name,

….

);

For example, to specify a primary key, you can use the following entry:

CONSTRAINT <constraint name> PRIMARY KEY (<list of primary key columns>)

Types of constraints:

- NOT NULL column cannot contain NULL values.

- UNIQUE – each row in a column must have a unique value.

- PRIMARY KEY is a combination of NOT NULL and UNIQUE.

- FOREIGN KEY – provides referential integrity. Indicates that the referencing data has a match in another table.

- CHECK – check for a specific rule execution.

DEFAULT – sets the default value for the column

3. How do you understand null in databases?

The meaning of NULL values is the absence of information or the inapplicability of this attribute in this tuple.

NULL value can mean that the value is not applicable to this column (for example, in the column “flight speed” for the table animals and “Elephant” entries)

NULL value can also mean the absence of information. Replacing the missing values, for example, with -1, ” or something, is not correct.

4. Aggregate functions, how they work with null. Do not forget about group by and having

The standard provides the following aggregate functions:

Title	Description
COUNT (*)	Returns the number of rows in the record source.
COUNT	Returns the number of values in the specified column.
SUM	Returns the sum of the values in the specified column.
Avg	Returns the average value in the specified column.
MIN	Returns the minimum value in the specified column.
MAX	Returns the maximum value in the specified column.

All of these functions return a single value. The COUNT , MIN, and MAX functions are applicable to data of any type, while SUM and AVG are used only for data of the numeric type. The difference between the COUNT (*) function and COUNT (column name | expression) is that the second (like the other aggregate functions) does not take into account NULL values when calculating.

The GROUP BY clause is used to define groups of output strings to which aggregate functions can be applied ( COUNT , MIN , MAX , AVG, and SUM ).

If the WHERE clause defines a predicate for filtering rows, then the HAVING clause is applied after grouping to define a similar predicate that filters groups by the values of aggregate functions.

This clause is necessary to check the values that are obtained using the aggregate function not from individual rows of the source of records defined in the FROM clause , but from groups of such rows. Therefore, such a check cannot be contained in the WHERE clause.

5. How is it better to add a large number of records to a table?

You can interpret this question in different ways. One answer is to use a subquery:

INSERT INTO <table name> [(<column name>, …)]

SELECT *

FROM <table name>

WHERE value = ‘something’;

6. What is the first normal form and normalization process? What are the normal forms?

The first normal form (1NF) is the basic normal form of the relation in the relational data model.

The normal form is a relation property in the relational data model, which characterizes it in terms of redundancy, potentially leading to logically erroneous results of sampling or changing data.

Normal form is defined as the set of requirements that a relation must satisfy. Normal forms: the first normal form, the second normal form, the third normal form, the Boyes-Codd normal form, the fourth normal form, the fifth normal form.

The topic is basic and necessary for detailed study. The size of the answer to this question does not fit the format of this article.

7. What is the meaning of the DBMS index, how are they arranged, how are they stored? How would you implement the same functionality?

Index (eng. Index) – a database object created to improve the performance of data retrieval. Tables in the database can have a large number of rows that are stored in arbitrary order, and searching them by a given criterion by sequentially viewing the table row by row can take a lot of time.

The index is formed from the values of one or several columns of the table and pointers to the corresponding rows of the table and, thus, allows you to search for rows that meet the search criteria. Acceleration of work with the use of indexes is achieved primarily due to the fact that the index has a structure optimized for the search – for example, a balanced tree.

The answer to this question also does not fit in one article. Learn by yourself.

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

JDBC is a standard for application interaction with various DBMS. JDBC is based on the driver concept, which allows you to get a connection to the database using a special url.

The JDBC APIs are in the java.sql and javax.sql packages. Using the JDBC API, you can create connections to the database, execute SQL queries, stored procedures, and process the results. The JDBC API makes it easy to work with databases from Java programs.

9. What is the JDBC Driver and what different types of JDBC drivers do you know?

JDBC is based on the concept of so-called drivers that allow you to get a connection to a database at a specially described URL. Drivers can load dynamically (while the program is running). Having loaded, the driver itself registers itself and is called automatically when the program requires a URL containing the protocol for which the driver is responsible.

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

Type 1: JDBC-ODBC Bridge ( JDBC-ODBC Bridge plus ODBC Driver) – translates JDBC to 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 / partly Java driver (Native API partly Java technology-enabled driver) – translates JDBC calls into database-specific calls such as SQL Server, Informix, Oracle or Sybase. The type 2 driver communicates directly with the database server, hence it requires that some binary code be on the side of the client machine.

Type 3: Network Protocol / Pure Java Driver for Database Middleware– uses a three-tier architecture, where JDBC calls are sent to an intermediate so-called. the application server, then this server translates calls (explicitly or indirectly) into calls to the native interface specific to the DBMS for further access to the database. If the middle layer server is written in Java, it can use type 1 and type 2 drivers for translating JDBC.

Type 4: Native protocol / “pure” Java driver (Direct-to-Database Pure Java Driver) – converts JDBC calls into a specific database vendor protocol, so client applications can directly contact the database server. Type 4 drivers are fully implemented in Java in order to achieve platform independence and troubleshoot administration and deployment problems.

10. How does the JDBC API help achieve weak communication 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 actually loaded once using Class.forName () , and then the JDBC API in Java is used. Thus, we write the code without really thinking about which database we will work with. If necessary, it is enough to specify a different driver and not rewrite a large amount of code.

Java JDBC Interview Questions And Answers

11. What is JDBC Connection? Show the steps to connect the program to the database.

JDBC Connection – a connection established with a database server. This is a kind of session or socket connection. Creating a JDBC connection requires only two steps:

1) Register and load the driver using Class.forName () . The driver class will be registered for the DriverManager and loaded into memory.

2) Using DriverManager.getConnection () to get the Connection object . Pass the database URL, name and password to the method.

Connection con = null;

try{

// load the Driver Class

Class.forName(“com.mysql.jdbc.Driver”);

// create the connection now

con = DriverManager.getConnection(“jdbc:mysql://localhost:3306/UserDB”,

“pankaj”,

“pankaj123”);

}catch (SQLException e) {

System.out.println(“Check database is UP and configs are correct”);

e.printStackTrace();

}catch (ClassNotFoundException e) {

System.out.println(“Please include JDBC MySQL jar in classpath”);

e.printStackTrace();

}

12. How is the JDBC DriverManager class used?

JDBC DriverManager is a factory through which you can get a Database Connection object. After loading the JDBC driver into memory, it registers itself with the DriverManager (which can be checked by looking at the source code for the JDBC Driver class). DriverManager is used to get a connection using registered drivers ( getConnection () method ).

13. How to get information about the database server from a java program?

Using the DatabaseMetaData interface object, you can get detailed information about the server. After connecting to the database, we can call the getMetaData () method and get a DatabaseMetaData object . There are many methods for obtaining various information, such as the database version, configuration, etc.

1 2	DatabaseMetaData metaData = con.getMetaData(); String dbProduct = metaData.getDatabaseProductName();

14. What is the JDBC Statement?

JDBC API Statement is used to perform SQL queries to the database. The Statement object can be retrieved using the Connection.getStatement () method . By calling the execute () , executeQuery () , executeUpdate () , etc. methods , you can execute various static SQL queries.

In the case of dynamically generated SQL queries inside a java program, when user input may not be validated, you can use SQL injection.

By default, only one ResultSet object for each Statement can be opened at the same time. Thus, if you need to work with several ResultSetobjects at the same time, we must use different Statement objects . All execute () methods in the Statement interface will close the current open ResultSet object at run time.

15. What are the differences between execute, executeQuery, 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 also execute () . Consider them separately.

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

Method execute () is used when SQL statements return more than one dataset, more than one update count, or both. The method returns true if the result is a ResultSet, like a SELECT query. Returns false if there is no ResultSet, for example, when you are requesting Insert, Update. Using the getResultSet () methods, we can get a ResultSet, and getUpdateCount () is the number of updated records.

The executeUpdate () method is used to execute INSERT, UPDATE or DELETE statements, as well as for DDL (Data Definition Language) statements, such as CREATE TABLE and DROP TABLE. The result of an INSERT, UPDATE, or DELETE statement is a modification of one or more columns in zero or more rows in a table.

MethodexecuteUpdate () returns an integer indicating how many lines have been modified. For expressions of type CREATE TABLE and DROP TABLE that do not operate on strings, the value returned by the executeUpdate () method is always zero.

All methods for executing SQL queries close the previous result set (result set) for this Statement object. This means that before you execute the following query on the same Statement object, you must finish processing the results of the previous one (ResultSet).

16. What is JDBC PreparedStatement?

The PreparedStatement object is used to execute precompiled SQL queries with or without input (IN) parameters. We can use setters to set values in a query. Since Since a PreparedStatement is precompiled, it can be effectively used many times.

PreparedStatement is considered a better choice than a Statement, since it automatically processes special characters, as well as prevents the so-called SQL injection attack (when you can substitute your code in a request).

17. How to set NULL values in JDBC Prepared Statement?

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

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

If automatic key generation is used in the table, then the method getGeneratedKeys () , which returns the generated key , is used to obtain them .

19. What are the advantages in using PreparedStatement over Statement?

- PreparedStatement allows you to prevent attacks like SQL injection, because it automatically escapes special characters.

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

- PreparedStatement is faster than Statement. This is especially noticeable with frequent use of a PreparedStatement or when used to invoke a group of queries.

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

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

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

1. Run Single Queries – poor performance and generally not recommended to do so.

1. Use the Stored Procedure (Stored Procedures) – are specific to a specific database and therefore bad for applications with the ability to connect to different databases.

1. 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 using a split into parts.

Advanced JDBC Interview Questions And Answers

21. What is JDBC ResultSet?

JDBC ResultSet is an interface whose object is created as a result of a database query. It can be represented in the form of a data table, which was formed in response to a query.

The ResultSet object supports a cursor that points to the current row of data. During initialization, the cursor is set to the first line. To move in rows, use the next () method . If there are rows after the current position, the next () method returns true, which can be used to iterate over the table of the results.

By default, the ResultSet object is not modifiable and supports a cursor that is only capable of moving forward. To circumvent this limitation, you can use the following design, which will allow bidirectional movement on the table, as well as the possibility of updating:

1 2	Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

The ResultSet object closes automatically when you close the object that generated it. Also, closing will occur when the query is re-executed or a result is returned from another result set.

To use the ResultSet getter, you can use the column name or an index that starts with 1.

22. What are the different types of JDBC ResultSet?

When creating a Statement, you can specify the different types of ResultSet to get.

Three types of ResultSet objects:

1. ResultSet.TYPE_FORWARD_ONLY : The default type. Supports cursor movement only in the forward direction.

1. ResultSet.TYPE_SCROLL_INSENSITIVE : Bidirectional cursor. The object is not sensitive to changes that occurred with the table after receiving the result.

ResultSet.TYPE_SCROLL_SENSITIVE : Bidirectional cursor. The object is sensitive to changes that have occurred to the database after creating the ResultSet object.

Two types of thread-safe ResultSet objects:

1. ResultSet.CONCUR_READ_ONLY : Supports only read (read only). It is applied by default.

ResultSet.CONCUR_UPDATABLE : Supports the ResultSet update method for updating rows in a data table.

23. How are the setFetchSize () and SetMaxRows () methods used in the Statement?

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

To understand the setFetchSize () method, you need to understand how the Statement and ResultSet work. When a database request is made, the result is processed and stored in the database cache and returned as a ResultSet. ResultSet is a cursor that references a result in a database.

Now let’s say we have a query that returns 100 rows and we set setFetchSize (10) . Now, for each access to the database, only 10 lines are allocated and it will take 10 queries to get all the data. Choosing the optimal amount of fetchSize () can improve the performance of executing a large number of calls to each line and in the case of a large number of lines in the output.

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

24. How to call Stored Procedures using JDBC API?

Stored procedures are groups of SQL queries that are compiled into a database and can be invoked using the JDBC API. The CallableStatement object is used to call stored procedures. We need to set the output parameters OUT before CallableStatement.

CallableStatement stmt = con.prepareCall(“{call insertEmployee(?,?,?,?,?,?)}”);

stmt.setInt(1, id);

stmt.setString(2, name);

stmt.setString(3, role);

stmt.setString(4, city);

stmt.setString(5, country);

//register the OUT parameter before calling the stored procedure

stmt.registerOutParameter(6, java.sql.Types.VARCHAR);

stmt.executeUpdate();

25. What is JDBC Batch Processing and what are its benefits?

It may be necessary to immediately execute a group of similar queries, for example, when loading data from CSV files of a relational database. This can be done simply by using a Statement or PreparedStatement to step through these requests in steps.

There is another possibility in the JDBC API that provides the ability to execute a group of queries at once. 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 the Statement and PreparedStatement. The advantages of this approach include faster work, because Calls to the database can be significantly less.

26. What is JDBC Transaction Management and why is it needed?

By default, when creating a database connection, auto-commit mode will be selected. This means that each time the request is executed, it will be automatically confirmed upon completion.

Each SQL query is transactional and executing any DML or DDL queries upon their completion will be accepted (saved) by the database. If we have to refuse to save the execution of a query (or a group of queries) 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 commits in a particular connection. It should be noted that when auto-commit is disabled, not one change will be saved in the database until the commit () method is called and this should be monitored.

The database server will block the necessary part of the database before confirming the transaction, and since this is a resource-intensive task, you must confirm the transaction immediately after completing the task.

27. How to roll back a JDBC transaction?

To do this, use the Connection rollback () method of the object , which rolls back the transaction. All changes to the transaction will be canceled and the database lock on this Connection object will be canceled.

28. 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 entire transaction, but only a part to the save point. Any savepoint is automatically released and becomes unavailable after confirming the transaction or its rollback. A rollback to the save point makes all subsequent saves unavailable and it will be impossible to return to them.

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

JDBC DataSource is a javax.sql package interface and is more advanced than DriverManager for connecting to a database. We can use the DataSource to create a database connection and implement a driver class that will do all the work of maintaining the connection. In addition to connecting through the Database, DataSource provides the following additional features:

- PreparedStatement caching to speed query processing

- Connection timeout settings

- Logging capabilities

- ResultSet Maximum Size Threshold

Connection Pooling support in a servlet container using JNDI support.

30. How to create a JDBC connection pool using JDBC DataSource and JNDI in Apache Tomcat Server?

Creating a JDBC connection pool when using Tomcat requires some simple steps. You must create a JDBC JNDI resource in the server configuration file (server.xml or context.xml).

server.xml :

<Resource name=”jdbc/MyDB”

global=”jdbc/MyDB”

auth=”Container”

type=”javax.sql.DataSource”

driverClassName=”com.mysql.jdbc.Driver”

url=”jdbc:mysql://localhost:3306/DataBaseName”

username=”root”

password=”admin”

maxActive=”100″

maxIdle=”20″

minIdle=”5″

maxWait=”10000″/>

context.xml :

<ResourceLink name=”jdbc/MyLocalDB”

global=”jdbc/MyDB”

auth=”Container”

type=”javax.sql.DataSource” />

In a web application using InitialContext, we use the following entry to search for the JNDI resource specified in the configuration above. And then you can get the connection.

1 2	Context ctx = new InitialContext(); DataSource ds = (DataSource) ctx.lookup(“java:/comp/env/jdbc/MyLocalDB”);

JDBC Interview Questions And Answers For Experienced

31. Tell us about the Apache DBCP API.

When using a DataSource to get a database connection, there is a problem of tight connection between the code and the DataSource implementation driver. In addition, most of the code is pattern-repeatable (the so-called boilerplate code), except for the choice of the data source implementation class.

The Apache DBCP API helps us get rid of these problems by providing a DataSource implementation that works as an abstraction layer between our program and various JDBC drivers. The Apache DBCP API library is based on the Commons Pool library, so you need to make sure that all the necessary dependencies are properly connected to the project.

32. What are the isolation levels of connections in JDBC?

The transaction isolation level is a value that determines the level at which inconsistent data is allowed in a transaction, that is, the degree of isolation of one transaction from another. A higher level of isolation improves data accuracy, but it may decrease the number of concurrent transactions. On the other hand, a lower level of isolation allows for more concurrent transactions, but reduces the accuracy of the data.

When we use transactions in JDBC to ensure data integrity, the DBMS uses locks to block the access of other accesses to the data involved in the transaction. Such locks are necessary to prevent dirty reading (Dirty Read), non-repeatable reading (Non-Repeatable Read) and phantom reading (Phantom-Read).

The isolation level of the JDBC transaction used by the DBMS for the locking mechanism can be set using the setTransactionIsolation () method . You can get information about the level of isolation used by the Connection getTransactionIsolation () method .

Isolation level	Transaction	Dirty read	Non-Repeatable Read	Phantom Read
TRANSACTION_NONE	Not Supported	Not applicable	Not applicable	Not applicable
TRANSACTION_READ_COMMITTED	Supported	Prevented	Allowed	Allowed
TRANSACTION_READ_UNCOMMITTED	Supported	Allowed	Allowed	Allowed
TRANSACTION_REPEATABLE_READ	Supported	Prevented	Prevented	Allowed
TRANSACTION_SERIALIZABLE	Supported	Prevented	Prevented	Prevented

33. What do you know about JDBC RowSet? What are the different types of RowSet?

JDBC RowSet contains tabular data in a more flexible format than ResultSet. All RowSet objects are derived from ResultSet, so they have all the capabilities of a ResultSet with some additional functions. The RowSet interface is defined in the javax.sql package. The following additional functions provided by RowSet can be highlighted:

- Functions similar to Java Beans with properties and get \ set methods for working with them. RowSet uses the JavaBeans event model. As a result, you can send notifications to any registered component for such events, for example, cursor movement, updates / insert / delete from the row and change the contents of the RowSet.

RowSet objects support scrolling by data (scrollable), as well as being updated by default. So if the DBMS does not support scrolling or an updated ResultSet, we can use RowSet to get these functions.

RowSet can be divided into two types:

1. Connected RowSet Objects – these objects connect to the database and are very similar to ResultSet objects. The JDBC API provides only one connection to the RowSet object – javax.sql.rowset.JdbcRowSet and this is the standard implementation of the com.sun.rowset.JdbcRowSetImpl class .

Disconnected RowSet Objects – these RowSet objects do not require a database connection. They are more lightweight and can be serialized. Such objects are well suited for data transmission over the network. There are four implementations of this type of object (disconnected RowSet objects).

Briefly consider the four implementation of Disconnected RowSet Objects :

- CachedRowSet – objects can receive a connection and execute a query, read ResultSet data to populate RowSet data. We can manage and update the data at the time of disconnection from the database and record the changed data during the next connection.

- WebRowSet obtained from CachedRowSet – such objects can read and write XML documents.

- JoinRowSet obtained from WebRowSet – can form SQL JOIN without connecting to a data source.

FilteredRowSet is obtained from WebRowSet – support for applying filtering criteria; therefore, only selected (received) data is visible.

34. What is the difference between ResultSet and RowSet?

RowSet objects are derived from ResultSet, so they have all the capabilities of a ResultSet with some additional functions. One of the significant advantages is the ability to work with data without connecting to the database, as well as their lightness and the ability to send data objects over the network.

Whether to use a ResultSet or RowSet depends on your requirements. ResultSet may be suitable for long-term connections, while RowSet will be the best choice for connections to databases with the possibility of disconnection and processing of received data.

35. Give an example of the most common exceptions in JDBC.

Some of the most common JDBC exceptions are:

- java.sql.SQLException is the base class for JDBC exceptions.

- java.sql.BatchUpdateException – occurs when batch requests are excepted. May depend on the type of JDBC driver that the base SQLException may throw instead.

- java.sql.SQLWarning – for warning messages of various SQL operations.

java.sql.DataTruncation – when data values are unexpectedly truncated due to reasons independent of exceeding MaxFieldSize.

36. Tell us about the data types CLOB and BLOB in JDBC.

Character Large OBjects (CLOBs) is a data type (internal character object) used to store large objects. When selecting a value of any LOB type, a pointer is returned via the SELECT statement, and not the value itself; In addition, LOB types can be external. This data type is suitable for storing textual information that may fall outside the normal VARCHAR data type (upper limit of 32 KB).

An internal blob (BLOB) is a large binary object that can contain a variable amount of data. This data type can store data larger than VARBINARY (32K limit). A type of data intended primarily for storing images, audio and video, as well as compiled program code.

37. What do you know about dirty read in JDBC? What isolation level does this type of reading prevent?

“Dirty” reading (eng. Dirty read) – reading data added or modified by a transaction, which is subsequently not confirmed (rolled back). Obtaining an invalid value after the transaction (after rolling back the transaction) may lead to unexpected results.

Transaction 1	Transaction 2
	SELECT f2 FROM tbl1 WHERE f1=1;
UPDATE tbl1 SET f2=f2+1 WHERE f1=1;
	SELECT f2 FROM tbl1 WHERE f1=1;
ROLLBACK WORK;

Dirty Read can be prevented using the following isolation levels: TRANSACTION_READ_COMMITTED , TRANSACTION_REPEATABLE_READand TRANSACTION_SERIALIZABLE .

38. What are the two commit phases?

When we work in distributed systems where multiple databases are involved, we must use a protocol with 2 commit phases. Phase 2 commit protocol is an atomic protocol for distributed systems.

In the first step, the transaction manager passes commit-request to all transaction resources. If all transaction resources are OK, then the transaction manager records transaction changes for all resources. If any of the transaction resources notifies of the cancellation, then the transaction manager can roll back all transaction changes.

39. Give an example of the different types of blocking in JDBC.

At a broader level, there are two types of locking mechanisms to prevent data from being damaged due to multiple users working with data simultaneously. According to the implementation logic, there are two types of locks.

Optimistic locking does not limit the modification of the data being processed by third-party sessions, but before starting the intended modification it requests the value of some selected attribute of each of the data lines (usually the VERSION name and the integer type with the initial value 0 are used).

Before writing modifications to the database, the value of the selected attribute is checked and, if it has changed, the transaction is rolled back or different collision resolution schemes are applied. If the value of the selected attribute has not changed – the modifications are recorded while changing the value of the selected attribute (for example, increment) to signal to other sessions that the data has changed.

Pessimistic blocking– it is superimposed before the intended modification of the data on all the lines that such modification presumably affects. All the time such a lock is in effect, modification of data from third-party sessions is excluded; data from blocked rows is available according to the isolation level of the transaction. Upon completion of the proposed modification, a consistent recording of the results is guaranteed.

Must Read Java Interview Questions Books 2020

- Java Interview Guide: How to Build Confidence with a Solid Understanding of Core Java Principles

- Core JAVA Interview Questions You’ll Most Likely Be Asked: Volume 8 (Job Interview Questions)

- Java / J2EE: Interview Questions You’ll Most Likely Be Asked: Volume 10 (Job Interview Questions)

- 500+ Java & J2ee Interview Questions & Answers: Java & J2ee Programming
- Java / J2EE Interview Questions: A Guide for Interview

- Java/J2EE Job Interview Companion
- Data Structures and Algorithms Made Easy in Java: Data Structure and Algorithmic Puzzles

- Cracking the C, C++ and Java Interview

- JAVA/J2EE: Interview Questions (With Set of 2 CDs)

Java Programming Interviews Exposed (WROX)

Transaction 1	Transaction 2
	SELECT SUM(f2) FROM tbl1;
INSERT INTO tbl1 (f1,f2) VALUES (15,20);
COMMIT;
	SELECT SUM(f2) FROM tbl1;