Statement for Querying Classroom Table

Introduction

In this project, you will learn how to use JDBC (Java Database Connectivity) to query data from a MySQL database using the Statement object. You will also learn how to encapsulate the result set using ResultSet.

👀 Preview

🎯 Tasks

In this project, you will learn:

• How to start the MySQL service and import data into the database
• How to create a new Java project directory and move the necessary files
• How to implement the JDBC query using the Statement object
• How to compile and run the Java application

🏆 Achievements

After completing this project, you will be able to:

• Establish a connection to a MySQL database using JDBC
• Create a Statement object to execute SQL queries
• Encapsulate the result set using ResultSet
• Retrieve and display data from a MySQL database

Start MySQL Service and Import Data

In this step, you will learn how to start the MySQL service and import the edusys.sql script into the MySQL database.

1. Start the MySQL service:

   sudo service mysql start
   

2. Import the edusys.sql script into the MySQL database:

   mysql -u root < ~/project/edusys.sql
   

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 classroom classroom/src classroom/lib classroom/bin
   

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

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

3. Move the JDBCQueryByStatement.java file to the classroom/src directory:

   mv JDBCQueryByStatement.java ./classroom/src
   

Implement the JDBC Query using Statement

In this step, you will learn how to implement the JDBC query using the Statement object and encapsulate the result set using ResultSet.

1. Open the JDBCQueryByStatement.java file in your preferred code 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);
   
       // Create Statement object
       Statement statement = connection.createStatement();
   
       // Execute the query
       String sql = "SELECT * FROM classroom";
       ResultSet resultSet = statement.executeQuery(sql);
   
       // Loop through the result set
       while (resultSet.next()) {
           String building = resultSet.getString("building");
           String roomNumber = resultSet.getString("room_number");
           int capacity = resultSet.getInt("capacity");
   
           System.out.println(building + "\t" + roomNumber + "\t" + capacity);
       }
   
       // Close resources
       resultSet.close();
       statement.close();
       connection.close();
   } catch (SQLException e) {
       e.printStackTrace();
   }
   

   This code establishes a connection to the MySQL database, creates a Statement object, executes the query to retrieve all data from the classroom table, and then loops through the result set to print the data.

Compile and Run the Application

In this step, you will learn how to compile and run the Java application.

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

   cd ~/project/classroom
   

2. Compile the Java file:

   javac -d bin/ src/JDBCQueryByStatement.java
   

3. Run the application:

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

   This will execute the JDBCQueryByStatement class and display the data from the classroom table.

4. Example:

   You can refer to the following results:

   

Congratulations! You have successfully completed the project. You have learned how to use the Statement object to query data from a MySQL database and encapsulate the result set using ResultSet.

Summary

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