DEV Community

Cover image for Building a Simple Java JDBC Project with MySQL: Displaying Cricket Players and Scores
Jayashree
Jayashree

Posted on

Building a Simple Java JDBC Project with MySQL: Displaying Cricket Players and Scores

#database #java #sql #tutorial

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;
Enter fullscreen mode Exit fullscreen mode

Then, I created two tables:

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

CREATE TABLE afghanistan (
    player_name VARCHAR(50),
    score INT
);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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";
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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"));
}
Enter fullscreen mode Exit fullscreen mode

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

Methods like:

rs.getString("country");
rs.getString("player_name");
rs.getInt("score");
Enter fullscreen mode Exit fullscreen mode

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"));
}
Enter fullscreen mode Exit fullscreen mode

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";
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)