PostgreSQL-Specific Types

package org.postgresql.util;

import java.io.Serializable;

/**
 * Base class for PostgreSQL custom data types.
 * Extend this class to create handlers for custom types.
 */
public class PGobject implements Serializable, Cloneable {
    /**
     * Sets the PostgreSQL type name for this object.
     *
     * @param type PostgreSQL type name (e.g., "int4", "json", "point")
     */
    public void setType(String type);

    /**
     * Gets the PostgreSQL type name.
     *
     * @return Type name
     */
    public String getType();

    /**
     * Sets the value from its PostgreSQL string representation.
     *
     * @param value String value
     * @throws SQLException if value cannot be parsed
     */
    public void setValue(String value) throws SQLException;

    /**
     * Gets the PostgreSQL string representation of this value.
     *
     * @return String representation
     */
    public String getValue();

    /**
     * Checks if this object represents SQL NULL.
     *
     * @return true if NULL
     */
    public boolean isNull();

    @Override
    public boolean equals(Object obj);

    @Override
    public Object clone() throws CloneNotSupportedException;

    @Override
    public String toString();
}

import org.postgresql.util.PGobject;
import java.sql.*;

// Example: Working with JSON type
public class JSONExample {
    public static void insertJSON(Connection conn, String jsonData) throws SQLException {
        String sql = "INSERT INTO documents (data) VALUES (?)";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            PGobject jsonObject = new PGobject();
            jsonObject.setType("json");
            jsonObject.setValue(jsonData);

            pstmt.setObject(1, jsonObject);
            pstmt.executeUpdate();
        }
    }

    public static String retrieveJSON(Connection conn, int id) throws SQLException {
        String sql = "SELECT data FROM documents WHERE id = ?";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    PGobject jsonObject = (PGobject) rs.getObject("data");
                    return jsonObject.getValue();
                }
            }
        }
        return null;
    }
}

import java.sql.Array;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;

/**
 * PostgreSQL Array interface (java.sql.Array).
 */
public interface Array {
    /**
     * Gets the SQL type name of array elements.
     *
     * @return Base type name (e.g., "integer", "text")
     * @throws SQLException if type cannot be determined
     */
    String getBaseTypeName() throws SQLException;

    /**
     * Gets the JDBC type code of array elements.
     *
     * @return JDBC type code from java.sql.Types
     * @throws SQLException if type cannot be determined
     */
    int getBaseType() throws SQLException;

    /**
     * Returns the array contents as a Java array.
     *
     * @return Java array (e.g., Integer[], String[])
     * @throws SQLException if array cannot be retrieved
     */
    Object getArray() throws SQLException;

    /**
     * Returns the array contents with custom type mapping.
     *
     * @param map Type mapping
     * @return Mapped array
     * @throws SQLException if retrieval fails
     */
    Object getArray(Map<String,Class<?>> map) throws SQLException;

    /**
     * Returns a subset of the array.
     *
     * @param index Starting index (1-based)
     * @param count Number of elements
     * @return Java array
     * @throws SQLException if retrieval fails
     */
    Object getArray(long index, int count) throws SQLException;

    /**
     * Returns array as ResultSet.
     * Each row has two columns: INDEX (long) and VALUE (Object).
     *
     * @return ResultSet representation
     * @throws SQLException if conversion fails
     */
    ResultSet getResultSet() throws SQLException;

    /**
     * Frees array resources.
     * Array cannot be used after this call.
     *
     * @throws SQLException if free fails
     */
    void free() throws SQLException;
}

import org.postgresql.PGConnection;
import java.sql.*;

// Example 1: Insert array
public class ArrayInsertExample {
    public static void insertArray(Connection conn) throws SQLException {
        // Create array from Java array
        String[] tags = {"java", "postgresql", "jdbc"};
        Array sqlArray = conn.createArrayOf("text", tags);

        String sql = "INSERT INTO articles (title, tags) VALUES (?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, "My Article");
            pstmt.setArray(2, sqlArray);
            pstmt.executeUpdate();
        }

