Soopro Pathshala

Java JDBC

100%

Java JDBC (Java Database Connectivity)

JDBC stands for Java Database Connectivity. It is a Java API used to connect Java applications to databases such as MySQL, PostgreSQL, and Oracle.

Using JDBC, we can:

Connect a Java application to a database.
Insert new records.
Retrieve records from a database.
Update existing records.
Delete records.

1. What Is JDBC?

JDBC provides interfaces and classes that allow Java programs to communicate with databases.

Main JDBC Components

Component	Purpose
`DriverManager`	Obtains database connections.
`Connection`	Represents a connection to a database.
`Statement`	Executes SQL statements.
`PreparedStatement`	Executes parameterized SQL statements.
`ResultSet`	Represents the results of a query.

2. JDBC Architecture

A basic JDBC application works in this order:

Java application sends a JDBC request.
JDBC obtains a connection through a suitable driver.
The JDBC driver communicates with the database.
The database executes the SQL statement.
The result is returned to the Java application.

Java Application
       |
       v
   JDBC API
       |
       v
  JDBC Driver
       |
       v
    Database

3. Requirements for JDBC

Before connecting Java to MySQL, you need:

Java Development Kit (JDK).
A running MySQL database server.
A database and table.
MySQL Connector/J JDBC driver.
A Java IDE or command-line environment.

For a Maven project, add the MySQL Connector/J dependency to your pom.xml. Use a current compatible version from the official MySQL documentation.

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>YOUR_COMPATIBLE_VERSION</version>
</dependency>

Replace YOUR_COMPATIBLE_VERSION with a real Connector/J version. The dependency must be downloaded and available to your project.

4. Creating a MySQL Database and Table

First, create a database and a table in MySQL.

CREATE DATABASE schooldb;

USE schooldb;

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT,
    course VARCHAR(100)
);

The students table stores a student's ID, name, age, and course.

5. Connecting Java to MySQL

