### Example Output with include-xids=false

Source: https://jdbc.postgresql.org/documentation/server-prepare

Demonstrates the expected output format from a PostgreSQL replication stream when the 'include-xids' option is set to false.

```sql
BEGIN
table public.test_logic_table: INSERT: pk[integer]:1 name[character varying]:'previous value'
COMMIT


```

--------------------------------

### Start Physical Replication Stream

Source: https://jdbc.postgresql.org/documentation/server-prepare

Initiates a physical replication stream from a specified log sequence number. Ensure you have a valid connection and have performed initial setup.

```java
LogSequenceNumber lsn = getCurrentLSN();

Statement st = sqlConnection.createStatement();
st.execute("insert into test_physic_table(name) values('previous value')");
st.close();

PGReplicationStream stream = 
    pgConnection
    .getReplicationAPI()
    .replicationStream()
    .physical()
    .withStartPosition(lsn)
    .start();

ByteBuffer read = stream.read();

```

--------------------------------

### Example Output with include-xids=true

Source: https://jdbc.postgresql.org/documentation/server-prepare

Demonstrates the format of decoded WAL changes when the 'include-xids' option is enabled for the output plugin.

```text
BEGIN 105779
table public.test_logic_table: INSERT: pk[integer]:1 name[character varying]:'previous value'
COMMIT 105779
```

--------------------------------

### pg_hba.conf Replication Access Example

Source: https://jdbc.postgresql.org/documentation/server-prepare

An example configuration for pg_hba.conf to permit replication connections from localhost using MD5 authentication.

```postgresql
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication   all                   trust
host    replication   all   127.0.0.1/32    md5
host    replication   all   ::1/128         md5
```

--------------------------------

### ConnectionPoolDataSource Configuration Properties Example

Source: https://jdbc.postgresql.org/documentation/datasource

This example shows how ConnectionPoolDataSource properties might be configured in an application server using a properties-style syntax with newlines as separators. Ensure these properties match your PostgreSQL server configuration.

```properties
serverName=localhost
databaseName=test
user=testuser
password=testpassword
```

--------------------------------

### Example: Using Server-Side Prepared Statements

Source: https://jdbc.postgresql.org/documentation/server-prepare

Demonstrates how to use server-side prepared statements with the PostgreSQL JDBC driver. It shows how to set a prepare threshold and check if server-side preparation is being used.

```java
import java.sql.*;

public class ServerSidePreparedStatement {

    public static void main(String args[]) throws Exception {
        
        String url = "jdbc:postgresql://localhost:5432/test";
        try (Connection conn = DriverManager.getConnection(url, "test", "")){

            try (PreparedStatement pstmt = conn.prepareStatement("SELECT ?")){

                // cast to the pg extension interface
                org.postgresql.PGStatement pgstmt = pstmt.unwrap(org.postgresql.PGStatement.class);

                // on the third execution start using server side statements
                pgstmt.setPrepareThreshold(3);

                for (int i = 1; i <= 5; i++) {
                    pstmt.setInt(1, i);
                    boolean usingServerPrepare = pgstmt.isUseServerPrepare();
                    ResultSet rs = pstmt.executeQuery();
                    rs.next();
                    System.out.println("Execution: " + i + ", Used server side: " + usingServerPrepare + ", Result: " + rs.getInt(1));
                    rs.close();
                }
            }        
        }
    }
}
```

--------------------------------

### ConnectionPoolDataSource Configuration Properties Example (Semicolon Separated)

Source: https://jdbc.postgresql.org/documentation/datasource

This example demonstrates an alternative properties-style syntax for configuring ConnectionPoolDataSource properties, using semicolons as separators. This format may be used when a single input area is provided for all properties.

```properties
serverName=localhost;databaseName=test;user=testuser;password=testpassword
```

--------------------------------

### Output Plugin Prepare and Write Example

Source: https://jdbc.postgresql.org/documentation/server-prepare

Illustrates the process of preparing and writing data using an output plugin within a PostgreSQL replication context. This is typically part of the server-side logic for data streaming.

```c
OutputPluginPrepareWrite(ctx, true);
appendStringInfo(ctx->out, "BEGIN %u", txn->xid);
OutputPluginWrite(ctx, true);
```

--------------------------------

### Example of creating a PKCS-12 key

Source: https://jdbc.postgresql.org/documentation/use

This command creates a PKCS-12 key file, which is recognized by the driver if it has a .p12 or .pfx extension. It includes options for specifying the certificate, key, name, CA file, and password.

```bash
openssl pkcs12 -export -in $< -inkey $*.key -out $@ -name user -CAfile $(SERVER_CRT_DIR)root.crt -caname local -passout pass:$(P12_PASSWORD)
```

--------------------------------

### String Function Translation Example (locate)

Source: https://jdbc.postgresql.org/documentation/escapes

Shows the translation for the 'locate' string function. The three-argument version is not supported due to argument order changes.

```sql
position(arg1 in arg2)
```

--------------------------------

### String Function Translation Example (right)

