Practice Programs on JDBC with MySQL -

Program 1: Establishing a Connection with MySQL

This program establishes a connection between a Java application and a MySQL database using JDBC.

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

public class MySQLConnectionExample {

    public static void main(String[] args) {

        String url =
            "jdbc:mysql://localhost:3306/jdbc_demo";

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

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

            System.out.println(
                "Connected to MySQL Database successfully.");

            con.close();

        } catch (SQLException e) {

            System.out.println(
                "Connection failed: " +
                e.getMessage());
        }
    }
}

Execution and Output:

PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" MySQLConnectionExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" MySQLConnectionExample
Connected to MySQL Database successfully.Code language: CSS (css)

Program 2: Creating an Employee Table

This program creates an employee table in the MySQL database using the JDBC Statement interface.

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

public class CreateEmployeeTable {

    public static void main(String[] args) {

        String url =
            "jdbc:mysql://localhost:3306/jdbc_demo";

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

        String sql =
            "CREATE TABLE employee (" +
            "employee_id INT PRIMARY KEY, " +
            "employee_name VARCHAR(100), " +
            "department VARCHAR(50), " +
            "salary DOUBLE)";

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

            Statement stmt =
                con.createStatement();

            stmt.executeUpdate(sql);

            System.out.println(
                "Employee table created successfully.");

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

        } catch (SQLException e) {

            System.out.println(
                "Error: " + e.getMessage());
        }
    }
}

Execution and Output:

PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" CreateEmployeeTable.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" CreateEmployeeTable
Employee table created successfully.Code language: CSS (css)

Program 3: Inserting Records Using PreparedStatement

This program inserts an employee record into MySQL using a parameterized SQL statement with PreparedStatement.

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

public class MySQLPreparedStatementExample {

    public static void main(String[] args) {

        String url =
            "jdbc:mysql://localhost:3306/jdbc_demo";

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

        String sql =
            "INSERT INTO employee " +
            "(employee_id, employee_name, department, salary) " +
            "VALUES (?, ?, ?, ?)";

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

            PreparedStatement ps =
                con.prepareStatement(sql);

            ps.setInt(1, 101);
            ps.setString(2, "Ravi");
            ps.setString(3, "IT");
            ps.setDouble(4, 50000);

            ps.executeUpdate();

            System.out.println(
                "Employee inserted successfully.");

            ps.close();
            con.close();

        } catch (SQLException e) {

            System.out.println(
                "Error: " + e.getMessage());
        }
    }
}

Execution and Output:

PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" MySQLPreparedStatementExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" MySQLPreparedStatementExample
Employee inserted successfully.Code language: CSS (css)

Program 4: Retrieving Records Using ResultSet

This program retrieves employee records from MySQL using Statement and ResultSet.

import java.sql.*;

public class MySQLResultSetExample {

    public static void main(String[] args) {

        String url =
            "jdbc:mysql://localhost:3306/jdbc_demo";

        try {
            Connection con =
                DriverManager.getConnection(
                    url, "root", "password");

            Statement stmt =
                con.createStatement();

            ResultSet rs =
                stmt.executeQuery(
                    "SELECT * FROM employee");

            System.out.println(
                "Employee Details");
            System.out.println(
                "-------------------------");

            while (rs.next()) {

                System.out.println(
                    rs.getInt("employee_id") + "  " +
                    rs.getString("employee_name") + "  " +
                    rs.getString("department") + "  " +
                    rs.getDouble("salary"));
            }

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

        } catch (SQLException e) {

            e.printStackTrace();
        }
    }
}

Execution and Output:

PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" MySQLResultSetExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" MySQLResultSetExample
Employee Details
-------------------------
101  Ravi  IT  50000.0Code language: CSS (css)

Program 5: Updating Records Using PreparedStatement

This program updates the salary of an employee in MySQL using PreparedStatement.

import java.sql.*;

public class MySQLUpdateExample {

    public static void main(String[] args) {

        String url =
            "jdbc:mysql://localhost:3306/jdbc_demo";

        String sql =
            "UPDATE employee " +
            "SET salary = ? " +
            "WHERE employee_id = ?";

        try {
            Connection con =
                DriverManager.getConnection(
                    url, "root", "password");

            PreparedStatement ps =
                con.prepareStatement(sql);

            ps.setDouble(1, 60000);
            ps.setInt(2, 101);

            int rows =
                ps.executeUpdate();

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

            ps.close();
            con.close();

        } catch (SQLException e) {

            e.printStackTrace();
        }
    }
}

Execution and Output:

PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" MySQLUpdateExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" MySQLUpdateExample
1 record updated.Code language: CSS (css)

