使用预编译语句将 JSON 对象插入 PostgreSQL_AI阅读总结 — 包阅AI

Storing JSON data in a PostgreSQL database can be quite efficient and effective. PostgreSQL offers two data types to store JSON: JSON and JSONB. Let us delve into understanding the differences between these types, how to create a PostgreSQL table with a JSON column, and how to insert a JSON object using Java’s PreparedStatement.

1. JSONB vs. JSON Type

PostgreSQL provides two data types for storing JSON data: JSON and JSONB.

• JSON: This type stores the exact JSON text as input. It performs input validation to ensure that the JSON is valid but does not process or store the data in a binary format. Querying JSON data stored as JSON can be slower since it needs to be parsed every time it is queried.
• JSONB: This type stores JSON data in a binary format. It parses the input JSON text and stores it in a decomposed binary format, which allows for faster querying. JSONB also supports indexing, which can significantly improve the performance of JSON queries.

For most use cases, JSONB is recommended due to its performance advantages and support for indexing.

1.1 Setting up a database on Docker

Usually, setting up the database is a tedious step but with Docker, it is a simple process. You can watch the video available at this link to understand the Docker installation on Windows OS. Once done open the terminal and trigger the below command to set and run postgresql.

-- Remember to change the password –docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=your_password --name postgres postgres-- command to stop the Postgres docker container --docker stop postgres-- command to remove the Postgres docker container --docker rm postgres

Remember to enter the password of your choice. If everything goes well the postgresql database server will be up and running on a port number – 5432 and you can connect with the Dbeaver GUI tool for connecting to the server.

2. Creating A PostgreSQL Table With JSON Column

To store JSON data in PostgreSQL, you need to create a table with a column of type JSON or JSONB. Here is an example of how to create such a table:

CREATE TABLE user_data (  id SERIAL PRIMARY KEY, user_info JSONB);

This SQL command creates a table named user_data with two columns: id, which is an auto-incrementing primary key, and user_info, which is of type JSONB.

3. Writing Java Code To Insert JSON Data

3.1 Maven Dependencies

First, ensure you have the necessary dependencies in your pom.xml if you are using Maven:

<dependency>    <groupId>org.postgresql</groupId>    <artifactId>postgresql</artifactId>    <version>42.2.18</version></dependency><dependency>    <groupId>com.fasterxml.jackson.core</groupId>    <artifactId>jackson-databind</artifactId>    <version>2.11.3</version></dependency>

3.2 Java Code

Here is an example that inserts a JSON object into a PostgreSQL table using PreparedStatement:

package jcg.example;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.SQLException;import com.fasterxml.jackson.databind.ObjectMapper;public class InsertJsonData {    private static final String URL = "jdbc:postgresql://localhost:5432/your_database";    private static final String USER = "your_username";    private static final String PASSWORD = "your_password";    public static void main(String[] args) {        Connection conn = null;        PreparedStatement pstmt = null;        try {            // Establish connection            conn = DriverManager.getConnection(URL, USER, PASSWORD);            // JSON data to be inserted            String jsonString = "{ \"name\": \"John Doe\", \"age\": 30, \"city\": \"New York\" }";            // Convert JSON string to JSONB object using Jackson            ObjectMapper objectMapper = new ObjectMapper();            Object jsonObject = objectMapper.readValue(jsonString, Object.class);            // Prepare SQL statement            String sql = "INSERT INTO user_data (user_info) VALUES (?::jsonb)";            pstmt = conn.prepareStatement(sql);            // Set JSONB data            pstmt.setObject(1, jsonObject.toString());            // Execute update            pstmt.executeUpdate();            System.out.println("JSON data is inserted successfully.");        } catch (Exception e) {            e.printStackTrace();        } finally {            // Close resources            try {                if (pstmt != null) {                    pstmt.close();                }                if (conn != null) {                    conn.close();                }            } catch (SQLException ex) {                ex.printStackTrace();            }        }    }}

3.2.1 Explanation

In the above code:

• We first import the necessary libraries, including JDBC and Jackson for JSON processing.
• We define the database connection details (URL, USER, PASSWORD).
• We establish a connection to the PostgreSQL database using DriverManager.getConnection().
• We create a JSON string representing the data to be inserted.
• We use Jackson’s ObjectMapper to convert the JSON string into a JSON object.
• We prepare an SQL INSERT statement with a placeholder for the JSON data.
• We set the JSON object as the value for the placeholder using pstmt.setObject(). The ?::jsonb syntax is used to explicitly cast the string to jsonb type.
• We execute the statement to insert the JSON data into the database.
• Finally, we close the resources (PreparedStatement and Connection).

Make sure to replace “your_database”, “your_username”, and “your_password” with actual values corresponding to your PostgreSQL setup.

3.2.2 Output

Here is the output of the Java code when it runs successfully.

JSON data is inserted successfully.

After running the Java code the user_data table will have a new row inserted with the JSON data.

SELECT * FROM user_data;

4. Conclusion

Storing JSON data in PostgreSQL using Java is straightforward and efficient. By leveraging PostgreSQL’s JSONB type and Java’s PreparedStatement, you can efficiently store and query JSON data. This approach allows for flexibility in data storage and retrieval, making it a powerful combination for modern applications.