        sqlArray.free();
    }

    // Integer array
    public static void insertIntArray(Connection conn) throws SQLException {
        Integer[] numbers = {1, 2, 3, 4, 5};
        Array sqlArray = conn.createArrayOf("integer", numbers);

        String sql = "INSERT INTO data (values) VALUES (?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setArray(1, sqlArray);
            pstmt.executeUpdate();
        }

        sqlArray.free();
    }
}

// Example 2: Retrieve array
public class ArrayRetrieveExample {
    public static String[] getArray(Connection conn, int id) throws SQLException {
        String sql = "SELECT tags FROM articles WHERE id = ?";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    Array sqlArray = rs.getArray("tags");
                    String[] tags = (String[]) sqlArray.getArray();
                    sqlArray.free();
                    return tags;
                }
            }
        }
        return null;
    }
}

// Example 3: Primitive arrays (PostgreSQL extension)
public class PrimitiveArrayExample {
    public static void insertPrimitiveArray(Connection conn) throws SQLException {
        // PostgreSQL driver supports primitive arrays
        PGConnection pgConn = conn.unwrap(PGConnection.class);

        int[] primitiveInts = {1, 2, 3, 4, 5};
        Array sqlArray = pgConn.createArrayOf("integer", primitiveInts);

        String sql = "INSERT INTO data (values) VALUES (?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setArray(1, sqlArray);
            pstmt.executeUpdate();
        }

        sqlArray.free();
    }
}

// Example 4: Multi-dimensional arrays
public class MultiDimensionalArrayExample {
    public static void insert2DArray(Connection conn) throws SQLException {
        Integer[][] matrix = {
            {1, 2, 3},
            {4, 5, 6},
            {7, 8, 9}
        };

        Array sqlArray = conn.createArrayOf("integer", matrix);

        String sql = "INSERT INTO matrices (data) VALUES (?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setArray(1, sqlArray);
            pstmt.executeUpdate();
        }

        sqlArray.free();
    }
}

package org.postgresql.geometric;

import org.postgresql.util.PGobject;
import java.sql.SQLException;

/**
 * Point type: (x, y)
 */
public class PGpoint extends PGobject {
    /**
     * The X coordinate.
     */
    public double x;

    /**
     * The Y coordinate.
     */
    public double y;

    /**
     * Indicates whether the value represents null::point.
     */
    public boolean isNull;

    /**
     * Creates an empty point.
     */
    public PGpoint();

    /**
     * Creates a point with specified coordinates.
     * @param x X coordinate
     * @param y Y coordinate
     */
    public PGpoint(double x, double y);

    /**
     * Creates a point from its string representation.
     * @param value String representation (e.g., "(1.0,2.0)")
     */
    public PGpoint(String value) throws SQLException;

    @Override
    public void setValue(String value) throws SQLException;
    @Override
    public boolean equals(Object obj);
    @Override
    public String toString();
}

/**
 * Box type: rectangle defined by two opposite corners
 */
public class PGbox extends PGobject {
    public PGpoint[] point; // Array of 2 points

    public PGbox();
    public PGbox(PGpoint p1, PGpoint p2);
    public PGbox(double x1, double y1, double x2, double y2);
    public PGbox(String value) throws SQLException;
}

/**
 * Circle type: center point and radius
 */
public class PGcircle extends PGobject {
    public PGpoint center;
    public double radius;

    public PGcircle();
    public PGcircle(PGpoint center, double radius);
    public PGcircle(double x, double y, double r);
    public PGcircle(String value) throws SQLException;
}

/**
 * Line type: infinite line (ax + by + c = 0)
 */
public class PGline extends PGobject {
    public double a;
    public double b;
    public double c;

    public PGline();
    public PGline(double a, double b, double c);
    public PGline(double x1, double y1, double x2, double y2);
    public PGline(PGpoint p1, PGpoint p2);
    public PGline(PGlseg lseg);
    public PGline(String value) throws SQLException;
}

/**
 * Line segment type: line between two points
 */
public class PGlseg extends PGobject {
    public PGpoint[] point; // Array of 2 endpoints

    public PGlseg();
    public PGlseg(PGpoint p1, PGpoint p2);
    public PGlseg(double x1, double y1, double x2, double y2);
    public PGlseg(String value) throws SQLException;
}

