Inserting Data into Table using JDBC PreparedStatement

Summary: in this tutorial, you will learn how to use a PreparedStatement object to insert data into a table.

This tutorial picks up where the Updating Data in MySQL Using JDBC PreparedStatement tutorial left off.

The following defines a new method called add() in the Candidate class that inserts a new row into the candidates table:

import java.sql.SQLException;
import java.sql.Date;
import java.sql.Statement;

public class Candidate {
    public static int add(String firstName,String lastName,Date dob, String email, String phone) {
        int id = 0;

        String sql = "INSERT INTO candidates(first_name,last_name,dob,phone,email) VALUES(?,?,?,?,?)";

        try (var conn = MySQLConnection.connect();
             var stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

            // set parameters for statement
            stmt.setString(1, firstName);
            stmt.setString(2, lastName);
            stmt.setDate(3, dob);
            stmt.setString(4, phone);
            stmt.setString(5, email);

            int rowAffected = stmt.executeUpdate();
            if(rowAffected == 1)
            {
                // get candidate id
                var rs = stmt.getGeneratedKeys();
                if(rs.next()){
                    id = rs.getInt(1);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return id;
    }
}Code language: Java (java)

How it works.

First, import the SQLException, Date, and Statement classes from the java.sql package:

import java.sql.SQLException;
import java.sql.Date;
import java.sql.Statement;Code language: Java (java)

Second, define the add() method that accepts six parameters that correspond to the columns of the candidates table:

public static int add(String firstName,String lastName,Date dob, String email, String phone) {Code language: Java (java)

Third, declare the variable id and initialize its value to zero:

int id = 0;Code language: Java (java)

Fourth, define an INSERT statement that inserts a new row into the candidates table:

String sql = "INSERT INTO candidates(first_name,last_name,dob,phone,email) VALUES(?,?,?,?,?)";Code language: Java (java)

Fifth, establish a connection by connecting to the database and prepare the SQL statement for execution:

try (var conn = MySQLConnection.connect();
     var stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {Code language: Java (java)

Sixth, set the parameters:

stmt.setString(1, firstName);
stmt.setString(2, lastName);
stmt.setDate(3, dob);
stmt.setString(4, phone);
stmt.setString(5, email);Code language: Java (java)

Seventh, execute the statement:

int rowAffected = stmt.executeUpdate();Code language: Java (java)

Eighth, get the last inserted id:

if(rowAffected == 1) {
    // get candidate id
    var rs = stmt.getGeneratedKeys();
    if(rs.next()) {
        id = rs.getInt(1);
    }
}Code language: Java (java)

Ninth, return the of the inserted candidate:

return id;Code language: Java (java)

The following shows how to call the add() method in the Main program to insert a new row into the candidates table:

import java.sql.Date;

public class Main {
    public static void main(String[] args){
        int id = Candidate.add("Jane", "Smith", Date.valueOf("1982-12-31"),"[email protected]","(408)-111-2222");
        System.out.println("The inserted id is " + id);
    }
}Code language: JavaScript (javascript)

Output:

The inserted id is 134

Summary

• Use the PreparedStatement object to insert a new row into a table and obtain the inserted ID.