Java Notes

JDBC Interview Questions

30+ JDBC interview questions with detailed answers covering connections, statements, transactions, connection pooling, and best practices.

1. What is JDBC?

JDBC (Java Database Connectivity) is a Java API for connecting to and interacting with relational databases. It provides a standard interface regardless of the underlying database.

java example

// Basic JDBC flow
Connection conn = DriverManager.getConnection(url, user, pass);
PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE id = ?");
ps.setInt(1, userId);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
    String name = rs.getString("name");
}
rs.close(); ps.close(); conn.close();

3. What are JDBC driver types?

Type	Name	Description
Type 1	JDBC-ODBC Bridge	Uses ODBC driver (deprecated)
Type 2	Native API	Uses native DB client library
Type 3	Network Protocol	Middleware translates to DB protocol
Type 4	Thin Driver	Pure Java, directly to DB (preferred)

4. Statement vs PreparedStatement vs CallableStatement

Feature	Statement	PreparedStatement	CallableStatement
SQL type	Static SQL	Parameterized SQL	Stored procedures
Performance	Slow (parsed each time)	Fast (pre-compiled)	Fast
SQL Injection	Vulnerable	Safe (parameterized)	Safe
Use for	DDL, simple queries	CRUD operations	Stored procedures

java example

// ❌ Statement — SQL Injection vulnerable!
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM users WHERE name = '" + userInput + "'");

// ✅ PreparedStatement — Safe
PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE name = ?");
ps.setString(1, userInput);
ResultSet rs = ps.executeQuery();

5. What is SQL Injection? How does PreparedStatement prevent it?

SQL Injection: Malicious SQL input that alters query logic.

Example

Input: admin' OR '1'='1 Query becomes: SELECT * FROM users WHERE name = 'admin' OR '1'='1' -- returns ALL users!

PreparedStatement sends SQL and parameters separately to the database. Parameters are treated as literal values, never as SQL code.

6. What is a Transaction?

A transaction is a group of operations that must ALL succeed or ALL fail (ACID):

java example

Connection conn = dataSource.getConnection();
try {
    conn.setAutoCommit(false);  // Start transaction

    // Transfer money: debit from A, credit to B
    PreparedStatement debit = conn.prepareStatement(
        "UPDATE accounts SET balance = balance - ? WHERE id = ?");
    debit.setDouble(1, 1000);
    debit.setInt(2, accountA);
    debit.executeUpdate();

    PreparedStatement credit = conn.prepareStatement(
        "UPDATE accounts SET balance = balance + ? WHERE id = ?");
    credit.setDouble(1, 1000);
    credit.setInt(2, accountB);
    credit.executeUpdate();

    conn.commit();  // All succeed
} catch (SQLException e) {
    conn.rollback();  // All fail — no partial update
} finally {
    conn.setAutoCommit(true);
    conn.close();
}

7. ACID Properties

Property	Meaning
Atomicity	All operations succeed or all fail
Consistency	Database moves from one valid state to another
Isolation	Concurrent transactions don't interfere
Durability	Committed data persists even after crash

8. Transaction Isolation Levels

Level	Dirty Read	Non-Repeatable Read	Phantom Read
READ_UNCOMMITTED	✅	✅	✅
READ_COMMITTED	❌	✅	✅
REPEATABLE_READ	❌	❌	✅
SERIALIZABLE	❌	❌	❌

java example

conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

9. What is Connection Pooling?

Creating database connections is expensive (~200-500ms). Connection pooling maintains a pool of reusable connections:

java example

// Using HikariCP (fastest Java connection pool)
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
config.setUsername("root");
config.setPassword("password");
config.setMaximumPoolSize(10);
config.setMinimumIdle(5);

HikariDataSource dataSource = new HikariDataSource(config);

// Get connection from pool (fast!)
Connection conn = dataSource.getConnection();
// Use connection...
conn.close();  // Returns to pool (doesn't actually close)

10. ResultSet types and concurrency

Type	Scrolling	Updates
TYPE_FORWARD_ONLY	Forward only	Read only
TYPE_SCROLL_INSENSITIVE	Any direction	Changes not reflected
TYPE_SCROLL_SENSITIVE	Any direction	Changes reflected

Concurrency	Description
CONCUR_READ_ONLY	Cannot update through ResultSet
CONCUR_UPDATABLE	Can update rows through ResultSet

11-30. Additional Questions

11. What is batch processing?

java example