Program 6: Deleting Records Using PreparedStatement

This program deletes an employee record from MySQL using a parameterized SQL statement.

import java.sql.*;

public class MySQLDeleteExample {

    public static void main(String[] args) {

        String url =
            "jdbc:mysql://localhost:3306/jdbc_demo";

        String sql =
            "DELETE FROM employee " +
            "WHERE employee_id = ?";

        try {
            Connection con =
                DriverManager.getConnection(
                    url, "root", "password");

            PreparedStatement ps =
                con.prepareStatement(sql);

            ps.setInt(1, 101);

            int rows =
                ps.executeUpdate();

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

            ps.close();
            con.close();

        } catch (SQLException e) {

            e.printStackTrace();
        }
    }
}

Execution and Output:

PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" MySQLDeleteExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" MySQLDeleteExample
1 record deleted.Code language: CSS (css)

Program 7: Performing Batch Insert Using PreparedStatement

This program inserts multiple employee records using JDBC batch processing. addBatch() adds each operation to the batch, and executeBatch() executes all operations together.

import java.sql.*;

public class MySQLBatchExample {

    public static void main(String[] args) {

        String url =
            "jdbc:mysql://localhost:3306/jdbc_demo";

        String sql =
            "INSERT INTO employee " +
            "(employee_id, employee_name, department, salary) " +
            "VALUES (?, ?, ?, ?)";

        try {
            Connection con =
                DriverManager.getConnection(
                    url, "root", "password");

            PreparedStatement ps =
                con.prepareStatement(sql);

            ps.setInt(1, 201);
            ps.setString(2, "Arun");
            ps.setString(3, "IT");
            ps.setDouble(4, 45000);
            ps.addBatch();

            ps.setInt(1, 202);
            ps.setString(2, "Priya");
            ps.setString(3, "HR");
            ps.setDouble(4, 42000);
            ps.addBatch();

            ps.setInt(1, 203);
            ps.setString(2, "Kiran");
            ps.setString(3, "Finance");
            ps.setDouble(4, 48000);
            ps.addBatch();

            int[] result =
                ps.executeBatch();

            System.out.println(
                result.length +
                " records processed in batch.");

            ps.close();
            con.close();

        } catch (SQLException e) {

            e.printStackTrace();
        }
    }
}

Execution and Output:

PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" MySQLBatchExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" MySQLBatchExample
3 records processed in batch.Code language: CSS (css)

Program 8: Using Transactions with commit() and rollback()

This program performs two salary updates inside a single MySQL transaction. If both operations are successful, commit() permanently saves the changes.

import java.sql.*;

public class MySQLTransactionExample {

    public static void main(String[] args) {

        String url =
            "jdbc:mysql://localhost:3306/jdbc_demo";

        try {
            Connection con =
                DriverManager.getConnection(
                    url, "root", "password");

            // Disable auto-commit
            con.setAutoCommit(false);

            PreparedStatement ps =
                con.prepareStatement(
                    "UPDATE employee " +
                    "SET salary = salary + ? " +
                    "WHERE employee_id = ?");

            // First update
            ps.setDouble(1, 5000);
            ps.setInt(2, 201);
            ps.executeUpdate();

            // Second update
            ps.setDouble(1, 3000);
            ps.setInt(2, 202);
            ps.executeUpdate();

            // Commit transaction
            con.commit();

            System.out.println(
                "Transaction committed successfully.");

            ps.close();
            con.close();

        } catch (SQLException e) {

            e.printStackTrace();
        }
    }
}

Execution and Output:

PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" MySQLTransactionExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" MySQLTransactionExample
Transaction committed successfully.Code language: CSS (css)

Program 9: Using Savepoint in MySQL Transaction

This program demonstrates how to create a savepoint during a MySQL transaction and roll back only the operation performed after the savepoint.

import java.sql.*;

public class MySQLSavepointExample {

    public static void main(String[] args) {

        String url =
            "jdbc:mysql://localhost:3306/jdbc_demo";

        try {
            Connection con =
                DriverManager.getConnection(
                    url, "root", "password");

            con.setAutoCommit(false);

            Statement stmt =
                con.createStatement();

            // First update
            stmt.executeUpdate(
                "UPDATE employee " +
                "SET salary = salary + 2000 " +
                "WHERE employee_id = 201");

            System.out.println(
                "First update completed.");

            // Create savepoint
            Savepoint savepoint =
                con.setSavepoint("AfterFirstUpdate");

            System.out.println(
                "Savepoint created.");

            // Second update
            stmt.executeUpdate(
                "UPDATE employee " +
                "SET salary = salary + 5000 " +
                "WHERE employee_id = 202");

            System.out.println(
                "Second update completed.");

            // Roll back to savepoint
            con.rollback(savepoint);

            System.out.println(
                "Rolled back to savepoint.");

            // Commit remaining changes
            con.commit();

            System.out.println(
                "Transaction committed.");

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

        } catch (SQLException e) {

            e.printStackTrace();
        }
    }
}

