Statement Execution

package org.postgresql;

import java.sql.Statement;
import java.sql.SQLException;

/**
 * PostgreSQL extensions to java.sql.Statement.
 * All Statement objects returned by PostgreSQL connections implement this interface.
 */
public interface PGStatement extends Statement {
    /**
     * Constant representing positive infinity for date/timestamp values.
     * Corresponds to PostgreSQL's 'infinity' date value.
     */
    long DATE_POSITIVE_INFINITY = 9223372036825200000L;

    /**
     * Constant representing negative infinity for date/timestamp values.
     * Corresponds to PostgreSQL's '-infinity' date value.
     */
    long DATE_NEGATIVE_INFINITY = -9223372036832400000L;

    /**
     * Smaller positive infinity value for compatibility.
     */
    long DATE_POSITIVE_SMALLER_INFINITY = 185543533774800000L;

    /**
     * Smaller negative infinity value for compatibility.
     */
    long DATE_NEGATIVE_SMALLER_INFINITY = -185543533774800000L;

    /**
     * Returns the OID of the last row inserted by this statement.
     * Only valid for INSERT statements that inserted exactly one row.
     *
     * @return OID of last inserted row, or 0 if not applicable
     * @throws SQLException if statement has not been executed
     */
    long getLastOID() throws SQLException;

    /**
     * Sets the threshold for when to use server-side prepared statements.
     * The statement will be prepared on the server after it has been executed
     * this many times.
     *
     * @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)
     * @throws SQLException if threshold cannot be set
     */
    void setPrepareThreshold(int threshold) throws SQLException;

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

    /**
     * Turn on the use of prepared statements in the server.
     * Server-side prepared statements are unrelated to JDBC PreparedStatements.
     * As of build 302, this method is equivalent to setPrepareThreshold(1).
     *
     * @param flag use server prepare
     * @throws SQLException if something goes wrong
     * @deprecated As of build 302, replaced by setPrepareThreshold(int)
     */
    @Deprecated
    void setUseServerPrepare(boolean flag) throws SQLException;

    /**
     * Returns whether server-side prepare will be used for this statement.
     * A return value of true indicates that the next execution will use
     * a server-prepared statement, assuming the protocol supports it.
     *
     * @return true if next reuse will use server prepare
     */
    boolean isUseServerPrepare();

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

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

import org.postgresql.PGStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

// Example 1: Basic Statement usage
public class BasicStatementExample {
    public static void executeQuery(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            // Execute a simple query
            try (ResultSet rs = stmt.executeQuery("SELECT id, name FROM users")) {
                while (rs.next()) {
                    System.out.println(rs.getInt("id") + ": " + rs.getString("name"));
                }
            }
        }
    }
}

// Example 2: Get last inserted OID
public class OIDExample {
    public static void insertAndGetOID(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            // Insert a row
            int rowsAffected = stmt.executeUpdate(
                "INSERT INTO documents (title, content) VALUES ('Doc1', 'Content')");

            // Get OID of inserted row (only works if table has OIDs)
            PGStatement pgStmt = stmt.unwrap(PGStatement.class);
            long oid = pgStmt.getLastOID();
            System.out.println("Inserted row OID: " + oid);
        }
    }
}

// Example 3: Configure prepare threshold
public class PrepareThresholdExample {
    public static void configurePrepare(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            PGStatement pgStmt = stmt.unwrap(PGStatement.class);

            // Set to prepare immediately
            pgStmt.setPrepareThreshold(0);

            // Or never prepare (always use simple protocol)
            // pgStmt.setPrepareThreshold(-1);

            // Execute query
            try (ResultSet rs = stmt.executeQuery("SELECT * FROM users")) {
                // process results
            }
        }
    }
}

// Example 4: Statement with fetch size
public class FetchSizeExample {
    public static void queryWithFetchSize(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            // Set fetch size to retrieve rows in batches of 100
            stmt.setFetchSize(100);

            // Execute large query
            try (ResultSet rs = stmt.executeQuery("SELECT * FROM large_table")) {
                while (rs.next()) {
                    // Process rows - driver fetches 100 at a time
                }
            }
        }
    }
}

// Example 5: Batch updates
public class BatchUpdateExample {
    public static void batchInsert(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            // Add multiple statements to batch
            stmt.addBatch("INSERT INTO logs (message) VALUES ('Log 1')");
            stmt.addBatch("INSERT INTO logs (message) VALUES ('Log 2')");
            stmt.addBatch("INSERT INTO logs (message) VALUES ('Log 3')");

            // Execute all at once
            int[] results = stmt.executeBatch();
            System.out.println("Inserted " + results.length + " rows");
        }
    }
}

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Date;
import java.sql.Timestamp;
import java.sql.Array;
import java.io.InputStream;
import java.io.Reader;

