JDBC Insert

Summary: in this tutorial, you will learn how to insert data into a table from a Java program using JDBC.

Inserting data into a table using JDBC

Generally, here are the steps for inserting data into a table using JDBC:

• First, open a new database connection.
• Second, create a new PreparedStatement object that accepts an INSERT statement.
• Third, set the parameters for the statement by calling the set* methods of the PreparedStatement object.
• Call the executeUpdate() to execute the statement to insert a row into a table.
• Finally, close the database connection.

The following program inserts a new row into the products table:

import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {

        var name = "T-Shirt";
        var price = 10.99;
        var sql = "INSERT INTO products (name, price) VALUES (?, ?)";

        try (var connection = DBConnection.connect();
             var pstmt = connection.prepareStatement(sql)) {

            pstmt.setString(1, name);
            pstmt.setDouble(2, price);

            int rowsInserted  = pstmt.executeUpdate();

            var message = rowsInserted > 0
                    ? "Row inserted successfully."
                    : "Failed to insert row.";

            System.out.println(message);

        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }
}Code language: Java (java)

How it works.

First, initialize variables for holding the product information:

var name = "T-Shirt";
var price = 10.99;Code language: SQL (Structured Query Language) (sql)

Second, construct an INSERT statement:

var sql = "INSERT INTO products (name, price) VALUES (?, ?)";Code language: SQL (Structured Query Language) (sql)

In this syntax, the question marks (?) are placeholders that correspond to the name and price. When you execute the statement, you need to provide both name and price.

Third, create a new connection to the database:

var connection = DBConnection.connect()Code language: SQL (Structured Query Language) (sql)

Fourth, create a PreparedStatement object with the input SQL statement

var pstmt = connection.prepareStatement(sql)Code language: SQL (Structured Query Language) (sql)

Fifth, set the values for the name and price by calling the setString() and setDouble() methods:

pstmt.setString(1, name);
pstmt.setDouble(2, price);Code language: SQL (Structured Query Language) (sql)

Sixth, execute the INSERT statement and return a number of inserted rows:

int rowsInserted  = pstmt.executeUpdate();Code language: SQL (Structured Query Language) (sql)

Seventh, display the inserted rows:

var message = rowsInserted > 0
                    ? "Row inserted successfully."
                    : "Failed to insert row.";Code language: SQL (Structured Query Language) (sql)

Finally, show the error message exception occurs in the catch block:

System.err.println(e.getMessage());Code language: SQL (Structured Query Language) (sql)

If you run a program and no exception occurs, you’ll see the following message:

Row inserted successfully.Code language: SQL (Structured Query Language) (sql)

Verify the insert

First, connect to the MariaDB server using the bob account:

mysql -u bob -pCode language: plaintext (plaintext)

It’ll prompt you for a password for the bob user account. Please enter it and press the Enter key to connect.

Second, change the current database to sales:

use sales;Code language: SQL (Structured Query Language) (sql)

Third, retrieve data from the products table:

select * from products;Code language: SQL (Structured Query Language) (sql)

Output:

+----+---------+-------+
| id | name    | price |
+----+---------+-------+
|  1 | T-Shirt | 10.99 |
+----+---------+-------+
1 row in set (0.00 sec)Code language: plaintext (plaintext)

Finally, quit the mysql program:

quitCode language: SQL (Structured Query Language) (sql)

Summary

• Use the PreparedStatement to execute an INSERT statement to insert a row into a table.