Basic Connectivity

package org.postgresql;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverPropertyInfo;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.Properties;
import java.util.logging.Logger;
import org.postgresql.util.SharedTimer;

/**
 * PostgreSQL JDBC Driver implementation
 * Automatically registered with DriverManager when class is loaded
 */
public class Driver implements java.sql.Driver {
    /**
     * Attempts to make a database connection to the given URL.
     * The driver returns null if it realizes it is the wrong kind of driver
     * to connect to the given URL.
     *
     * @param url JDBC URL of the form:
     *            jdbc:postgresql://host:port/database
     *            jdbc:postgresql://host1:port1,host2:port2/database
     *            jdbc:postgresql:database
     *            jdbc:postgresql:/
     * @param info Connection properties including:
     *             - user: database username
     *             - password: database password
     *             - ssl: enable SSL (true/false)
     *             - and many other optional properties
     * @return A Connection object that represents a connection to the URL,
     *         or null if the URL is not suitable for this driver
     * @throws SQLException if a database access error occurs
     */
    public Connection connect(String url, Properties info) throws SQLException;

    /**
     * Returns true if the driver thinks it can open a connection to the given URL.
     * Typically drivers will return true if they understand the subprotocol
     * specified in the URL and false if they don't.
     *
     * @param url JDBC URL to test
     * @return true if this driver can connect to the given URL
     * @throws SQLException if a database access error occurs
     */
    public boolean acceptsURL(String url) throws SQLException;

    /**
     * Gets information about the possible properties for this driver.
     *
     * @param url JDBC URL for which properties are requested
     * @param info Proposed set of properties
     * @return Array of DriverPropertyInfo objects describing possible properties
     * @throws SQLException if a database access error occurs
     */
    public DriverPropertyInfo[] getPropertyInfo(String url, Properties info)
            throws SQLException;

    /**
     * Gets the driver's major version number.
     *
     * @return Driver's major version number
     */
    public int getMajorVersion();

    /**
     * Gets the driver's minor version number.
     *
     * @return Driver's minor version number
     */
    public int getMinorVersion();

    /**
     * Returns the driver version as a string (e.g., "42.7.5").
     *
     * @return Driver version string
     */
    public static String getVersion();

    /**
     * Reports whether this driver is a genuine JDBC Compliant driver.
     * Returns false because PostgreSQL is not yet SQL92 Entry Level compliant.
     *
     * @return false, PostgreSQL is not yet SQL92 Entry Level compliant
     */
    public boolean jdbcCompliant();

    /**
     * Returns the parent Logger of all loggers used by this driver.
     *
     * @return The parent Logger for this driver
     * @throws SQLFeatureNotSupportedException if the driver does not use java.util.logging
     */
    public Logger getParentLogger() throws SQLFeatureNotSupportedException;

    /**
     * Registers the driver with DriverManager.
     * This is called automatically when the class is loaded, but can be
     * called manually if needed.
     *
     * @throws SQLException if driver cannot be registered
     */
    public static void register() throws SQLException;

    /**
     * Deregisters the driver from DriverManager.
     * Useful for cleaning up in application servers or when hot-redeploying.
     *
     * @throws SQLException if driver cannot be deregistered
     */
    public static void deregister() throws SQLException;

    /**
     * Checks if the driver is currently registered with DriverManager.
     *
     * @return true if driver is registered
     */
    public static boolean isRegistered();

    /**
     * Returns the shared timer instance used for connection timeouts.
     *
     * @return SharedTimer instance
     */
    public static SharedTimer getSharedTimer();

    /**
     * Helper method that creates a SQLFeatureNotSupportedException for
     * features not implemented by the driver.
     *
     * @param callClass Class where the unimplemented method was called
     * @param functionName Name of the unimplemented function
     * @return SQLFeatureNotSupportedException with appropriate message
     */
    public static SQLFeatureNotSupportedException notImplemented(
            Class<?> callClass, String functionName);

