JDBC

?? Why You Should Still Learn Core JDBC in the Era of Spring Data JPA

Nowadays, developers rely heavily on Spring Data JPA for database interactions, simplifying many boilerplate code. However, understanding core JDBC is crucial because:

? JPA internally uses JDBC – Knowing JDBC helps debug and optimize queries.

? Direct JDBC gives more control – In performance-critical applications, JDBC allows fine-tuning.

? JDBC knowledge helps in legacy systems – Many existing enterprise applications still use JDBC.

So, let's dive into Java JDBC, its drivers, why we use Type-4 drivers, how JDBC interfaces work, and advanced concepts like connection pooling, transactions, and stored procedures.

?? JDBC Drivers and Why We Use Type-4?

JDBC provides four types of drivers:

1?? Type-1: JDBC-ODBC Bridge (Deprecated)

2?? Type-2: Native-API Driver (Platform-dependent)

3?? Type-3: Network Protocol Driver (Uses middleware)

4?? Type-4: Thin Driver (Pure Java) ?

?? Why use Type-4?

It’s platform-independent.

Directly communicates with the database (no intermediate layers).

Offers better performance than other types.

Popular Type-4 Drivers:

MySQL → com.mysql.cj.jdbc.Driver

Oracle → oracle.jdbc.OracleDriver

PostgreSQL → org. postgresql.Driver

?? JDBC API: Interfaces & Their Implementations

JDBC follows the interface-implementation pattern. Key interfaces:

Connection – Manages database connections.

Statement – Executes SQL queries.

PreparedStatement – Precompiled SQL execution (prevents SQL injection).

CallableStatement – Calls stored procedures.

ResultSet – Handles query results.

Each database provides its implementation:

MySQL → com.mysql.cj.jdbc.ConnectionImpl

Oracle → oracle.jdbc.driver.OracleConnection

?? Connecting Java with MySQL (Step-by-Step)

? Using DriverManager (Traditional Approach)

Class.forName("com.mysql.cj.jdbc.Driver");

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "user", "password");

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("SELECT * FROM employees");        

?? Statement vs. PreparedStatement: Performance & Security

Feature Statement PreparedStatement Query Execution Compiled & executed every time Precompiled once, executed multiple times Performance Slower due to recompilation Faster due to reuse SQL Injection Vulnerable ?? Safe ? (Parameterized queries)

?? Why PreparedStatement?

Performance boost (avoids query recompilation).

SQL Injection prevention (query parameters are sanitized).

? Using PreparedStatement for security & performance

String sql = "SELECT * FROM employees WHERE id = ?";

PreparedStatement pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, 101);

ResultSet rs = pstmt.executeQuery();        

?? Connection Pooling: Why & How?

Creating a new DB connection each time is expensive. Connection pooling optimizes this by reusing connections.

?? Popular Connection Pooling Libraries:

HikariCP ? (Fastest & most efficient)

Apache DBCP

C3P0

? Using HikariCP for Connection Pooling

HikariConfig config = new HikariConfig();

config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");

config.setUsername("user");

config.setPassword("password");

config.setMaximumPoolSize(10);

HikariDataSource ds = new HikariDataSource(config);

Connection conn = ds.getConnection();        

?? Why Connection Pooling?

? Reduces DB connection latency

? Optimizes resource utilization

? Handles multiple concurrent requests efficiently

?? Transactions in JDBC: commit(), rollback(), savepoint()

Example: Using Savepoints for Partial Rollback

conn.setAutoCommit(false);

Savepoint sp = conn.setSavepoint();



try {

Statement stmt = conn.createStatement();

stmt.executeUpdate("INSERT INTO employees VALUES (105, 'Alice')");

conn.commit(); // Commit all changes

} catch (SQLException e) {

conn.rollback(sp); // Rollback only up to savepoint

conn.commit(); // Ensure remaining changes are saved

}        

?? When to use Transactions?

Bank Transfers (Rollback if one step fails).

Bulk Insert Operations (Ensure atomicity).

?? Storing Images & Videos Using JDBC

Binary data like images, videos can be stored using BLOB (Binary Large Object).

? Storing Image in MySQL using JDBC

PreparedStatement pstmt = conn.prepareStatement("INSERT INTO files (name, data) VALUES (?, ?)");

pstmt.setString(1, "profile.jpg");

pstmt.setBlob(2, new FileInputStream(new File("path/to/image.jpg")));

pstmt.executeUpdate();        

?? When to Use BLOB?

? Profile pictures, product images, documents stored in DB.

? If storing large files, prefer cloud storage (AWS S3, GCS) instead.

?? Calling Stored Procedures with CallableStatement

Example: Executing a Stored Procedure

CallableStatement cstmt = conn.prepareCall("{call getEmployeeDetails(?)}");

cstmt.setInt(1, 101);

ResultSet rs = cstmt.executeQuery();        

?? Why Use Stored Procedures?

? Reduces network calls (DB-side processing).

? Improves security (avoid exposing SQL logic).

?? Final Thoughts

Even though Spring Data JPA is widely used, knowing core JDBC helps in:

? Debugging performance bottlenecks.

? Optimizing queries & transactions.

? Understanding connection pooling & stored procedures.

? Preventing SQL Injection with PreparedStatement.

?? GitHub Repository with Examples: [GitHub Link Here]