Java MySQL – INSERT Row(s) using JDBC – Examples

Java MySQL – INSERT Row(s)

In this tutorial, you will learn how to insert a row into a MySQL table using Java. We’ll cover starting the MySQL server, setting up a connection in Java, writing the Java code to insert a row, and verifying the row in MySQL Workbench.

Prerequisites

• MySQL installed and running
• Java Development Kit (JDK) installed
• MySQL Connector/J (JDBC Driver) downloaded
• MySQL Workbench to verify the row insertion

Step 1: Start MySQL Server

Before running any MySQL commands or connecting to MySQL from Java, make sure the MySQL server is running.

1. Start MySQL Server on Windows

Open the Start menu, search for Services, find MySQL in the list, right-click, and select Start.

2. Start MySQL Server on macOS

Open System Preferences, click on MySQL, and select Start MySQL Server.

3. Start MySQL Server on Linux

Open a terminal and run:

sudo systemctl start mysql

Step 2: Set Up MySQL Connection in Java

To connect to MySQL from Java, you need the MySQL Connector/J library. Add this library to your Java project to establish the connection.

1. Download MySQL Connector/J

Go to the official MySQL Connector/J download page, download the connector, and add the JAR file to your Java project’s classpath.

Click on the Download button.

You may click No thanks, just start my download.
In the downloaded package, there should be a jar file.

2. Add the Connector to Your Java Project

For the sake of convenience, let us copy the jar file to libs folder in the Java Project.

Right-click on your Java project and go to Properties.

Select Java Build Path > Libraries.

Add the MySQL Connector/J JAR file you downloaded.

Click OK.

Click Apply and Close.

Step 3: Create a MySQL Table

Before running the Java code to insert data, create a sample table in MySQL to store the rows. Here, we’ll create a table named students with columns for ID, name, and age.

Open MySQL Workbench, connect to your MySQL server, and run the following SQL:

CREATE DATABASE IF NOT EXISTS school;
USE school;

CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

This SQL script creates a database named school and a students table within it.

Step 4: Write Java Code to Insert a Row

Now, we’ll write a Java program to connect to MySQL and insert a row into the students table.

Java Program to Insert a Row

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class InsertRowExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/school";
        String user = "root";
        String password = "your_password";

        try {
            // Establish a connection to the MySQL database
            Connection connection = DriverManager.getConnection(jdbcUrl, user, password);

            // SQL command to insert a row
            String sql = "INSERT INTO students (name, age) VALUES (?, ?)";

            // Prepare the statement to execute
            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            // Set the values for the placeholders
            preparedStatement.setString(1, "John Doe");
            preparedStatement.setInt(2, 20);

            // Execute the insert command
            int rowsInserted = preparedStatement.executeUpdate();

            // Output the result
            if (rowsInserted > 0) {
                System.out.println("A new row was inserted successfully.");
            }

            // Close resources
            preparedStatement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Explanation of the Code

• jdbcUrl: The URL to connect to the MySQL database. Replace localhost:3306/school with your MySQL server address if different.
• user and password: Your MySQL credentials. Replace your_password with your MySQL password.
• PreparedStatement preparedStatement: Used to execute an INSERT command with placeholders for secure insertion.
• executeUpdate(): Executes the insert command and returns the number of rows affected.

Step 5: Run the Java Program

Compile and run the Java program in your IDE or from the command line:

Command Line

javac InsertRowExample.java
java InsertRowExample

If the program executes successfully, you’ll see the following message in the console:

A new row was inserted successfully.

Step 6: Verify Row Insertion in MySQL Workbench

After running the Java program, open MySQL Workbench to verify the row insertion:

Run the following SQL query in MySQL Workbench to view rows in the students table:

SELECT * FROM students;

You should see a row with the name “John Doe” and age 20, as shown in the following screenshot.

Java Program to Insert Multiple Rows in MySQL Table

Now, we’ll write a Java program that connects to MySQL and inserts multiple rows into the students table using a loop.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class InsertMultipleRowsExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/school";
        String user = "root";
        String password = "your_password";

        // Data to be inserted into the students table
        String[][] students = {
            {"Alice", "19"},
            {"Bob", "20"},
            {"Charlie", "21"},
            {"Daisy", "22"}
        };

        try {
            // Establish a connection to the MySQL database
            Connection connection = DriverManager.getConnection(jdbcUrl, user, password);

            // SQL command to insert a row
            String sql = "INSERT INTO students (name, age) VALUES (?, ?)";

            // Prepare the statement once
            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            // Loop through each row in the students array and add it to the batch
            for (String[] student : students) {
                preparedStatement.setString(1, student[0]);  // Set name
                preparedStatement.setInt(2, Integer.parseInt(student[1]));  // Set age
                preparedStatement.addBatch();  // Add the insert statement to the batch
            }

            // Execute the batch of insert commands
            int[] rowsInserted = preparedStatement.executeBatch();

            // Output the result
            System.out.println(rowsInserted.length + " rows were inserted successfully.");

            // Close resources
            preparedStatement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Explanation of the Code

• students: A two-dimensional array containing the name and age of each student to be inserted.
• addBatch(): Adds each individual insert operation to a batch, so that they can be executed together.
• executeBatch(): Executes all the insert statements in the batch, which is more efficient than executing each insert statement individually.
• rowsInserted: Holds the result of the batch execution, showing the number of rows affected.

This example is an efficient way to insert multiple records as it sends all insert statements to the database in a single batch, reducing the number of network calls and improving performance.

Run the program

Check in MySQL Workbench.

Troubleshooting Common Issues

Here are some common issues you may encounter and their solutions:

• SQLException: Access denied: Ensure that your MySQL username and password are correct and that the user has permission to insert data into the table.
• ClassNotFoundException: com.mysql.cj.jdbc.Driver: Ensure that the MySQL Connector/J JAR file is added to your project’s classpath.
• SQLException: Table ‘school.students’ doesn’t exist: Ensure that the students table has been created in the school database.

Conclusion

Inserting a row into a MySQL table using Java is simple with JDBC. This tutorial provided a step-by-step guide to connect to MySQL, execute an INSERT command, and verify the insertion in MySQL Workbench. With this approach, you can dynamically add data to MySQL tables from your Java applications.