    /**
     * Parses a JDBC URL into a Properties object.
     *
     * @param url JDBC URL to parse
     * @param defaults Default properties to merge with URL properties (can be null)
     * @return Properties object containing all connection parameters, or null if URL is not recognized
     */
    public static Properties parseURL(String url, Properties defaults);
}

import org.postgresql.Driver;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

// Example 1: Automatic driver registration (most common)
// The driver registers itself automatically when the class is loaded
public class AutomaticRegistration {
    public static void main(String[] args) throws SQLException {
        // No explicit registration needed
        String url = "jdbc:postgresql://localhost:5432/mydb";
        Connection conn = DriverManager.getConnection(url, "user", "password");
        // use connection...
        conn.close();
    }
}

// Example 2: Manual driver registration
public class ManualRegistration {
    public static void main(String[] args) throws SQLException {
        // Explicitly load and register the driver
        Driver.register();

        String url = "jdbc:postgresql://localhost:5432/mydb";
        Connection conn = DriverManager.getConnection(url, "user", "password");
        conn.close();
    }
}

// Example 3: Check driver version
public class DriverInfo {
    public static void main(String[] args) throws SQLException {
        Driver driver = new Driver();
        System.out.println("Driver version: " +
                driver.getMajorVersion() + "." + driver.getMinorVersion());
        System.out.println("JDBC compliant: " + driver.jdbcCompliant());
    }
}

// Example 4: Parse URL to see properties
public class URLParsing {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost:5432/mydb?ssl=true&user=postgres";
        Properties props = Driver.parseURL(url, new Properties());

        // See what properties were extracted
        if (props != null) {
            for (String key : props.stringPropertyNames()) {
                System.out.println(key + " = " + props.getProperty(key));
            }
        }
    }
}

jdbc:postgresql://[host][:port][/database][?property=value[&property=value...]]

// Basic connection to localhost
String url1 = "jdbc:postgresql://localhost/mydb";

// Specify port
String url2 = "jdbc:postgresql://localhost:5432/mydb";

// Specify user and password in URL (not recommended for security)
String url3 = "jdbc:postgresql://localhost/mydb?user=postgres&password=secret";

// SSL connection
String url4 = "jdbc:postgresql://localhost/mydb?ssl=true&sslmode=require";

// Multiple hosts for high availability
String url5 = "jdbc:postgresql://host1:5432,host2:5432,host3:5432/mydb"
            + "?targetServerType=primary";

// Unix socket connection (requires pgsql.so library)
String url6 = "jdbc:postgresql://localhost/mydb"
            + "?socketFactory=org.newsclub.net.unix.AFUNIXSocketFactory$FactoryArg"
            + "&socketFactoryArg=/var/run/postgresql/.s.PGSQL.5432";

// Database on different port
String url7 = "jdbc:postgresql://db.example.com:5433/production";

// Connect using pg_service.conf
String url8 = "jdbc:postgresql://?service=myservice";

// Minimal URL (uses defaults)
String url9 = "jdbc:postgresql:mydb";

// IPv6 address
String url10 = "jdbc:postgresql://[::1]:5432/mydb";

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

/**
 * Standard JDBC method for getting a connection using DriverManager
 */
public class java.sql.DriverManager {
    /**
     * Establishes a connection to the database
     * @param url JDBC URL
     * @param user Database username
     * @param password Database password
     * @return Connection object
     * @throws SQLException if connection fails
     */
    public static Connection getConnection(String url, String user, String password)
            throws SQLException;

    /**
     * Establishes a connection using Properties
     * @param url JDBC URL
     * @param info Connection properties
     * @return Connection object
     * @throws SQLException if connection fails
     */
    public static Connection getConnection(String url, Properties info)
            throws SQLException;