Source: https://jdbc.postgresql.org/documentation/escapes

Demonstrates the translation for the 'right' string function. It's noted as not supported in its original form due to argument duplication in translation.

```sql
substring(arg1 from (length(arg1)+1-arg2))
```

--------------------------------

### String Function Translation Example (soundex)

Source: https://jdbc.postgresql.org/documentation/escapes

Indicates that the 'soundex' string function is not supported by the PostgreSQL driver as it requires the fuzzystrmatch contrib module.

```sql
soundex(arg1)
```

--------------------------------

### Receiving Notifications with PostgreSQL JDBC

Source: https://jdbc.postgresql.org/documentation/server-prepare

This example demonstrates how to set up a listener and a notifier to communicate using PostgreSQL's LISTEN and NOTIFY commands. It requires establishing two connections, one for listening and one for notifying, and uses separate threads for each.

```java
import java.sql.*;

public class NotificationTest {
    public static void main(String args[]) throws Exception {
        Class.forName("org.postgresql.Driver");
        String url = "jdbc:postgresql://localhost:5432/test";

        // Create two distinct connections, one for the notifier
        // and another for the listener to show the communication
        // works across connections although this example would
        // work fine with just one connection.

        Connection lConn = DriverManager.getConnection(url, "test", "");
        Connection nConn = DriverManager.getConnection(url, "test", "");

        // Create two threads, one to issue notifications and
        // the other to receive them.

        Listener listener = new Listener(lConn);
        Notifier notifier = new Notifier(nConn);
        listener.start();
        notifier.start();
    }
}

class Listener extends Thread {
    private Connection conn;
    private org.postgresql.PGConnection pgconn;

    Listener(Connection conn) throws SQLException {
        this.conn = conn;
        this.pgconn = conn.unwrap(org.postgresql.PGConnection.class);
        Statement stmt = conn.createStatement();
        stmt.execute("LISTEN mymessage");
        stmt.close();
    }

    public void run() {
        try {
            while (true) {
                org.postgresql.PGNotification notifications[] = pgconn.getNotifications();

                // If this thread is the only one that uses the connection, a timeout can be used to
                // receive notifications immediately:
                // org.postgresql.PGNotification notifications[] = pgconn.getNotifications(10000);

                for (int i = 0; i < notifications.length; i++) {
                    System.out.println("Got notification: " + notifications[i].getName());
                }

                // wait a while before checking again for new
                // notifications

                Thread.sleep(500);
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        } catch (InterruptedException ie) {
            ie.printStackTrace();
        }
    }
}

class Notifier extends Thread {
    private Connection conn;

    public Notifier(Connection conn) {
        this.conn = conn;
    }

    public void run() {
        while (true) {
            try {
                Statement stmt = conn.createStatement();
                stmt.execute("NOTIFY mymessage");
                stmt.close();
                Thread.sleep(2000);
            } catch (SQLException sqle) {
                sqle.printStackTrace();
            } catch (InterruptedException ie) {
                ie.printStackTrace();
            }
        }
    }
}

```

--------------------------------

### Set Classpath and Run Java Application

Source: https://jdbc.postgresql.org/documentation/setup

Include the JDBC driver JAR in the classpath to run a Java application. This example adds the application JAR and the PostgreSQL JDBC driver JAR to the CLASSPATH environment variable.

```bash
export CLASSPATH=/usr/local/lib/myapp.jar:/usr/local/pgsql/share/java/postgresql-42.5.0.jar:.
java MyApp
```

--------------------------------

### String Function Translation Example (concat)

Source: https://jdbc.postgresql.org/documentation/escapes

Demonstrates the translation of the concat string function. Note that the PostgreSQL driver uses the '|' operator for concatenation.

```sql
(arg1 |
```

--------------------------------

### String Function Translation Example (difference)

Source: https://jdbc.postgresql.org/documentation/escapes

Indicates that the 'difference' string function is not supported by the PostgreSQL driver as it requires the fuzzystrmatch contrib module.

```sql
difference(arg1, arg2)
```

--------------------------------

### String Function Translation Example (insert)

Source: https://jdbc.postgresql.org/documentation/escapes

Illustrates the translation of the insert string function, which is not directly supported and is mapped to 'overlay'. This mapping can change argument order, posing issues for prepared statements.

```sql
overlay(arg1 placing arg4 from arg2 for arg3)
```

--------------------------------

### Obtain and Use a Connection from a Pooling DataSource

Source: https://jdbc.postgresql.org/documentation/datasource

This example shows how to obtain a database connection from a pooling DataSource and use it within a try-with-resources block to ensure proper closure. It is critical to close connections to prevent pool leaks.

```java
try (Connection conn = source.getConnection()) {
    // use connection
} catch (SQLException e) {
    // log error
}
```

--------------------------------

### Accessing PostgreSQL Datasource in Java

Source: https://jdbc.postgresql.org/documentation/datasource

Java code to look up and use a configured PostgreSQL datasource in a Tomcat environment. This example demonstrates fetching data from a 'testdata' table.

