PostgreSQL jsonb、`` 和 JDBC

使用PostgreSQL的jsonb类型进行数据存储和查询是一种灵活且强大的方法。结合JDBC（Java Database Connectivity）驱动程序，我们可以轻松地在Java应用程序中操作jsonb数据。本文将介绍如何使用jsonb和`?`操作符进行查询，并提供相关的代码示例。

java
String sql = "SELECT * FROM my_table WHERE my_column->'name' ? 'John'";
try (Connection connection = DriverManager.getConnection(url, username, password);
     PreparedStatement statement = connection.prepareStatement(sql);
     ResultSet resultSet = statement.executeQuery()) {
    while (resultSet.next()) {
        // 处理查询结果
    }
} catch (SQLException e) {
    e.printStackTrace();
}

sql
CREATE TABLE user_info (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    extra_info JSONB
);
INSERT INTO user_info (name, extra_info)
VALUES ('Alice', '{"age": 25, "gender": "female"}'),
       ('Bob', '{"age": 30, "gender": "male"}'),
       ('Charlie', '{"age": 35, "gender": "male", "job": "engineer"}');

java
String sql = "SELECT * FROM user_info WHERE extra_info ? 'age' AND (extra_info->>'age')::integer > 30";
try (Connection connection = DriverManager.getConnection(url, username, password);
     PreparedStatement statement = connection.prepareStatement(sql);
     ResultSet resultSet = statement.executeQuery()) {
    while (resultSet.next()) {
        int id = resultSet.getInt("id");
        String name = resultSet.getString("name");
        String extraInfo = resultSet.getString("extra_info");
        System.out.println("ID: " + id + ", Name: " + name + ", Extra Info: " + extraInfo);
    }
} catch (SQLException e) {
    e.printStackTrace();
}