Query Teacher Salary Using PreparedStatement

Introduction

In this project, you will learn how to use JDBC (Java Database Connectivity) to query a MySQL database using a PreparedStatement. The goal is to retrieve the names and salaries of teachers whose salary is higher than 6000 from the instructor table in the edusys database.

👀 Preview

🎯 Tasks

In this project, you will learn:

• How to establish a database connection using JDBC
• How to create a PreparedStatement with a parameter placeholder
• How to execute the query and process the result set
• How to handle exceptions and close resources properly

🏆 Achievements

After completing this project, you will be able to:

• Understand the basics of JDBC and how to use it to interact with a MySQL database
• Write Java code that uses PreparedStatement to execute a SQL query with a parameter
• Retrieve and process data from a database using JDBC
• Implement error handling and resource management in a JDBC application

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 Java project directory

In this step, you will learn how to create a Java project directory. Follow the steps below to complete this step:

1. Create a new directory for your Java project:

   mkdir /home/labex/project/salary
   cd /home/labex/project/salary
   

2. Create the necessary subdirectories for your project:

   mkdir src lib bin
   

   • src directory will contain your Java source code files.
   • lib directory will contain any external libraries your project uses.
   • bin directory will contain the compiled Java class files.

Move the JDBC driver and Java file

In this step, you will learn how to move the JDBC driver and the JDBCQueryByPreparedStatement.java file to the appropriate directories. Follow the steps below to complete this step:

1. Move the JDBC driver to the lib directory:

   mv /home/labex/project/mysql-connector-java-8.0.21.jar /home/labex/project/salary/lib
   

2. Move the JDBCQueryByPreparedStatement.java file to the src directory:

   mv /home/labex/project/JDBCQueryByPreparedStatement.java /home/labex/project/salary/src
   

Implement the query system

In this step, you will implement the code for the university information query system in the JDBCQueryByPreparedStatement.java file. Follow the steps below to complete this step:

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

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

        try {
            String url = "jdbc:mysql://localhost:3306/edusys";
            String username = "root";
            String password = "";
            // Establish database connection
            Connection connection = DriverManager.getConnection(url, username, password);
   
            // Prepare SQL statement with parameter placeholder
            String sql = "SELECT name, salary FROM instructor WHERE salary > ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
   
            // Set the parameter value
            preparedStatement.setDouble(1, 6000);
   
            // Execute the query
            ResultSet resultSet = preparedStatement.executeQuery();
   
            // Loop through the result set
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                double salary = resultSet.getDouble("salary");
                System.out.println(name + " " + salary);
            }
   
            // Close resources
            resultSet.close();
            preparedStatement.close();
            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 JDBCQueryByPreparedStatement.java file.

Run the Application

In this final step, you will run the application.

1. Compile the Java file:

javac -d bin/ src/JDBCQueryByPreparedStatement.java

2. Run the application:

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

You should see the following output:

Srinivasan 65000.0
Wu 90000.0
Mozart 40000.0
Einstein 95000.0
El Said 60000.0
Gold 87000.0
Katz 75000.0
Califieri 62000.0
Singh 80000.0
Crick 72000.0
Brandt 92000.0
Kim 80000.0

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.