```java
import javax.naming.*;
import javax.sql.*;
import java.sql.*;
public class DBTest {

    String foo = "Not Connected";
    int bar = -1;

    public void init() {
        try {
            Context ctx = new InitialContext();
            if (ctx == null)
                throw new Exception("Boom - No Context");

            // /jdbc/postgres is the name of the resource above
            DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/postgres");

            if (ds != null) {
                Connection conn = ds.getConnection();

                if (conn != null) {
                    foo = "Got Connection " + conn.toString();
                    Statement stmt = conn.createStatement();
                    ResultSet rst = stmt.executeQuery("select id, foo, bar from testdata");

                    if (rst.next()) {
                        foo = rst.getString(2);
                        bar = rst.getInt(3);
                    }
                    conn.close();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public String getFoo() {
        return foo;
    }

    public int getBar() {
        return bar;
    }
}
```

--------------------------------

### Native SQL Equivalent for JDBC Function Escape

Source: https://jdbc.postgresql.org/documentation/escapes

This shows the native SQL equivalent for the JDBC function escape example, demonstrating the translation performed by the JDBC driver.

```sql
SELECT extract(week from DATE '2005-01-24')
```

--------------------------------

### Create Logical Replication Stream from Specific Position

Source: https://jdbc.postgresql.org/documentation/server-prepare

Starts a logical replication stream from a specified Log Sequence Number (LSN) position, '6F/E3C53568', with custom slot options.

```java
LogSequenceNumber waitLSN = LogSequenceNumber.valueOf("6F/E3C53568");

PGReplicationStream stream = 
    replConnection.getReplicationAPI()
        .replicationStream()
        .logical()
        .withSlotName("demo_logical_slot")
        .withSlotOption("include-xids", false)
        .withSlotOption("skip-empty-xacts", true)
        .withStartPosition(waitLSN)
        .start();
```

--------------------------------

### Prepared Statement with Escaped Function (Potential Issue)

Source: https://jdbc.postgresql.org/documentation/escapes

Example of using an escaped function with a prepared statement. Note that translation might alter the number of parameters required, causing issues.

```sql
{fn right(?,?)}
```

--------------------------------

### Example of converting PEM key to DER format

Source: https://jdbc.postgresql.org/documentation/use

Use the openssl command to convert a PEM private key to DER format for use with the PostgreSQL JDBC driver. Ensure the alias or name is set to 'user'.

```bash
openssl pkcs8 -topk8 -inform PEM -in postgresql.key -outform DER -out postgresql.pk8 -v1 PBE-MD5-DES
```

--------------------------------

### Connect to Database with URL Parameters

Source: https://jdbc.postgresql.org/documentation/use

Establish an SSL connection by including user, password, and SSL settings directly in the connection URL. The driver automatically loads when connecting.

```java
String url = "jdbc:postgresql://localhost/test?user=fred&password=secret&ssl=true";
Connection conn = DriverManager.getConnection(url);

```

--------------------------------

### Connect to Database with Properties Object

Source: https://jdbc.postgresql.org/documentation/use

Establish an SSL connection by providing user, password, and SSL settings in a Properties object. The driver automatically loads when connecting.

```java
String url = "jdbc:postgresql://localhost/test";
Properties props = new Properties();
props.setProperty("user", "fred");
props.setProperty("password", "secret");
props.setProperty("ssl", "true");
Connection conn = DriverManager.getConnection(url, props);

```

--------------------------------

### Get refcursor Value From a Function

Source: https://jdbc.postgresql.org/documentation/callproc

Call a PostgreSQL function that returns a refcursor and process its results as a ResultSet. Ensure you are within a transaction.

```java
Statement stmt = conn.createStatement();
stmt.execute("CREATE OR REPLACE FUNCTION refcursorfunc() RETURNS refcursor AS '" +
    " DECLARE " +
    "    mycurs refcursor; " +
    " BEGIN " +
    "    OPEN mycurs FOR SELECT 1 UNION SELECT 2; " +
    "    RETURN mycurs; " +
    " END;'");
stmt.close();

conn.setAutoCommit(false);

CallableStatement func = conn.prepareCall("{? = call refcursorfunc() }");
func.registerOutParameter(1, Types.OTHER);
func.execute();
ResultSet results = (ResultSet) func.getObject(1);
while (results.next()) {
    // do something with the results.
}
results.close();
func.close();

```

--------------------------------

### Basic PreparedStatement Usage

Source: https://jdbc.postgresql.org/documentation/server-prepare

Demonstrates the standard usage of PreparedStatement where each execution involves preparing and executing the statement separately. The driver can still optimize this using server-side prepared statements.

```java
PreparedStatement ps = con.prepareStatement("select /*test*/ ?::int4");
ps.setInt(1, 42);
ps.executeQuery().close();
ps.close();

PreparedStatement ps = con.prepareStatement("select /*test*/ ?::int4");
ps.setInt(1, 43);
ps.executeQuery().close();
ps.close();

```