Execution and Output:

PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" MySQLSavepointExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" MySQLSavepointExample
First update completed.
Savepoint created.
Second update completed.
Rolled back to savepoint.
Transaction committed.Code language: CSS (css)

Program 10: Calling a MySQL Stored Procedure Using CallableStatement

This program demonstrates how to create and execute a MySQL stored procedure using JDBC CallableStatement.

Create the Stored Procedure

Run this SQL in MySQL:

USE jdbc_demo;

DELIMITER //

CREATE PROCEDURE get_employee_name(
    IN p_employee_id INT,
    OUT p_employee_name VARCHAR(100)
)
BEGIN
    SELECT employee_name
    INTO p_employee_name
    FROM employee
    WHERE employee_id = p_employee_id;
END //

DELIMITER ;Code language: PHP (php)

import java.sql.*;

public class MySQLCallableStatementExample {

    public static void main(String[] args) {

        String url =
            "jdbc:mysql://localhost:3306/jdbc_demo";

        try {
            Connection con =
                DriverManager.getConnection(
                    url, "root", "password");

            CallableStatement cs =
                con.prepareCall(
                    "{call get_employee_name(?, ?)}");

            // IN parameter
            cs.setInt(1, 201);

            // OUT parameter
            cs.registerOutParameter(
                2, Types.VARCHAR);

            // Execute procedure
            cs.execute();

            // Retrieve OUT parameter
            String employeeName =
                cs.getString(2);

            System.out.println(
                "Employee Name: " +
                employeeName);

            cs.close();
            con.close();

        } catch (SQLException e) {

            e.printStackTrace();
        }
    }
}

Execution and Output:

PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" MySQLCallableStatementExample.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" MySQLCallableStatementExample
Employee Name: ArunCode language: CSS (css)

java.sql package

Introduction to java.sql package

JDBC Basics

Connection and DataSource(Demonstration with mysql database)

Statement and PreparedStatement(Demonstration with mysql database)

ResultSet(Demonstration with mysql database)

Batch Processing(Demonstration with mysql database)

Transactions(Demonstration with mysql database)

CallableStatement(Demonstration with mysql database)

Metadata(Demonstration with mysql database)

RowSet(Demonstration with mysql database)

Savepoint(Demonstration with mysql database)

Handling SQL Exceptions

Connecting JDBC Application with Different Databases

Oracle Database

MySQL Database

PostgresSQL Database

SQLite Database

SQLServer Database

H2 Database

MongoDB Database

Practice Programs on JDBC with MySQL

Program 1: Establishing a Connection with MySQL

Program 2: Creating an Employee Table

Program 3: Inserting Records Using PreparedStatement

Program 4: Retrieving Records Using ResultSet

Program 5: Updating Records Using PreparedStatement

Program 6: Deleting Records Using PreparedStatement

Program 7: Performing Batch Insert Using PreparedStatement

Program 8: Using Transactions with commit() and rollback()

Program 9: Using Savepoint in MySQL Transaction

Program 10: Calling a MySQL Stored Procedure Using CallableStatement

java.sql package

Introduction to java.sql package

JDBC Basics

Connection and DataSource(Demonstration with mysql database)

Statement and PreparedStatement(Demonstration with mysql database)

ResultSet(Demonstration with mysql database)

Batch Processing(Demonstration with mysql database)

Transactions(Demonstration with mysql database)

CallableStatement(Demonstration with mysql database)

Metadata(Demonstration with mysql database)

RowSet(Demonstration with mysql database)

Savepoint(Demonstration with mysql database)

Handling SQL Exceptions

Connecting JDBC Application with Different Databases

Oracle Database

MySQL Database

PostgresSQL Database

SQLite Database

SQLServer Database

H2 Database

MongoDB Database

Program 1: Establishing a Connection with MySQL

Program 2: Creating an Employee Table

Program 3: Inserting Records Using PreparedStatement

Program 4: Retrieving Records Using ResultSet

Program 5: Updating Records Using PreparedStatement

Program 6: Deleting Records Using PreparedStatement

Program 7: Performing Batch Insert Using PreparedStatement

Program 8: Using Transactions with commit() and rollback()

Program 9: Using Savepoint in MySQL Transaction

Program 10: Calling a MySQL Stored Procedure Using CallableStatement

Related Posts