/**
 * PreparedStatement represents a precompiled SQL statement.
 * PostgreSQL driver implements full JDBC PreparedStatement interface
 * with extensions from PGStatement.
 */
public interface PreparedStatement extends Statement {
    /**
     * Executes the SQL query and returns a ResultSet.
     *
     * @return ResultSet containing query results
     * @throws SQLException if execution fails
     */
    ResultSet executeQuery() throws SQLException;

    /**
     * Executes an SQL INSERT, UPDATE, or DELETE statement.
     *
     * @return Number of rows affected
     * @throws SQLException if execution fails
     */
    int executeUpdate() throws SQLException;

    /**
     * Executes any SQL statement.
     *
     * @return true if result is a ResultSet, false if update count or no result
     * @throws SQLException if execution fails
     */
    boolean execute() throws SQLException;

    // Parameter setters

    /**
     * Sets a parameter to SQL NULL.
     *
     * @param parameterIndex Parameter index (1-based)
     * @param sqlType SQL type code from java.sql.Types
     * @throws SQLException if index is invalid
     */
    void setNull(int parameterIndex, int sqlType) throws SQLException;

    /**
     * Sets a boolean parameter.
     */
    void setBoolean(int parameterIndex, boolean x) throws SQLException;

    /**
     * Sets a byte parameter.
     */
    void setByte(int parameterIndex, byte x) throws SQLException;

    /**
     * Sets a short parameter.
     */
    void setShort(int parameterIndex, short x) throws SQLException;

    /**
     * Sets an int parameter.
     */
    void setInt(int parameterIndex, int x) throws SQLException;

    /**
     * Sets a long parameter.
     */
    void setLong(int parameterIndex, long x) throws SQLException;

    /**
     * Sets a float parameter.
     */
    void setFloat(int parameterIndex, float x) throws SQLException;

    /**
     * Sets a double parameter.
     */
    void setDouble(int parameterIndex, double x) throws SQLException;

    /**
     * Sets a BigDecimal parameter.
     */
    void setBigDecimal(int parameterIndex, java.math.BigDecimal x) throws SQLException;

    /**
     * Sets a String parameter.
     */
    void setString(int parameterIndex, String x) throws SQLException;

    /**
     * Sets a byte array parameter.
     */
    void setBytes(int parameterIndex, byte[] x) throws SQLException;

    /**
     * Sets a Date parameter.
     */
    void setDate(int parameterIndex, Date x) throws SQLException;

    /**
     * Sets a Time parameter.
     */
    void setTime(int parameterIndex, java.sql.Time x) throws SQLException;

    /**
     * Sets a Timestamp parameter.
     */
    void setTimestamp(int parameterIndex, Timestamp x) throws SQLException;

    /**
     * Sets a parameter from an InputStream (for large text/binary data).
     */
    void setBinaryStream(int parameterIndex, InputStream x, int length)
            throws SQLException;

    /**
     * Sets a parameter from a Reader (for large character data).
     */
    void setCharacterStream(int parameterIndex, Reader reader, int length)
            throws SQLException;

    /**
     * Sets an Object parameter with automatic type mapping.
     */
    void setObject(int parameterIndex, Object x) throws SQLException;

    /**
     * Sets an Object parameter with target SQL type.
     */
    void setObject(int parameterIndex, Object x, int targetSqlType)
            throws SQLException;

    /**
     * Sets an Array parameter.
     */
    void setArray(int parameterIndex, Array x) throws SQLException;

    /**
     * Clears all parameter values.
     */
    void clearParameters() throws SQLException;

    /**
     * Adds a set of parameters to the batch.
     * Call after setting all parameters for one execution.
     */
    void addBatch() throws SQLException;
}

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Date;
import java.sql.Timestamp;

// Example 1: Basic PreparedStatement
public class BasicPreparedStatement {
    public static void queryWithParameters(Connection conn) throws SQLException {
        String sql = "SELECT id, name, email FROM users WHERE active = ? AND created_date > ?";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            // Set parameters (1-based indexing)
            pstmt.setBoolean(1, true);
            pstmt.setDate(2, Date.valueOf("2024-01-01"));

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    System.out.println(rs.getInt("id") + ": " + rs.getString("name"));
                }
            }
        }
    }
}

