Unit 3: Java Programming

Chapter 6: Database Connectivity - JDBC and MySQL

1. What is JDBC?

JDBC (Java Database Connectivity) is a standard Java API (part of java.sql package) that defines how Java applications connect to, query, and interact with relational databases like MySQL.

2. JDBC Architecture

[ Java Application / Swing GUI ]
              |
     [ JDBC API (java.sql) ]
              |
  [ JDBC Driver Manager ]
              |
 [ MySQL Connector/J Driver ]
              |
       [ MySQL Database ]

3. Core JDBC Interfaces and Classes

1. DriverManager: Factory class that manages registered database drivers and creates connections.
2. Connection: Interface representing an active session/connection with a specific database.
3. Statement: Interface used to execute static SQL statements without parameters.
4. PreparedStatement: Sub-interface of Statement used for precompiled, parameterized SQL queries (prevents SQL injection).
5. ResultSet: A virtual table of data returned from a query (SELECT), maintaining a cursor pointing to the current data row.

4. The 5 Steps to Connect Java with MySQL

Step 1: Load the JDBC Driver Class

Class.forName("com.mysql.cj.jdbc.Driver");

Step 2: Establish the Database Connection

String url = "jdbc:mysql://localhost:3306/schooldb";
String user = "root";
String password = "password123";
Connection con = DriverManager.getConnection(url, user, password);

Step 3: Create Statement / PreparedStatement Object

Statement stmt = con.createStatement();

Step 4: Execute the SQL Query

• For retrieving data (SELECT): Use executeQuery() $ o$ returns ResultSet.
• For modifying data (INSERT, UPDATE, DELETE, CREATE): Use executeUpdate() $ o$ returns int (number of rows affected).

// Example A: Executing a SELECT query
ResultSet rs = stmt.executeQuery("SELECT RollNo, Name, Marks FROM Student");
while (rs.next()) {
    int roll = rs.getInt("RollNo");
    String name = rs.getString("Name");
    double marks = rs.getDouble("Marks");
    System.out.println(roll + " | " + name + " | " + marks);
}

// Example B: Executing an INSERT query
int rows = stmt.executeUpdate("INSERT INTO Student VALUES (105, 'Karan', 'M', 91.5, '2006-05-10', 'Science')");
System.out.println(rows + " record(s) inserted.");

Step 5: Close Database Resources

rs.close();
stmt.close();
con.close();

5. Navigating the ResultSet

• rs.next(): Moves cursor forward one row. Returns true if next row exists, false at the end.
• rs.first(): Moves cursor to first row.
• rs.last(): Moves cursor to last row.
• rs.getString(colName) / rs.getInt(colName): Retrieves field value of current row.

6. Complete End-to-End Code Example with Exception Handling

import java.sql.*;

public class StudentDBViewer {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/school";
        String user = "root";
        String pass = "root123";

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection conn = DriverManager.getConnection(url, user, pass);
            Statement st = conn.createStatement();

            ResultSet rs = st.executeQuery("SELECT * FROM Student WHERE Marks > 80");

            System.out.println("RollNo\tName\tMarks");
            while (rs.next()) {
                System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getDouble(3));
            }

            rs.close();
            st.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            System.out.println("MySQL Driver not found: " + e.getMessage());
        } catch (SQLException e) {
            System.out.println("Database error: " + e.getMessage());
        }
    }
}

7. Board Exam Questions & Viva Tips

1. Differentiate between executeQuery() and executeUpdate().
2. Ans: executeQuery() is used with SELECT queries and returns a ResultSet. executeUpdate() is used with DDL and DML statements (INSERT, UPDATE, DELETE) and returns an integer indicating affected row count.
3. What is the function of rs.next() method?
4. Ans: It moves the cursor to the next record in the ResultSet and returns true if a row is present, or false when records are exhausted.