--------------------------------

### Create Custom Truststore

Source: https://jdbc.postgresql.org/documentation/ssl

Use this command to import a server certificate into a new or existing Java keystore. This is necessary if you don't have access to the system's default cacerts.

```bash
keytool -keystore mystore -alias postgresql -import -file server.crt.der
```

--------------------------------

### Configure Client Logging with java.util.logging

Source: https://jdbc.postgresql.org/documentation/server-prepare

Set up a logging.properties file to configure the java.util.logging framework for detailed driver tracing. This helps in debugging communication between the driver and the PostgreSQL backend.

```properties
handlers=java.util.logging.FileHandler
.level= INFO

java.util.logging.FileHandler.level=FINEST
java.util.logging.FileHandler.formatter=java.util.logging.SimpleFormatter
java.util.logging.FileHandler.pattern=/tmp/debug.log

java.util.logging.ConsoleHandler.level = INFO
java.util.logging.ConsoleHandler.formatter = java.util.logging.SimpleFormatter
org.postgresql.level = FINEST
```

--------------------------------

### Get Application Name from PGConnection

Source: https://jdbc.postgresql.org/documentation/server-prepare

Access the application name parameter from an existing java.sql.Connection object by casting it to PGConnection. This is useful for monitoring and debugging connections.

```java
import org.postgresql.PGConnection;

void my_function(Connection conn) {
    System.out.println("My application name is " + ((PGConnection) conn).getParameterStatus("application_name"));
}

```

--------------------------------

### Run Java Application with Custom Logging Configuration

Source: https://jdbc.postgresql.org/documentation/logging

This command demonstrates how to run a Java application with a specified 'logging.properties' file. The '-Djava.util.logging.config.file' system property points to the configuration file.

```bash
java -jar -Djava.util.logging.config.file=logging.properties run.jar
```

--------------------------------

### Set JAVA_HOME Environment Variable

Source: https://jdbc.postgresql.org/documentation/setup

Set the JAVA_HOME environment variable to specify the Java version for compilation. This is useful when multiple Java compilers are installed.

```bash
JAVA_HOME=/usr/local/jdk1.8.0_45
```

--------------------------------

### Numeric Function Translation Example

Source: https://jdbc.postgresql.org/documentation/escapes

Shows the translation of a supported numeric function from JDBC escape syntax to PostgreSQL syntax. Some functions like log() are translated to ln().

```sql
log(arg1)
```

--------------------------------

### Configure Server-Side Prepared Statements via URL and Connection

Source: https://jdbc.postgresql.org/documentation/server-prepare

Illustrates setting the server-side prepared statement threshold through the JDBC URL and directly on the connection object. This allows for default threshold settings for all statements.

```java
// pg extension interfaces
org.postgresql.PGConnection pgconn;
org.postgresql.PGStatement pgstmt;

// set a prepared statement threshold for connections created from this url
String url = "jdbc:postgresql://localhost:5432/test?prepareThreshold=3";

// see that the connection has picked up the correct threshold from the url
Connection conn = DriverManager.getConnection(url, "test", "");
pgconn = conn.unwrap(org.postgresql.PGConnection.class);
System.out.println(pgconn.getPrepareThreshold()); // Should be 3

// see that the statement has picked up the correct threshold from the connection
PreparedStatement pstmt = conn.prepareStatement("SELECT ?");
pgstmt = pstmt.unwrap(org.postgresql.PGStatement.class);
System.out.println(pgstmt.getPrepareThreshold()); // Should be 3

// change the connection's threshold and ensure that new statements pick it up
pgconn.setPrepareThreshold(5);
PreparedStatement pstmt = conn.prepareStatement("SELECT ?");
pgstmt = pstmt.unwrap(org.postgresql.PGStatement.class);
System.out.println(pgstmt.getPrepareThreshold()); // Should be 5
```

--------------------------------

### Create and Use Logical Replication Slot with JDBC

Source: https://jdbc.postgresql.org/documentation/server-prepare

This snippet shows the complete process of setting up a logical replication slot, performing DML operations, and then streaming those changes using the PostgreSQL JDBC driver. Ensure the 'test_decoding' output plugin is available on your PostgreSQL server.

