PGJDBC-NG User Guide

PreparedStatement pstmt = connection.prepareStatement("INSERT INTO ledger(balance) VALUES (?)");
pstmt.setObject(1, 113.54, PGType.MONEY); (1)
pstmt.executeUpdate();

PGConnection pgConnection = connection.unwrap(PGConnection.class); (1)
PGAnyType myType = pgConnection.resolveType("my_type"); (2)

ResultSet rs = statement.getResultSet();
LocalDateTime incorrect = rs.getTimestamp(1).toLocalDateTime(); (1)
LocalDateTime correct = rs.getObjecct(1, LocalDateTime.class); (2)

PreparedStatement stmt = conn.prepareStatement("INSERT INTO log VALUES (? , ?)");
stmt.setText("Something happened");
stmt.setTimestamp(1, Timestamp.valueOf(LocalDateTime.now())); (1)
stmt.setObject(1, LocalDateTime.now()); (2)

Connection connection = DriverManager.getConnection(url);
PGConnection pgConnection = (PGConnection)connection; (1)
PGConnection pgConnection = connection.unwrap(PGConnection.class); (2)

System.setIn(new ByteArrayInputStream("1-1\t1-2\n2-1\t2-2\n3-1\t3-2".getBytes(UTF_8))); (1)
try (Statement statement = connection.createStatement()) {
  statement.execute("COPY a_table FROM STDIN"); (2)
}

System.setOut(new FileOutputStream("table.txt")); (1)
try (Statement statement = connection.createStatement()) {
  statement.execute("COPY a_table TO STDOUT"); (2)
}

InputStream tableData = new ByteArrayInputStream("1-1\t1-2\n2-1\t2-2\n3-1\t3-2".getBytes(UTF_8)); (1)
connection.unwrap(PGConnection.class).copyFrom("COPY a_table FROM STDIN", tableData); (2)

OutputStream fileOut = new FileOutputStream("table.txt")); (1)
connection.unwrap(PGConnection.class).copyTo("COPY a_table TO STDOUT", fileOut); (2)

java -jar udt-gen-all-0.8.9.jar [options] <type names>

import com.impossibl.postgres.tools.UDTGenerator;
import java.sql.Connection;
import java.util.List;
import java.io.File;

class ExecGen {
  public static void executeGenerator(Connection connection, List<String> typeNames) {
    new UDTGenerator(connection, "sql.schema.types", typeNames)
        .generate(new File("out"));
  }
}

CREATE TYPE title as enum ('mr', 'mrs', 'ms', 'dr');
CREATE TYPE address as (street text, city text, state char(2), zip char(5));
CREATE TYPE v_card as (id int4, name text, title title, addresses address[]);

public class Address implements SQLData {
  private static final String TYPE_NAME = "public.address";

  private String street;

  private String city;

  private String state;

  private String zip;

  @Override
  public String getSQLTypeName() throws SQLException {
    return TYPE_NAME;
  }

  public String getStreet() {
    return street;
  }

  public void setStreet(String street) {
    this.street = street;
  }

  public String getCity() {
    return city;
  }

  public void setCity(String city) {
    this.city = city;
  }

  public String getState() {
    return state;
  }

  public void setState(String state) {
    this.state = state;
  }

  public String getZip() {
    return zip;
  }

  public void setZip(String zip) {
    this.zip = zip;
  }

  @Override
  public void readSQL(SQLInput in, String typeName) throws SQLException {
    this.street = in.readString();
    this.city = in.readString();
    this.state = in.readString();
    this.zip = in.readString();
  }

  @Override
  public void writeSQL(SQLOutput out) throws SQLException {
    out.writeString(this.street);
    out.writeString(this.city);
    out.writeString(this.state);
    out.writeString(this.zip);
  }
}

public enum Title {
  MR("mr"),

  MRS("mrs"),

  MS("ms"),

  DR("dr");

  private String label;

  Title(String label) {
    this.label = label;
  }

  public String getLabel() {
    return label;
  }

  public static Title valueOfLabel(String label) {
    for (Title value : values()) {
      if (value.label.equals(label)) return value;
    }
    throw new IllegalArgumentException("Invalid label");}
}


public class VCard implements SQLData {
  private static final String TYPE_NAME = "public.v_card";

  private Integer id;

  private String name;

  private Title title;

  private PGStruct[] addresses;

  @Override
  public String getSQLTypeName() throws SQLException {
    return TYPE_NAME;
  }

  public Integer getId() {
    return id;
  }

