Explore topic-wise InterviewSolutions in .

We can dynamically create a table by using the FOLLOWING code:

• The transaction isolation level is a value that decides the level at which inconsistent data is permitted in a transaction, which means it represents the degree of isolation of one transaction from another. A higher level of isolation will result in improvement of data accuracy, but it might decrease the number of concurrent transactions. Similarly, a lower level of isolation PERMITS for more concurrent transactions, but it reduces the data accuracy.
• To ensure data integrity during transactions in JDBC, the DBMS make USE of locks to prevent access to other accesses to the data which is INVOLVED in the transaction. Such locks are necessary for preventing Dirty Read, Non-Repeatable Read, and Phantom-Read in the database.
• It is used for the locking mechanism by DBMS and can be SET using setTransactionIsolation() method. You can obtain details about the level of isolation used by the connection using getTransactionIsolation() method.

• Two-phase commit is useful for a distributed environment where numerous processes take part in the distributed transaction process. In simpler words, we can say that, if a transaction is executing and it is affecting multiple databases then a two-phase commit will be used to make sure that all databases are synchronized with each other.
• In two-phase commit, commit or ROLLBACK is performed by two phases given below:
  • Commit request phase: In this phase, the main process or co-ordinator process take a vote of all other process that they have COMPLETED their process successfully and ready to commit, if all the votes are “yes” then they CONTINUE for the NEXT phase. And if “No” then rollback will be performed.
  • Commit phase: As per vote, if all the votes are “yes” then commit is DONE.
• In the same way, when any transaction changes multiple databases after transaction execution, it will issue a pre-commit command on each database and all databases will send an acknowledgment. Based on acknowledgment, if all are positive transactions then it will issue the commit command otherwise rollback will be done.

Some of the most common JDBC exceptions are GIVEN below:

• java.sql.SQLException - It is the base class for JDBC exceptions.
• java.sql.BatchUpdateException – It occurs during the batch update operation. May DEPEND on the JDBC driver TYPE that the base SQLException may throw instead.
• java.sql.SQLWarning – It is displayed as a warning MESSAGE of various SQL operations.
• java.sql.DataTruncation – This exception occurs when data values are unexpectedly truncated due to reasons independent of EXCEEDING MaxFieldSize.

The connection interface is having 5 methods for transaction management. They are given below:

• setAutoCommit() method:
  The value of AutoCommit is set to TR
  UE by default. After the SQL statement execution, it will be committed automatically. By using this method we can set the value for AutoCommit.
  Syntax: conn.setAutoCommit(boolean_value)
  Here, boolean_value is set to TRUE for enabling autocommit mode for the connection, FALSE for disabling it.
• Commit() method:
  The commit() method is USED for committing the data. After the SQL statement execution, we can call the commit() method. It will commit the CHANGES made by the SQL statement.
  Syntax: conn.commit();
• Rollback() method:
  The rollback() method is used to undo the changes made till the LAST commit has occurred. If we face any problem or exception in the SQL statements execution flow, we may roll back the transaction.
  Syntax: conn.rollback();
• setSavepoint() method:
  If you have set a savepoint in the transaction (a group of SQL statements), you can use the rollback() method to undo all the changes till the savepoint or after the savepoint(), if something goes WRONG within the current transaction. The setSavepoint() method is used to create a new savepoint which refers to the current state of the database within the transaction.
  Syntax:  Savepoint sp= conn.setSavepoint("MysavePoint")
• releaseSavepoint() method:
  It is used for deleting or releasing the created savepoint.
  Syntax: conn.releaseSavepoint("MysavePoint");

Benefits of PREPAREDSTATEMENT over Statement interface are:

• It performs faster COMPARED to the Statement because the Statement needs to be compiled each time when we run the code whereas the PreparedStatement is compiled once and then executed only on runtime.
• It can execute parametrized QUERIES. But Statement can only run static queries.
• The query used in PreparedStatement looks similar each time, so the database can REUSE the previous access plan. Statement inline the parameters into the string, so the query doesn’t look to be the same every time which prevents reusage of cache.

