PostgreSQL JDBC: Insert Data into a Table

Summary: in this tutorial, you will learn how to insert data into a table in the PostgreSQL database using JDBC.

Inserting one row into a table

We’ll use theproducts table from thesales database for the demonstration.

Defining a Product class

The following createsProduct.java file and defines theProduct class with three propertiesid,name, andprice. These properties correspond to the columns in theproducts table:

public class Product {    private int id;    private String name;    private double price;    public Product(int id, String name, double price){        this(name,price);        this.id = id;    }    public Product(String name, double price){        this.name = name;        this.price = price;    }    public String getName(){        return name;    }    public void setName(String name){        this.name = name;    }    public double getPrice(){        return price;    }    public void setPrice(double price){        this.price = price;    }    public int getId(){ return id;  }    public void setId(int id){ this.id = id; }    @Override    public String toString(){        return "Product{" +                "id=" + id +                ", name='" + name + '\'' +                ", price=" + price +                '}';    }}

Defining a ProductDB class

The following creates a new file calledProductDB.java and defines theProductDB class:

import java.sql.SQLException;import java.sql.Statement;import java.util.List;public class ProductDB {    public static int add(Product product) {        var sql= "INSERT INTO products(name, price) "                + "VALUES(?,?)";        try (var conn=  DB.connect();             var pstmt= conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {            // bind the values            pstmt.setString(1, product.getName());            pstmt.setDouble(2, product.getPrice());            // execute the INSERT statement and get the inserted id            int insertedRow= pstmt.executeUpdate();            if (insertedRow> 0) {                var rs= pstmt.getGeneratedKeys();                if (rs.next()) {                    return rs.getInt(1);                }            }        }catch (SQLException e) {            e.printStackTrace();        }        return -1;    }}

Theadd() method inserts a new row into theproducts table.

How it works.

First, initialize anINSERT statement:

var sql= "INSERT INTO products(name,price) "          + "VALUES(?,?)";

The question mark (?) is a placeholder that will be replaced by the actual values later.

Second, open a connection to the sales database on the local PostgreSQL server using theDB class:

var conn=  DB.connect();

Third, create aPreparedStatement object by calling thepreparedStatement() method:

var pstmt= conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)

TheStatement.RETURN_GENERATED_KEYS argument instructs thePreparedStatement object to return the generated id key of the product.

Fourth, bind the values to the statement:

pstmt.setString(1, product.getName());pstmt.setDouble(2, product.getPrice());

Behind the scenes, thePreparedStatement will validate the values and bind them to the placeholders (?) accordingly.

Fifth, execute theINSERT statement and return the number of inserted rows by calling theexecuteUpdate() method of thePreparedStatement object:

int insertedRow= pstmt.executeUpdate();

Sixth, retrieve the inserted id and return it:

if (insertedRow> 0) {    var rs= pstmt.getGeneratedKeys();    if (rs.next()) {        return rs.getInt(1);    }}

If anySQLException occurs, display the detail of the exception in the catch block.

Since the Connection andPreparedStatement objects are created in the try-with-resources statement, they will be automatically closed.

Adding a product

The followingmain() method uses theadd() method of the ProductDB class to insert a new row into theproducts table:

public class Main {    public static void main(String[] args) {        int id= ProductDB.add(new Product("Phone Case", 19.99));        System.out.println("Inserted id:" + id);    }}

If you run the program, it’ll show the following output:

Inserted id:1

Verify the insert

Connect to thesales database and retrieve the data from theproducts table to verify the insert:

SELECT * FROM products;

Output:

id |    name    | price----+------------+-------  1 | Phone Case | 19.99(1 row)

Inserting multiple rows into a table

Define a new method add() that accepts a list ofProduct objects and inserts them into theproducts table:

import java.sql.SQLException;import java.sql.Statement;import java.util.List;public class ProductDB {    public static void add(List<Product> products){        var sql= "INSERT INTO products(name, price) "                + "VALUES(?,?)";        try (var conn=  DB.connect();             var pstmt= conn.prepareStatement(sql)) {             for (var product: products) {                 pstmt.setString(1, product.getName());                 pstmt.setDouble(2, product.getPrice());                 pstmt.addBatch();             }             pstmt.executeBatch();        }catch (SQLException e) {            e.printStackTrace();        }    }    // ...}

How it works.

First, initialize anINSERT statement:

var sql = "INSERT INTO products(name,price) "          + "VALUES(?,?)";

Second, open a connection and create aPreparedStatement object:

try (var conn=  DB.connect();     var pstmt= conn.prepareStatement(sql)) {// ...

The try-with-resources statement ensures that thePreparedStatement andConnection objects will be closed automatically.

Third, iterate over theProduct in theProducts list, bind the values to the statement, and add the statement to a batch for insertion:

for (var product: products) {    pstmt.setString(1, product.getName());    pstmt.setDouble(2, product.getPrice());    pstmt.addBatch();}

Finally, execute insert statements in batch by calling theexecuteBatch() method of thePreparedStatement object:

pstmt.executeBatch();

Adding multiple products

The following shows how to use theProductDB class to add multiple products to theproducts table:

import java.util.ArrayList;public class Main {    public static void main(String[] args) {        var products= new ArrayList<Product>();        products.add(new Product("Power Bank",19.99));        products.add(new Product("Screen Protector", 29.99));        products.add(new Product("Wireless Charger", 35.99));        products.add(new Product("Bluetooth Headphones", 199));        products.add(new Product("Phone Stand", 24.99));        products.add(new Product("Ring Holder", 39.99));        products.add(new Product("Car Mount", 29.98));        products.add(new Product("Selfie Stick", 29.99));        products.add(new Product("Smartwatch", 399.97));        ProductDB.add(products);    }}

Verify the inserts

Connect to thesales database and query data from theproducts table to verify the inserts:

SELECT* FROM products;

Output:

id|         name| price----+----------------------+--------  1 | Phone Case|  19.99  2 | Power Bank|  19.99  3 | Screen Protector|  29.99  4 | Wireless Charger|  35.99  5 | Bluetooth Headphones| 199.00  6 | Phone Stand|  24.99  7 | Ring Holder|  39.99  8 | Car Mount|  29.98  9 | Selfie Stick|  29.99 10 | Smartwatch| 399.97(10 rows)

The output indicates the newadd() method added nine rows to theproducts table successfully.

Summary

Call theexecuteUpdate() method of thePreparedStatement object to execute theINSERT statement to insert a new row into a table.
Use theaddBatch() and theexecuteBatch() methods of thePreparedStatement object to execute batch inserts.

PreviousPostgreSQL JDBC: Creating Tables NextPostgreSQL JDBC: Querying Data

Last updated onFebruary 2, 2024

Was this page helpful?

Thank you for your feedback!

Movatterモバイル変換