PostgreSQL JSONB Columns in Java Applications

Database Schema Setup

Create a table with a JSONB column to store semi-structured data:

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_documents_content ON documents USING GIN (content);

The GIN index enables efficient JSONB queries and operator usage.

JDBC Implementation

package com.example.storage;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.time.LocalDateTime;

public class DocumentRepository {
    
    private final String connectionUrl;
    private final String credentials;
    
    public DocumentRepository(String host, int port, String database, 
                              String user, String password) {
        this.connectionUrl = String.format(
            "jdbc:postgresql://%s:%d/%s", host, port, database);
        this.credentials = user + "/" + password;
    }
    
    public void persist(String payload) throws Exception {
        String sql = "INSERT INTO documents (content, created_at) VALUES (?, ?)";
        
        try (Connection link = DriverManager.getConnection(connectionUrl, 
                   credentials.split("/")[0], credentials.split("/")[1]);
             PreparedStatement stmt = link.prepareStatement(sql)) {
            
            stmt.setObject(1, payload);
            stmt.setTimestamp(2, Timestamp.valueOf(LocalDateTime.now()));
            stmt.executeUpdate();
        }
    }
    
    public static void main(String[] args) {
        DocumentRepository repo = new DocumentRepository(
            "localhost", 5432, "testdb", "admin", "secret");
        
        String json = """
            {
                "username": "bob",
                "metadata": {
                    "role": "admin",
                    "permissions": ["read", "write", "delete"]
                }
            }
            """;
        
        repo.persist(json);
    }
}

The setObject() method passes the JSON string directly to PostgreSQL's JSONB type. JDBC handles type conversion automatically.

Querying JSONB Data

public List<String> findByField(String key, String value) throws Exception {
    List<String> results = new ArrayList<>();
    String sql = "SELECT content FROM documents WHERE content ->> ? = ?";
    
    try (Connection link = DriverManager.getConnection(connectionUrl, 
               credentials.split("/")[0], credentials.split("/")[1])) {
        
        try (PreparedStatement stmt = link.prepareStatement(sql)) {
            stmt.setString(1, key);
            stmt.setString(2, value);
            
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    results.add(rs.getString("content"));
                }
            }
        }
    }
    return results;
}

The ->> operator extracts a text value from the JSON document. Use -> for JSON object retrieval.

Batch Insert Operasions

public void batchPersist(List<String> documents) throws Exception {
    String sql = "INSERT INTO documents (content) VALUES (?)";
    
    try (Connection link = DriverManager.getConnection(connectionUrl, 
               credentials.split("/")[0], credentials.split("/")[1])) {
        link.setAutoCommit(false);
        
        try (PreparedStatement stmt = link.prepareStatement(sql)) {
            for (String doc : documents) {
                stmt.setObject(1, doc);
                stmt.addBatch();
            }
            stmt.executeBatch();
            link.commit();
        } catch (SQLException e) {
            link.rollback();
            throw e;
        }
    }
}

Batch operations significantly improve throughput when inserting multiple JSON documents.

Configuraton via Properties File

# database.properties
db.host=localhost
db.port=5432
db.name=application_db
db.user=app_user
db.password=encrypted_password

Properties props = new Properties();
props.load(new FileInputStream("database.properties"));

Repository repo = new Repository(
    props.getProperty("db.host"),
    Integer.parseInt(props.getProperty("db.port")),
    props.getProperty("db.name"),
    props.getProperty("db.user"),
    props.getProperty("db.password")
);

Externalizing database configuration prevents hardcoded credentials in source code.