Easy Notes Of Java programming unit-6 @Computer Diploma

Description

6.1 Introduction to JDBC and ODBC

6.2 JDBC Architecture: Two-Tier and Three-Tier Models

JDBC defines how a client application interacts with a database. This interaction is typically modeled in two ways:

Two-Tier Architecture (Client-Server)

• Structure: A Java application (the Client) communicates directly with the Database (the Server).
• Mechanism: The application makes JDBC calls, which the JDBC driver translates and sends to the database. The database executes the SQL and sends results back directly to the client.
• Use Case: Simple applications, workgroup applications, or situations where the database load is managed locally.

Three-Tier Architecture (Client-Application Server-Database)

• Structure: A Java application (the Client) communicates with a Middle Tier (often a Java Application Server), which then connects to the Database.
• Mechanism: The client sends requests to the application server (e.g., using technologies like servlets or EJBs). The application server contains the JDBC code and driver, manages the database connections, executes the SQL, and returns processed results to the client.
• Use Case: Large-scale enterprise applications, web applications, load balancing, and enforcing centralized business logic.

6.3 Key JDBC Components and Classes

Types of JDBC Drivers

There are four main types of JDBC drivers:

1. Type 1: JDBC-ODBC Bridge Driver (Legacy/Deprecated)
   • Mechanism: Translates JDBC calls into ODBC calls, which are then handled by the ODBC driver.
   • Requires: The native ODBC driver to be installed on the client machine.
   • Drawback: Performance overhead and client-side dependency on the ODBC configuration.
2. Type 2: Native-API/Partially Java Driver
   • Mechanism: Converts JDBC calls into the native API calls of the database (e.g., Oracle OCI).
   • Requires: Database-specific native libraries to be installed on the client.
   • Drawback: Platform dependency; performance can be good.
3. Type 3: Network Protocol/Middleware Driver (All-Java)
   • Mechanism: Converts JDBC calls into a database-independent network protocol. A middle-tier server then translates this generic protocol into the database-specific protocol.
   • Advantage: Completely written in Java, no native libraries required on the client, good for three-tier architecture.
4. Type 4: Thin Driver/Protocol Converter (All-Java)
   • Mechanism: Converts JDBC calls directly into the database’s native network protocol (e.g., Oracle’s TNS or MySQL’s protocol).
   • Advantage: Purely Java, fastest, most popular, no client-side native libraries or middleware required.

Key Java Classes and Interfaces

Class Class (Used for Driver Loading)

The java.lang.Class class is used to dynamically load the JDBC driver into memory.

• Method: Class.forName("com.mysql.cj.jdbc.Driver"); (Example for MySQL)
• Purpose: The static block in the driver class is executed when loaded, which typically registers the driver with the DriverManager.
  • Note: Since JDBC 4.0, this explicit loading step is often unnecessary for Type 4 drivers as the driver is automatically loaded via the Java Service Provider mechanism.

DriverManager Class

This is the fundamental management class in JDBC.

• Role: Manages a list of loaded JDBC drivers and selects the appropriate one to establish a connection.
• Key Method: static Connection getConnection(String url, String user, String password)
  • It uses the Connection URL (e.g., jdbc:mysql://localhost:3306/db_name) to determine which driver to use and initiates the connection process.

Connection Interface

This interface represents a session with a specific database.

• Role: All communication with the database goes through the Connection object. It provides methods for creating Statement objects and managing transactions.
• Key Methods:
  • createStatement(): Creates a simple Statement object.
  • prepareStatement(String sql): Creates a PreparedStatement object.
  • setAutoCommit(boolean autoCommit): Controls transaction behavior.
  • commit(): Makes all changes permanent.
  • rollback(): Undoes all changes since the last commit.
  • close(): Releases the database connection.

Statement Interface

The basic interface for executing static SQL statements.

• Role: Used to execute simple, non-parameterized SQL queries.
• Key Methods:
  • executeQuery(String sql): Executes a SELECT query and returns a ResultSet.
  • executeUpdate(String sql): Executes INSERT, UPDATE, DELETE, or DDL statements and returns the count of affected rows.
  • execute(String sql): Executes an arbitrary SQL statement (returns a boolean indicating the type of result).

PreparedStatement Interface

An extension of the Statement interface used for executing pre-compiled SQL statements.

• Role: Used for executing the same SQL statement repeatedly with different input values (parameters). It uses ‘?’ as placeholders for parameters.
• Advantage:
  1. Performance: The database compiles the SQL statement only once.
  2. Security: Prevents SQL Injection by automatically treating input values as data, not part of the executable SQL command.
• Key Methods:
  • Inherits executeQuery(), executeUpdate(), etc.
  • setInt(int parameterIndex, int x): Sets the value of a parameter at a specific index.
  • setString(int parameterIndex, String x): Sets a string value.

ResultSet Interface

This interface represents the data resulting from an executed SQL query (SELECT).

• Role: Provides access to the rows and columns of the query result, acting like an iterator over the data.
• Key Methods:
  • next(): Moves the cursor to the next row (returns true if a next row exists).
  • getString(int columnIndex): Retrieves the column value as a String.
  • getInt(String columnName): Retrieves the column value by name as an int.
  • close(): Closes the ResultSet and releases resources.