    /**
     * Establishes a connection with embedded credentials in URL
     * @param url JDBC URL with user/password parameters
     * @return Connection object
     * @throws SQLException if connection fails
     */
    public static Connection getConnection(String url) throws SQLException;
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

// Example 1: Simple connection with user/password
public class SimpleConnection {
    public static Connection getConnection() throws SQLException {
        String url = "jdbc:postgresql://localhost:5432/mydb";
        String user = "postgres";
        String password = "secret";
        return DriverManager.getConnection(url, user, password);
    }
}

// Example 2: Connection with Properties
public class PropertiesConnection {
    public static Connection getConnection() throws SQLException {
        String url = "jdbc:postgresql://localhost:5432/mydb";

        Properties props = new Properties();
        props.setProperty("user", "postgres");
        props.setProperty("password", "secret");
        props.setProperty("ssl", "true");
        props.setProperty("sslmode", "require");
        props.setProperty("ApplicationName", "MyApp");
        props.setProperty("connectTimeout", "10"); // 10 seconds

        return DriverManager.getConnection(url, props);
    }
}

// Example 3: All-in-URL connection
public class URLConnection {
    public static Connection getConnection() throws SQLException {
        String url = "jdbc:postgresql://localhost:5432/mydb"
                   + "?user=postgres"
                   + "&password=secret"
                   + "&ssl=true"
                   + "&ApplicationName=MyApp";
        return DriverManager.getConnection(url);
    }
}

// Example 4: Connection with timeout and retry logic
public class RobustConnection {
    public static Connection getConnectionWithRetry(int maxRetries)
            throws SQLException {
        String url = "jdbc:postgresql://localhost:5432/mydb";
        Properties props = new Properties();
        props.setProperty("user", "postgres");
        props.setProperty("password", "secret");
        props.setProperty("connectTimeout", "5");
        props.setProperty("socketTimeout", "30");
        props.setProperty("loginTimeout", "10");

        SQLException lastException = null;
        for (int i = 0; i < maxRetries; i++) {
            try {
                return DriverManager.getConnection(url, props);
            } catch (SQLException e) {
                lastException = e;
                System.err.println("Connection attempt " + (i + 1) +
                        " failed: " + e.getMessage());
                if (i < maxRetries - 1) {
                    try {
                        Thread.sleep(1000); // Wait 1 second before retry
                    } catch (InterruptedException ie) {
                        Thread.currentThread().interrupt();
                        throw new SQLException("Interrupted during retry", ie);
                    }
                }
            }
        }
        throw lastException;
    }
}

package org.postgresql;

import org.postgresql.copy.CopyManager;
import org.postgresql.fastpath.Fastpath;
import org.postgresql.jdbc.AutoSave;
import org.postgresql.jdbc.PreferQueryMode;
import org.postgresql.largeobject.LargeObjectManager;
import org.postgresql.replication.PGReplicationConnection;
import org.postgresql.util.PGobject;

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

/**
 * PostgreSQL extensions to java.sql.Connection.
 * All connections returned by the PostgreSQL driver implement this interface.
 * Cast a standard Connection to PGConnection to access PostgreSQL-specific features.
 */
public interface PGConnection extends Connection {
    /**
     * Creates a PostgreSQL array with support for primitive arrays.
     * This is an enhanced version of Connection.createArrayOf() that also
     * supports primitive array types.
     *
     * @param typeName SQL name of the array element type (e.g., "integer", "text")
     * @param elements Array of elements (may be primitive array, Object[], or null)
     * @return Array object wrapping the elements
     * @throws SQLException if array cannot be created
     */
    Array createArrayOf(String typeName, @Nullable Object elements) throws SQLException;

    /**
     * Returns notifications received via LISTEN/NOTIFY since last call.
     * Returns null if no notifications are available.
     *
     * @return Array of notifications or null
     * @throws SQLException if retrieval fails
     */
    PGNotification[] getNotifications() throws SQLException;

    /**
     * Returns notifications with timeout support.
     * Blocks until at least one notification is received or timeout expires.
     *
     * @param timeoutMillis Timeout in milliseconds (0 = block forever,
     *                      >0 = wait up to specified time)
     * @return Array of notifications or null if timeout expires
     * @throws SQLException if retrieval fails
     */
    PGNotification[] getNotifications(int timeoutMillis) throws SQLException;

