Java JDBC Questions

What is JDBC?

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.

Describe a general JDBC Architecture.

General JDBC Architecture consists of two layers JDBC API (This provides the application-to-JDBC Manager connection) and JDBC Driver API (This supports the JDBC Manager-to-Driver Connection).

What are the common JDBC API components?

JDBC API consists of following interfaces and classes DriverManager, Driver, Connection, Statement, ResultSet, SQLException.

What is a JDBC DriverManager?

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.

What is a JDBC Driver?

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.

What is a connection?

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

What is a statement?

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

What is a ResultSet?

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.

What is the design pattern followed by JDBC?

JDBC architecture decouples an abstraction from its implementation. Hence JDBC follows a bridge design pattern. The JDBC API provides the abstraction and the JDBC drivers provide the implementation. New drivers can be plugged-in to the JDBC API without changing the client code.

What are the different types of JDBC Statements?

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.

What is difference between statement and prepared statement?

Prepared statements offer better performance, as they are pre-compiled. Prepared statements reuse the same execution plan for different arguments rather than creating a new execution plan every time. Prepared statements use bind arguments, which are sent to the database engine. This allows mapping different requests with same prepared statement but different arguments to execute the same execution plan. Prepared statements are more secure because they use bind variables, which can prevent SQL injection attack.

How do you register a driver?

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.

How cursor works in scrollable result set?

There are several methods in the ResultSet interface that involve moving the cursor, like beforeFirst(), afterLast(), first(), last(), absolute(int row), relative(int row), previous(), next(), getRow(), moveToInsertRow(), moveToCurrentRow().

What does the Class.forName(“MyClass”) do?

Class.forName(“MyClass”)

  • Loads the class MyClass.
  • Execute any static block code of MyClass.
  • Returns an instance of MyClass.

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’. Because there is always a chance that the first transaction might rollback the change which causes the second transaction reads an invalid value.

What is the difference between execute, executeQuery, executeUpdate?

Statement execute(String query) is used to execute any SQL query and it returns TRUE if the result is an ResultSet such as running Select queries. The output is FALSE when there is no ResultSet object such as running Insert or Update queries. We can use getResultSet() to get the ResultSet and getUpdateCount() method to retrieve the update count.

Statement executeQuery(String query) is used to execute Select queries and returns the ResultSet. ResultSet returned is never null even if there are no records matching the query. When executing select queries we should use executeQuery method so that if someone tries to execute insert/update statement it will throw java.sql.SQLException with message “executeQuery method can not be used for update”.

Statement executeUpdate(String query) is used to execute Insert/Update/Delete (DML) statements or DDL statements that returns nothing. The output is int and equals to the row count for SQL Data Manipulation Language (DML) statements. For DDL statements, the output is 0.

You should use execute() method only when you are not sure about the type of statement else use executeQuery or executeUpdate method.

What is JDBC PreparedStatement?

JDBC PreparedStatement object represents a precompiled SQL statement. We can use it’s setter method to set the variables for the query.

Since PreparedStatement is precompiled, it can then be used to efficiently execute this statement multiple times. PreparedStatement is better choice that Statement because it automatically escapes the special characters and avoid SQL injection attacks.

What is the use of getGeneratedKeys() method in Statement.

Sometimes a table can have auto generated keys used to insert the unique column value for primary key. We can use Statement getGeneratedKeys() method to get the value of this auto generated key.

What are the benefits of PreparedStatement over Statement?

Some of the benefits of PreparedStatement over Statement are:

PreparedStatement helps us in preventing SQL injection attacks because it automatically escapes the special characters.

PreparedStatement allows us to execute dynamic queries with parameter inputs.

PreparedStatement is faster than Statement. It becomes more visible when we reuse the PreparedStatement or use it’s batch processing methods for executing multiple queries.

PreparedStatement helps us in writing object Oriented code with setter methods whereas with Statement we have to use String Concatenation to create the query. If there are multiple parameters to set, writing Query using String concatenation looks very ugly and error prone.

What is JDBC Batch Processing and what are it’s benefits?

Sometimes we need to run bulk queries of similar kind for a database, for example loading data from CSV files to relational database tables. As we know that we have option to use Statement or PreparedStatement to execute queries. Apart from that JDBC API provides Batch Processing feature through which we can execute bulk of queries in one go for a database.

JDBC API supports batch processing through Statement and PreparedStatement addBatch() and executeBatch() methods.

Batch Processing is faster than executing one statement at a time because the number of database calls are less.

What is JDBC Transaction Management and why do we need it?

By default when we create a database connection, it runs in auto-commit mode. It means that whenever we execute a query and it’s completed, the commit is fired automatically. So every SQL query we fire is a transaction and if we are running some DML or DDL queries, the changes are getting saved into database after every SQL statement finishes.

Sometimes we want a group of SQL queries to be part of a transaction so that we can commit them when all the queries runs fine and if we get any exception, we have a choice of rollback all the queries executed as part of the transaction.

JDBC API provide method setAutoCommit(boolean flag) through which we can disable the auto commit feature of the connection. We should disable auto commit only when it’s required because the transaction will not be committed unless we call the commit() method on connection. Database servers uses table locks to achieve transaction management and it’s resource intensive process. So we should commit the transaction as soon as we are done with it.

How to rollback a JDBC transaction?

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

What are common JDBC Exceptions?

Some of the common JDBC Exceptions are:

java.sql.SQLException – This is the base exception class for JDBC exceptions.

java.sql.BatchUpdateException – This exception is thrown when Batch operation fails, but it depends on the JDBC driver whether they throw this exception or the base SQLException.

java.sql.SQLWarning – For warning messages in SQL operations.

java.sql.DataTruncation – when a data values is unexpectedly truncated for reasons other than its having exceeded MaxFieldSize.

What is CLOB and BLOB datatypes in JDBC?

Character Large OBjects (CLOBs) are character string made up of single-byte characters with an associated code page. This data type is appropriate for storing text-oriented information where the amount of information can grow beyond the limits of a regular VARCHAR data type (upper limit of 32K bytes).

Binary Large OBjects (BLOBs) are binary string made up of bytes with no associated code page. This data type can store binary data larger than VARBINARY (32K limit). This data type is good for storing image, voice, graphical, and other types of business or application-specific data.

What do you understand by DDL and DML statements?

Data Definition Language (DDL) statements are used to define the database schema. Create, Alter, Drop, Truncate, Rename statements comes under DDL statements and usually they don’t return any result.

Data Manipulation Language (DML) statements are used to manipulate data in the database schema. Select, Insert, Update, Delete, Call etc are example of DML statements.

What is difference between java.util.Date and java.sql.Date?

java.util.Date contains information about the date and time whereas java.sql.Date contains information only about the date, it doesn’t have time information. So if you have to keep time information in the database, it is advisable to use Timestamp or DateTime fields.

How to insert an image or raw data into database?

We can use BLOB to insert image or raw binary data into database.

What is SQL Warning? How to retrieve SQL warnings in the JDBC program?

SQLWarning is the subclass of SQLException and we can retrieve it by calling getWarnings() method on Connection, Statement, and ResultSet objects. SQL Warnings doesn’t stops the execution of the script but alerts the user about the warning.

What is cold backup, hot backup, warm backup recovery?

Cold backup means all these files must be backed up at the same time, before the database is restarted. Hot backup (official name is ‘online backup’ ) is a backup taken of each tablespace while the database is running and is being accessed by the users