• The sequence of actions (SQL statements) served as a single unit that is called a transaction. Transaction Management places an important ROLE in RDBMS-oriented applications to maintain data consistency and integrity.
• Transaction Management can be described well – by using ACID properties. ACID STANDS for Atomicity, Consistency, Isolation, and Durability.
  • Atomicity - If all queries are successfully executed, then only data will be committed to the database.
  • Consistency - It ensures bringing the database into a consistent STATE after any transaction.
  • Isolation - It ensures that the transaction is isolated from other transactions.
  • Durability - If a transaction has been committed once, it will remain always committed, even in the situation of errors, power loss, etc.
• Need for Transaction Management:
  When creating a connection to the database, the auto-commit mode will be selected by default. This implies that every time when the request is executed, it will be committed automatically upon completion.
  We might want to commit the transaction after the execution of few more SQL statements. In such a situation, we must set the auto-commit value to False. So that data will not be able to commit before EXECUTING all the queries. In case if we get an exception in the transaction, we can rollback() changes MADE and make it like before.

JDBC supports 2 types of processing MODELS to access the DATABASE. They are:

• Two-tier Architecture: Here Java programs are explicitly connected with the database. It doesn’t require any MEDIATOR such as an application server for connecting with the database except the JDBC driver. It is also called client-server architecture.

• Three-tier Architecture: It is the complete opposite of two-tier architecture. There will be no explicit communication between the JDBC driver or Java application and the database. It will make use of an application server as a mediator between them. Java CODE will send the request to an application server, then the server will send it to the database and receive the response from the database.

Stored procedures are a set of SQL queries that are compiled in the DATABASE and will be executed from JDBC API. For executing Stored procedures in the database, JDBC CallableStatement can be used. The SYNTAX for initializing a CallableStatement is:

We must register the OUT parameters before executing the CallableStatement.

Loading the driver: At first, you need to load or register the driver before using it in the program. Registration must be done once in your program. You can register a driver by using any one of the two methods mentioned below:

• Class.forName(): Using this, we load the driver’s class file into memory during runtime. It’s not required to use a new or creation of an object.

The below given example uses Class.forName() to load the Oracle driver:

The MySQL Connector/J version 8.0 library comes with a JDBC driver class: com.mysql.jdbc.Driver. Before Java 6, we had to load the driver explicitly using the statement given below:

However, this statement is no longer needed, because of a new update in JDBC 4.0 that comes from Java 6. As long as you place the MySQL JDBC driver JAR file into the classpath of your program, the driver manager can find and load the driver.

• DriverManager.registerDriver(): DriverManager is a built-in Java class with a static member register. Here we will be calling the constructor of the driver class during compile time.

The below given example uses DriverManager.registerDriver() to register the Oracle driver:

For registering the MySQL driver, use the below-given CODE:

Create the connections: 

• After loading the driver into the program, establish connections using the code given below:

Here,

con: Reference to a Connection interface.

url: Uniform Resource Locator.

user: Username from which SQL command prompt is accessed.

password: Password from which SQL command prompt is accessed.

• Url in Oracle can be created as follows:

Where oracle represents the database used, thin is the driver used, @localhost is the IP(Internet Protocol) address where the database is stored, 1521 is the PORT number and xe represents the service provider.

All 3 parameters given above are of string type and are expected to be declared by the programmer before the function call. Use of this can be REFERRED from the final code of an application.

• Url in MySQL can be created as follows:

Where localhost represents hostname or IP address of the MySQL server, 3306 port number of the server and by default, it is 3306, test1 is the name of the database on the server.

Create a statement:

• Once a connection establishment is done, you can interact with the database. The Statement, PreparedStatement, and CallableStatement JDBC interfaces will define the methods that permit you to send SQL COMMANDS and receive data from the database.
• We can use JDBC Statement as follows:

Here, con is a reference to the Connection interface used in the earlier step.

Execute the query:

• Here, query means an SQL query. We can have various types of queries. A few of them are as follows:
  • Query for updating or inserting a table in a database.
  • Query for data retrieval.