    /**
     * Returns the COPY API for bulk data operations.
     *
     * @return CopyManager for this connection
     * @throws SQLException if CopyManager cannot be obtained
     */
    CopyManager getCopyAPI() throws SQLException;

    /**
     * Returns the Large Object API for BLOB operations.
     *
     * @return LargeObjectManager for this connection
     * @throws SQLException if LargeObjectManager cannot be obtained
     */
    LargeObjectManager getLargeObjectAPI() throws SQLException;

    /**
     * Returns the Fastpath API (deprecated).
     *
     * @return Fastpath interface
     * @throws SQLException if Fastpath cannot be obtained
     * @deprecated Use PreparedStatement with binary parameters instead
     */
    @Deprecated
    Fastpath getFastpathAPI() throws SQLException;

    /**
     * Registers a custom data type handler.
     * The class must extend org.postgresql.util.PGobject.
     *
     * @param type PostgreSQL type name
     * @param klass Class that handles the type
     * @throws SQLException if registration fails or class is invalid
     */
    void addDataType(String type, Class<? extends PGobject> klass)
            throws SQLException;

    /**
     * Deprecated: Registers a custom data type handler using class name.
     * The class must extend org.postgresql.util.PGobject.
     *
     * @param type PostgreSQL type name
     * @param className Fully qualified class name that handles the type
     * @throws RuntimeException if class cannot be loaded or is invalid
     * @deprecated Use {@link #addDataType(String, Class)} instead
     */
    @Deprecated
    void addDataType(String type, String className);

    /**
     * Sets the default threshold for server-side prepared statements.
     * Statements will be prepared on the server after this many executions.
     *
     * @param threshold Number of executions before server prepare:
     *                  - Positive value N: Use server prepare on Nth and subsequent executions
     *                  - 0: Never use server prepare
     *                  - Negative: Reserved for internal use (forceBinary mode)
     *                  - Default: 5 (configurable via prepareThreshold connection property)
     * @see org.postgresql.PGStatement#setPrepareThreshold(int)
     */
    void setPrepareThreshold(int threshold);

    /**
     * Gets the default prepare threshold for this connection.
     *
     * @return Current prepare threshold
     */
    int getPrepareThreshold();

    /**
     * Sets the default fetch size for statements created from this connection.
     *
     * @param fetchSize Default fetch size (0 = fetch all rows at once)
     * @throws SQLException if fetchSize is negative
     */
    void setDefaultFetchSize(int fetchSize) throws SQLException;

    /**
     * Gets the default fetch size for this connection.
     *
     * @return Current default fetch size
     */
    int getDefaultFetchSize();

    /**
     * Returns the PostgreSQL backend process ID for this connection.
     * Useful for monitoring and debugging.
     *
     * @return Backend process ID
     */
    int getBackendPID();

    /**
     * Cancels the currently executing query on this connection.
     * This sends a cancel request to the backend.
     *
     * @throws SQLException if cancellation fails
     */
    void cancelQuery() throws SQLException;

    /**
     * Escapes an identifier for safe use in SQL.
     * Adds quotes if necessary and escapes embedded quotes.
     *
     * @param identifier Identifier to escape
     * @return Properly escaped identifier
     * @throws SQLException if escaping fails
     */
    String escapeIdentifier(String identifier) throws SQLException;

    /**
     * Escapes a string literal for safe use in SQL.
     * Properly escapes quotes and backslashes.
     *
     * @param literal String literal to escape
     * @return Properly escaped literal
     * @throws SQLException if escaping fails
     */
    String escapeLiteral(String literal) throws SQLException;

    /**
     * Returns the query execution mode for this connection.
     *
     * @return Current PreferQueryMode
     */
    PreferQueryMode getPreferQueryMode();

    /**
     * Returns the autosave configuration for this connection.
     *
     * @return Current AutoSave setting
     */
    AutoSave getAutosave();