```java
String url = "jdbc:postgresql://localhost:5432/test";
Properties props = new Properties();
PGProperty.USER.set(props, "postgres");
PGProperty.PASSWORD.set(props, "postgres");
PGProperty.ASSUME_MIN_SERVER_VERSION.set(props, "9.4");
PGProperty.REPLICATION.set(props, "database");
PGProperty.PREFER_QUERY_MODE.set(props, "simple");

Connection con = DriverManager.getConnection(url, props);
PGConnection replConnection = con.unwrap(PGConnection.class);

replConnection.getReplicationAPI()
    .createReplicationSlot()
    .logical()
    .withSlotName("demo_logical_slot")
    .withOutputPlugin("test_decoding")
    .make();

//some changes after create replication slot to demonstrate receive it
sqlConnection.setAutoCommit(true);
Statement st = sqlConnection.createStatement();
st.execute("insert into test_logic_table(name) values('first tx changes')");
st.close();

st = sqlConnection.createStatement();
st.execute("update test_logic_table set name = 'second tx change' where pk = 1");
st.close();

st = sqlConnection.createStatement();
st.execute("delete from test_logic_table where pk = 1");
st.close();

PGReplicationStream stream = 
    replConnection.getReplicationAPI()
    .replicationStream()
    .logical()
    .withSlotName("demo_logical_slot")
    .withSlotOption("include-xids", false)
    .withSlotOption("skip-empty-xacts", true)
    .withStatusInterval(20, TimeUnit.SECONDS)
    .start();

while (true) {
    //non blocking receive message
    ByteBuffer msg = stream.readPending();

    if (msg == null) {
        TimeUnit.MILLISECONDS.sleep(10 L);
        continue;
    }

    int offset = msg.arrayOffset();
    byte[] source = msg.array();
    int length = source.length - offset;
    System.out.println(new String(source, offset, length));

    //feedback
    stream.setAppliedLSN(stream.getLastReceiveLSN());
    stream.setFlushedLSN(stream.getLastReceiveLSN());
}

```

--------------------------------

### Verify SSL Connection with psql

Source: https://jdbc.postgresql.org/documentation/ssl

This command-line output demonstrates a successful SSL connection to a PostgreSQL server using the psql client. Ensure you can connect via psql before attempting Java connections.

```bash
$ ./bin/psql -h localhost -U postgres
psql (14.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#

```

--------------------------------

### Initialize a Pooling DataSource

Source: https://jdbc.postgresql.org/documentation/datasource

This code snippet demonstrates how to initialize a PGPoolingDataSource with essential configuration properties. It sets the data source name, server names, database name, user credentials, and the maximum number of connections.

```java
PGPoolingDataSource source = new PGPoolingDataSource();
source.setDataSourceName("A Data Source");
source.setServerNames(new String[] {
    "localhost"
});
source.setDatabaseName("test");
source.setUser("testuser");
source.setPassword("testpassword");
source.setMaxConnections(10);
```

--------------------------------

### Specify Truststore and Password for Java Application

Source: https://jdbc.postgresql.org/documentation/ssl

When launching your Java application, use these system properties to point to your custom truststore and provide its password. This ensures your application uses the correct SSL certificates.

```bash
java -Djavax.net.ssl.trustStore=mystore -Djavax.net.ssl.trustStorePassword=mypassword com.mycompany.MyApp
```

--------------------------------

### Build JDBC Driver with Gradle

Source: https://jdbc.postgresql.org/documentation/setup

Compile the JDBC driver using Gradle. Add -DskipTests to skip test execution.

```bash
gradlew assemble
```

```bash
gradlew build
```

```bash
gradlew -DskipTests assemble
```

--------------------------------

### Getting SETOF Type Values from a Function

Source: https://jdbc.postgresql.org/documentation/callproc

Shows how to create and execute a PostgreSQL function that returns a SETOF integer type using a standard Statement and executeQuery. This method is used when a function returns multiple rows of a specific data type, not via CallableStatement.

```java
Statement stmt = conn.createStatement();
stmt.execute("CREATE OR REPLACE FUNCTION setoffunc() RETURNS SETOF int AS " +
    "' SELECT 1 UNION SELECT 2;' LANGUAGE sql");
ResultSet rs = stmt.executeQuery("SELECT * FROM setoffunc()");
while (rs.next()) {
    // do something
}
rs.close();
stmt.close();

```

--------------------------------

### Create Table for Binary Data

Source: https://jdbc.postgresql.org/documentation/binary-data

Defines a table with a text column for file names and a BYTEA column for binary image data.

```sql
CREATE TABLE images (imgname text, img bytea);
```

--------------------------------

### Initialize and Register Pooling DataSource with JNDI

Source: https://jdbc.postgresql.org/documentation/datasource

Use this code to initialize a PGPoolingDataSource and register it with JNDI. Ensure all necessary properties like server name, database name, user, and password are set before adding it to JNDI.

```java
PGPoolingDataSource source = new PGPoolingDataSource();
source.setDataSourceName("A Data Source");
source.setServerName("localhost");
source.setDatabaseName("test");
source.setUser("testuser");
source.setPassword("testpassword");
source.setMaxConnections(10);
new InitialContext().rebind("DataSource", source);
```

--------------------------------

### Configure PostgreSQL JDBC Driver Logging with logging.properties

Source: https://jdbc.postgresql.org/documentation/logging

This snippet shows a sample 'logging.properties' file to configure the PostgreSQL JDBC Driver's logging. It specifies file handlers, logging levels, output patterns, and formatters for general and driver-specific logging.

