Key Concepts of JDBC

 JDBC (Java Database Connectivity) is an API in Java that allows applications to connect to a database, execute queries, and update records. It provides a standard interface for Java applications to interact with various databases like MySQL, PostgreSQL, Oracle, and more.

Key Concepts of JDBC:

1. JDBC Drivers:

   • JDBC requires a database-specific driver to communicate with the database.
   • There are four types of JDBC drivers:
     1. Type 1: JDBC-ODBC Bridge Driver
     2. Type 2: Native-API Driver
     3. Type 3: Network Protocol Driver
     4. Type 4: Thin Driver (most commonly used today)

2. Establishing Connection:

   • Use DriverManager or DataSource to establish a connection to the database.
   • Example:

     java
     Connection conn = DriverManager.getConnection(url, username, password);
     

3. Creating Statements:

   • There are different types of statements:
     • Statement: Used for simple SQL queries.
     • PreparedStatement: Used for precompiled SQL queries, often with parameters.
     • CallableStatement: Used for executing stored procedures.

4. Executing Queries:

   • Execute SQL queries using methods like executeQuery() for SELECT statements and executeUpdate() for INSERT, UPDATE, DELETE queries.
   • Example:

     java
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT * FROM users");
     

5. Processing Results:

   • Use the ResultSet object to handle the data returned from a SELECT query.
   • Example:

     java
     while (rs.next()) {
         String name = rs.getString("name");
         int age = rs.getInt("age");
     }
     

6. Handling Exceptions:

   • SQLException is the primary exception in JDBC, and it is used for handling errors related to database access.

7. Transaction Management:

   • JDBC supports transaction management using the commit() and rollback() methods.
   • Example:

     java
     conn.setAutoCommit(false);
     // execute queries
     conn.commit(); // commit transaction
     

8. Closing Connections:

   • Always close the Connection, Statement, and ResultSet objects after use to prevent resource leaks.
   • Example:

     java
     rs.close();
     stmt.close();
     conn.close();
     

Example of JDBC Code:

java
import java.sql.*;

public class JDBCExample {
    public static void main(String[] args) {
        try {
            // Load the JDBC driver
            Class.forName("com.mysql.cj.jdbc.Driver");

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

            // Create a statement
            Statement stmt = conn.createStatement();

            // Execute a query
            ResultSet rs = stmt.executeQuery("SELECT * FROM employees");

            // Process the result set
            while (rs.next()) {
                System.out.println("Employee ID: " + rs.getInt("id"));
                System.out.println("Employee Name: " + rs.getString("name"));
            }

            // Close resources
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Benefits of JDBC:

• Database Independence: JDBC allows Java applications to interact with different types of databases.
• Efficient Data Access: It enables efficient access and manipulation of relational data in a database.
• Support for Transactions: JDBC provides built-in transaction management features.