    /**
     * Sets the autosave behavior for automatic savepoint management.
     *
     * @param autoSave AutoSave mode to use
     */
    void setAutosave(AutoSave autoSave);

    /**
     * Enables or disables adaptive fetch for this connection.
     * Existing statements and result sets won't be affected by this change.
     *
     * Adaptive fetch automatically adjusts the fetch size based on the
     * rate at which rows are consumed by the application.
     *
     * @param adaptiveFetch true to enable, false to disable
     */
    void setAdaptiveFetch(boolean adaptiveFetch);

    /**
     * Returns the current adaptive fetch setting for this connection.
     *
     * @return true if adaptive fetch is enabled, false otherwise
     */
    boolean getAdaptiveFetch();

    /**
     * Returns the replication API for this connection.
     * Only available if connection was opened with replication=database or
     * replication=true parameter.
     *
     * @return PGReplicationConnection interface
     */
    PGReplicationConnection getReplicationAPI();

    /**
     * Changes a PostgreSQL user's password.
     * The password is encrypted locally before transmission.
     *
     * @param user Username to modify
     * @param newPassword New password (will be zeroed after use)
     * @param encryptionType Encryption type: null (use server default),
     *                       "md5", or "scram-sha-256"
     * @throws SQLException if password change fails
     */
    void alterUserPassword(String user, char[] newPassword, String encryptionType)
            throws SQLException;

    /**
     * Returns all server parameters reported by PostgreSQL.
     * PostgreSQL reports values for GUC_REPORT parameters like
     * server_version, TimeZone, DateStyle, etc.
     *
     * @return Unmodifiable map of parameter names to values
     */
    Map<String, String> getParameterStatuses();

    /**
     * Returns a specific server parameter value.
     *
     * @param parameterName Parameter name (case-insensitive)
     * @return Parameter value or null if not reported
     */
    String getParameterStatus(String parameterName);

    /**
     * Enables or disables adaptive fetch size adjustment.
     * When enabled, the driver automatically adjusts fetch size based on
     * result set characteristics.
     *
     * @param adaptiveFetch true to enable adaptive fetch
     */
    void setAdaptiveFetch(boolean adaptiveFetch);

    /**
     * Returns whether adaptive fetch is enabled.
     *
     * @return true if adaptive fetch is enabled
     */
    boolean getAdaptiveFetch();
}

import org.postgresql.PGConnection;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

// Example 1: Cast to PGConnection and use extensions
public class PGConnectionExample {
    public static void useExtensions() throws SQLException {
        String url = "jdbc:postgresql://localhost/mydb";
        Connection conn = DriverManager.getConnection(url, "user", "password");

        // Cast to PGConnection to access PostgreSQL-specific features
        PGConnection pgConn = conn.unwrap(PGConnection.class);

        // Get backend process ID
        int pid = pgConn.getBackendPID();
        System.out.println("Connected to backend PID: " + pid);

        // Configure prepare threshold
        pgConn.setPrepareThreshold(5); // Prepare after 5 executions

        // Set default fetch size
        pgConn.setDefaultFetchSize(100); // Fetch 100 rows at a time

        // Get server parameters
        String serverVersion = pgConn.getParameterStatus("server_version");
        String timeZone = pgConn.getParameterStatus("TimeZone");
        System.out.println("Server version: " + serverVersion);
        System.out.println("Server timezone: " + timeZone);

        conn.close();
    }
}

// Example 2: Escape identifiers and literals
public class SQLEscaping {
    public static void demonstrateEscaping() throws SQLException {
        Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://localhost/mydb", "user", "password");
        PGConnection pgConn = conn.unwrap(PGConnection.class);

        // Escape table name for dynamic SQL
        String tableName = "user's data"; // Contains quote
        String escapedTable = pgConn.escapeIdentifier(tableName);
        System.out.println("Escaped table: " + escapedTable);
        // Output: "user's data"

        // Escape string literal
        String userInput = "O'Brien"; // Contains quote
        String escapedLiteral = pgConn.escapeLiteral(userInput);
        System.out.println("Escaped literal: " + escapedLiteral);
        // Output: 'O''Brien'

        conn.close();
    }
}

// Example 3: Check connection parameters
public class ConnectionInfo {
    public static void showConnectionInfo() throws SQLException {
        Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://localhost/mydb", "user", "password");
        PGConnection pgConn = conn.unwrap(PGConnection.class);

        // Get all server parameters
        Map<String, String> params = pgConn.getParameterStatuses();
        System.out.println("Server Parameters:");
        for (Map.Entry<String, String> entry : params.entrySet()) {
            System.out.println("  " + entry.getKey() + " = " + entry.getValue());
        }

        // Get query mode
        System.out.println("Query mode: " + pgConn.getPreferQueryMode());

        // Get autosave setting
        System.out.println("Autosave: " + pgConn.getAutosave());

        conn.close();
    }
}

package org.postgresql;

import java.sql.DriverPropertyInfo;
import java.util.Properties;

/**
 * Enumeration of all connection properties supported by PostgreSQL JDBC driver.
 * Properties can be set in JDBC URL, Properties object, or DataSource setters.
 * Note: This enum contains 80+ properties; the most commonly used properties are shown below.
 */
public enum PGProperty {
    // Authentication properties
    USER("user", null, "Database user name", true),
    PASSWORD("password", null, "Database user password", false),

