Soopro Pathshala

Java MySQL Database Connectivity

100%

Java MySQL Database Connectivity

MySQL is a relational database management system used to store and manage data. Java applications can connect to MySQL using JDBC (Java Database Connectivity).

In this tutorial, you will learn how to:

Create a MySQL database and table.
Connect Java to MySQL.
Insert, display, update, and delete records.
Search for records using SQL conditions.
Use PreparedStatement to execute parameterized queries.

1. What Is MySQL?

MySQL stores data in tables consisting of rows and columns. It uses SQL (Structured Query Language) to create, retrieve, update, and delete data.

Term	Meaning
Database	A collection of related data.
Table	Data organized into rows and columns.
Row	A single record in a table.
Column	A field that stores a particular type of information.
Primary Key	A column or group of columns that uniquely identifies a row.

2. Installing the Required Software

To connect Java to MySQL, you need:

JDK installed on your computer.
A running MySQL server.
A Java IDE such as IntelliJ IDEA, Eclipse, or NetBeans.
The MySQL Connector/J JDBC driver.

If you use Maven, add the MySQL Connector/J dependency to your project's pom.xml.

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

Replace YOUR_COMPATIBLE_VERSION with a released Connector/J version compatible with your project. The driver must be available on the application's classpath.

3. Creating a Database

Open MySQL Workbench or the MySQL command-line client and execute the following SQL statement.

CREATE DATABASE schooldb;

Select the database:

USE schooldb;

The schooldb database will be used in the remaining examples.

4. Creating a Students Table

Create a table to store student information.

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

Insert a few sample records:

INSERT INTO students (name, age, course)
VALUES
('Rahul', 20, 'Java'),
('Amit', 21, 'Python'),
('Priya', 19, 'Web Development');

Display all records:

SELECT * FROM students;

Example Result:

ID	Name	Age	Course
1	Rahul	20	Java
2	Amit	21	Python
3	Priya	19	Web Development

5. Connecting Java to MySQL

Use DriverManager.getConnection() to connect your Java application to the MySQL database.

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(
                "MySQL connected successfully!"
            );

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

Expected Output:

MySQL connected successfully!

Replace YOUR_PASSWORD with your actual MySQL password. Make sure MySQL is running and the JDBC driver is installed. Do not publish database credentials in your source code.

6. Inserting Data from Java

Use PreparedStatement to insert records safely with parameterized SQL.

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, "Neha");
            ps.setInt(2, 22);
            ps.setString(3, "Java");

            int rows = ps.executeUpdate();

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

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

Expected Output:

1 student inserted successfully

The question marks are placeholders. The setString() and setInt() methods supply the values.

7. Displaying Records from MySQL

Use executeQuery() to retrieve records with a SELECT statement.

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(
                    rs.getInt("id") + " | " +
                    rs.getString("name") + " | " +
                    rs.getInt("age") + " | " +
                    rs.getString("course")
                );
            }

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

Example Output:

1 | Rahul | 20 | Java
2 | Amit | 21 | Python
3 | Priya | 19 | Web Development

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

8. Searching for a Student

Use a WHERE condition with a parameter to find a particular student.

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 WHERE id = ?";

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

            ps.setInt(1, 1);

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    System.out.println(
                        "Name: " + rs.getString("name")
                    );

                    System.out.println(
                        "Course: " + rs.getString("course")
                    );
                } else {
                    System.out.println("Student not found");
                }
            }

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

Example Output:

Name: Rahul
Course: Java

9. Updating Student Information

Use the SQL UPDATE statement to modify an existing record.

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, "Advanced Java");
            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 changes the course of the student whose ID is 1.

10. Deleting a Student Record

Use the DELETE statement with a WHERE condition to remove a specific record.

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, 3);

            int rows = ps.executeUpdate();

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

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

Example Output:

1 record(s) deleted

This example deletes the record with ID 3. Run it only when you intend to delete that record.

11. Counting Student Records

SQL's COUNT() function returns the number of records matching a query.

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 COUNT(*) AS total FROM students";

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

            if (rs.next()) {
                System.out.println(
                    "Total Students: " + rs.getInt("total")
                );
            }

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

Example Output:

Total Students: 3

The number changes as records are inserted or deleted.

12. Important SQL and JDBC Methods

Method or Query	Purpose
`getConnection()`	Connects Java to the database.
`executeQuery()`	Executes a SELECT query.
`executeUpdate()`	Executes INSERT, UPDATE, DELETE, and other suitable statements that do not return a ResultSet.
`rs.next()`	Moves to the next row in the result set.
`INSERT`	Adds new records.
`SELECT`	Retrieves records.
`UPDATE`	Changes existing records.
`DELETE`	Removes records.

Prefer PreparedStatement for queries that use user input. It helps prevent SQL injection when values are bound through parameters.

13. Java MySQL Assignments

Create a database named schooldb.
Create a table named students with ID, name, age, and course columns.
Connect Java to MySQL using JDBC.
Insert five student records using PreparedStatement.
Display all students using a SELECT query.
Search for a student using their ID.
Update a student's course.
Delete a student by ID.
Count the total number of students.
Build a menu-driven student management application with insert, view, search, update, and delete operations.

Quick Quiz: Java MySQL

Select the correct answer for each question.

1. Which JDBC method is commonly used to establish a database connection?

DriverManager.getConnection()
System.out.println()
Scanner.nextInt()

2. Which SQL command retrieves records from a table?

DELETE
SELECT
UPDATE

3. Which class is preferred for parameterized SQL queries?

ResultSet
DriverManager
PreparedStatement

← Previous Next →

Java Tutorial

Java MySQL Database Connectivity

Example Result:

Expected Output:

Expected Output:

Example Output:

Example Output:

Example Output:

Example Output:

Example Output:

Quick Quiz: Java MySQL