• The executeQuery() method that belongs to the Statement interface is used for executing queries related to values retrieval from the database. This method returns the ResultSet object which can be used to get all the table records.
• The executeUpdate(sql_query) method of the Statement interface is used for executing queries related to the update/insert operation.

Example:

Here SQL is the SQL query of string type.

Close the connection:

• So finally we have sent the data to the location specified and now we are at the end of our task completion.
• On connection closing, objects of Statement and ResultSet will be automatically closed. The close() method of the Connection interface is used for closing the connection.

Example:

con.close();

Implementation of JDBC Oracle database connection using a Java program:

Implementation of JDBC MySQL database connection using Java program:

“No suitable driver” ERROR occurs during a call to the DriverManager.getConnection() method, because of the following reasons:

• Unable to LOAD the appropriate JDBC drivers before calling the getConnection() method.
• It can specify an invalid or wrong JDBC URL, which cannot be recognized by the JDBC driver.
• This error may OCCUR when one or more shared libraries required by the bridge cannot be LOADED.

• Dirty read implies the meaning “read the value which may or may not be correct”. In the database, when a transaction is executing and changing some field value, at the same time another transaction comes and reads the changed field value before the first transaction could commit or rollback the value, which may cause an invalid value for that particular field. This situation is known as a dirty read.
• Consider an example given below, where Transaction 2 changes a row but does not commit the changes made. Then Transaction 1 reads the uncommitted data. Now, if Transaction 2 GOES for roll BACKING its changes (which is already read by Transaction 1) or UPDATES any changes to the database, then the view of the data may be wrong in the RECORDS related to Transaction 1. But in this case, no row exists that has an id of 100 and an age of 25.

• Connection POOLING means database connections will be STORED in the cache and can be REUSED when future requests to the database are required. So in this mechanism, the client need not make new connections every time for interacting with the database. Instead of that, connection objects are stored in the connection POOL and the client will get the connection object from there.
• Advantages of using a connection pool are:
  • It is faster.
  • Easier to diagnose and ANALYZE database connections.
  • Increases the performance of executing commands on a database.

• If two users are viewing the same record, then there is no issue, and LOCKING will not be DONE. If one user is updating a record and the second user also wants to update the same record, in this situation, we are going to use locking so that there will be no lost update.
• Two types of locking are AVAILABLE in JDBC by which we can handle multiple user issues using the record. They are:
  • OPTIMISTIC Locking: It will lock the record only when an update takes place. This type of locking will not MAKE use of exclusive locks when reading or selecting the record.
  • Pessimistic Locking: It will lock the record as soon as it selects the row to update. The strategy of this locking system guarantees that the changes are made safely and consistently.

• Getter methods: These are used for retrieving the particular column values of the table from RESULTSET. As a parameter, EITHER the column index value or column NAME should be PASSED. Usually, the getter method is represented as getXXX() methods.
  Example:
  int getInt(string Column_Name)

The above statement is used to retrieve the value of the specified column Index and the return type is an int data type.

• SETTER Methods: These methods are used to set the value in the database. It is almost similar to getter methods, but here it requires to pass the data/values for the particular column to insert into the database and the column name or index value of that column. Usually, setter method is represented as setXXX() methods.
  Example:
  void setInt(int Column_Index, int Data_Value)

The above statement is used to insert the value of the specified column Index with an int value.

A RowSet is an OBJECT that encapsulates a set of rows from JDBC result sets or tabular data sources.
There are five types of RowSet interfaces available in JDBC. They are:

• JDBCRowSet: It is a connected RowSet, which is having a live connection to the database, and all calls on this are percolated to the mapping call in the JDBC connection, result set, or statement. The Oracle implementation of JDBCRowSet is done by using oracle.jdbc.rowset.OracleJDBCRowSet .
• CachedRowSet: It is a RowSet in which the rows are cached and RowSet is disconnected, which means it does not maintain an active database connection. The oracle.jdbc.rowset.OracleCachedRowSet class is used as the Oracle implementation of CachedRowSet.
• WebRowSet: It is an extension to CachedRowSet and it represents a set of fetched rows of tabular data that can be passed between tiers and components so that no active data source connections need to be maintained.
  It PROVIDES support for the production and consumption of result sets and their synchronization with the data source, both in XML(Extensible Markup Language) format and in a disconnected fashion. This permits result sets to be transmitted across tiers and over Internet protocols. The Oracle implementation of WebRowSet is done by using oracle.jdbc.rowset.OracleWebRowSet .