    // Connection properties
    PG_HOST("PGHOST", "localhost", "PostgreSQL server hostname", false),
    PG_PORT("PGPORT", "5432", "PostgreSQL server port", false),
    PG_DBNAME("PGDBNAME", null, "Database name", false),

    // SSL properties
    SSL("ssl", "false", "Enable SSL connection", false),
    SSL_MODE("sslmode", "prefer", "SSL mode: disable, allow, prefer, require, verify-ca, verify-full", false),
    SSL_FACTORY("sslfactory", null, "Custom SSL socket factory class", false),
    SSL_FACTORY_ARG("sslfactoryarg", null, "Argument for SSL factory", false),
    SSL_CERT("sslcert", null, "Client certificate file", false),
    SSL_KEY("sslkey", null, "Client key file", false),
    SSL_ROOT_CERT("sslrootcert", null, "Root certificate file", false),
    SSL_PASSWORD("sslpassword", null, "Password for encrypted client key", false),
    SSL_PASSWORD_CALLBACK("sslpasswordcallback", null, "SSL password callback class", false),
    SSL_HOSTNAME_VERIFIER("sslhostnameverifier", null, "Custom hostname verifier", false),

    // Timeout properties
    CONNECT_TIMEOUT("connectTimeout", "10", "Connection timeout in seconds", false),
    SOCKET_TIMEOUT("socketTimeout", "0", "Socket timeout in seconds (0 = no timeout)", false),
    LOGIN_TIMEOUT("loginTimeout", "0", "Login timeout in seconds", false),
    CANCEL_SIGNAL_TIMEOUT("cancelSignalTimeout", "10", "Query cancel signal timeout in seconds", false),

    // Performance properties
    PREPARE_THRESHOLD("prepareThreshold", "5", "Statement executions before server prepare", false),
    PREPARED_STATEMENT_CACHE_QUERIES("preparedStatementCacheQueries", "256",
            "Prepared statement cache size", false),
    PREPARED_STATEMENT_CACHE_SIZE_MIB("preparedStatementCacheSizeMiB", "5",
            "Prepared statement cache size in MiB", false),
    DEFAULT_ROW_FETCH_SIZE("defaultRowFetchSize", "0",
            "Default fetch size for ResultSets (0 = all rows)", false),
    BINARY_TRANSFER("binaryTransfer", "true", "Use binary format for applicable types", false),
    BINARY_TRANSFER_ENABLE("binaryTransferEnable", "",
            "Comma-separated list of OIDs to enable binary transfer", false),
    BINARY_TRANSFER_DISABLE("binaryTransferDisable", "",
            "Comma-separated list of OIDs to disable binary transfer", false),

