Modifying the Teacher Table Using PreparedStatement

Introduction

In this project, you will learn how to use JDBC and PreparedStatement to delete data from a MySQL database table. The project focuses on demonstrating the benefits of using PreparedStatement over regular SQL statements to improve security and performance.

👀 Preview

🎯 Tasks

In this project, you will learn:

• How to establish a database connection using JDBC
• How to create a PreparedStatement to execute a DELETE query
• How to set parameters in the PreparedStatement
• How to execute the DELETE operation and handle the results

🏆 Achievements

After completing this project, you will be able to:

• Understand the importance of using PreparedStatement for database operations
• Implement a Java program that deletes data from a MySQL database table using PreparedStatement
• Demonstrate the ability to manage database connections and resources in a Java application
• Apply best practices for secure and efficient database interactions

Skills Graph

Start MySQL Service and Import the Database

In this step, you will learn how to start the MySQL service and import the edusys.sql database. Follow the steps below to complete this step:

1. Start the MySQL service. You can do this by running the following command in your terminal:

   sudo service mysql start

2. Import the edusys.sql database into MySQL. You can do this by running the following command in your terminal:

   mysql -u root < /home/labex/project/edusys.sql

This command will import the edusys.sql database into MySQL using the root user.

Create a New Java Project

In this step, you will learn how to create a new Java project directory and move the necessary files into it.

1. Create a new Java project directory:

   mkdir teacher teacher/src teacher/lib teacher/bin

2. Move the JDBC driver package to the teacher/lib directory:

   mv mysql-connector-java-8.0.21.jar ./teacher/lib

3. Move the JDBCDeleteByPreparedStatement.java file to the teacher/src directory:

   mv JDBCDeleteByPreparedStatement.java ./teacher/src

Implementation of the Deletion System

In this step, you will implement the code for the University Information Deletion System in the ' JDBCDeleteByPreparedStatement.java ' file. Follow the following steps to complete this step.

1. Open the JDBCDeleteByPreparedStatement.java file in a text editor.

2. Add the following code to the main() method:

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            String url = "jdbc:mysql://localhost:3306/edusys";
            String username = "root";
            String password = "";
            // Establish the database connection
            connection = DriverManager.getConnection(url, username, password);
   
            // Create and prepare the delete SQL statement
            String deleteSQL = "DELETE FROM instructor WHERE name = ?";  // Using a placeholder for the parameter
            preparedStatement = connection.prepareStatement(deleteSQL);
            preparedStatement.setString(1, "Wu");  // Set the parameter value
   
            // Execute the delete operation
            int affectedRows = preparedStatement.executeUpdate();
            System.out.println("The delete operation affected " + affectedRows + " rows");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // Close the connection and release resources
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

   This code connects to the MySQL database, prompts the user to select a table to query, executes the appropriate SQL query, and prints the results to the console.

3. Save the JDBCDeleteByPreparedStatement.java file.

Run the Application

In this final step, you will run the application.

1. Open a terminal and navigate to the teacher directory:

   cd ~/project/teacher

2. Compile the Java file:

   javac -d bin/ src/JDBCDeleteByPreparedStatement.java

3. Run the application:

   java -cp bin/:lib/mysql-connector-java-8.0.21.jar JDBCDeleteByPreparedStatement

You should see the following output:

The delete operation affected 1 rows

Congratulations! You have successfully completed the project.

Summary

Congratulations! You have completed this project. You can practice more labs in LabEx to improve your skills.