PreparedStatement ps = conn.prepareStatement("INSERT INTO users VALUES (?, ?)");
for (User user : users) {
    ps.setString(1, user.getName());
    ps.setString(2, user.getEmail());
    ps.addBatch();
}
int[] results = ps.executeBatch();

12. What is RowSet? A wrapper around ResultSet that is disconnected, serializable, and scrollable.

13. How to call stored procedures?

java example

CallableStatement cs = conn.prepareCall("{call getUser(?, ?)}");
cs.setInt(1, userId);
cs.registerOutParameter(2, Types.VARCHAR);
cs.execute();
String name = cs.getString(2);

14. What is DatabaseMetaData? Provides info about the database (tables, columns, capabilities).

15. What is DataSource vs DriverManager?

DriverManager: Simple, creates new connection each time
DataSource: Supports pooling, distributed transactions, preferred in production

16. How to handle CLOB and BLOB?

java example

ps.setClob(1, new StringReader(largeText));
ps.setBlob(2, new FileInputStream(file));

17. What is the N+1 problem? Loading N entities then executing 1 query per entity for relationships. Solution: JOIN queries or batch fetching.

18. What is savepoint? Named point within transaction you can rollback to:

java example

Savepoint sp = conn.setSavepoint("afterFirstInsert");
// ... if error ...
conn.rollback(sp);  // Rollback to savepoint, not entire transaction

19. close() vs close in try-with-resources? Always use try-with-resources for automatic cleanup:

java example

try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement(sql);
     ResultSet rs = ps.executeQuery()) {
    // Auto-closed in reverse order
}

20. What is JDBC template in Spring? Spring's abstraction over JDBC that handles connection management, exception translation, and resource cleanup.

21-30: DriverManager.getConnection() steps, ResultSet navigation methods (next, previous, first, last), getGeneratedKeys() for auto-increment IDs, executeUpdate vs executeQuery vs execute, setting fetch size for performance, handling NULL values with wasNull(), database connection URL formats for different databases, JDBC vs Hibernate vs JPA differences, connection leak detection, and prepared statement caching.

11. What is Connection Pooling?

Answer: A pool of pre-created database connections is maintained. Instead of creating a new connection for each request, connections are reused from the pool. Benefits: performance improvement, resource management, scalability. Popular: HikariCP, Apache DBCP, C3P0.

12. What are the ResultSet types?

Answer: TYPE_FORWARD_ONLY (forward only, default), TYPE_SCROLL_INSENSITIVE (bidirectional, not sensitive to DB changes), TYPE_SCROLL_SENSITIVE (bidirectional, sensitive to changes). Concurrency: CONCUR_READ_ONLY, CONCUR_UPDATABLE.

13. How does Batch Processing work?

Answer: addBatch() queues SQL statements, executeBatch() executes them all at once. Network round-trips are reduced. Essential for large datasets.

Example

PreparedStatement ps = conn.prepareStatement("INSERT INTO users VALUES(?,?)"); for (int i = 1; i <= 1000; i++) { ps.setInt(1, i); ps.setString(2, "User" + i); ps.addBatch(); if (i % 100 == 0) ps.executeBatch(); } ps.executeBatch();

14. What is DatabaseMetaData?

Answer: Provides database information - name, version, supported features, tables, columns. Access it using conn.getMetaData().

15. What is ResultSetMetaData?

Answer: Provides information about ResultSet columns - count, names, types, sizes. Access it using rs.getMetaData(). Useful for processing dynamic query results.

16. How to retrieve auto-generated keys?

Answer: Use the Statement.RETURN_GENERATED_KEYS flag, then getGeneratedKeys() returns a ResultSet containing the generated IDs.

17. What is a Savepoint?

Answer: A checkpoint within a transaction. You can do a partial rollback to the savepoint without a full rollback. Create with conn.setSavepoint("name"), partial rollback with conn.rollback(savepoint).

18. RowSet vs ResultSet?

Answer: RowSet follows the JavaBeans model and supports disconnected mode. Types: JdbcRowSet (connected), CachedRowSet (disconnected), WebRowSet (XML). Disconnected RowSet holds data even after the connection is closed.

19. What are CLOB and BLOB?

Answer: CLOB: Character Large Object (text data). BLOB: Binary Large Object (images, files). Handle them using setClob()/setBlob() or setCharacterStream()/setBinaryStream().

20. What is a Connection leak? How to prevent it?

