Java and MySQL – Inserting Data

In order to insert data into a MySQL database, the SQL ‘Insert’ statement needs to be used. The following example inserts a record into the same ‘person’ table that was used in the example for selecting data.

Inserting data works in a similar fashion as selecting data using parameters. Firstly, a connection to the database is established, then the query parameters are defined as variables, which are bound in to the following SQL statement. The SQL statement is then executed and a confirmation message is displayed. The execution of the SQL statement is wrapped in a ‘try-catch’ block to catch any errors that may arise.

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

public class DemoMySQL {

    public static void main(String[] args) {

        // Connection variable.
        Connection connect = null;

        try {

            // Connect to database.
            connect = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/Demo?useSSL=false",
                    "DemoUN", "DemoPW");

        } catch (SQLException e) {

            // Confirm unsuccessful connection and stop program execution.
            System.out.println("Database connection unsuccessful.");
            System.exit(0);

        }

        // Query parameters.
        String firstname = "Fiona";
        String lastname = "Jones";
        String title = "Miss";
        Date dob = Date.valueOf("1985-05-19");

        // SQL to insert data into the person table.
        String sqlInsert =
                "INSERT INTO person " +
                "       (firstname, lastname, title, dob) " +
                "VALUES (?, ?, ?, ?) ";

        try {

            // Create statement and bind the parameters.
            PreparedStatement statement = connect.prepareStatement(sqlInsert);
            statement.setString(1, firstname);
            statement.setString(2, lastname);
            statement.setString(3, title);
            statement.setDate(4, dob);

            // Execute query.
            statement.executeUpdate();

            // Confirm successful addition of person information.
            System.out.println("Person information added successfully.");

            // Close the statement and database connection.
            statement.close();
            connect.close();

        } catch (SQLException e) {

            // Confirm error adding person information and quit.
            System.out.println("Error adding person information.");
            System.exit(0);

        }

    }

}

The contents of the ‘person’ table now looks as follows.

id firstname lastname title dob
1 Bob Smith Mr 1980-01-20
2 George Jones Mr 1997-12-15
3 Fred Bloggs Mr 1975-05-07
4 Alan White Mr 1989-03-20
5 Fiona Jones Miss 1985-05-19