Insert JSON Object into PostgreSQL using Java preparedStatement

1. Introduction

In modern software development, handling JSON data has become ubiquitous due to its lightweight and versatile nature. PostgreSQL, with its robust support for JSON, provides an excellent platform for storing and querying JSON data. As a popular programming language, Java often interacts with databases using JDBC. This article demonstrates how to insert JSON objects into a PostgreSQL database using Java’s PreparedStatement.

2. Dependencies

Before diving into the code, we need to set up our environment. In addition to installing and running PostgreSQL, we also need to include the PostgreSQL JDBC driver and the org.json library in our project’s dependencies.

2.1. Installing and Running Postgresql

If PostgreSQL is not installed, we can download and install it from the official PostgreSQL website. Considering that PostgreSQL has had JSON support for a considerable time, we can choose any version starting from PostgreSQL 9. For this article, we will be utilizing the most recent and stable version, which is PostgreSQL 16. We need to ensure that PostgreSQL is up and running and accessible with the necessary credentials.

2.2. Including PostgreSQL JDBC Driver

Add the PostgreSQL JDBC driver to our project’s dependencies. For Maven projects, we need to specify the dependency in pom.xml:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.3</version>
</dependency>

2.3. Including JSON Library Dependency

To work with JSON data in our Java code, we also need to include a JSON library as a dependency. There are several popular JSON libraries available for Java, such as Jackson, Gson, and org.json. For this article, we will be using the org.json library, which provides a simple and lightweight JSON processing solution. To include the org.json library in our project, we can add the following dependency to the pom.xml file for Maven projects:

<dependency>
    <groupId>org.json</groupId>
    <artifactId>json</artifactId>
    <version>20240303</version>
</dependency>

Now that we have the necessary dependencies in place, let’s proceed to the next sections for creating the table and writing the Java code to insert JSON data.

3. JSONB vs. JSON Type

PostgreSQL provides two main types for storing JSON data: JSONB and JSON. While both types serve the purpose of storing and manipulating JSON data, they have some differences.

The JSONB type offers efficient binary storage and indexing capabilities, resulting in faster query execution. It performs validation and transformation of JSON data during insertion, preserving the order of keys within JSON objects. PostgreSQL can automatically convert values of other data types to JSON.

On the other hand, the JSON type stores JSON data as plain text without binary representation or specialized indexing. It performs validation during insertion but lacks the optimization and key order preservation of JSONB. Explicit casting or conversion is required to convert values to JSON when using the JSONB type.

In this article, we’ll be utilizing the JSONB type to store and query JSON data in PostgreSQL.

4. Creating A PostgreSQL Table With JSON Column

First, we need to create a PostgreSQL table that includes a JSON column. Connect to the PostgreSQL instance we set up before and run the following SQL command:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    info JSONB
);

This table has three columns: id, name, and info. The info column is of type JSONB, which stores JSON data in a binary format, providing efficient storage and query capabilities.

5. Writing Java Code To Insert JSON Data

Now, let’s move to the Java part. We’ll write a Java program to insert JSON data into the users table using PreparedStatement.

5.1. Establishing A Database Connection

First, we need to establish a JDBC connection to the PostgreSQL database. Here’s a method to get a database connection:

public class InsertJsonData {
    private static final String URL = "jdbc:postgresql://localhost:5432/database_name";
    private static final String USER = "username";
    private static final String PASSWORD = "password";

    public Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }
}

Please note that database_name, username, and password need to be replaced with the actual PostgreSQL database name, username, and password.

5.2. Inserting JSON Data

Next, we need to write a method to insert a JSON object into the users table:

public class InsertJsonData {
    public static void insertUser(String name, JSONObject info) {
        String sql = "INSERT INTO users (name, info) VALUES (?, ?::jsonb)";

        Connection conn = DatabaseConnection.getConnection();
        PreparedStatement pstmt = conn.prepareStatement(sql);

        pstmt.setString(1, name);
        pstmt.setString(2, info.toString());
        pstmt.executeUpdate();

        System.out.println("Data inserted successfully.");
    }

    public static void main(String[] args) {
        JSONObject jsonInfo = new JSONObject();
        jsonInfo.put("email", "[email protected]");
        jsonInfo.put("age", 30);
        jsonInfo.put("active", true);

        insertUser("John Doe", jsonInfo);
    }
}

5.3. Code Breakdown

Let’s break down the code and explore some of its components:

• Database Connection: The getConnection() method establishes a connection to the PostgreSQL database.
• SQL Query: The INSERT INTO users (name, info) VALUES (?, ?::jsonb) query inserts a record into the users table. The ?::jsonb syntax is a PostgreSQL-specific syntax used for type casting. The double colon operator :: is a synonym for the CAST keyword in PostgreSQL, indicating a type conversion operation. By using ?::jsonb, we’re instructing PostgreSQL to cast the second parameter, which is a JSON string, to the jsonb data type before inserting it into the info column. This allows for proper handling and storage of JSON data within PostgreSQL.
• PreparedStatement: The PreparedStatement sets the parameters and executes the SQL query. pstmt.setString(1, name) sets the name, and pstmt.setString(2, info.toString()) sets the JSON data.
• JSON Handling: The JSONObject class from the org.json library creates and handles JSON data.

6. Conclusion

Inserting JSON objects into PostgreSQL using Java’s PreparedStatement is straightforward and efficient. This approach leverages PostgreSQL’s powerful JSON capabilities and Java’s robust JDBC API. Following the steps outlined in this article, we can seamlessly store JSON data in our PostgreSQL database and take advantage of its rich querying features.

In some cases, if Java Persistence API (JPA) is preferred for database operations, it may be beneficial to explore storing PostgreSQL JSONB data using Spring Boot and JPA. This approach provides another convenient way to insert JSON data into the PostgreSQL database.