Answer: A leak occurs when a connection is not closed after use. Prevention: try-with-resources, finally block close, pool monitoring. Leaked connections exhaust the pool.

21. Why is the Type 4 driver preferred?

Answer: Pure Java, no native libraries, platform independent, best performance, direct DB communication. Examples: MySQL Connector/J, PostgreSQL JDBC.

22. SQLException handling best practices?

Answer: Log error code, SQL state, and message. Handle chained exceptions. Proper cleanup in finally. Transaction rollback on error.

23. Explain Transaction Isolation Levels.

Answer: READ_UNCOMMITTED (dirty reads allowed), READ_COMMITTED (no dirty reads), REPEATABLE_READ (no non-repeatable reads), SERIALIZABLE (full isolation, slowest). Higher isolation = more consistency but less concurrency.

24. DataSource vs DriverManager?

Answer: DriverManager: basic, no pooling. DataSource: enterprise-level, connection pooling, distributed transactions, JNDI support. Always use DataSource in production.

25. Stored Procedure OUT parameter handling?

Answer: Call registerOutParameter(index, sqlType) before execute. After execute, retrieve the value using getXxx(index).

26. JDBC Performance Tips?

Answer: PreparedStatement (cached), batch processing, connection pooling, proper fetch size, select needed columns only, indexes, close resources properly.

27. Pagination in JDBC?

Answer: Use LIMIT OFFSET (MySQL), ROWNUM (Oracle), or ResultSet scrolling. Server-side pagination is preferred for large datasets.

28. JDBC vs JPA/Hibernate?

Answer: JDBC: low-level SQL, full control, verbose. JPA/Hibernate: high-level ORM, auto SQL, caching, relationships via annotations. JDBC for complex queries, JPA for standard CRUD.

29. What is Connection.isValid()?

Answer: Used to check if a connection is alive. Parameter: timeout in seconds. Connection pools use it internally to detect stale connections.

30. What is the role of setFetchSize()?

Answer: Sets how many rows to fetch from the database at a time. Important for memory optimization with large ResultSets. Default is driver-specific.

Summary

In this chapter, we learned about JDBC Interview Questions in detail. Here are the key points:

Basic introduction to the concept and why it is needed
Syntax and structure with complete examples
Internal working and best practices
Real-world applications and use cases
Common mistakes and how to avoid them
How this topic is asked in interviews

To practice these concepts, write and run the code in your IDE and verify the output. Modify each example and experiment so that you deeply understand the concept.

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for JDBC Interview Questions.

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this Java Master Course topic.

Search Terms

java-master-course, java master course, java, master, course, interview, questions, jdbc

Continue learning this concept

Interview QuestionsCoding Interview Questions30+ Java coding interview questions with solutions covering strings, arrays, patterns, recursion, and data structure problems commonly asked in placements.Interview QuestionsCollections Interview Questions35+ Java Collections Framework interview questions covering List, Set, Map, Queue, sorting, concurrent collections, and internal implementations.Interview QuestionsCore Java Interview Questions40+ Core Java interview questions with detailed answers covering JVM, data types, strings, wrapper classes, memory management, and Java fundamentals.Interview QuestionsMultithreading Interview Questions35+ Java Multithreading interview questions covering threads, synchronization, locks, executor framework, concurrent collections, and thread communication.Interview QuestionsOOPs Interview Questions35+ Object-Oriented Programming interview questions with detailed answers covering inheritance, polymorphism, abstraction, encapsulation, and design patterns.Interview QuestionsSpring Framework Interview Questions35+ Spring Framework and Spring Boot interview questions covering IoC, DI, annotations, REST, JPA, Security, and microservices concepts.

PreviousCore Java Interview Questions NextMultithreading Interview Questions

Java Notes

JDBC Interview Questions

30+ JDBC interview questions with detailed answers covering connections, statements, transactions, connection pooling, and best practices.

1. What is JDBC?

JDBC (Java Database Connectivity) is a Java API for connecting to and interacting with relational databases. It provides a standard interface regardless of the underlying database.

java example

// Basic JDBC flow
Connection conn = DriverManager.getConnection(url, user, pass);
PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE id = ?");
ps.setInt(1, userId);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
    String name = rs.getString("name");
}
rs.close(); ps.close(); conn.close();

3. What are JDBC driver types?

Type	Name	Description
Type 1	JDBC-ODBC Bridge	Uses ODBC driver (deprecated)
Type 2	Native API	Uses native DB client library
Type 3	Network Protocol	Middleware translates to DB protocol
Type 4	Thin Driver	Pure Java, directly to DB (preferred)

