Add Student Information to Table

Introduction

In this project, you will learn how to add student information to a MySQL database using Java programming. This project will guide you through the process of starting the MySQL server, importing a database script, creating a Java program to insert a new student record, and verifying the inserted record.

👀 Preview

🎯 Tasks

In this project, you will learn:

• How to start the MySQL server and log in to the MySQL command-line interface
• How to import a database script to create the necessary tables
• How to create a Java program to insert a new student record into the database using JDBC
• How to compile and run the Java program to insert the new record
• How to verify the inserted record in the MySQL database

🏆 Achievements

After completing this project, you will be able to:

• Understand the basic process of interacting with a MySQL database using Java
• Write Java code to execute SQL statements and insert data into a database
• Verify the correctness of the inserted data by querying the database
• Apply these skills to build more complex database-driven applications

Skills Graph

Start MySQL and Import the Database

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

1. Start the MySQL server:

   sudo /etc/init.d/mysql start

2. Log in to the MySQL command-line interface:

   mysql -uroot

3. Import the edusys.sql script:

   SOURCE ~/project/edusys.sql

This will create the edusys database and the necessary tables, including the student table.

Create the Java Program

In this step, you will create a Java program to insert a new student record into the student table.

1. Create a new Java file named JDBCInsertByStatement.java in the ~/project directory.

2. In the JDBCInsertByStatement.java file, add the following code:

   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.PreparedStatement;
   import java.sql.SQLException;
   
   public class JDBCInsertByStatement {
       static String url = "jdbc:mysql://localhost:3306/edusys?useSSL=false";
       static String user = "root";
       static String password = "";
       static Connection connection = null;
   
       public static void main(String[] args) throws SQLException {
           connection = DriverManager.getConnection(url, user, password);
           PreparedStatement statement = connection.prepareStatement(
                   "insert into student(id,name,dept_name,tot_cred) values (?,?,?,?)");
   
           statement.setString(1, "33123");
           statement.setString(2, "Rong");
           statement.setString(3, "Music");
           statement.setInt(4, 67);
           statement.execute();
   
           statement.close();
           connection.close();
       }
   }

   This code establishes a connection to the MySQL database, creates a PreparedStatement to insert a new student record, and then executes the statement.

Compile and Run the Program

In this step, you will compile and run the Java program to insert the new student record into the student table.

1. Compile the Java program:

   javac JDBCInsertByStatement.java

2. Run the Java program:

   java -cp .:lib/mysql-connector-java-8.0.21.jar JDBCInsertByStatement

   This command runs the JDBCInsertByStatement class, which will insert the new student record into the student table.

Verify the Inserted Record

In this step, you will verify that the new student record has been successfully inserted into the student table.

1. Log in to the MySQL command-line interface:

   mysql -uroot

2. Select the new student record from the student table:

   use edusys;
   SELECT * FROM student WHERE id = 33123;

   This should display the new student record with the following information:

   +-------+------+-----------+----------+
   | ID    | name | dept_name | tot_cred |
   +-------+------+-----------+----------+
   | 33123 | Rong | Music     |       67 |
   +-------+------+-----------+----------+

Congratulations! You have successfully added a new student record to the student table using the Java program.

Summary

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