Initializing the Driver

This section describes how to load and initialize the JDBC driver in your programs.

Importing JDBC

Any source file that uses JDBC needs to import thejava.sql package, using:

importjava.sql.*;

NOTE

You should not import theorg.postgresql package unless you are using PostgreSQL® extensions to the JDBC API.

Loading the Driver

Applications do not need to explicitly load theorg.postgresql.Driver class because the pgJDBC driver jar supports the Java Service Provider mechanism. The driver will be loaded by the JVM when the application connects to PostgreSQL® (as long as the driver’s jar file is on the classpath).

NOTE

Prior to Java 1.6, the driver had to be loaded by the application: either by callingClass.forName("org.postgresql.Driver"); or by passing the driver class name as a JVM parameterjava -Djdbc.drivers=org.postgresql.Driver example.ImageViewer

These older methods of loading the driver are still supported, but they are no longer necessary.

Connecting to the Database

With JDBC, a database is represented by a URL (Uniform Resource Locator). With PostgreSQL®, this takes one of the following forms:

The parameters have the following meanings:

To connect, you need to get aConnection instance from JDBC. To do this, you use theDriverManager.getConnection() method:Connection db = DriverManager.getConnection(url, username, password)

Important

Any reserved characters for URLs (for example, /, :, @, (, ), [, ], &, #, =, ?, and space) that appear in any part of the connection URL must be percent encoded. SeeRFC 3986 for details.

System Properties

pgjdbc.config.cleanup.thread.ttl (milliseconds, default: 30000). The driver has an internal cleanup thread which monitors and cleans up unclosed connections. This property sets the duration the cleanup thread will keep running if there is nothing to clean up.

Connection Parameters

In addition to the standard connection parameters the driver supports a number of additional properties which can be used to specify additional driver behaviour specific to PostgreSQL®. These properties may be specified in either the connectionURL or an additionalProperties object parameter toDriverManager.getConnection . The following examples illustrate the use of both methods to establish an SSL connection.

If a property is specified both in URL and inProperties object, the value fromProperties object is ignored.

Stringurl="jdbc:postgresql://localhost/test";Propertiesprops=newProperties();props.setProperty("user","fred");props.setProperty("password","secret");props.setProperty("ssl","true");Connectionconn=DriverManager.getConnection(url,props);Stringurl="jdbc:postgresql://localhost/test?user=fred&password=secret&ssl=true";Connectionconn=DriverManager.getConnection(url);

The value of this property may contain spaces or other special characters, and it should be properly encoded if provided in the connection URL. Spaces are considered to separate command-line arguments, unless escaped with a backslash (\ );\\ represents a literal backslash.

Propertiesprops=newProperties();props.setProperty("options","-c search_path=test,public,pg_catalog -c statement_timeout=90000");Connectionconn=DriverManager.getConnection(url,props);Stringurl="jdbc:postgresql://localhost:5432/postgres?options=-c%20search_path=test,public,pg_catalog%20-c%20statement_timeout=90000";Connectionconn=DriverManager.getConnection(url);

NOTE

This parameter is ignored when using PKCS-12 keys, since in that case the certificate is also retrieved from the same keyfile.

NOTE

The key filemust be inPKCS-12 or inPKCS-8DER format.A PEM key can be converted to DER format using the openssl command:openssl pkcs8 -topk8 -inform PEM -in postgresql.key -outform DER -out postgresql.pk8 -v1 PBE-MD5-DESWhen you create the key thealias or thename must beuser. The test codes uses the following to create a .p12 keyopenssl pkcs12 -export -in $< -inkey $*.key -out $@ -name user -CAfile $(SERVER_CRT_DIR)root.crt -caname local -passout pass:$(P12_PASSWORD)

PKCS-12 key files are only recognized if they have the “.p12” (42.2.9+) or the “.pfx” (42.2.16+) extension.

If your key has a password, provide it using thesslpassword connection parameter described below. Otherwise, you can add the flag-nocrypt to the above command to prevent the driver from requesting a password.

NOTE

The use of -v1 PBE-MD5-DES might be inadequate in environments where high level of security is needed and the key is not protected by other means (e.g. access control of the OS),or the key file is transmitted in untrusted channels. We are depending on the cryptography providers provided by the java runtime. The solution documented here is known to work at the time of writing. If you have stricter security needs, please seehere for a discussion of the problem and information on choosing a better cipher suite.

Channel binding is a method for the server to authenticate itself to the client. It is only supported over SSL connections with PostgreSQL 11 or later servers using the SCRAM authentication method.

Unix sockets

By adding junixsocket you can obtain a socket factory that works with the driver.Code can be foundhere and instructionshere

Dependencies for junixsocket are :

<dependency><groupId>com.kohlschutter.junixsocket</groupId><artifactId>junixsocket-core</artifactId><version>2.5.1</version></dependency>

Simply add?socketFactory=org.newsclub.net.unix.AFUNIXSocketFactory$FactoryArg&socketFactoryArg=[path-to-the-unix-socket]to the connection URL.

For many distros the default path is /var/run/postgresql/.s.PGSQL.5432

Connection Fail-over

To support simple connection fail-over it is possible to define multiple endpoints (host and port pairs) in the connectionurl separated by commas. The driver will try once to connect to each of them in order until the connection succeeds.If none succeeds a normal connection exception is thrown.

The syntax for the connection url is:jdbc:postgresql://host1:port1,host2:port2/database

The simple connection fail-over is useful when running against a high availability postgres installation that has identicaldata on each node. For example streaming replication postgres or postgres-xc cluster.

For example an application can create two connection pools.One data source is for writes, another for reads. The write pool limits connections only to a primary node:jdbc:postgresql://node1,node2,node3/accounting?targetServerType=primary .

And the read pool balances connections between secondary nodes, but allows connections also to a primary if no secondariesare available:jdbc:postgresql://node1,node2,node3/accounting?targetServerType=preferSecondary&loadBalanceHosts=true

If a secondary fails, all secondaries in the list will be tried first. In the case that there are no available secondariesthe primary will be tried. If all the servers are marked as “can’t connect” in the cache then an attemptwill be made to connect to all the hosts in the URL, in order.

Setting up the JDBC Driver
Using SSL
Initializing the Driver