4. Statement vs PreparedStatement vs CallableStatement

Feature	Statement	PreparedStatement	CallableStatement
SQL type	Static SQL	Parameterized SQL	Stored procedures
Performance	Slow (parsed each time)	Fast (pre-compiled)	Fast
SQL Injection	Vulnerable	Safe (parameterized)	Safe
Use for	DDL, simple queries	CRUD operations	Stored procedures

java example

// ❌ Statement — SQL Injection vulnerable!
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM users WHERE name = '" + userInput + "'");

// ✅ PreparedStatement — Safe
PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE name = ?");
ps.setString(1, userInput);
ResultSet rs = ps.executeQuery();

5. What is SQL Injection? How does PreparedStatement prevent it?

SQL Injection: Malicious SQL input that alters query logic.

Example

Input: admin' OR '1'='1 Query becomes: SELECT * FROM users WHERE name = 'admin' OR '1'='1' -- returns ALL users!

PreparedStatement sends SQL and parameters separately to the database. Parameters are treated as literal values, never as SQL code.

6. What is a Transaction?

A transaction is a group of operations that must ALL succeed or ALL fail (ACID):

java example

Connection conn = dataSource.getConnection();
try {
    conn.setAutoCommit(false);  // Start transaction

    // Transfer money: debit from A, credit to B
    PreparedStatement debit = conn.prepareStatement(
        "UPDATE accounts SET balance = balance - ? WHERE id = ?");
    debit.setDouble(1, 1000);
    debit.setInt(2, accountA);
    debit.executeUpdate();

    PreparedStatement credit = conn.prepareStatement(
        "UPDATE accounts SET balance = balance + ? WHERE id = ?");
    credit.setDouble(1, 1000);
    credit.setInt(2, accountB);
    credit.executeUpdate();

    conn.commit();  // All succeed
} catch (SQLException e) {
    conn.rollback();  // All fail — no partial update
} finally {
    conn.setAutoCommit(true);
    conn.close();
}

7. ACID Properties

Property	Meaning
Atomicity	All operations succeed or all fail
Consistency	Database moves from one valid state to another
Isolation	Concurrent transactions don't interfere
Durability	Committed data persists even after crash

8. Transaction Isolation Levels

Level	Dirty Read	Non-Repeatable Read	Phantom Read
READ_UNCOMMITTED	✅	✅	✅
READ_COMMITTED	❌	✅	✅
REPEATABLE_READ	❌	❌	✅
SERIALIZABLE	❌	❌	❌

java example

conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

9. What is Connection Pooling?

Creating database connections is expensive (~200-500ms). Connection pooling maintains a pool of reusable connections:

java example

// Using HikariCP (fastest Java connection pool)
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
config.setUsername("root");
config.setPassword("password");
config.setMaximumPoolSize(10);
config.setMinimumIdle(5);

HikariDataSource dataSource = new HikariDataSource(config);

// Get connection from pool (fast!)
Connection conn = dataSource.getConnection();
// Use connection...
conn.close();  // Returns to pool (doesn't actually close)

10. ResultSet types and concurrency

Type	Scrolling	Updates
TYPE_FORWARD_ONLY	Forward only	Read only
TYPE_SCROLL_INSENSITIVE	Any direction	Changes not reflected
TYPE_SCROLL_SENSITIVE	Any direction	Changes reflected

Concurrency	Description
CONCUR_READ_ONLY	Cannot update through ResultSet
CONCUR_UPDATABLE	Can update rows through ResultSet

11-30. Additional Questions

11. What is batch processing?

java example

PreparedStatement ps = conn.prepareStatement("INSERT INTO users VALUES (?, ?)");
for (User user : users) {
    ps.setString(1, user.getName());
    ps.setString(2, user.getEmail());
    ps.addBatch();
}
int[] results = ps.executeBatch();

12. What is RowSet? A wrapper around ResultSet that is disconnected, serializable, and scrollable.

13. How to call stored procedures?

java example

CallableStatement cs = conn.prepareCall("{call getUser(?, ?)}");
cs.setInt(1, userId);
cs.registerOutParameter(2, Types.VARCHAR);
cs.execute();
String name = cs.getString(2);

14. What is DatabaseMetaData? Provides info about the database (tables, columns, capabilities).

15. What is DataSource vs DriverManager?

