Objective

Understand the JDBC (Java Database Connectivity) API, its architecture, and how to use it for database operations in Java. Learn transaction management, SQL injection prevention, and the Data Access Object (DAO) design pattern to build robust database-driven applications.

Lecture Topics

1. Introduction to JDBC API

• Definition: JDBC is a Java API that enables Java applications to interact with relational databases (e.g., MySQL, Oracle, PostgreSQL) using SQL.
• Purpose: Provides a standard interface to connect, query, and manipulate databases in a platform-independent manner.
• Key Features:
  • Execute SQL queries (SELECT, INSERT, UPDATE, DELETE).
  • Handle results from database queries.
  • Support for transaction management.
  • Database-agnostic (works with any database providing a JDBC driver).
• Components:
  • JDBC API (java.sql and javax.sql packages).
  • JDBC Driver (vendor-specific implementation for a particular database).

2. JDBC Architecture

• Two-Tier Architecture:
  • Java application communicates directly with the database.
  • JDBC Driver acts as a bridge between the Java application and the database.
• Three-Tier Architecture:
  • Java application communicates with a middle tier (e.g., application server), which then interacts with the database.
  • Used in enterprise applications for scalability and separation of concerns.
• JDBC Driver Manager:
  • Manages multiple JDBC drivers and establishes connections to databases.
• Key Layers:
  • Application Layer: Java code using JDBC API.
  • Driver Layer: Vendor-specific driver implementing JDBC interfaces.
  • Database Layer: The actual database (e.g., MySQL, Oracle).

3. JDBC Drivers

• 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 Driver:
     • Uses ODBC driver to connect to the database.
     • Requires ODBC configuration on the client machine.
     • Not suitable for production due to performance overhead and dependency on native code.
  2. Type 2: Native-API Driver:
     • Uses database-specific native libraries.
     • Faster than Type 1 but requires native libraries on the client machine.
  3. Type 3: Network Protocol Driver:
     • Uses a middleware server to translate JDBC calls to database-specific protocols.
     • Platform-independent but requires a middle tier.
  4. Type 4: Thin Driver:
     • Pure Java driver that communicates directly with the database using the database’s native protocol.
     • Most commonly used, portable, and efficient (e.g., MySQL Connector/J).
• Choosing a Driver:
  • Type 4 is preferred for modern applications due to its portability and performance.
  • Example: For MySQL, use com.mysql.cj.jdbc.Driver.

4. JDBC Classes & Interfaces

• Key Classes and Interfaces:
  1. Driver:
     • Interface implemented by JDBC drivers.
     • Loaded to register the driver with the DriverManager.
     • Example: Class.forName("com.mysql.cj.jdbc.Driver");
  2. Connection:
     • Represents a connection to the database.
     • Obtained via DriverManager.getConnection(url, username, password).
     • Example: Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "user", "pass");
  3. Statement:
     • Used to execute static SQL queries.
     • Example: Statement stmt = conn.createStatement();
     • Methods: executeQuery() (for SELECT), executeUpdate() (for INSERT/UPDATE/DELETE).
  4. PreparedStatement:
     • Extends Statement, used for precompiled SQL queries with parameters.
     • Prevents SQL injection and improves performance for repeated queries.
     • Example: PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM users WHERE id = ?");
  5. ResultSet:
     • Represents the result of a query.
     • Provides methods to iterate and retrieve data (e.g., getInt(), getString()).
     • Example: ResultSet rs = stmt.executeQuery("SELECT * FROM users");