// Example 2: INSERT with PreparedStatement
public class InsertExample {
    public static void insertUser(Connection conn, String name, String email)
            throws SQLException {
        String sql = "INSERT INTO users (name, email, created_at) VALUES (?, ?, ?)";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, name);
            pstmt.setString(2, email);
            pstmt.setTimestamp(3, new Timestamp(System.currentTimeMillis()));

            int rowsAffected = pstmt.executeUpdate();
            System.out.println("Inserted " + rowsAffected + " row(s)");
        }
    }
}

// Example 3: Batch INSERT with PreparedStatement
public class BatchInsertExample {
    public static void batchInsert(Connection conn, List<User> users)
            throws SQLException {
        String sql = "INSERT INTO users (name, email) VALUES (?, ?)";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            for (User user : users) {
                pstmt.setString(1, user.getName());
                pstmt.setString(2, user.getEmail());
                pstmt.addBatch(); // Add to batch
            }

            // Execute all inserts at once
            int[] results = pstmt.executeBatch();
            System.out.println("Batch inserted " + results.length + " rows");
        }
    }
}

// Example 4: Handling NULL values
public class NullHandlingExample {
    public static void insertWithNulls(Connection conn) throws SQLException {
        String sql = "INSERT INTO products (name, description, price) VALUES (?, ?, ?)";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, "Product A");
            pstmt.setNull(2, java.sql.Types.VARCHAR); // NULL description
            pstmt.setBigDecimal(3, new java.math.BigDecimal("19.99"));

            pstmt.executeUpdate();
        }
    }
}

// Example 5: Reusing PreparedStatement
public class ReusePreparedStatement {
    public static void insertMultiple(Connection conn) throws SQLException {
        String sql = "INSERT INTO logs (level, message) VALUES (?, ?)";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            // First execution
            pstmt.setString(1, "INFO");
            pstmt.setString(2, "Application started");
            pstmt.executeUpdate();

            // Clear and set new values
            pstmt.clearParameters();
            pstmt.setString(1, "DEBUG");
            pstmt.setString(2, "Debug message");
            pstmt.executeUpdate();
        }
    }
}

// Example 6: Binary data
public class BinaryDataExample {
    public static void insertBinaryData(Connection conn, byte[] imageData)
            throws SQLException {
        String sql = "INSERT INTO images (name, data) VALUES (?, ?)";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, "photo.jpg");
            pstmt.setBytes(2, imageData);
            pstmt.executeUpdate();
        }
    }
}

// Example 7: PostgreSQL arrays
public class ArrayExample {
    public static void insertArray(Connection conn) throws SQLException {
        String sql = "INSERT INTO documents (tags) VALUES (?)";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            // Create PostgreSQL array
            String[] tags = {"java", "postgresql", "jdbc"};
            Array sqlArray = conn.createArrayOf("text", tags);

            pstmt.setArray(1, sqlArray);
            pstmt.executeUpdate();

            sqlArray.free();
        }
    }
}

import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Types;

/**
 * CallableStatement is used to execute stored procedures and functions.
 * PostgreSQL driver provides full support for calling database functions.
 */
public interface CallableStatement extends PreparedStatement {
    /**
     * Registers an OUT parameter for a stored procedure.
     *
     * @param parameterIndex Parameter index (1-based)
     * @param sqlType SQL type code from java.sql.Types
     * @throws SQLException if registration fails
     */
    void registerOutParameter(int parameterIndex, int sqlType) throws SQLException;

    /**
     * Registers an OUT parameter with specific type name.
     *
     * @param parameterIndex Parameter index
     * @param sqlType SQL type code
     * @param typeName PostgreSQL type name
     * @throws SQLException if registration fails
     */
    void registerOutParameter(int parameterIndex, int sqlType, String typeName)
            throws SQLException;

    /**
     * Indicates whether the last OUT parameter read had value SQL NULL.
     *
     * @return true if last parameter was NULL
     * @throws SQLException if check fails
     */
    boolean wasNull() throws SQLException;

    /**
     * Retrieves a String OUT parameter.
     */
    String getString(int parameterIndex) throws SQLException;

    /**
     * Retrieves a boolean OUT parameter.
     */
    boolean getBoolean(int parameterIndex) throws SQLException;

    /**
     * Retrieves an int OUT parameter.
     */
    int getInt(int parameterIndex) throws SQLException;

    /**
     * Retrieves a long OUT parameter.
     */
    long getLong(int parameterIndex) throws SQLException;

    /**
     * Retrieves a double OUT parameter.
     */
    double getDouble(int parameterIndex) throws SQLException;

    /**
     * Retrieves an Object OUT parameter.
     */
    Object getObject(int parameterIndex) throws SQLException;

    /**
     * Retrieves an Array OUT parameter.
     */
    java.sql.Array getArray(int parameterIndex) throws SQLException;
}