Use DriverManager.getConnection() to establish a connection to MySQL.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        String url =
            "jdbc:mysql://localhost:3306/schooldb";

        String username = "root";
        String password = "YOUR_PASSWORD";

        try (Connection con =
                 DriverManager.getConnection(
                     url, username, password)) {

            System.out.println("Database connected successfully");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Expected Output on Successful Connection:

Database connected successfully

Replace YOUR_PASSWORD with your local MySQL password. Do not commit real database passwords to public source-code repositories.

Modern JDBC drivers are normally discovered automatically when the driver JAR is available. Explicitly calling Class.forName() is usually unnecessary.

6. Inserting Data Using PreparedStatement

A PreparedStatement executes parameterized SQL. The question marks are replaced with values using setter methods.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        String url =
            "jdbc:mysql://localhost:3306/schooldb";

        String username = "root";
        String password = "YOUR_PASSWORD";

        String sql =
            "INSERT INTO students (name, age, course) " +
            "VALUES (?, ?, ?)";

        try (Connection con =
                 DriverManager.getConnection(
                     url, username, password);
             PreparedStatement ps =
                 con.prepareStatement(sql)) {

            ps.setString(1, "Rahul");
            ps.setInt(2, 20);
            ps.setString(3, "Java");

            int rows = ps.executeUpdate();

            System.out.println(
                rows + " record inserted successfully"
            );

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Expected Output:

1 record inserted successfully

executeUpdate() returns the number of affected rows for an insert, update, or delete operation.

7. Reading Data Using ResultSet

Use executeQuery() to execute a SELECT statement. The returned ResultSet lets you read each row.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        String url =
            "jdbc:mysql://localhost:3306/schooldb";

        String username = "root";
        String password = "YOUR_PASSWORD";

        String sql = "SELECT id, name, age, course FROM students";

        try (Connection con =
                 DriverManager.getConnection(
                     url, username, password);
             PreparedStatement ps =
                 con.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            while (rs.next()) {
                System.out.println("ID: " + rs.getInt("id"));
                System.out.println("Name: " + rs.getString("name"));
                System.out.println("Age: " + rs.getInt("age"));
                System.out.println("Course: " + rs.getString("course"));
                System.out.println("----------------");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Example Output:

ID: 1
Name: Rahul
Age: 20
Course: Java
----------------

The output depends on the records currently stored in the database.

8. Updating Data

Use an UPDATE statement with PreparedStatement to change existing records.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        String url =
            "jdbc:mysql://localhost:3306/schooldb";

        String username = "root";
        String password = "YOUR_PASSWORD";

        String sql =
            "UPDATE students SET course = ? WHERE id = ?";

        try (Connection con =
                 DriverManager.getConnection(
                     url, username, password);
             PreparedStatement ps =
                 con.prepareStatement(sql)) {

            ps.setString(1, "Python");
            ps.setInt(2, 1);

            int rows = ps.executeUpdate();

            System.out.println(
                rows + " record(s) updated"
            );

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Example Output:

1 record(s) updated

This updates the course for the student whose ID is 1. The number of affected rows depends on the database data and whether the update changes any values.

9. Deleting Data

Use a DELETE statement to remove a record. Always use a suitable WHERE condition to avoid deleting unintended records.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        String url =
            "jdbc:mysql://localhost:3306/schooldb";

        String username = "root";
        String password = "YOUR_PASSWORD";

        String sql = "DELETE FROM students WHERE id = ?";

        try (Connection con =
                 DriverManager.getConnection(
                     url, username, password);
             PreparedStatement ps =
                 con.prepareStatement(sql)) {

            ps.setInt(1, 1);

            int rows = ps.executeUpdate();

            System.out.println(
                rows + " record(s) deleted"
            );

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Example Output:

1 record(s) deleted

Run this example only when you intend to delete the student with ID 1. A DELETE statement without a WHERE clause can remove every row from the table.

10. Using WHERE with PreparedStatement

Use a parameterized WHERE condition to retrieve specific records.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        String url =
            "jdbc:mysql://localhost:3306/schooldb";

        String username = "root";
        String password = "YOUR_PASSWORD";

        String sql =
            "SELECT id, name, course FROM students WHERE age > ?";

        try (Connection con =
                 DriverManager.getConnection(
                     url, username, password);
             PreparedStatement ps =
                 con.prepareStatement(sql)) {

            ps.setInt(1, 18);

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    System.out.println(
                        rs.getInt("id") + " - " +
                        rs.getString("name") + " - " +
                        rs.getString("course")
                    );
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This query returns students older than 18. The results depend on the database records.

11. Statement vs PreparedStatement

Statement	PreparedStatement
Executes SQL statements.	Executes parameterized SQL statements.
Values may need manual string construction.	Values can be supplied using setter methods.
String concatenation can create SQL injection risks.	Helps prevent SQL injection for bound parameter values.
Suitable for simple fixed SQL.	Usually preferred for queries with input values.

Use PreparedStatement for user-supplied values. Do not build SQL queries by concatenating untrusted input.

12. JDBC Methods You Should Know

Method	Purpose
`getConnection()`	Opens a database connection.
`prepareStatement()`	Creates a PreparedStatement.
`executeQuery()`	Executes a SELECT query and returns a ResultSet.
`executeUpdate()`	Executes an INSERT, UPDATE, DELETE, or suitable SQL statement that does not return a ResultSet.
`execute()`	Executes SQL when the result type may vary.
`next()`	Moves the ResultSet cursor to the next row.
`getString()`	Reads a column as a String.
`getInt()`	Reads a column as an int.
`close()`	Closes a JDBC resource.

Try-with-resources, used in the examples above, automatically closes connections, statements, and result sets when they leave the try block.

13. Java JDBC Assignments

Create a MySQL database named schooldb.
Create a students table with ID, name, age, and course columns.
Write a Java program to connect to MySQL using JDBC.
Insert a new student using PreparedStatement.
Display all student records using ResultSet.
Update a student's course using their ID.
Delete a student using a parameterized DELETE statement.
Display students whose age is greater than 18.
Count the number of students in the table using SQL.
Create a simple student management application with insert, view, update, and delete operations.

Quick Quiz: Java JDBC

Select the correct answer for each question.

1. What does JDBC stand for?

Java Database Connectivity
Java Data Class
Java Development Compiler

2. Which JDBC method is normally used for SELECT queries?

executeUpdate()
executeQuery()
getConnection()

3. Which JDBC class is preferred for parameterized SQL?

ResultSet
DriverManager
PreparedStatement

← Previous Next →

Java Tutorial

Java JDBC (Java Database Connectivity)

Main JDBC Components

Expected Output on Successful Connection:

Expected Output:

Example Output:

Example Output:

Example Output:

Quick Quiz: Java JDBC