DriverManager: Simple, creates new connection each time
DataSource: Supports pooling, distributed transactions, preferred in production

16. How to handle CLOB and BLOB?

java example

ps.setClob(1, new StringReader(largeText));
ps.setBlob(2, new FileInputStream(file));

17. What is the N+1 problem? Loading N entities then executing 1 query per entity for relationships. Solution: JOIN queries or batch fetching.

18. What is savepoint? Named point within transaction you can rollback to:

java example

Savepoint sp = conn.setSavepoint("afterFirstInsert");
// ... if error ...
conn.rollback(sp);  // Rollback to savepoint, not entire transaction

19. close() vs close in try-with-resources? Always use try-with-resources for automatic cleanup:

java example

try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement(sql);
     ResultSet rs = ps.executeQuery()) {
    // Auto-closed in reverse order
}

20. What is JDBC template in Spring? Spring's abstraction over JDBC that handles connection management, exception translation, and resource cleanup.

11. What is Connection Pooling?

12. What are the ResultSet types?

13. How does Batch Processing work?

Answer: addBatch() queues SQL statements, executeBatch() executes them all at once. Network round-trips are reduced. Essential for large datasets.

Example

14. What is DatabaseMetaData?

Answer: Provides database information - name, version, supported features, tables, columns. Access it using conn.getMetaData().

15. What is ResultSetMetaData?

Answer: Provides information about ResultSet columns - count, names, types, sizes. Access it using rs.getMetaData(). Useful for processing dynamic query results.

16. How to retrieve auto-generated keys?

Answer: Use the Statement.RETURN_GENERATED_KEYS flag, then getGeneratedKeys() returns a ResultSet containing the generated IDs.

17. What is a Savepoint?

18. RowSet vs ResultSet?

19. What are CLOB and BLOB?

Answer: CLOB: Character Large Object (text data). BLOB: Binary Large Object (images, files). Handle them using setClob()/setBlob() or setCharacterStream()/setBinaryStream().

20. What is a Connection leak? How to prevent it?

Answer: A leak occurs when a connection is not closed after use. Prevention: try-with-resources, finally block close, pool monitoring. Leaked connections exhaust the pool.

21. Why is the Type 4 driver preferred?

Answer: Pure Java, no native libraries, platform independent, best performance, direct DB communication. Examples: MySQL Connector/J, PostgreSQL JDBC.

22. SQLException handling best practices?

Answer: Log error code, SQL state, and message. Handle chained exceptions. Proper cleanup in finally. Transaction rollback on error.

23. Explain Transaction Isolation Levels.

24. DataSource vs DriverManager?

Answer: DriverManager: basic, no pooling. DataSource: enterprise-level, connection pooling, distributed transactions, JNDI support. Always use DataSource in production.

25. Stored Procedure OUT parameter handling?

Answer: Call registerOutParameter(index, sqlType) before execute. After execute, retrieve the value using getXxx(index).

26. JDBC Performance Tips?

Answer: PreparedStatement (cached), batch processing, connection pooling, proper fetch size, select needed columns only, indexes, close resources properly.

27. Pagination in JDBC?

Answer: Use LIMIT OFFSET (MySQL), ROWNUM (Oracle), or ResultSet scrolling. Server-side pagination is preferred for large datasets.

28. JDBC vs JPA/Hibernate?

Answer: JDBC: low-level SQL, full control, verbose. JPA/Hibernate: high-level ORM, auto SQL, caching, relationships via annotations. JDBC for complex queries, JPA for standard CRUD.

29. What is Connection.isValid()?

Answer: Used to check if a connection is alive. Parameter: timeout in seconds. Connection pools use it internally to detect stale connections.

30. What is the role of setFetchSize()?

Answer: Sets how many rows to fetch from the database at a time. Important for memory optimization with large ResultSets. Default is driver-specific.

Summary

In this chapter, we learned about JDBC Interview Questions in detail. Here are the key points:

Basic introduction to the concept and why it is needed
Syntax and structure with complete examples
Internal working and best practices
Real-world applications and use cases
Common mistakes and how to avoid them
How this topic is asked in interviews

To practice these concepts, write and run the code in your IDE and verify the output. Modify each example and experiment so that you deeply understand the concept.

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for JDBC Interview Questions.

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this Java Master Course topic.

Search Terms

java-master-course, java master course, java, master, course, interview, questions, jdbc

Continue learning this concept

PreviousCore Java Interview Questions NextMultithreading Interview Questions