/**
 * Path type: sequence of connected points
 */
public class PGpath extends PGobject {
    public boolean open; // true for open path, false for closed
    public PGpoint[] points;

    public PGpath();
    public PGpath(PGpoint[] points, boolean open);
    public PGpath(String value) throws SQLException;

    /**
     * Checks if the path is open.
     * @return true if path is open, false if closed
     */
    public boolean isOpen();

    /**
     * Checks if the path is closed.
     * @return true if path is closed, false if open
     */
    public boolean isClosed();

    /**
     * Closes the path.
     */
    public void closePath();

    /**
     * Opens the path.
     */
    public void openPath();
}

/**
 * Polygon type: closed path
 */
public class PGpolygon extends PGobject {
    public PGpoint[] points;

    public PGpolygon();
    public PGpolygon(PGpoint[] points);
    public PGpolygon(String value) throws SQLException;
}

import org.postgresql.geometric.*;
import java.sql.*;

// Example: Geometric types
public class GeometricExample {
    public static void insertPoint(Connection conn) throws SQLException {
        PGpoint point = new PGpoint(10.5, 20.3);

        String sql = "INSERT INTO locations (name, position) VALUES (?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, "Location A");
            pstmt.setObject(2, point);
            pstmt.executeUpdate();
        }
    }

    public static void insertCircle(Connection conn) throws SQLException {
        PGpoint center = new PGpoint(0, 0);
        PGcircle circle = new PGcircle(center, 5.0);

        String sql = "INSERT INTO zones (name, area) VALUES (?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, "Central Zone");
            pstmt.setObject(2, circle);
            pstmt.executeUpdate();
        }
    }

    public static PGpoint retrievePoint(Connection conn, int id) throws SQLException {
        String sql = "SELECT position FROM locations WHERE id = ?";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return (PGpoint) rs.getObject("position");
                }
            }
        }
        return null;
    }
}

package org.postgresql.util;

/**
 * PostgreSQL interval type.
 */
public class PGInterval extends PGobject {
    private int years;
    private int months;
    private int days;
    private int hours;
    private int minutes;
    private double seconds;

    /**
     * Creates an empty interval.
     */
    public PGInterval();

    /**
     * Creates an interval from its string representation.
     * @param value String representation (e.g., '3 years 2 mons' or ISO 8601 format)
     */
    public PGInterval(String value) throws SQLException;

    /**
     * Creates an interval with specified values.
     * @param years Years component
     * @param months Months component
     * @param days Days component
     * @param hours Hours component
     * @param minutes Minutes component
     * @param seconds Seconds component (can include fractional seconds)
     */
    public PGInterval(int years, int months, int days, int hours, int minutes, double seconds);

    /**
     * Sets interval components.
     */
    public void setValue(int years, int months, int days,
                        int hours, int minutes, double seconds);

    // Getters and setters
    public int getYears();
    public void setYears(int years);

    public int getMonths();
    public void setMonths(int months);

    public int getDays();
    public void setDays(int days);

    public int getHours();
    public void setHours(int hours);

    public int getMinutes();
    public void setMinutes(int minutes);

    public double getSeconds();
    public void setSeconds(double seconds);

    @Override
    public void setValue(String value) throws SQLException;
    @Override
    public String getValue();
}

// Example: Interval type
public class IntervalExample {
    public static void insertInterval(Connection conn) throws SQLException {
        PGInterval interval = new PGInterval();
        interval.setDays(5);
        interval.setHours(12);
        interval.setMinutes(30);

        String sql = "INSERT INTO tasks (name, duration) VALUES (?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, "Task A");
            pstmt.setObject(2, interval);
            pstmt.executeUpdate();
        }
    }
}

package org.postgresql.util;

/**
 * PostgreSQL money type.
 */
public class PGmoney extends PGobject {
    /**
     * The monetary value as a double.
     */
    public double val;

    /**
     * Indicates whether the value represents null::money.
     */
    public boolean isNull;

    /**
     * Creates an empty money object.
     */
    public PGmoney();