• FilteredRowSet: It’s an extension to WebRowSet and gives programmatic support to FILTER its content. This enables you to avoid the difficulty of query supply and processing involved. The Oracle implementation of FilteredRowSet is done by using oracle.jdbc.rowset.OracleFilteredRowSet .
• JoinRowSet: It’s an extension to WebRowSet and consists of related data from various RowSets. There is no standard way to establish a SQL JOIN OPERATION between disconnected RowSets WITHOUT a data source connection. A JoinRowSet addresses this problem. The Oracle implementation of JoinRowSet is done by using oracle.jdbc.rowset.OracleJoinRowSet class.

The execute() method is used in the situations when you are not sure about the type of statement else you can use executeQuery() or executeUpdate() method.

• DATASOURCE is an interface defined in javax.sql package and is used for obtaining the DATABASE connection. It can be used as a GOOD alternative for a DriverManager class as it allows the details about the database to your application program.
• A driver that is accessed through a DataSource object, does not register itself with the DriverManager. Instead, a DataSource object is retrieved through a lookup operation and then it can be used to create a Connection object.
• Benefits of DataSource:
  • Caching of PreparedStatement for FASTER processing
  • ResultSet maximum size threshold
  • Logging features
  • Connection timeout settings
  • Connection POOLING in servlet container using the support of JNDI registry.

• Batch processing is the process of executing multiple SQL statements in one transaction. For example, consider the CASE of LOADING data from CSV(Comma-Separated Values) files to relational database tables. Instead of using Statement or PreparedStatement, we can use Batch processing which executes the bulk of queries in a single go for a database.
• Advantages of Batch processing:
  • It will reduce the communication time and improves performance.
  • Batch processing makes it easier to process a huge AMOUNT of data and CONSISTENCY of data is also maintained.
  • It is much faster than executing a single statement at a time because of the FEWER number of database calls.
• How to perform Batch processing?
  To perform Batch processing, addBatch() and executeBatch() methods are used. These 2 methods are available in the Statement and PreparedStatement classes of JDBC API.

Statements are USEFUL for sending SQL commands to the database and receiving data from the database. There are three TYPES of statements in JDBC. They are:

• Statement: It is the factory for ResultSet. It is used for general-purpose access to the database by executing the static SQL query at runtime. Example:

• PreparedStatement: It is used when we need to GIVE input data to the query at runtime and also if we want to execute SQL statements repeatedly. It is more EFFICIENT than a statement because it involves the pre-compilation of SQL. Example:

• CallableStatement: It is used to call stored procedures on the database. It is capable of accepting runtime parameters. Example:

The java.sql package contains different interfaces and classes for JDBC API. They are:

Interfaces:

• Connection: The object of Connection is created by using getConnection() method of DriverManager class. DriverManager is the factory for connection.
• STATEMENT: The object of the Statement is created by using createStatement() method of the Connection class. The Connection interface is the factory for Statement.
• PreparedStatement: The PreparedStatement object is created by using prepareStatement() method of Connection class. It is used for executing the parameterized query.
• ResultSet: The ResultSet object maintains a cursor pointing to a table row. At first, the cursor points before the first row. The executeQuery() method of the Statement interface returns the object of ResultSet.
• ResultSetMetaData: The ResultSetMetaData interface object contains the details about the data(table) such as number of columns, name of the COLUMN, column type etc. The getMetaData() method of ResultSet returns the ResultSetMetaData object.
• DatabaseMetaData: It is an interface that has methods to get metadata of a database, like name of the database product, version of database product, driver name, name of the total number of views, name of the total number of tables, etc. The getMetaData() method that belongs to Connection interface returns the DatabaseMetaData object.
• CallableStatement: CallableStatement interface is useful for calling the stored procedures and FUNCTIONS. We can have business logic on the database through the usage of stored procedures and functions, which will be helpful for the improvement in the performance as these are pre-compiled. The prepareCall() method that belongs to the Connection interface returns the object of CallableStatement.