  public void setId(Integer id) {
    this.id = id;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public Title getTitle() {
    return title;
  }

  public void setTitle(Title title) {
    this.title = title;
  }

  public PGStruct[] getAddresses() {
    return addresses;
  }

  public void setAddresses(PGStruct[] addresses) {
    this.addresses = addresses;
  }

  @Override
  public void readSQL(SQLInput in, String typeName) throws SQLException {
    this.id = in.readInt();
    this.name = in.readString();
    this.title = Title.valueOfLabel(in.readString());
    this.addresses = in.readObject(PGStruct[].class);
  }

  @Override
  public void writeSQL(SQLOutput out) throws SQLException {
    out.writeInt(this.id);
    out.writeString(this.name);
    out.writeString(this.title.getLabel());
    out.writeObject(this.addresses, null);
  }
}

Address address = new Address();        (1)
address.setStreet("123 Easy Street");
address.setCity("Easy Town");
address.setState("IL");
address.setZip("12345");

VCard vCard = new VCard();              (2)
vCard.setId(5);
vCard.setTitle(Title.MR);
vCard.setName("A Guy");
vCard.setAddresses(new Address[]{address});

PreparedStatement stmt = connection.prepareStatement("INSERT INTO contacts(card) VALUES (?)");
stmt.setObject(1, vCard); (3)

Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT card FROM contacts;")
while (rs.next()) {
  VCard vCard = (VCard) rs.getObject(1, VCard.class); (4)

  HashMap<String, Class<?>> customTypes = new HashMap<>(); (5)
  customTypes.put("v_card", VCard.class);
  customTypes.put("address", Address.class);

  VCard vCard = (VCard) rs.getObject(1, customTypes); (6)
}

Connection conn = ConnectionTracer(DriverManager.getConnection(...), FileTracer("api.trace"))

Connection conn = DriverManager.getConnection("jdbc:spy:pgjdbc://localhost/db");

<module xmlns="urn:jboss:module:1.3" name="com.impossibl.pgjdbc-ng">
  <resources>
    <resource-root path="pgjdbc-ng-all-0.8.9.jar"/>
  </resources>
  <dependencies>
    <module name="javax.api"/>
    <module name="javax.transaction.api"/>
  </dependencies>
</module>

<driver name="pgjdbc-ng" module="com.impossibl.pgjdbc-ng">
  <datasource-class>com.impossibl.postgres.jdbc.PGXADataSource</datasource-class>
  <xa-datasource-class>com.impossibl.postgres.jdbc.xa.PGXADataSource</xa-datasource-class>
</driver>

<datasource jndi-name="java:jboss/datasources/PostgreSQLDS" pool-name="PostgreSQLDS">
   <connection-url>jdbc:pgsql://localhost:5432/test</connection-url> (1)
   <connection-property name="housekeeper">false</connection-property> (2)
   <connection-property name="parsedSqlCacheSize">50</connection-property> (2)
   <connection-property name="preparedStatementCacheSize">0</connection-property> (2)
   <driver>pgjdbc-ng</driver>
   <security>
     <user-name>user</user-name> (3)
     <password>secret</password> (4)
   </security>
   <validation>
     <validate-on-match>true</validate-on-match>
     <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"></valid-connection-checker>
     <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"></exception-sorter>
   </validation>
   <statement>
     <track-statements>nowarn</track-statements>
     <prepared-statement-cache-size>32</prepared-statement-cache-size>
   </statement>
</datasource>

<xa-datasource jndi-name="java:jboss/datasources/PostgreSQLXADS" pool-name="PostgreSQLXADS">
   <xa-datasource-property name="host">localhost</xa-datasource-property> (1)
   <xa-datasource-property name="port">5432</xa-datasource-property> (2)
   <xa-datasource-property name="database">test</xa-datasource-property> (3)
   <xa-datasource-property name="housekeeper">false</xa-datasource-property> (4)
   <xa-datasource-property name="parsedSqlCacheSize">50</xa-datasource-property> (4)
   <xa-datasource-property name="preparedStatementCacheSize">0</xa-datasource-property> (4)
   <driver>pgjdbc-ng</driver>
   <security>
     <user-name>user</user-name> (5)
     <password>secret</password> (6)
   </security>
   <validation>
     <validate-on-match>true</validate-on-match>
     <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"></valid-connection-checker>
     <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"></exception-sorter>
   </validation>
   <statement>
     <track-statements>nowarn</track-statements>
     <prepared-statement-cache-size>32</prepared-statement-cache-size>
   </statement>
</xa-datasource>