JDBC Interview Questions: Ace Your Technical Screening

Prepare for common JDBC questions asked in Java developer interviews.

Basic JDBC Questions

1. What is JDBC?

Answer:
JDBC (Java Database Connectivity) is a Java API that enables Java applications to interact with relational databases. It provides methods to connect, execute SQL queries, and process results.

2. Explain the JDBC Workflow.

Answer:

1. Load Driver: Register the database driver (e.g., com.mysql.cj.jdbc.Driver).
2. Establish Connection: Use DriverManager.getConnection(url, user, password).
3. Create Statement: Use Statement, PreparedStatement, or CallableStatement.
4. Execute Query: Run SQL via executeQuery() (SELECT) or executeUpdate() (INSERT/UPDATE/DELETE).
5. Process ResultSet: Extract data from the query result.
6. Close Resources: Close Connection, Statement, and ResultSet in finally or try-with-resources.

3. What’s the Difference Between Statement and PreparedStatement?

Answer:

4. Name the Types of JDBC Drivers.

Answer:

1. Type 1 (JDBC-ODBC Bridge): Deprecated.
2. Type 2 (Native API): Uses database-specific native libraries.
3. Type 3 (Network Protocol): Middleware-based (e.g., for distributed systems).
4. Type 4 (Thin Driver): Pure Java (most common, e.g., MySQL Connector/J).

Intermediate JDBC Questions

5. How Do Transactions Work in JDBC?

Answer:

• Disable auto-commit: conn.setAutoCommit(false).
• Group operations: Execute queries as a single unit.
• Commit: conn.commit() on success.
• Rollback: conn.rollback() on failure.

6. What is Connection Pooling? Why Use It?

Answer:
Connection pooling reuses existing database connections instead of creating new ones for each request. Benefits:

• Reduces overhead.
• Improves performance for high-traffic apps.
• Libraries like HikariCP and Apache DBCP are popular implementations.

7. How Do You Handle BLOB/CLOB Data in JDBC?

Answer:

• BLOB (Binary Data):

PreparedStatement pstmt = conn.prepareStatement("INSERT INTO files (data) VALUES (?)");  
pstmt.setBinaryStream(1, new FileInputStream("image.jpg"));  

• CLOB (Text Data):

Clob clob = conn.createClob();  
clob.setString(1, largeText);  
pstmt.setClob(1, clob);  

Advanced JDBC Questions

8. What’s the Difference Between execute(), executeQuery(), and executeUpdate()?

Answer:

• executeQuery(): Returns ResultSet (used for SELECT).
• executeUpdate(): Returns rows affected (used for INSERT/UPDATE/DELETE).
• execute(): Returns boolean (used for DDL or multiple results).

9. How Does ResultSet Handle Scrollable and Updatable Records?

Answer:

• Scrollable:

Statement stmt = conn.createStatement(  
    ResultSet.TYPE_SCROLL_INSENSITIVE,  
    ResultSet.CONCUR_READ_ONLY  
);  

• Updatable:

ResultSet rs = stmt.executeQuery("SELECT * FROM employees");  
rs.updateString("name", "Alice");  
rs.updateRow();  

10. Explain the DAO Pattern in JDBC.

Answer:
The Data Access Object (DAO) pattern separates database logic from business logic. Example:

public class UserDao {  
    public User getUserById(int id) {  
        // JDBC code to fetch user  
    }  
}  

Scenario-Based Questions

11. How Would You Troubleshoot a “Connection Refused” Error?

Answer:

• Verify the database server is running.
• Check firewall settings for blocked ports (e.g., MySQL’s port 3306).
• Test connectivity using telnet localhost 3306.

12. How Do You Prevent SQL Injection in JDBC?

Answer:
Always use PreparedStatement for user inputs:

String sql = "SELECT * FROM users WHERE name = ?";  
PreparedStatement pstmt = conn.prepareStatement(sql);  
pstmt.setString(1, userInput);  

Coding Examples

13. Write Code to Insert a Record Using PreparedStatement.

String sql = "INSERT INTO employees (name, age) VALUES (?, ?)";  
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {  
    pstmt.setString(1, "Alice");  
    pstmt.setInt(2, 30);  
    pstmt.executeUpdate();  
}  

14. How to Perform Batch Updates?

try (PreparedStatement pstmt = conn.prepareStatement("INSERT INTO logs (message) VALUES (?)")) {  
    for (String msg : messages) {  
        pstmt.setString(1, msg);  
        pstmt.addBatch();  
    }  
    pstmt.executeBatch();  
}  

Summary

• Basics: Understand drivers, connections, and PreparedStatement.
• Intermediate: Transactions, connection pooling, BLOB/CLOB.
• Advanced: Scrollable ResultSet, DAO pattern, batch processing.

Final Tip: Practice coding examples and emphasize best practices (e.g., try-with-resources, avoiding SQL injection).