Mockpreps
🧑💼
🪴
Login now and master your exams with Mockpreps’ mock tests!
Top Interview Questions
Java Database Connectivity (JDBC) is a Java-based API that enables Java applications to interact with databases. It is part of the Java Standard Edition platform and is included in the java.sql package. JDBC provides a standard interface for connecting to a wide range of relational databases, executing SQL queries, retrieving results, and handling database updates. It allows developers to write database-independent Java applications that can connect to any database supporting JDBC drivers.
The primary purpose of JDBC is to abstract the low-level details of database communication and allow Java programs to interact with databases in a consistent and straightforward manner. This capability is crucial for building enterprise applications, web applications, and any system requiring persistent data storage.
JDBC follows a layered architecture, which separates the application, JDBC API, JDBC Driver, and the database. The architecture can be visualized in the following layers:
Java Application Layer:
This is the topmost layer where developers write Java programs. These programs use JDBC API methods to perform database operations such as querying, updating, and managing database metadata.
JDBC API Layer:
This layer provides classes and interfaces for interacting with databases. Key interfaces include Connection, Statement, PreparedStatement, CallableStatement, and ResultSet. The JDBC API defines methods to perform database operations without worrying about the underlying database implementation.
JDBC Driver Layer:
JDBC drivers are responsible for translating Java calls into database-specific calls. There are four types of JDBC drivers:
Type 1 – JDBC-ODBC Bridge Driver: Uses ODBC drivers to connect to databases. It is largely obsolete due to performance and platform dependency issues.
Type 2 – Native-API/Partly Java Driver: Converts JDBC calls into database-specific native calls. Requires native libraries.
Type 3 – Network Protocol Driver: Translates JDBC calls into a database-independent network protocol, which is then translated to database-specific protocol by a server.
Type 4 – Thin Driver: Pure Java driver that converts JDBC calls directly into database-specific protocol. This is the most commonly used driver today.
Database Layer:
The database layer contains the actual database management system (DBMS), such as MySQL, Oracle, PostgreSQL, or SQL Server. This layer processes SQL queries, returns results, and handles transactions.
JDBC provides several interfaces and classes that enable seamless database interaction. Understanding these components is essential for any Java developer working with databases.
DriverManager and Driver:
The DriverManager class manages a list of database drivers and establishes connections with the database.
The Driver interface is implemented by JDBC drivers. It helps in registering the driver with the DriverManager.
Connection:
The Connection interface represents a session with a specific database. It provides methods for creating Statement objects, managing transactions, and closing the connection.
Example methods:
createStatement()
prepareStatement(String sql)
setAutoCommit(boolean autoCommit)
commit() and rollback()
Statement:
The Statement interface allows executing SQL queries. There are three main types:
Statement: Used for executing simple SQL queries without parameters.
PreparedStatement: Used for precompiled SQL queries with parameters. Improves performance and prevents SQL injection attacks.
CallableStatement: Used to execute stored procedures in the database.
ResultSet:
The ResultSet interface represents the result of a SQL query. It allows iterating over the rows of the result and retrieving column data. Key methods include:
next()
getInt(), getString(), getDouble()
updateRow() (for updatable ResultSets)
SQLException:
JDBC uses the SQLException class to handle database errors. It provides information about the error, including SQL state, error code, and message. Exception handling is crucial to ensure reliable database interaction.
Connecting a Java application to a database using JDBC typically involves the following steps:
Load the JDBC Driver:
This step registers the JDBC driver with the DriverManager. For example:
Class.forName("com.mysql.cj.jdbc.Driver");
Establish a Connection:
Use the DriverManager class to open a connection to the database:
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/mydatabase", "username", "password");
Create a Statement:
Statements are used to execute SQL queries:
Statement stmt = con.createStatement();
Execute SQL Queries:
Depending on the type of SQL operation, use:
executeQuery() for SELECT statements.
executeUpdate() for INSERT, UPDATE, DELETE statements.
Process the ResultSet:
Iterate over the results returned by a query:
ResultSet rs = stmt.executeQuery("SELECT * FROM employees");
while(rs.next()) {
System.out.println(rs.getString("name"));
}
Close the Connection:
It is important to close the ResultSet, Statement, and Connection to release database resources:
rs.close();
stmt.close();
con.close();
Database Independence:
JDBC abstracts the database communication, allowing developers to switch databases without changing the core application logic.
Ease of Use:
JDBC provides a standard API, simplifying database operations.
Support for Multiple Databases:
With appropriate drivers, JDBC can connect to MySQL, Oracle, PostgreSQL, SQL Server, and many others.
Integration with Java:
Being part of Java SE, JDBC integrates seamlessly with other Java APIs, making it ideal for enterprise-level applications.
Support for Transactions:
JDBC supports transaction management using commit() and rollback(), ensuring data consistency.
Prepared Statements:
Prepared statements prevent SQL injection attacks and improve query execution performance.
Relational Databases Only:
JDBC is designed for relational databases. It does not support NoSQL databases directly.
Verbose Code:
Writing JDBC code can be repetitive and verbose, requiring boilerplate code for connection handling and result processing.
Manual Resource Management:
Developers must explicitly close connections, statements, and result sets to avoid memory leaks.
Limited Object Mapping:
JDBC works with SQL and relational tables, which can be cumbersome when dealing with complex objects. ORM frameworks like Hibernate are often used on top of JDBC to overcome this limitation.
Use Prepared Statements:
Always use PreparedStatement instead of Statement for parameterized queries to prevent SQL injection and enhance performance.
Close Resources in finally Block or Use Try-With-Resources:
Properly close connections, statements, and result sets to prevent resource leaks.
try (Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement ps = con.prepareStatement(query);
ResultSet rs = ps.executeQuery()) {
// Process results
} catch (SQLException e) {
e.printStackTrace();
}
Use Connection Pooling:
Opening and closing database connections frequently can be expensive. Connection pooling improves performance by reusing connections.
Handle Exceptions Gracefully:
Always catch and log SQLException properly to understand errors and avoid crashing the application.
Answer:
JDBC (Java Database Connectivity) is an API provided by Java that allows Java applications to interact with databases. It enables executing SQL queries, retrieving results, and updating records in relational databases.
Key points:
Part of java.sql package.
Works with any relational database like MySQL, Oracle, SQL Server, DB2, etc.
Provides a standard interface for connecting to databases.
Answer:
JDBC has four main components:
Driver Manager – Loads and manages database drivers.
Connection – Represents a connection to the database.
Statement – Used to execute SQL queries (can be Statement, PreparedStatement, or CallableStatement).
ResultSet – Stores the result returned by a SQL query.
Answer:
Type 1: JDBC-ODBC Bridge Driver
Translates JDBC calls into ODBC calls.
Platform-dependent and slow.
Type 2: Native-API Driver
Uses database-specific native libraries.
Faster than Type 1 but platform-dependent.
Type 3: Network Protocol Driver
Translates JDBC calls into a database-independent network protocol.
Can be used over a network.
Type 4: Thin Driver (Pure Java Driver)
Converts JDBC calls directly to database-specific protocol.
Platform-independent and fastest.
Most modern applications use Type 4 drivers.
Answer:
Steps to establish a JDBC connection:
Load the driver class
Class.forName("com.mysql.cj.jdbc.Driver");
Create a connection
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/dbname", "username", "password");
Use the connection to create statements and execute queries
Close the connection after operations are done.
Answer:
| Type | Description | When to use |
|---|---|---|
| Statement | Used for static SQL queries | Simple queries without parameters |
| PreparedStatement | Precompiled SQL query with parameters | Queries executed multiple times or with dynamic input |
| CallableStatement | Used to call stored procedures in the database | Stored procedures execution |
Answer:
| Feature | Statement | PreparedStatement |
|---|---|---|
| SQL execution | Executes plain SQL | Precompiled SQL |
| Security | Vulnerable to SQL Injection | Prevents SQL Injection |
| Performance | Slower for repeated queries | Faster for repeated execution |
| Parameterization | No parameters | Supports ? as placeholders |
Answer:
Execute SELECT query using executeQuery() – returns a ResultSet.
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM employees");
while(rs.next()) {
System.out.println(rs.getString("name"));
}
Execute INSERT, UPDATE, DELETE using executeUpdate() – returns affected row count.
int rows = stmt.executeUpdate("UPDATE employees SET salary=50000 WHERE id=1");
Answer:
ResultSet stores the data returned by a SELECT query.
Types of ResultSet:
Based on Cursor Movement:
TYPE_FORWARD_ONLY – Can move only forward.
TYPE_SCROLL_INSENSITIVE – Can scroll forward and backward; insensitive to database changes.
TYPE_SCROLL_SENSITIVE – Can scroll and sensitive to database changes.
Based on Concurrency:
CONCUR_READ_ONLY – Cannot update the ResultSet.
CONCUR_UPDATABLE – Allows updating the database directly through ResultSet.
Answer:
By default, JDBC uses auto-commit mode. To manage transactions manually:
con.setAutoCommit(false); // Disable auto-commit
try {
stmt.executeUpdate("INSERT INTO accounts VALUES(1, 'John', 1000)");
stmt.executeUpdate("INSERT INTO accounts VALUES(2, 'Jane', 2000)");
con.commit(); // Commit transaction
} catch(SQLException e) {
con.rollback(); // Rollback on error
}
commit() saves all changes.
rollback() undoes all changes.
Answer:
Always use PreparedStatement with parameterized queries instead of concatenating strings.
PreparedStatement ps = con.prepareStatement("SELECT * FROM users WHERE username=? AND password=?");
ps.setString(1, "admin");
ps.setString(2, "12345");
ResultSet rs = ps.executeQuery();
Avoid dynamic query construction using Statement.
Answer:
Always close ResultSet, Statement, and Connection to prevent memory leaks.
Use try-with-resources in Java 7+:
try (Connection con = DriverManager.getConnection(url, user, pass);
PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
while(rs.next()) {
System.out.println(rs.getString("name"));
}
} catch(SQLException e) {
e.printStackTrace();
}
| Method | Returns | Usage |
|---|---|---|
executeQuery() |
ResultSet | SELECT queries |
executeUpdate() |
int (affected rows) | INSERT, UPDATE, DELETE |
execute() |
boolean | Any SQL statement; returns true if ResultSet is returned |
Answer:
| Feature | DriverManager | DataSource |
|---|---|---|
| Creation | Creates connection directly | Connection pool support |
| Performance | Slower | Faster |
| Advanced features | Limited | Supports distributed transactions, connection pooling |
| Configuration | Hard-coded URL, username, password | Can be configured externally |
DataSource is preferred in enterprise applications.
What is JDBC API?
Explain JDBC architecture.
What are different types of JDBC drivers?
How do you connect to a database using JDBC?
Difference between Statement and PreparedStatement.
Difference between execute(), executeQuery(), and executeUpdate().
How to handle transactions?
How to prevent SQL injection?
How to handle exceptions in JDBC?
What is ResultSet? Explain its types.
Difference between Connection and DataSource.
What is batch processing in JDBC?
How to fetch metadata of database tables?
To execute multiple queries in a single go for performance improvement:
Statement stmt = con.createStatement();
stmt.addBatch("INSERT INTO employees VALUES(1, 'John')");
stmt.addBatch("INSERT INTO employees VALUES(2, 'Jane')");
int[] count = stmt.executeBatch();
Reduces database round trips and improves performance.
DatabaseMetaData – Provides information about the database: tables, drivers, version, etc.
DatabaseMetaData dbMeta = con.getMetaData();
System.out.println(dbMeta.getDatabaseProductName());
ResultSetMetaData – Provides info about columns in ResultSet: column count, names, types, etc.
ResultSetMetaData rsMeta = rs.getMetaData();
System.out.println(rsMeta.getColumnName(1));
Absolutely! Let’s extend your JDBC Q&A set with more detailed and slightly advanced questions that freshers may face. I’ll cover 40+ additional questions with explanations.
commit() and rollback() in JDBC?Answer:
commit() – Saves all changes made in the current transaction to the database.
rollback() – Undoes all changes made in the current transaction since the last commit.
Example:
con.setAutoCommit(false); // Start transaction
stmt.executeUpdate("INSERT INTO accounts VALUES(1,'John',1000)");
con.commit(); // Save changes
Answer:
By default, JDBC enables auto-commit mode, meaning each SQL statement is automatically committed after execution.
You can disable it to manage transactions manually:
con.setAutoCommit(false);
Answer:
SQL Exception – Thrown for any database-related error.
SQLWarning – Non-critical issues; doesn’t stop program execution.
BatchUpdateException – Thrown when an error occurs during batch execution.
executeBatch() in JDBC?Answer:
executeBatch() allows executing multiple SQL statements in a batch to reduce database round-trips and improve performance.
stmt.addBatch("INSERT INTO employees VALUES(1, 'John')");
stmt.addBatch("INSERT INTO employees VALUES(2, 'Jane')");
int[] result = stmt.executeBatch();
Answer:
When inserting rows, some databases generate keys automatically (like auto-increment ID).
Retrieve using:
PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
if(rs.next()) {
int id = rs.getInt(1);
}
getConnection() and DataSource?| Feature | DriverManager.getConnection() | DataSource |
|---|---|---|
| Connection creation | Direct | Supports connection pooling |
| Performance | Low for multiple connections | High |
| Configuration | Hard-coded | External configuration |
| Advanced features | Limited | Transactions, pooling, distributed |
ResultSet cursor?Answer:
The cursor points to the current row in the ResultSet.
By default, cursor is forward-only.
Can be scrollable using TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE.
ResultSet?Answer:
ResultSet can be updatable (CONCUR_UPDATABLE) to modify database rows directly:
ResultSet rs = stmt.executeQuery("SELECT * FROM employees");
rs.next();
rs.updateString("name", "Mike");
rs.updateRow(); // Updates database
CallableStatement?Answer:
Used to call stored procedures in the database.
Supports input and output parameters:
CallableStatement cs = con.prepareCall("{call getEmployeeName(?,?)}");
cs.setInt(1, 101);
cs.registerOutParameter(2, Types.VARCHAR);
cs.execute();
String name = cs.getString(2);
execute() and executeQuery()| Method | Returns | Usage |
|---|---|---|
executeQuery() |
ResultSet |
SELECT queries |
executeUpdate() |
int | INSERT, UPDATE, DELETE |
execute() |
boolean | Any SQL statement; true if ResultSet returned |
Answer:
Thrown when one or more statements in a batch fail.
Use BatchUpdateException.getUpdateCounts() to see which statements succeeded.
Answer:
Use ResultSet streaming (TYPE_FORWARD_ONLY) for memory efficiency.
Use Blob/Clob for handling large binary/text data.
Example:
Blob blob = rs.getBlob("file_data");
InputStream input = blob.getBinaryStream();
Statement and PreparedStatement performance-wise?Answer:
PreparedStatement is precompiled, so repeated execution is faster.
Statement is compiled each time, slower for multiple executions.
Answer:
Use DatabaseMetaData for database-level info:
DatabaseMetaData meta = con.getMetaData();
System.out.println(meta.getDatabaseProductName());
System.out.println(meta.getDriverName());
Use ResultSetMetaData for result set info:
ResultSetMetaData rsMeta = rs.getMetaData();
System.out.println(rsMeta.getColumnCount());
TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE| Type | Description |
|---|---|
TYPE_SCROLL_INSENSITIVE |
Scrollable but not affected by database changes after query execution |
TYPE_SCROLL_SENSITIVE |
Scrollable and reflects changes made to the database after query execution |
Answer:
Platform-independent database access.
Standard API for any relational DB.
Supports dynamic SQL queries.
Supports transactions, batch processing, metadata retrieval.
Easy integration with Java applications.
Answer:
Limited to relational databases.
Requires manual resource management (close() methods).
Error-prone for complex queries.
No ORM features like Hibernate.
Statement, PreparedStatement, and CallableStatement?| Feature | Statement | PreparedStatement | CallableStatement |
|---|---|---|---|
| Use | Static SQL | Dynamic/precompiled SQL | Stored procedures |
| Security | Vulnerable | Safe | Safe |
| Performance | Slower | Faster | Depends on procedure |
Answer:
Use ResultSet.wasNull() to check if the last column read was null:
int age = rs.getInt("age");
if(rs.wasNull()) {
System.out.println("Age is null");
}
ResultSet.TYPE_FORWARD_ONLY and ResultSet.TYPE_SCROLL_INSENSITIVE| Type | Movement | Sensitivity to DB changes |
|---|---|---|
FORWARD_ONLY |
Only forward | N/A |
SCROLL_INSENSITIVE |
Forward/backward | No |
Answer:
JDBC is designed for relational databases.
Some NoSQL databases provide JDBC drivers (like MongoDB, Cassandra), but it’s not native.
Answer:
Use PreparedStatement with parameters instead of concatenating strings to avoid SQL injection:
String sql = "SELECT * FROM users WHERE username=? AND password=?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, "admin");
ps.setString(2, "12345");
Answer:
RowSet is a wrapper over ResultSet with additional features:
Can be disconnected from DB (CachedRowSet)
Supports event listeners
More flexible for GUI apps
Example:
CachedRowSet crs = RowSetProvider.newFactory().createCachedRowSet();
crs.setCommand("SELECT * FROM employees");
crs.execute(con);
executeQuery() and executeUpdate() return values?| Method | Returns |
|---|---|
executeQuery() |
ResultSet |
executeUpdate() |
int (number of rows affected) |
Answer:
Use connection pooling with DataSource (e.g., Apache DBCP, HikariCP).
Avoid opening/closing connections frequently.
Always close resources using try-with-resources.
Answer:
Controls how changes are visible to other transactions:
TRANSACTION_READ_UNCOMMITTED – Dirty reads allowed
TRANSACTION_READ_COMMITTED – Prevents dirty reads
TRANSACTION_REPEATABLE_READ – Prevents non-repeatable reads
TRANSACTION_SERIALIZABLE – Highest isolation; prevents phantom reads
con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
Answer:
JDBC (Java Database Connectivity) is an API that allows Java applications to interact with relational databases using SQL.
Architecture:
JDBC API – Used by Java applications to send SQL commands.
JDBC Driver Manager – Manages a list of database drivers and establishes connections.
JDBC Drivers – Types 1–4 drivers translate JDBC calls to database-specific calls.
Database – Relational database like Oracle, MySQL, SQL Server.
Flow:
Application -> JDBC API -> DriverManager -> Database Driver -> Database
Answer:
Use connection pooling with DataSource instead of DriverManager for performance.
Popular connection pools: HikariCP, Apache DBCP, C3P0.
Example using HikariCP:
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/db");
config.setUsername("root");
config.setPassword("password");
HikariDataSource ds = new HikariDataSource(config);
Connection con = ds.getConnection();
Pooling avoids creating/closing connections repeatedly, improving throughput.
Statement, PreparedStatement, and CallableStatement for performance| Feature | Statement | PreparedStatement | CallableStatement |
|---|---|---|---|
| Compilation | Every execution | Precompiled | Precompiled stored procedure |
| Performance | Low for repeated queries | High for repeated queries | Depends on procedure |
| SQL Injection | Vulnerable | Safe | Safe |
Always prefer
PreparedStatementin production for repeated queries.
Answer:
JDBC supports manual transaction management by disabling auto-commit:
con.setAutoCommit(false);
try {
stmt.executeUpdate("INSERT INTO accounts VALUES(1,'John',1000)");
stmt.executeUpdate("INSERT INTO accounts VALUES(2,'Jane',2000)");
con.commit();
} catch(SQLException e) {
con.rollback();
}
Important for multi-step operations to maintain ACID properties.
Isolation Levels:
READ_UNCOMMITTED – Dirty reads allowed
READ_COMMITTED – Prevents dirty reads
REPEATABLE_READ – Prevents non-repeatable reads
SERIALIZABLE – Prevents phantom reads
Answer:
Batch updates reduce network round-trips for large inserts/updates:
PreparedStatement ps = con.prepareStatement("INSERT INTO employees VALUES(?, ?)");
for(Employee e : employees){
ps.setInt(1, e.getId());
ps.setString(2, e.getName());
ps.addBatch();
}
int[] result = ps.executeBatch();
Use Statement.RETURN_GENERATED_KEYS for auto-increment IDs in batch inserts.
Answer:
Use PreparedStatement or CallableStatement with parameters.
Never concatenate user input into SQL queries.
Validate inputs on both server and client-side.
For dynamic queries, consider ORM frameworks like Hibernate.
Answer:
RowSets are wrapper over ResultSet with extra features: disconnected operation, scrollability, and event listeners.
Types:
Connected RowSets: JdbcRowSet
Disconnected RowSets: CachedRowSet, WebRowSet, FilteredRowSet
Example:
CachedRowSet crs = RowSetProvider.newFactory().createCachedRowSet();
crs.setCommand("SELECT * FROM employees");
crs.execute(con);
Answer:
DatabaseMetaData – Info about database:
DatabaseMetaData dbMeta = con.getMetaData();
System.out.println(dbMeta.getDatabaseProductName());
System.out.println(dbMeta.getMaxConnections());
ResultSetMetaData – Info about columns in ResultSet:
ResultSetMetaData rsMeta = rs.getMetaData();
System.out.println(rsMeta.getColumnCount());
System.out.println(rsMeta.getColumnName(1));
Answer:
BLOB – Binary Large Objects (images, PDFs)
CLOB – Character Large Objects (text files)
Example:
Blob blob = rs.getBlob("file_data");
InputStream is = blob.getBinaryStream();
Use streaming to avoid memory issues.
execute(), executeQuery(), and executeUpdate()| Method | Returns | Use Case |
|---|---|---|
executeQuery() |
ResultSet |
SELECT queries |
executeUpdate() |
int | INSERT, UPDATE, DELETE |
execute() |
boolean | Any SQL; true if returns ResultSet |
Answer:
int age = rs.getInt("age");
if(rs.wasNull()) {
System.out.println("Age is null");
}
Always check wasNull() after reading primitive columns.
CallableStatement cs = con.prepareCall("{call getEmployeeName(?,?)}");
cs.setInt(1, 101); // IN parameter
cs.registerOutParameter(2, Types.VARCHAR); // OUT parameter
cs.execute();
String name = cs.getString(2);
Answer:
Use PreparedStatement for repeated queries.
Use batch processing for inserts/updates.
Use connection pooling.
Avoid fetching unnecessary columns (SELECT *).
Use proper indexes in the database.
Close ResultSet, Statement, and Connection promptly.
For huge data, use streaming LOBs instead of loading entire objects in memory.
DriverManager and DataSource| Feature | DriverManager | DataSource |
|---|---|---|
| Connection creation | Direct | Connection pool support |
| Performance | Low for repeated connections | High |
| Transactions | Limited | Supports distributed transactions |
| Configuration | Hard-coded | Externalized |
Answer:
JDBC connections are not thread-safe, do not share Connection/Statement objects between threads.
Each thread should have its own connection (ideally from a connection pool).
Answer:
Always use try-with-resources to auto-close resources:
try (Connection con = ds.getConnection();
PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
while(rs.next()) {
System.out.println(rs.getString("name"));
}
} catch(SQLException e) {
e.printStackTrace();
}
Handle SQLState and vendor-specific error codes for better error reporting.
READ_UNCOMMITTED: Allows dirty reads
READ_COMMITTED: Prevents dirty reads
REPEATABLE_READ: Prevents non-repeatable reads
SERIALIZABLE: Full isolation; prevents phantom reads
Example:
con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
| Feature | Connected (ResultSet) | Disconnected (RowSet) |
|---|---|---|
| Database connection | Always open | Can close DB after fetching data |
| Memory | Uses DB memory | Uses local memory |
| Use case | Real-time updates | GUI applications, offline data |
Use Statement.execute() which returns boolean:
Statement stmt = con.createStatement();
boolean hasResultSet = stmt.execute("SELECT * FROM emp; UPDATE emp SET salary=5000 WHERE id=1");
if(hasResultSet) {
ResultSet rs = stmt.getResultSet();
}
Use stmt.getUpdateCount() to handle update counts between queries.
Use connection pools with retry configuration (HikariCP, DBCP).
Implement try-catch with retry for transient failures:
int attempts = 0;
while(attempts < 3) {
try(Connection con = ds.getConnection()) {
// Execute queries
break;
} catch(SQLException e) {
attempts++;
}
}
PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
for(Employee e : employees){
ps.setString(1, e.getName());
ps.addBatch();
}
ps.executeBatch();
ResultSet keys = ps.getGeneratedKeys();
while(keys.next()) {
System.out.println("Generated ID: " + keys.getInt(1));
}
Use standard SQL where possible.
Avoid vendor-specific features.
Use DataSource abstraction.
Consider ORM frameworks (Hibernate/JPA) for cross-database support.
Enable profiling/logging for queries.
Monitor connection pool statistics.
Minimize fetch size for large queries:
stmt.setFetchSize(100);
Index frequently queried columns.
Answer:
Use pagination to fetch a limited number of rows at a time instead of SELECT *.
Set fetch size to control the number of rows fetched per round-trip:
Statement stmt = con.createStatement();
stmt.setFetchSize(1000);
ResultSet rs = stmt.executeQuery("SELECT * FROM employees");
Use streaming for large LOBs (Blob/Clob).
Avoid unnecessary joins or SELECT * queries.
Consider batch processing for inserts/updates.
Offload heavy operations to database stored procedures if possible.
Answer:
Deadlocks occur when two or more transactions block each other.
Handling strategies:
Always acquire locks in the same order.
Use short transactions to minimize lock duration.
Set transaction isolation levels carefully (READ_COMMITTED is safer than SERIALIZABLE).
Implement retry logic if deadlock occurs:
boolean success = false;
int attempts = 0;
while(!success && attempts < 3) {
try {
con.setAutoCommit(false);
// SQL operations
con.commit();
success = true;
} catch(SQLException e) {
if(e.getSQLState().equals("40001")) { // Deadlock
con.rollback();
attempts++;
} else throw e;
}
}
Answer:
Optimistic Locking:
No locks are held on the database rows.
Use a version number or timestamp column.
Example:
UPDATE employees SET salary=5000, version=version+1 WHERE id=101 AND version=1;
If the version doesn’t match, update fails, preventing lost updates.
Pessimistic Locking:
Lock rows explicitly during the transaction.
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM employees WHERE id=101 FOR UPDATE");
Other transactions must wait until lock is released.
Answer:
Use manual transaction management with addBatch() and executeBatch().
Rollback entire batch if any statement fails:
con.setAutoCommit(false);
try {
PreparedStatement ps = con.prepareStatement("INSERT INTO employees VALUES(?, ?)");
for(Employee e : list) {
ps.setInt(1, e.getId());
ps.setString(2, e.getName());
ps.addBatch();
}
ps.executeBatch();
con.commit();
} catch(SQLException e) {
con.rollback(); // Rollback entire batch
}
Answer:
Presentation Layer (Web/App Layer): JSP/Servlets or Spring MVC interact with business logic.
Business Layer (Service/Logic Layer): Handles transactions, validation, and business rules; uses JDBC/DAO layer to interact with DB.
Data Access Layer (DAO Layer): Contains JDBC code to connect and fetch/update database records.
Flow:
Client (UI) -> Service Layer -> DAO Layer -> JDBC -> Database
Ensures separation of concerns and easier maintenance.
JDBC connections should be pooled and managed at DAO layer.
Answer:
Always close ResultSet, Statement, and Connection.
Use try-with-resources in Java 7+:
try(Connection con = ds.getConnection();
PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
// Process result
}
Avoid holding connections for long periods.
Avoid creating too many unnecessary ResultSets or Statements in loops.
Use connection pool to manage connections efficiently.
Answer:
Use PreparedStatement and conditionally append query parts:
StringBuilder sql = new StringBuilder("SELECT * FROM employees WHERE 1=1");
if(name != null) sql.append(" AND name=?");
if(department != null) sql.append(" AND department=?");
PreparedStatement ps = con.prepareStatement(sql.toString());
int index = 1;
if(name != null) ps.setString(index++, name);
if(department != null) ps.setString(index++, department);
ResultSet rs = ps.executeQuery();
Benefits:
Prevents SQL injection.
Optimizes query execution.
Avoids unnecessary WHERE clauses.
Answer:
Identify Entity Classes for each table.
Replace DAO layer SQL statements with HQL or Criteria API.
Example JDBC to Hibernate:
// JDBC
PreparedStatement ps = con.prepareStatement("SELECT * FROM employees WHERE id=?");
ps.setInt(1, 101);
ResultSet rs = ps.executeQuery();
// Hibernate
Employee emp = session.get(Employee.class, 101);
Benefits:
Automatic transaction management
Less boilerplate code
Cross-database portability
Answer:
Use Statement.execute() which returns a boolean.
Example:
Statement stmt = con.createStatement();
boolean hasResultSet = stmt.execute("SELECT * FROM emp; UPDATE emp SET salary=5000 WHERE id=1;");
if(hasResultSet) {
ResultSet rs = stmt.getResultSet();
} else {
int rowsUpdated = stmt.getUpdateCount();
}
Answer:
Enable query logging or use profiling tools.
Minimize SELECT *, fetch only required columns.
Use fetch size for large result sets:
stmt.setFetchSize(500);
Use batch processing for multiple inserts/updates.
Use connection pool metrics (active connections, idle connections).
Index frequently used columns and optimize queries in the database.
Answer:
Use connection pool with retry or failover configuration (HikariCP, DBCP).
Implement retry logic for transient errors:
int attempts = 0;
while(attempts < 3) {
try(Connection con = ds.getConnection()) {
// execute query
break;
} catch(SQLException e) {
attempts++;
Thread.sleep(1000); // wait before retry
}
}
Answer:
Use JTA (Java Transaction API) or XA DataSource for distributed transactions.
Each resource participates in a two-phase commit.
JDBC alone does not handle multiple database transaction coordination; requires transaction manager in application server (Spring, JBoss).
Answer:
Use master-slave DB architecture.
Writes go to master, reads go to slave.
Implement in DAO layer or use frameworks like Spring RoutingDataSource.
Example:
if(queryType.equals("READ")) con = slaveDS.getConnection();
else con = masterDS.getConnection();
Answer:
Use connection pool with leak detection (HikariCP has leakDetectionThreshold).
Close connections in finally block or use try-with-resources.
Avoid passing connections across threads.
Answer:
Use SQL LIMIT/OFFSET or database-specific pagination.
PreparedStatement ps = con.prepareStatement("SELECT * FROM employees LIMIT ? OFFSET ?");
ps.setInt(1, pageSize);
ps.setInt(2, (page-1) * pageSize);
For large datasets, fetch in chunks instead of all rows.
Answer:
Implement second-level caching at application layer (EhCache, Caffeine).
Cache frequently read data to reduce DB load.
Use ResultSet caching only for read-only operations.
Answer:
Capture SQL queries and parameters for debugging using logging frameworks.
Use triggers in DB or DAO layer to log insert/update/delete operations.
Example:
logger.info("Executing SQL: " + ps.toString());