```properties
# Specify the handler, the handlers will be installed during VM startup.
handlers = java.util.logging.FileHandler

# Default global logging level.
.level = OFF

# Default file output is in user's home directory.
java.util.logging.FileHandler.pattern = %h/pgjdbc%u.log
java.util.logging.FileHandler.limit = 5000000
java.util.logging.FileHandler.count = 20
java.util.logging.FileHandler.formatter = java.util.logging.SimpleFormatter
java.util.logging.FileHandler.level = FINEST

java.util.logging.SimpleFormatter.format = %1$tY-%1$tm-%1$td %1$tH:%1$tM:%1$tS %4$s %2$s %5$s%6$s%n

# Facility specific properties.
org.postgresql.level = FINEST

```

--------------------------------

### Enable server-prepared statements for regular Statements

Source: https://jdbc.postgresql.org/documentation/server-prepare

Set 'preferQueryMode' to 'extendedCacheEverything' to enable server-side prepared statements for regular 'Statement' objects, improving performance for repeated executions.

```properties
preferQueryMode=extendedCacheEverything
```

--------------------------------

### Accessing Fastpath API

Source: https://jdbc.postgresql.org/documentation/server-prepare

Demonstrates how to obtain the Fastpath API from a JDBC connection to interact with PostgreSQL server extensions. Requires casting the connection to PGConnection.

```java
Connection db = Driver.getConnection(url, username, password);
// ...
// later on
Fastpath fp = db.unwrap(org.postgresql.PGConnection.class).getFastpathAPI();

```

--------------------------------

### Create Logical Replication Stream

Source: https://jdbc.postgresql.org/documentation/server-prepare

Initiates a logical replication stream from the 'demo_logical_slot', with options to exclude transaction IDs and skip empty transactions.

```java
PGReplicationStream stream = 
    replConnection.getReplicationAPI()
        .replicationStream()
        .logical()
        .withSlotName("demo_logical_slot")
        .withSlotOption("include-xids", false)
        .withSlotOption("skip-empty-xacts", true)
        .start();
```

--------------------------------

### Build Maven Source Distribution with Gradle

Source: https://jdbc.postgresql.org/documentation/setup

Generate a Maven-based source distribution artifact using Gradle. This artifact can be built with Maven.

```bash
gradlew -d :postgresql:sourceDistribution -Prelease -Psigning.gpg.enabled=OFF
```

--------------------------------

### Build JDBC Driver with Maven

Source: https://jdbc.postgresql.org/documentation/setup

Compile the JDBC driver from the Maven-based project. Add -DskipTests to skip test execution.

```bash
mvn package
```

```bash
mvn -DskipTests package
```

--------------------------------

### Import JDBC Package

Source: https://jdbc.postgresql.org/documentation/use

Any source file that uses JDBC needs to import the java.sql package.

```java
import java.sql.*;

```

--------------------------------

### Set Connection Options with Properties Object

Source: https://jdbc.postgresql.org/documentation/use

Configure connection options, such as statement timeout, by setting the 'options' property in a Properties object. This allows for server-specific initialization parameters.

```java
Properties props = new Properties();
props.setProperty("options", "-c search_path=test,public,pg_catalog -c statement_timeout=90000");
Connection conn = DriverManager.getConnection(url, props);

```

--------------------------------

### Set Connection Options in URL

Source: https://jdbc.postgresql.org/documentation/use

Specify connection options, like search path and statement timeout, directly within the connection URL using percent-encoded values. This is an alternative to using a Properties object.

```java
String url = "jdbc:postgresql://localhost:5432/postgres?options=-c%20search_path=test,public,pg_catalog%20-c%20statement_timeout=90000";
Connection conn = DriverManager.getConnection(url);

```

--------------------------------

### Tomcat Datasource Configuration for PostgreSQL

Source: https://jdbc.postgresql.org/documentation/datasource

Configure a PostgreSQL datasource in Tomcat's server.xml or context.xml. Ensure the postgresql.jar is in the Tomcat common/lib directory.

```xml
<Resource name="jdbc/postgres" scope="Shareable" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/postgres">
	<parameter>
		<name>validationQuery</name>
		<value>select version();</value>
	</parameter>
	<parameter>
		<name>url</name>
		<value>jdbc:postgresql://localhost/davec</value>
	</parameter>
	<parameter>
		<name>password</name>
		<value>davec</value>
	</parameter>
	<parameter>
		<name>maxActive</name>
		<value>4</value>
	</parameter>
	<parameter>
		<name>maxWait</name>
		<value>5000</value>
	</parameter>
	<parameter>
		<name>driverClassName</name>
		<value>org.postgresql.Driver</value>
	</parameter>
	<parameter>
		<name>username</name>
		<value>davec</value>
	</parameter>
	<parameter>
		<name>maxIdle</name>
		<value>2</value>
	</parameter>
</ResourceParams>
```

--------------------------------

### Enable SSL Debugging

Source: https://jdbc.postgresql.org/documentation/ssl

Add this system property to your Java application's command line to enable detailed SSL debugging output. This is useful for troubleshooting SSL connection issues.

```bash
java -Djavax.net.debug=ssl com.mycompany.MyApp
```

--------------------------------

