Java Database Connectivity Interview Questions

Main JDBC components: 1) DriverManager: manages database drivers, 2) Connection: represents database connection, 3) Statement/PreparedStatement/CallableStatement: execute SQL, 4) ResultSet: holds query results, 5) DataSource: for connection pooling and distributed transactions. Each component serves specific purpose in database interaction. Modern applications typically use DataSource instead of DriverManager.

Statement: basic SQL execution, no pre-compilation. PreparedStatement: pre-compiled SQL, prevents SQL injection, better performance for repeated execution, supports parameters. CallableStatement: extends PreparedStatement for stored procedure calls. PreparedStatement preferred for most use cases due to security and performance benefits. Use Statement only for one-time, dynamic SQL.

Transaction handling: 1) Connection.setAutoCommit(false) to start transaction, 2) Execute SQL statements, 3) commit() on success, rollback() on failure, 4) Use try-with-resources and finally for proper cleanup. Best practices: appropriate isolation level, transaction boundaries, error handling. Consider using transaction management frameworks for complex scenarios.

Connection pooling maintains cache of database connections for reuse, improving performance. Implementation: 1) Use DataSource implementation (C3P0, HikariCP, etc.), 2) Configure pool size, timeout, validation, 3) Handle connection leaks. Benefits: reduced overhead, better resource utilization, connection management. Important settings: max pool size, idle timeout, validation query.

SQL injection prevention: 1) Use PreparedStatement with parameterized queries, 2) Never concatenate user input into SQL strings, 3) Validate and sanitize input, 4) Use stored procedures when appropriate, 5) Implement proper access controls. Additional measures: escape special characters, use proper permissions, regular security audits. Critical for application security.

ResultSet types: TYPE_FORWARD_ONLY (default, forward-only cursor), TYPE_SCROLL_INSENSITIVE (scrollable, snapshot), TYPE_SCROLL_SENSITIVE (scrollable, reflects changes). Concurrency modes: CONCUR_READ_ONLY (default), CONCUR_UPDATABLE (allows updates). Holdability: HOLD_CURSORS_OVER_COMMIT, CLOSE_CURSORS_AT_COMMIT. Choose based on requirements and database support.

Large object handling: 1) Use streaming methods (setBlob/setClob) for writing, 2) Stream in chunks when reading, 3) Consider performance and memory implications, 4) Properly close resources. Best practices: use try-with-resources, appropriate buffer sizes, transaction management. Consider file system storage for very large objects.

Batch updates allow multiple SQL statements to be executed in single database round trip. Implementation: addBatch() to queue statements, executeBatch() to execute. Benefits: improved performance for multiple operations, reduced network traffic. Use cases: bulk inserts, multiple related updates. Consider batch size, error handling, and transaction boundaries.

Database metadata accessed through DatabaseMetaData interface. Uses: 1) Query database capabilities, 2) Get table/column information, 3) Discover supported features, 4) Schema introspection. Important for: dynamic SQL generation, database-independent code, tool development. Consider caching metadata for performance. Handle database-specific variations.

Isolation levels: READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE. Each level addresses different concurrency issues: dirty reads, non-repeatable reads, phantom reads. Higher isolation provides more consistency but reduces concurrency. Choose based on requirements: data consistency needs vs performance/scalability.

HikariCP implementation: 1) Configure HikariConfig with pool properties, 2) Create HikariDataSource, 3) Manage connection lifecycle. Key settings: maximum pool size, connection timeout, idle timeout, minimum idle. Best practices: proper sizing, connection testing, metrics monitoring. Popular choice due to performance and reliability.

Exception handling practices: 1) Use appropriate exception types (SQLException hierarchy), 2) Properly close resources in finally block or try-with-resources, 3) Log relevant error information, 4) Implement proper retry logic, 5) Wrap in domain-specific exceptions. Consider: transaction rollback, connection state, cleanup procedures. Balance between recovery and failure propagation.

Database vendor handling: 1) Use vendor-neutral SQL, 2) Abstract vendor-specific code, 3) Use DatabaseMetaData for feature detection, 4) Implement dialect patterns for SQL differences. Consider: pagination differences, data type mappings, feature support. Use database abstraction layers or JPA for better portability.

NULL handling: 1) Use setNull() for parameters, 2) wasNull() for primitive type reads, 3) Handle null in result set appropriately, 4) Consider database-specific NULL handling. Best practices: proper null checks, default values where appropriate, consistent null handling strategy. Important for data integrity and application robustness.

Connection retry/failover: 1) Implement retry logic with exponential backoff, 2) Handle different failure types appropriately, 3) Use connection pools with test-on-borrow, 4) Implement circuit breaker pattern. Consider: timeout settings, maximum retry attempts, failover nodes. Balance between availability and response time.

Savepoints create points within transaction to roll back to, without rolling back entire transaction. Usage: 1) Complex transactions with partial rollback needs, 2) Nested transaction-like behavior, 3) Error recovery within transaction. Considerations: performance impact, database support, proper release. Use sparingly due to complexity.

Performance optimization: 1) Use connection pooling, 2) Batch updates for bulk operations, 3) Appropriate fetch size for ResultSet, 4) Proper transaction boundaries, 5) Prepared statement caching, 6) Index usage awareness. Monitor: connection usage, query performance, resource utilization. Balance between performance and maintainability.

Type mapping approaches: 1) Standard JDBC type mappings, 2) Custom type mappings using SQLData interface, 3) Handle vendor-specific types. Considerations: precision/scale for numbers, timezone for dates, character encoding. Important for data integrity and application correctness. Document mapping decisions.

Connection validation: 1) Test-on-borrow/return in connection pool, 2) Validation query configuration, 3) Connection timeout settings, 4) Idle connection testing. Importance: preventing stale connections, ensuring reliability. Balance validation frequency with performance impact. Consider database-specific validation queries.

DataSource benefits: 1) Connection pooling support, 2) Distributed transaction integration, 3) Better resource management, 4) Container management capability. Implementation: JNDI lookup, programmatic configuration. Important for enterprise applications. Prefer over DriverManager for production use. Consider security integration.