    /**
     * Creates a money object with the specified numeric value.
     * @param value Numeric value
     */
    public PGmoney(double value);

    /**
     * Creates a money object from string representation.
     * @param value String representation (e.g., "$123.45" or "($50.00)" for negative)
     */
    public PGmoney(String value) throws SQLException;

    /**
     * Sets value from string representation.
     * @param value String representation of money value
     */
    @Override
    public void setValue(String value) throws SQLException;

    /**
     * Returns string representation of the money value.
     * @return String in format "$123.45" or "-$50.00"
     */
    @Override
    public String getValue();
}

package org.postgresql.util;

import java.util.Map;

/**
 * Converter for PostgreSQL hstore type.
 */
public class HStoreConverter {
    /**
     * Parses hstore string to Map.
     *
     * @param s hstore string
     * @return Map of key-value pairs
     * @throws SQLException if parsing fails
     */
    public static Map<String, String> fromString(String s) throws SQLException;

    /**
     * Converts Map to hstore string.
     *
     * @param m Map of key-value pairs
     * @return hstore string
     */
    public static String toString(Map<String, String> m);
}

import org.postgresql.util.HStoreConverter;
import org.postgresql.util.PGobject;
import java.util.HashMap;
import java.util.Map;
import java.sql.*;

// Example: HStore type
public class HStoreExample {
    public static void insertHStore(Connection conn) throws SQLException {
        Map<String, String> attributes = new HashMap<>();
        attributes.put("color", "red");
        attributes.put("size", "large");
        attributes.put("material", "cotton");

        String hstoreString = HStoreConverter.toString(attributes);

        PGobject hstore = new PGobject();
        hstore.setType("hstore");
        hstore.setValue(hstoreString);

        String sql = "INSERT INTO products (name, attributes) VALUES (?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, "Product A");
            pstmt.setObject(2, hstore);
            pstmt.executeUpdate();
        }
    }

    public static Map<String, String> retrieveHStore(Connection conn, int id)
            throws SQLException {
        String sql = "SELECT attributes FROM products WHERE id = ?";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    PGobject hstore = (PGobject) rs.getObject("attributes");
                    return HStoreConverter.fromString(hstore.getValue());
                }
            }
        }
        return null;
    }
}

import java.util.UUID;
import java.sql.*;

// Example: UUID type
public class UUIDExample {
    public static void insertUUID(Connection conn) throws SQLException {
        UUID uuid = UUID.randomUUID();

        String sql = "INSERT INTO entities (id, name) VALUES (?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setObject(1, uuid);
            pstmt.setString(2, "Entity A");
            pstmt.executeUpdate();
        }
    }

    public static UUID retrieveUUID(Connection conn, String name) throws SQLException {
        String sql = "SELECT id FROM entities WHERE name = ?";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, name);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getObject("id", UUID.class);
                }
            }
        }
        return null;
    }
}

import org.postgresql.PGConnection;
import org.postgresql.util.PGobject;
import java.sql.*;

// Example: Custom type
public class CustomTypeExample {
    // Define custom type class
    public static class MyCustomType extends PGobject {
        private String value1;
        private int value2;

        @Override
        public void setValue(String value) throws SQLException {
            // Parse PostgreSQL representation
            String[] parts = value.replaceAll("[()]", "").split(",");
            this.value1 = parts[0];
            this.value2 = Integer.parseInt(parts[1]);
        }

        @Override
        public String getValue() {
            // Return PostgreSQL representation
            return "(" + value1 + "," + value2 + ")";
        }
    }

    // Register custom type
    public static void registerCustomType(Connection conn) throws SQLException {
        PGConnection pgConn = conn.unwrap(PGConnection.class);
        pgConn.addDataType("mytype", MyCustomType.class);
    }

    // Use custom type
    public static void useCustomType(Connection conn) throws SQLException {
        registerCustomType(conn);

        MyCustomType custom = new MyCustomType();
        custom.setType("mytype");
        custom.setValue("(test,42)");

        String sql = "INSERT INTO custom_table (data) VALUES (?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setObject(1, custom);
            pstmt.executeUpdate();
        }
    }
}