    // Application properties
    APPLICATION_NAME("ApplicationName", "PostgreSQL JDBC Driver",
            "Application name for connection tracking", false),
    ASSUME_MIN_SERVER_VERSION("assumeMinServerVersion", null,
            "Assume minimum server version to skip version checks", false),

    // Behavior properties
    AUTOSAVE("autosave", "never", "Automatic savepoint mode: never, always, conservative", false),
    PREFER_QUERY_MODE("preferQueryMode", "extended",
            "Query mode: simple, extended, extendedForPrepared, extendedCacheEverything", false),
    REPLICATION("replication", null, "Enable replication protocol: true, database", false),
    STRING_TYPE("stringtype", "varchar", "String type mapping: varchar or unspecified", false),

    // Multi-host properties
    TARGET_SERVER_TYPE("targetServerType", "any",
            "Target server type: any, primary, secondary, preferSecondary", false),
    LOAD_BALANCE_HOSTS("loadBalanceHosts", "false",
            "Enable random host selection for load balancing", false),
    HOST_RECHECK_SECONDS("hostRecheckSeconds", "10",
            "Seconds between host status rechecks", false),

    // Authentication properties
    GSS_LIB("gsslib", "auto", "GSS library: auto, sspi, gssapi", false),
    SSPI_SERVICE_CLASS("sspiServiceClass", "POSTGRES",
            "SSPI service class for authentication", false),
    ALLOW_ENCODING_CHANGES("allowEncodingChanges", "false",
            "Allow client_encoding changes", false),
    LOG_UNCLOSED_CONNECTIONS("logUnclosedConnections", "false",
            "Log stack trace of unclosed connections", false),

    // Additional properties
    TCP_KEEP_ALIVE("tcpKeepAlive", "false", "Enable TCP keepalive", false),
    ADAPTIVE_FETCH("adaptiveFetch", "false", "Enable adaptive fetch size", false),
    LOGGER_LEVEL("loggerLevel", null, "Logger level: OFF, DEBUG, TRACE", false),
    LOGGER_FILE("loggerFile", null, "Log file location", false);

    private final String name;
    private final String defaultValue;
    private final String description;
    private final boolean required;

    /**
     * Gets the property name
     */
    public String getName();

    /**
     * Gets the default value for this property
     */
    public String getDefaultValue();

    /**
     * Gets the description of this property
     */
    public String getDescription();

    /**
     * Returns whether this property is required
     */
    public boolean isRequired();

    /**
     * Gets allowed choices for this property (if restricted)
     */
    public String[] getChoices();

    /**
     * Gets the property value or default from Properties
     */
    public String getOrDefault(Properties properties);

    /**
     * Sets the property value in Properties
     */
    public void set(Properties properties, String value);

    /**
     * Gets property value as boolean
     */
    public boolean getBoolean(Properties properties);

    /**
     * Gets property value as int
     */
    public int getInt(Properties properties);

    /**
     * Checks if property is present in Properties
     */
    public boolean isPresent(Properties properties);

    /**
     * Converts to JDBC DriverPropertyInfo
     */
    public DriverPropertyInfo toDriverPropertyInfo(Properties properties);

    /**
     * Looks up a property by name
     */
    public static PGProperty forName(String name);
}

import org.postgresql.PGProperty;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

// Example: Configure connection with various properties
public class ConnectionConfiguration {
    public static Connection getConfiguredConnection() throws SQLException {
        String url = "jdbc:postgresql://localhost:5432/mydb";
        Properties props = new Properties();

        // Authentication
        PGProperty.USER.set(props, "postgres");
        PGProperty.PASSWORD.set(props, "secret");

        // SSL
        PGProperty.SSL.set(props, "true");
        PGProperty.SSL_MODE.set(props, "require");

        // Timeouts
        PGProperty.CONNECT_TIMEOUT.set(props, "10");
        PGProperty.SOCKET_TIMEOUT.set(props, "30");

        // Performance
        PGProperty.PREPARE_THRESHOLD.set(props, "5");
        PGProperty.DEFAULT_ROW_FETCH_SIZE.set(props, "100");
        PGProperty.BINARY_TRANSFER.set(props, "true");

        // Application tracking
        PGProperty.APPLICATION_NAME.set(props, "MyApplication");

        // Autosave
        PGProperty.AUTOSAVE.set(props, "conservative");

        return DriverManager.getConnection(url, props);
    }