Classes:

• DriverManager: It pretends to be an interface between the user and drivers. DriverManager keeps track of the available drivers and handles establishing a connection between a database and the relevant driver. It contains various methods to keep the interaction between the user and drivers.
• BLOB: BLOB stands for Binary Large Object. It represents a collection of binary data such as images, audio, and video files, etc., which is stored as a SINGLE entity in the DBMS(Database Management System).
• CLOB: CLOB stands for Character Large Object. This data type is used by multiple database management systems to store character files. It is the same as BLOB except for the difference, instead of binary data, CLOB represents character stream data such as character files, etc.

ResultSet refers to the row and column data contained in a ResultSet object. The object of ResultSet maintains a cursor pointing to the CURRENT row of data in the result set.
There are three types of ResultSet which have constants to control the movement of the cursor in backward, FORWARD, and in a particular row. If we do not declare any ResultSet, then by default TYPE_FORWARD_ONLY will be called.

• ResultSet.TYPE_FORWARD_ONLY: USING this, the cursor can only move forward from start to END in the result set.
• ResultSet.TYPE_SCROLL_INSENSITIVE: Using this, the cursor can move in both forward and backward directions. Here, the result set is INSENSITIVE to the changes done in the database by others, that occur after the result set was created.
• ResultSet.TYPE_SCROLL_SENSITIVE: Using this, the cursor can move in forward and backward direction, and the result set is sensitive to changes made to the database by others, that occur after the result set was created.

There are four types of JDBC drivers in Java. They are:

• Type I: JDBC - ODBC bridge DRIVER
  • In this, the JDBC–ODBC bridge acts as an interface between the client and database server. When a user uses a Java application to send requests to the database using JDBC–ODBC bridge, it converts the JDBC API into ODBC API and then sends it to the database. When the result is received from the database, it is sent to ODBC API and then to JDBC API.
  • It is platform-dependent because it uses ODBC which depends on the native library of the operating system. In this, JDBC–ODBC driver should be installed in every client system and database must support for ODBC driver.'
  • It is easier to use but it gives low performance because it involves the conversion of JDBC method calls to the ODBC method calls.

• Type II: Native API – Partially Java Driver:
  • It is almost similar to a Type I driver. Here, native code replaces the ODBC part. This native code part is targeted at a PARTICULAR database product. It uses libraries of the client-side of the database. This Type II Driver converts the JDBC method calls to native calls of the database native API.
  • When the database gets the requests from the user, the requests are processed and sends the results back in the native format which is then converted into JDBC format and pass it to the Java application.
  • It was instantly adopted by the database vendors because it was quick and cheaper to implement. This driver gives faster response and performance compared to the Type I driver.

• Type III: Network Protocol - Fully Java Driver:
  • The type III driver is completely written in Java. It is similar to the 3-tier approach to access the database. It helps to send the JDBC method calls to an intermediate server. The intermediate server communicates with the database on behalf of JDBC. The application server converts the JDBC calls either DIRECTLY or indirectly to the database protocol which is vendor-specific.
  • This approach does not increase the efficiency of architecture and it is costlier, due to this most of the database vendors don’t choose this driver. You need to have good knowledge about the application server for using this approach since the application server is used here.

• Type IV: Thin Driver - Fully Java Driver
  • Type IV driver is directly implemented and it directly converts JDBC calls into vendor-specific database protocol. Most of the JDBC Drivers used today are type IV drivers.
  • It is platform-independent since it is written fully in Java. It can be installed inside the Java Virtual Machine(JVM) of the client, so there is no need of installing any software on the client or server side. This drive architecture is having all the logic to communicate directly with the database in a single driver.
  • It provides better performance compared to other driver types. It permits easy deployment. Nowadays, this driver is developed by the database vendor itself so that programmers can use it directly without any dependencies on other sources.