University Information Query System: Learn Java and MySQL

Introduction

In this project, you will learn how to create a university information query system using Java and MySQL. This project will guide you through the process of setting up a MySQL database, connecting to it using Java, and querying information about students, courses, and instructors.

👀 Preview

University query system preview

🎯 Tasks

In this project, you will learn:

How to start the MySQL service and import a database
How to create a Java project directory and organize your files
How to connect to a MySQL database using Java and the JDBC driver
How to write Java code to query information from the database and display the results

🏆 Achievements

After completing this project, you will be able to:

Understand the basics of connecting a Java application to a MySQL database
Write Java code to execute SQL queries and retrieve data
Develop a simple information query system that can display data from a university database

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:

Start the MySQL service. You can do this by running the following command in your terminal:
```
sudo service mysql start
```
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:

Create a new directory for your Java project:

mkdir /home/labex/project/demo
cd /home/labex/project/demo

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 Querysys.java file to the appropriate directories. Follow the steps below to complete this step:

Move the JDBC driver to the lib directory:

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

Move the Querysys.java file to the src directory:

mv /home/labex/project/Querysys.java /home/labex/project/demo/src

Implement the query system

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

Open the Querysys.java file in a text editor.

Add the following code to the executeQuery() method:

    public static void executeQuery() {
     // Write code here
     Connection connection = null;
     Statement stmt = null;
     ResultSet rs = null;
     try{
         // Write code here
         Class.forName("com.mysql.jdbc.Driver");

         String url = "jdbc:mysql://localhost:3306/edusys";
         String username = "root";
         String password = "";
         connection = DriverManager.getConnection(url, username, password);

         Scanner scanner = new Scanner(System.in);

         System.out.println("Enter the table to query:");
         System.out.println("1. student");
         System.out.println("2. course");
         System.out.println("3. instructor");

         int option = scanner.nextInt();

         String sql = "";
         switch (option) {
             case 1:
                 sql = "SELECT ID, name, dept_name FROM student";
                 break;
             case 2:
                 sql = "SELECT course_id, title, dept_name FROM course";
                 break;
             case 3:
                 sql = "SELECT ID, name, dept_name FROM instructor";
                 break;
             default:
                 System.out.println("Invalid option");
         }

         stmt = connection.createStatement();
         rs = stmt.executeQuery(sql);

         while (rs.next()) {
             String column1 = rs.getString(1);
             String column2 = rs.getString(2);
             String column3 = rs.getString(3);

             System.out.println(column1 + "\t" + column2 + "\t" + column3);
         }
     } catch (ClassNotFoundException e) {
         e.printStackTrace();
     } catch (SQLException e) {
         e.printStackTrace();
     } catch (Exception e) {
         e.printStackTrace();
     } finally{
         try {
             if(rs != null)rs.close();
             if(stmt != null)stmt.close();
             if(connection != null)connection.close();
         } catch (SQLException e) {
             e.printStackTrace();
         } catch (Exception 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.

Save the Querysys.java file.

Compile and run the program

In this step, you will compile and run the Querysys.java program. Follow the steps below to complete this step:

Compile the Querysys.java file:
```
javac -d /home/labex/project/demo/bin/ /home/labex/project/demo/src/Querysys.java
```
This command compiles the Querysys.java file and places the compiled class file in the bin directory.

Run the Querysys program:

java -cp /home/labex/project/demo/bin/:/home/labex/project/demo/lib/mysql-connector-java-8.0.21.jar Querysys

This command runs the Querysys program, using the compiled class file in the bin directory and the JDBC driver in the lib directory.

You should see the following output:

Enter the table to query:
1. student
2. course
3. instructor
2
BIO-101 Intro. to Biology       Biology
BIO-301 Genetics        Biology
BIO-399 Computational Biology   Biology
CS-101  Intro. to Computer Science      Comp. Sci.
CS-190  Game Design     Comp. Sci.
CS-315  Robotics        Comp. Sci.
CS-319  Image Processing        Comp. Sci.
CS-347  Database System Concepts        Comp. Sci.
EE-181  Intro. to Digital Systems       Elec. Eng.
FIN-201 Investment Banking      Finance
HIS-351 World History   History
MU-199  Music Video Production  Music
PHY-101 Physical Principles     Physics

Congratulations! You have successfully implemented the university information query system.

Summary

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