import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.Types;
import java.sql.SQLException;

// Example 1: Call function with return value
public class FunctionCallExample {
    public static void callFunction(Connection conn) throws SQLException {
        // PostgreSQL function: CREATE FUNCTION get_user_count() RETURNS integer
        String sql = "{ ? = call get_user_count() }";

        try (CallableStatement cstmt = conn.prepareCall(sql)) {
            // Register OUT parameter for return value
            cstmt.registerOutParameter(1, Types.INTEGER);

            // Execute
            cstmt.execute();

            // Get return value
            int count = cstmt.getInt(1);
            System.out.println("User count: " + count);
        }
    }
}

// Example 2: Call function with IN and OUT parameters
public class InOutParametersExample {
    public static void callWithParameters(Connection conn) throws SQLException {
        // Function: CREATE FUNCTION calculate_discount(price numeric, OUT discount numeric)
        String sql = "{ call calculate_discount(?, ?) }";

        try (CallableStatement cstmt = conn.prepareCall(sql)) {
            // Set IN parameter
            cstmt.setBigDecimal(1, new java.math.BigDecimal("100.00"));

            // Register OUT parameter
            cstmt.registerOutParameter(2, Types.NUMERIC);

            // Execute
            cstmt.execute();

            // Get OUT parameter
            java.math.BigDecimal discount = cstmt.getBigDecimal(2);
            System.out.println("Discount: " + discount);
        }
    }
}

// Example 3: Call function returning composite type
public class CompositeReturnExample {
    public static void callReturningComposite(Connection conn) throws SQLException {
        // Function returning user record
        String sql = "SELECT * FROM get_user_by_id(?)";

        try (CallableStatement cstmt = conn.prepareCall(sql)) {
            cstmt.setInt(1, 123);

            try (ResultSet rs = cstmt.executeQuery()) {
                if (rs.next()) {
                    String name = rs.getString("name");
                    String email = rs.getString("email");
                    System.out.println(name + ": " + email);
                }
            }
        }
    }
}

// Example 4: Call procedure (PostgreSQL 11+)
public class ProcedureCallExample {
    public static void callProcedure(Connection conn) throws SQLException {
        // Procedure: CREATE PROCEDURE transfer_funds(from_id int, to_id int, amount numeric)
        String sql = "{ call transfer_funds(?, ?, ?) }";

        try (CallableStatement cstmt = conn.prepareCall(sql)) {
            cstmt.setInt(1, 100); // from_id
            cstmt.setInt(2, 200); // to_id
            cstmt.setBigDecimal(3, new java.math.BigDecimal("50.00")); // amount

            cstmt.execute();
            System.out.println("Funds transferred");
        }
    }
}

package org.postgresql.jdbc;

/**
 * Query execution mode options.
 * Controls which PostgreSQL protocol is used for query execution.
 *
 * Note: Invalid mode values default to EXTENDED.
 */
public enum PreferQueryMode {
    /**
     * Use simple query protocol (text-only).
     * Sends queries as plain text, receives results as text.
     * Does not support binary transfer or certain features.
     */
    SIMPLE,

    /**
     * Always use extended query protocol.
     * Supports binary transfer, prepared statements, and all features.
     * Slightly more overhead than simple protocol.
     * This is the default fallback for invalid/unknown mode values.
     */
    EXTENDED,

    /**
     * Use extended protocol only for prepared statements.
     * Simple statements use simple protocol.
     * Default mode providing good balance of features and performance.
     */
    EXTENDED_FOR_PREPARED,

    /**
     * Extended protocol with aggressive caching.
     * Caches all statement plans, even for simple statements.
     * Provides best performance for repeated queries.
     */
    EXTENDED_CACHE_EVERYTHING;

    /**
     * Returns the string value of this mode for use in connection properties.
     *
     * @return String value (e.g., "simple", "extended", "extendedForPrepared", "extendedCacheEverything")
     */
    public String value();

    /**
     * Parses a string mode value and returns the corresponding enum constant.
     *
     * @param mode String mode value
     * @return PreferQueryMode enum constant, or EXTENDED if mode is invalid/unknown
     */
    public static PreferQueryMode of(String mode);
}

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

public class QueryModeExample {
    public static Connection getConnectionWithMode(String mode) throws SQLException {
        Properties props = new Properties();
        props.setProperty("user", "postgres");
        props.setProperty("password", "secret");
        props.setProperty("preferQueryMode", mode); // simple, extended, etc.

        return DriverManager.getConnection("jdbc:postgresql://localhost/mydb", props);
    }
}