Building a Simple Java JDBC Project with MySQL: Displayin...

While learning Java, I wanted to understand how Java connects with a database and retrieves data. Instead of using a random example, I created a small cricket project. The idea was simple: store Indian and Afghanistan players' scores in MySQL and display them through Java.

Step 1: Creating the Database

First, I created a database named cricket.

CREATE DATABASE cricket;
USE cricket;

Then, I created two tables:

CREATE TABLE india (
    player_name VARCHAR(50),
    score INT
);

CREATE TABLE afghanistan (
    player_name VARCHAR(50),
    score INT
);

After that, I inserted player data into both tables.

Step 2: Connecting Java with MySQL

To connect Java with MySQL, I used JDBC.

String url = "jdbc:mysql://localhost:3306/cricket";
String user = "root";
String password = "root";

Connection con = DriverManager.getConnection(url, user, password);

This line creates a connection between the Java application and the MySQL database.

Step 3: Creating a Statement Object

Once the connection is established, we need a Statement object to send SQL queries to the database.

Statement st = con.createStatement();

Think of it as a messenger that sends SQL commands from Java to MySQL.

Step 4: Writing the SQL Query

Since player data was stored in two different tables, I used UNION ALL to combine them.

String query =
"SELECT 'India' AS country, player_name, score FROM india " +
"UNION ALL " +
"SELECT 'Afghanistan' AS country, player_name, score FROM afghanistan";

The AS country part adds a country name because the tables themselves contain only player names and scores.

Step 5: Executing the Query

ResultSet rs = st.executeQuery(query);

The executeQuery() method sends the query to MySQL and returns the result in a ResultSet object.

Step 6: Reading Data from ResultSet

while (rs.next()) {
    System.out.println(
        rs.getString("country") + " " +
        rs.getString("player_name") + " " +
        rs.getInt("score"));
}

rs.next() moves row by row through the result.

Methods like:

rs.getString("country");
rs.getString("player_name");
rs.getInt("score");

retrieve column values from each row.

Formatting the Output

To make the output look neat, I used printf().

System.out.printf("%-15s %-25s %-10s%n",
                  "Country", "Player", "Score");

while (rs.next()) {
    System.out.printf("%-15s %-25s %-10d%n",
            rs.getString("country"),
            rs.getString("player_name"),
            rs.getInt("score"));
}

Output:

Country Player Score

India Rohit Sharma 76
India Shubman Gill 187
India Virat Kohli 154
Afghanistan Rahmanullah Gurbaz 170
Afghanistan Azmatullah Omarzai 140

Filtering Players with Scores Above 100

I also tried displaying only players who scored more than 100.

String query =
"SELECT 'India' AS country, player_name, score FROM india WHERE score > 100 " +
"UNION ALL " +
"SELECT 'Afghanistan' AS country, player_name, score FROM afghanistan WHERE score > 100";

This returned only the top performers.

What I Learned

Through this mini project, I got hands-on experience with:

MySQL database creation
Table creation and inserting data
JDBC connection
Statement and ResultSet
Executing SQL queries
Combining tables using UNION ALL
Reading records with while(rs.next())
Formatting output with printf()
Filtering records using WHERE

Although it was a simple project, it helped me understand how Java applications interact with databases in real-world scenarios. It also gave me a solid foundation for learning advanced JDBC concepts like PreparedStatement, transactions, and CRUD operations.

推荐订阅源

DEV Community

Country Player Score