JDBC in Java

Java Database Connectivity (JDBC) is an API that allows Java applications to interact with databases. It provides methods to query and update data in a database using SQL.

---

1. JDBC Driver

• Definition: A JDBC driver is a software component that enables a Java application to interact with a specific database.
• Types of JDBC Drivers:
  1. Type-1 (JDBC-ODBC Bridge): Connects Java applications to an ODBC driver.
  2. Type-2 (Native-API Driver): Uses the native library of the database.
  3. Type-3 (Network Protocol Driver): Converts JDBC calls to a network protocol.
  4. Type-4 (Thin Driver): Directly converts JDBC calls to database-specific calls.

---

2. Steps in JDBC

1. Load the Driver Class: Register the JDBC driver.
2. Establish Connection: Use DriverManager.getConnection() to connect to the database.
3. Create a Statement: Use Connection.createStatement() to create a SQL statement.
4. Execute Query: Use methods like executeQuery() or executeUpdate() to run SQL queries.
5. Process Results: Use ResultSet to fetch and process data.
6. Close Resources: Close ResultSet, Statement, and Connection to free resources.

Example: JDBC Steps

import java.sql.*;

public class JDBCDemo {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb";
        String user = "root";
        String password = "password";

        try {
            // 1. Load the Driver Class
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 2. Establish Connection
            Connection connection = DriverManager.getConnection(url, user, password);

            // 3. Create a Statement
            Statement statement = connection.createStatement();

            // 4. Execute Query
            ResultSet resultSet = statement.executeQuery("SELECT * FROM users");

            // 5. Process Results
            while (resultSet.next()) {
                System.out.println("User: " + resultSet.getString("username"));
            }

            // 6. Close Resources
            resultSet.close();
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

---

3. Components of JDBC API

1. DriverManager: Manages database drivers and establishes connections.
2. Connection: Represents the connection to a database.
3. Statement: Executes SQL queries.
4. PreparedStatement: Precompiled SQL statement to improve performance.
5. CallableStatement: Executes stored procedures in a database.
6. ResultSet: Holds the data retrieved from a database query.

---

4. JDBC Connection Interface

• Methods in Connection:
  1. createStatement(): Creates a Statement object.
  2. prepareStatement(String sql): Creates a PreparedStatement object.
  3. setAutoCommit(boolean autoCommit): Enables or disables auto-commit mode.
  4. commit(): Commits the transaction.
  5. rollback(): Rolls back the transaction.

---

5. Difference Between executeQuery(), executeUpdate(), and execute()

Method	Purpose	Return Value	Use Case
executeQuery()	Executes a SELECT query.	Returns a ResultSet.	Fetching data from the database.
executeUpdate()	Executes INSERT, UPDATE, or DELETE statements.	Returns an int (rows affected).	Modifying data in the database.
execute()	Executes any SQL statement (SELECT, INSERT, UPDATE, DELETE, etc.).	Returns a boolean:	Dynamic queries where query type is unknown.
	– true if the result is a ResultSet.
	– false if it is an update count.

Examples:

• executeQuery() Example:

ResultSet rs = statement.executeQuery("SELECT * FROM users");
while (rs.next()) {
    System.out.println(rs.getString("username"));
}

• executeUpdate() Example:

int rows = statement.executeUpdate("UPDATE users SET status='active' WHERE id=1");
System.out.println(rows + " rows updated.");

• execute() Example:

boolean isResultSet = statement.execute("SELECT * FROM users");
if (isResultSet) {
    ResultSet rs = statement.getResultSet();
    while (rs.next()) {
        System.out.println(rs.getString("username"));
    }
} else {
    int updateCount = statement.getUpdateCount();
    System.out.println(updateCount + " rows affected.");
}

---

Conclusion

JDBC is a powerful API for connecting Java applications to databases. By understanding its components, methods, and steps, developers can effectively manage database operations and build robust applications.