Question Answer

Java Database Connectivity (JDBC) Program with Explanation

What You Will Learn

This example shows how to connect a Java program to a MySQL database using JDBC, create a table, insert records safely with PreparedStatement, and fetch data. It uses clean, beginner-friendly code suitable for B.Tech CSE students.

Prerequisites

• Java installed (JDK 8 or later)
• MySQL server running locally
• MySQL JDBC driver (Connector/J) added to your project classpath
• A database named college_db (create it if not present)

Create Database (run in MySQL)

CREATE DATABASE IF NOT EXISTS college_db;

Complete Java Program (JDBC Example)

// File: JdbcDemo.java
import java.sql.*;
import java.math.BigDecimal;

public class JdbcDemo {
    // Update these with your DB details
    private static final String DB_URL =
        "jdbc:mysql://localhost:3306/college_db?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
    private static final String USER = "root";
    private static final String PASS = "password";

    public static void main(String[] args) {
        String createTableSql =
            "CREATE TABLE IF NOT EXISTS students (" +
            " id INT PRIMARY KEY AUTO_INCREMENT," +
            " name VARCHAR(50) NOT NULL," +
            " cgpa DECIMAL(3,2) NOT NULL" +
            ")";
        String insertSql = "INSERT INTO students(name, cgpa) VALUES(?, ?)";
        String querySql = "SELECT id, name, cgpa FROM students";

        // Optional for JDBC 4+, but harmless if present
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.err.println("MySQL JDBC driver not found. Add Connector/J to classpath.");
            return;
        }

        // Try-with-resources automatically closes Connection, Statements, and ResultSet
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS)) {
            System.out.println("Connected to database.");

            // 1) Create table (if not exists)
            try (Statement st = conn.createStatement()) {
                st.execute(createTableSql);
            }

            // 2) Insert sample rows using PreparedStatement (safe and efficient)
            try (PreparedStatement ps = conn.prepareStatement(insertSql)) {
                ps.setString(1, "Asha");
                ps.setBigDecimal(2, new BigDecimal("8.70"));
                ps.executeUpdate();

                ps.setString(1, "Rahul");
                ps.setBigDecimal(2, new BigDecimal("9.10"));
                ps.executeUpdate();
            }

            // 3) Query and display records
            try (PreparedStatement ps = conn.prepareStatement(querySql);
                 ResultSet rs = ps.executeQuery()) {

                System.out.println("Student Records:");
                while (rs.next()) {
                    int id = rs.getInt("id");
                    String name = rs.getString("name");
                    BigDecimal cgpa = rs.getBigDecimal("cgpa");
                    System.out.printf("%d\t%s\t%s%n", id, name, cgpa);
                }
            }

        } catch (SQLException e) {
            // Print detailed SQL error
            e.printStackTrace();
        }
    }
}

How the Program Works (Step-by-Step)

1. Load the driver: Class.forName("com.mysql.cj.jdbc.Driver") loads the MySQL JDBC driver (optional in JDBC 4+, but included for clarity).
2. Open a connection: DriverManager.getConnection(DB_URL, USER, PASS) connects your Java app to the database.
3. Create a table: A simple SQL statement creates the students table if it does not exist.
4. Insert data safely: PreparedStatement prevents SQL injection and handles types correctly.
5. Execute a query: Run a SELECT query and iterate over the ResultSet to read rows.
6. Automatic cleanup: Try-with-resources closes Connection, Statement, and ResultSet automatically.

Compilation and Run

Make sure the MySQL Connector/J JAR is on the classpath. Replace the path and version as per your setup.

// Windows (PowerShell or CMD)
javac -cp ".;mysql-connector-j-9.0.0.jar" JdbcDemo.java
java  -cp ".;mysql-connector-j-9.0.0.jar" JdbcDemo

// Linux/Mac
javac -cp ".:mysql-connector-j-9.0.0.jar" JdbcDemo.java
java  -cp ".:mysql-connector-j-9.0.0.jar" JdbcDemo

Why Use PreparedStatement?

• Prevents SQL injection by separating SQL logic from data.
• Automatically handles data types and special characters.
• Can be reused for multiple inserts/queries with different parameters.

Common JDBC URL Formats (for other databases)

• MySQL: jdbc:mysql://host:3306/dbname
• PostgreSQL: jdbc:postgresql://host:5432/dbname
• SQLite: jdbc:sqlite:database-file.db
• Oracle: jdbc:oracle:thin:@host:1521/serviceName
• SQL Server: jdbc:sqlserver://host:1433;databaseName=dbname

Tips and Best Practices

• Never hard-code real passwords in source code. Use environment variables or a config file.
• Use try-with-resources to prevent connection leaks.
• Validate user inputs and use transactions for multi-step operations.
• Log SQL exceptions with message, SQLState, and error code for easier debugging.

Key Takeaways

• JDBC provides a standard way to connect Java programs to databases.
• DriverManager, Connection, PreparedStatement, and ResultSet are the core APIs you will use.
• Prepared statements and proper resource management make your code secure and reliable.