### Set search_path and query tables

Source: https://jdbc.postgresql.org/documentation/server-prepare

Demonstrates how changing the search_path can affect which table is queried. pgJDBC attempts to track these changes to invalidate the prepare cache.

```sql
set search_path='app_v1';
SELECT * FROM mytable;
set search_path='app_v2';
SELECT * FROM mytable; -- Does mytable mean app_v1.mytable or app_v2.mytable here?
```

--------------------------------

### Execute DEALLOCATE ALL and DISCARD ALL

Source: https://jdbc.postgresql.org/documentation/server-prepare

Use these commands at the top level to invalidate the client-side cache and deallocate server-side prepared statements. Avoid nesting them within pl/pgsql.

```sql
DEALLOCATE ALL;
DISCARD ALL;
```

--------------------------------

### Create PostgreSQL Replication Connection

Source: https://jdbc.postgresql.org/documentation/server-prepare

Establishes a JDBC connection to PostgreSQL in replication mode using specific properties for user, password, server version, and replication.

```java
String url = "jdbc:postgresql://localhost:5432/postgres";
Properties props = new Properties();
PGProperty.USER.set(props, "postgres");
PGProperty.PASSWORD.set(props, "postgres");
PGProperty.ASSUME_MIN_SERVER_VERSION.set(props, "9.4");
PGProperty.REPLICATION.set(props, "database");
PGProperty.PREFER_QUERY_MODE.set(props, "simple");

Connection con = DriverManager.getConnection(url, props);
PGConnection replConnection = con.unwrap(PGConnection.class);
```

--------------------------------

### PostgreSQL Replication Configuration

Source: https://jdbc.postgresql.org/documentation/server-prepare

Set essential parameters in postgresql.conf for replication, including WAL sender processes, segment count, WAL level, and replication slots.

```ini
max_wal_senders = 4             # max number of walsender processes
wal_keep_segments = 4           # in logfile segments, 16MB each; 0 disables
wal_level = logical             # minimal, replica, or logical
max_replication_slots = 4       # max number of replication slots
```

--------------------------------

### Create Logical Replication Slot via pgJDBC API

Source: https://jdbc.postgresql.org/documentation/server-prepare

Creates a logical replication slot named 'demo_logical_slot' using the 'test_decoding' output plugin via the pgJDBC replication API.

```java
replConnection.getReplicationAPI()
    .createReplicationSlot()
    .logical()
    .withSlotName("demo_logical_slot")
    .withOutputPlugin("test_decoding")
    .make();
```

--------------------------------

### Retrieve and Use Connection from Pooled DataSource via JNDI

Source: https://jdbc.postgresql.org/documentation/datasource

This code demonstrates how to look up a DataSource from JNDI and obtain a connection from the pool. It includes basic error handling for SQLException and NamingException, and ensures the connection is closed in the finally block.

```java
Connection conn = null;
try {
    DataSource source = (DataSource) new InitialContext().lookup("DataSource");
    conn = source.getConnection();
    // use connection
} catch (SQLException e) {
    // log error
} catch (NamingException e) {
    // DataSource wasn't found in JNDI
} finally {
    if (con != null) {
        try {
            conn.close();
        } catch (SQLException e) {}
    }
}
```

--------------------------------

### Copying Data In using CopyManager

Source: https://jdbc.postgresql.org/documentation/server-prepare

Demonstrates how to copy data from a Java byte array into a PostgreSQL table using the CopyManager's copyIn method. Ensure the target table exists and matches the data structure.

```Java
/*
* DDL for code below
* create table copytest (stringvalue text, intvalue int, numvalue numeric(5,2));
*/
private static String[] origData =
            {"First Row\t1\t1.10\n",
                    "Second Row\t2\t-22.20\n",
                    "\\N\t\\N\t\\N\n",
                    "\t4\t444.40\n"};
private int dataRows = origData.length;
private String sql = "COPY copytest FROM STDIN";

try (Connection con = DriverManager.getConnection(url, "postgres", "somepassword")){
    PGConnection pgConnection = con.unwrap(org.postgresql.PGConnection.class);
    CopyManager copyAPI = pgConnection.getCopyAPI();
    CopyIn cp = copyAPI.copyIn(sql);

    for (String anOrigData : origData) {
        byte[] buf = anOrigData.getBytes();
        cp.writeToCopy(buf, 0, buf.length);
    }

    long updatedRows = cp.endCopy();
    long handledRowCount = cp.getHandledRowCount();
    System.err.println(String.format("copy Updated %d Rows, and handled %d rows", updatedRows, handledRowCount));

    int rowCount = getCount(con);
    System.err.println(rowCount);

}
```

--------------------------------

### Configure Replication Stream with Feedback Interval

Source: https://jdbc.postgresql.org/documentation/server-prepare

Configures a logical replication stream with a specified feedback interval to prevent timeouts. Use this when you need to ensure regular communication with the database during replication.