    // Get property value
    public static void checkProperty() {
        Properties props = new Properties();
        PGProperty.PREPARE_THRESHOLD.set(props, "10");

        int threshold = PGProperty.PREPARE_THRESHOLD.getInt(props);
        System.out.println("Prepare threshold: " + threshold);

        String defaultValue = PGProperty.PREPARE_THRESHOLD.getDefaultValue();
        System.out.println("Default threshold: " + defaultValue);
    }
}

jdbc:postgresql://host1:port1,host2:port2,host3:port3/database?properties

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

// Example 1: Basic failover configuration
public class FailoverConnection {
    public static Connection getConnection() throws SQLException {
        // Driver will try hosts in order until successful connection
        String url = "jdbc:postgresql://primary:5432,standby1:5432,standby2:5432/mydb"
                   + "?user=postgres&password=secret";
        return DriverManager.getConnection(url);
    }
}

// Example 2: Connect to primary server only
public class PrimaryConnection {
    public static Connection getConnection() throws SQLException {
        String url = "jdbc:postgresql://host1:5432,host2:5432,host3:5432/mydb";
        Properties props = new Properties();
        props.setProperty("user", "postgres");
        props.setProperty("password", "secret");
        props.setProperty("targetServerType", "primary");
        return DriverManager.getConnection(url, props);
    }
}

// Example 3: Load balancing across read replicas
public class LoadBalancedConnection {
    public static Connection getConnection() throws SQLException {
        String url = "jdbc:postgresql://replica1:5432,replica2:5432,replica3:5432/mydb";
        Properties props = new Properties();
        props.setProperty("user", "postgres");
        props.setProperty("password", "secret");
        props.setProperty("targetServerType", "secondary");
        props.setProperty("loadBalanceHosts", "true"); // Random selection
        return DriverManager.getConnection(url, props);
    }
}

// Example 4: Prefer secondary, fallback to primary
public class PreferSecondaryConnection {
    public static Connection getConnection() throws SQLException {
        String url = "jdbc:postgresql://primary:5432,secondary:5432/mydb";
        Properties props = new Properties();
        props.setProperty("user", "postgres");
        props.setProperty("password", "secret");
        props.setProperty("targetServerType", "preferSecondary");
        return DriverManager.getConnection(url, props);
    }
}

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;

// Example 1: Basic connection validation
public class ConnectionValidation {
    public static boolean isConnectionValid(Connection conn) {
        if (conn == null) {
            return false;
        }

        try {
            // JDBC 4.0+ method with timeout
            return conn.isValid(5); // 5 second timeout
        } catch (SQLException e) {
            return false;
        }
    }

    // Alternative validation with simple query
    public static boolean isConnectionValidWithQuery(Connection conn) {
        if (conn == null || conn.isClosed()) {
            return false;
        }

        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT 1")) {
            return rs.next();
        } catch (SQLException e) {
            return false;
        }
    }
}

// Example 2: Connection pool validation query
public class PoolValidation {
    public static final String VALIDATION_QUERY = "SELECT 1";

    public static boolean validateConnection(Connection conn) {
        try (Statement stmt = conn.createStatement()) {
            stmt.setQueryTimeout(5); // 5 second timeout
            try (ResultSet rs = stmt.executeQuery(VALIDATION_QUERY)) {
                return rs.next();
            }
        } catch (SQLException e) {
            return false;
        }
    }
}