JDBC and API in Java | BhauAutomation

🗄️ JDBC

What is JDBC in Java?

JDBC (Java Database Connectivity) is an API that enables Java applications to connect with relational databases such as MySQL, Oracle, PostgreSQL, and SQL Server. It provides methods to send SQL queries from Java code and process results dynamically, making it a fundamental technology for database-driven applications.

🏗️ JDBC Architecture

Java Application
       │
       ▼
   JDBC API
       │
       ▼
JDBC Driver Manager
       │
       ▼
Database Driver (MySQL, Oracle, PostgreSQL)
       │
       ▼
   Database

🎯 Objectives of JDBC

Connect Java applications to databases and perform CRUD (Create, Read, Update, Delete) operations.

Example: In an online gas booking system, JDBC is used to store customer booking details, transaction records, and user information in a MySQL database.

✅ Advantages

JDBC is part of the standard Java library, making it easy to use with any relational database that provides a JDBC driver.

Example: A single JDBC interface can switch between MySQL and Oracle by just changing the driver class and connection URL.

⚠️ Limitations

Requires specific JDBC drivers for each database and involves verbose coding for large applications.

Example: Manually closing ResultSet, Statement, and Connection objects can be tedious and error-prone in big projects.

📋 JDBC Working Process

Import JDBC package (java.sql.*)
Register and load the JDBC driver (Class.forName())
Establish connection using DriverManager.getConnection()
Create and execute SQL statements (Statement or PreparedStatement)
Process results using ResultSet
Close connection to release database resources

🔑 JDBC Interfaces & Classes

DriverManager Connection Statement PreparedStatement CallableStatement ResultSet SQLException

💻 Example of JDBC Code

import java.sql.*;

public class JDBCDemo {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb";
        String username = "root";
        String password = "password";
        
        // Using try-with-resources for automatic resource management
        try (Connection con = DriverManager.getConnection(url, username, password);
             Statement stmt = con.createStatement()) {
            
            // Class.forName("com.mysql.cj.jdbc.Driver"); // For older JDBC versions
            
            // SELECT query
            ResultSet rs = stmt.executeQuery("SELECT * FROM users");
            
            while (rs.next()) {
                System.out.println("ID: " + rs.getInt("id") + 
                                 ", Name: " + rs.getString("name") +
                                 ", Email: " + rs.getString("email"));
            }
            
            // INSERT using PreparedStatement (prevents SQL injection)
            String insertSQL = "INSERT INTO users (name, email) VALUES (?, ?)";
            try (PreparedStatement pstmt = con.prepareStatement(insertSQL)) {
                pstmt.setString(1, "John Doe");
                pstmt.setString(2, "john@example.com");
                int rowsInserted = pstmt.executeUpdate();
                System.out.println("Rows inserted: " + rowsInserted);
            }
            
        } catch (SQLException e) {
            System.out.println("Database error: " + e.getMessage());
        }
    }
}

🏆 Best Practices for JDBC

✅ Always close database connections, statements, and result sets in finally block or use try-with-resources

✅ Use PreparedStatement instead of Statement to prevent SQL injection attacks

✅ Handle exceptions gracefully with meaningful error messages

✅ Use connection pooling (like HikariCP, Apache DBCP) for better performance in enterprise applications

✅ Keep database credentials in configuration files, not hardcoded

▼ API SECTION ▼

🔌 API

What is API in Java?

API (Application Programming Interface) provides a set of rules and methods for software components to interact. In Java, APIs are used to communicate between applications or systems, typically through REST (Representational State Transfer) or SOAP (Simple Object Access Protocol) protocols. Java APIs are essential for building microservices, web services, and integrating with third-party services.

🎯 Objectives of API in Java

Enable communication between different systems using standardized methods and data formats (JSON/XML).

Example: A Java REST API can send customer data from a website to a mobile app, or fetch product information from an external service.

✅ Advantages

APIs promote modular development, simplify integration with other technologies, and enable scalability.

Example: A payment gateway API (like Paytm, Razorpay, Stripe) helps connect your Java e-commerce application with external payment processors without building payment logic from scratch.

⚠️ Limitations

API development involves security challenges (authentication, rate limiting), version mismatches between client and server, and dependency on network reliability.

Example: A REST API call may fail if the internet connection is unstable or if the server is experiencing downtime.

📋 API Workflow in Java

Create API endpoints using frameworks like Spring Boot, JAX-RS, or Servlets
Define HTTP methods for CRUD operations (GET, POST, PUT, DELETE)
Connect backend with databases or external services via JDBC or ORM
Send and receive JSON/XML responses between client and server
Implement validation, authentication, and error handling

🔑 Common API Types in Java

REST API SOAP API GraphQL WebSocket gRPC

💻 Example of Java REST API (Spring Boot)

// UserController.java
package com.example.demo.controller;

import org.springframework.web.bind.annotation.*;
import java.util.*;

@RestController
@RequestMapping("/api/users")
public class UserController {
    
    private List<String> users = new ArrayList<>();
    
    // GET endpoint - retrieve all users
    @GetMapping
    public List<String> getUsers() {
        return users;
    }
    
    // POST endpoint - add a new user
    @PostMapping
    public String addUser(@RequestBody String user) {
        users.add(user);
        return "User added: " + user;
    }
    
    // GET by ID endpoint
    @GetMapping("/{index}")
    public String getUserById(@PathVariable int index) {
        if (index < users.size()) {
            return users.get(index);
        }
        return "User not found";
    }
    
    // DELETE endpoint
    @DeleteMapping("/{index}")
    public String deleteUser(@PathVariable int index) {
        if (index < users.size()) {
            String removed = users.remove(index);
            return "Deleted: " + removed;
        }
        return "User not found";
    }
}

// Main Application Class
@SpringBootApplication
public class DemoApplication {
    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }
}

📋 HTTP Methods in REST APIs

GET - Retrieve data POST - Create data PUT - Update entire resource PATCH - Partial update DELETE - Remove data

🏆 Best Practices for API Development

✅ Follow RESTful standards (proper HTTP methods, status codes, resource naming)

✅ Use authentication and authorization (JWT, OAuth 2.0, API Keys) for security

✅ Handle errors with proper HTTP status codes (200, 400, 401, 403, 404, 500)

✅ Version your APIs (e.g., /api/v1/users, /api/v2/users)

✅ Use Swagger/OpenAPI for API documentation

✅ Test endpoints using Postman, Insomnia, or automated testing frameworks

✅ Implement rate limiting to prevent abuse

✅ Return meaningful error messages and use consistent response structures

🛠️ Popular Java Frameworks for API Development

Spring Boot JAX-RS (Jersey) Dropwizard Micronaut Quarkus Vert.x

← Previous Java Stack And Vector Next Java →