```java
PGReplicationStream stream =
    replConnection.getReplicationAPI()
        .replicationStream()
        .logical()
        .withSlotName("demo_logical_slot")
        .withSlotOption("include-xids", false)
        .withSlotOption("skip-empty-xacts", true)
        .withStatusInterval(20, TimeUnit.SECONDS)
        .start();
```

--------------------------------

### Configure Unix Socket Connection

Source: https://jdbc.postgresql.org/documentation/use

Append the socketFactory and socketFactoryArg parameters to the JDBC connection URL to specify the Unix domain socket path. This allows the driver to connect to the PostgreSQL server via a local socket file.

```text
?socketFactory=org.newsclub.net.unix.AFUNIXSocketFactory$FactoryArg&socketFactoryArg=[path-to-the-unix-socket]
```

--------------------------------

### Load Logging Configuration in Java

Source: https://jdbc.postgresql.org/documentation/server-prepare

Load the custom logging configuration programmatically using LogManager. This ensures that the specified logging levels and handlers are applied to your application.

```java
LogManager.getLogManager().readConfiguration(YourClass.class.getResourceAsStream("/logging.properties"));
```

--------------------------------

### Using PostgreSQL CIRCLE Datatype with JDBC

Source: https://jdbc.postgresql.org/documentation/server-prepare

Demonstrates how to insert and retrieve CIRCLE geometric data types using the PGcircle class from the org.postgresql.geometric package. Requires a running PostgreSQL instance and a database.

```Java
import java.sql.*;

import org.postgresql.geometric.PGpoint;
import org.postgresql.geometric.PGcircle;

public class GeometricTest {
    public static void main(String args[]) throws Exception {
        String url = "jdbc:postgresql://localhost:5432/test";
        try (Connection conn = DriverManager.getConnection(url, "test", "")) {
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("CREATE TEMP TABLE geomtest(mycirc circle)");
            }
            insertCircle(conn);
            retrieveCircle(conn);
        }
    }

    private static void insertCircle(Connection conn) throws SQLException {
        PGpoint center = new PGpoint(1, 2.5);
        double radius = 4;
        PGcircle circle = new PGcircle(center, radius);
        try (PreparedStatement ps = conn.prepareStatement("INSERT INTO geomtest(mycirc) VALUES (?)")) {
            ps.setObject(1, circle);
            ps.executeUpdate();
        }
    }

    private static void retrieveCircle(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            try (ResultSet rs = stmt.executeQuery("SELECT mycirc, area(mycirc) FROM geomtest")) {
                while (rs.next()) {
                    PGcircle circle = (PGcircle) rs.getObject(1);
                    double area = rs.getDouble(2);

                    System.out.println("Center (X, Y) = (" + circle.center.x + ", " + circle.center.y + ")");
                    System.out.println("Radius = " + circle.radius);
                    System.out.println("Area = " + area);
                }
            }
        }
    }
}
```

--------------------------------

### Configure Fetch Size for Cursor-Based Results

Source: https://jdbc.postgresql.org/documentation/query

Demonstrates how to enable and disable cursor-based fetching by setting the `fetchSize` on a `Statement`. Ensure autocommit is disabled for cursor-based `ResultSets`. Setting `fetchSize` to 0 reverts to default behavior (fetching all rows at once).

```java
// make sure autocommit is off
conn.setAutoCommit(false);
Statement st = conn.createStatement();

// Turn use of the cursor on.
st.setFetchSize(50);
ResultSet rs = st.executeQuery("SELECT * FROM mytable");
while (rs.next()) {
    System.out.print("a row was returned.");
}
rs.close();

// Turn the cursor off.
st.setFetchSize(0);
rs = st.executeQuery("SELECT * FROM mytable");
while (rs.next()) {
    System.out.print("many rows were returned.");
}
rs.close();

// Close the statement.
st.close();

```

--------------------------------

### Establish SSL Connection Without Certificate Validation

Source: https://jdbc.postgresql.org/documentation/ssl

Configure the JDBC connection URL by setting the 'sslfactory' parameter to 'org.postgresql.ssl.NonValidatingFactory'. This bypasses all SSL certificate validation, use with caution due to security risks.

```java
jdbc:postgresql://host:port/database?sslfactory=org.postgresql.ssl.NonValidatingFactory
```

--------------------------------

### Copying Data Out using CopyManager

Source: https://jdbc.postgresql.org/documentation/server-prepare

Shows how to retrieve data from a PostgreSQL table into a Java byte array using the CopyManager's copyOut method. This is useful for bulk data retrieval.

```Java
String sql = "COPY copytest TO STDOUT";
try (Connection con = DriverManager.getConnection(url, "postgres", "somepassword")){
    PGConnection pgConnection = con.unwrap(org.postgresql.PGConnection.class);
    CopyManager copyAPI = pgConnection.getCopyAPI();
    CopyOut cp = copyAPI.copyOut(sql);
    int count = 0;
    byte[] buf;

    while ((buf = cp.readFromCopy()) != null) {
        count++;
    }
    long rowCount = cp.getHandledRowCount();
}
```