PostgreSQL的客戶端接口

在PostgreSQL發行版中只包含兩個客戶端接口: libpq 和 ECPG

• libpq is included because it is the primary C language interface, and because many other client interfaces are built on top of it.
• ECPG is included because it depends on the server-side SQL grammar, and is therefore sensitive to changes in PostgreSQL itself.

其他語言客戶端接口：

JDBC是啥？

??Java Database Connectivity (JDBC) 是一種用于執行 SQL 語句的 Java API , 可以為多種關系數據庫提供統一訪問 , 它由一組用 Java 語言編寫的類和接口組成。

??JDBC API 是 SUN 公司提出的訪問數據庫的接口標準，是訪問數據庫的通用API。

PostgreSQL JDBC Driver

??PostgreSQL JDBC Driver 官網和 JDBC 驅動下載：https://jdbc.postgresql.org/
??PostgreSQL JDBC Driver 官方文檔： https://jdbc.postgresql.org/documentation/head/index.html

使用JDBC連接PostgreSQL數據庫的幾種方式，推薦方式六

??以下學習了幾種JDBC連接PostgreSQL數據庫的JAVA代碼，依次迭代，推薦方式六。在這里，只學習怎么連接數據庫，至于怎么操作數據庫就暫時放棄了，畢竟術業有專攻。上次接觸JAVA代碼還是上大學的時候，已經過去好多年了，所以以下代碼肯定還不夠嚴謹，僅供參考學習，主要還是研究JDBC連接PostgreSQL數據庫的問題。

方式一，Driver 方式連接數據庫

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

public class PostgreSQLJDBC {
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		Driver driver = new org.postgresql.Driver();
		String url = "jdbc:postgresql://192.168.58.20:5000/postgres";
		Properties info = new Properties();
		info.setProperty("user", "postgres");
		info.setProperty("password", "postgres");
		Connection conn = driver.connect(url, info);
		System.out.println(conn);
	}
}

方式二，使用反射獲取Driver實現類對象

??使用反射獲取Driver實現類對象，不需要使用第三方的API[new org.postgresql.Driver()]，使程序具有更好的可移植性(更換數據庫)，對方式一的迭代。

import java.sql.Connection;
import java.sql.Driver;
import java.util.Properties;

public class PostgreSQLJDBC {
	public static void main(String[] args) throws Exception {
		Class cs = Class.forName("org.postgresql.Driver");
		Driver driver = (Driver) cs.newInstance();
		String url = "jdbc:postgresql://192.168.58.20:5000/postgres";
		Properties info = new Properties();
		info.setProperty("user", "postgres");
		info.setProperty("password", "postgres");
		Connection conn = driver.connect(url, info);
		System.out.println(conn);
	}
}

方式三，使用 DriverManager 替換 Driver，對方式二的迭代

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

public class PostgreSQLJDBC {
	public static void main(String[] args) throws Exception {
		Class cs = Class.forName("org.postgresql.Driver");
		Driver driver = (Driver) cs.newInstance();
		String url = "jdbc:postgresql://192.168.58.20:5000/postgres";
		String username = "postgres";
		String password = "postgres";
		DriverManager.registerDriver(driver);
		Connection conn = DriverManager.getConnection(url, username, password);
		System.out.println(conn);
	}
}

方式四，對方式三的優化

??只加載(Class.forName)驅動，不用顯式的注冊(registerDriver)驅動，JDBC Driver類中寫了注冊，加載org.postgresql.Driver自動就注冊了。

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

public class PostgreSQLJDBC {
	public static void main(String[] args) throws Exception {
		String url = "jdbc:postgresql://192.168.58.20:5000/postgres";
		String username = "postgres";
		String password = "postgres";
		Class.forName("org.postgresql.Driver");
		Connection conn = DriverManager.getConnection(url, username, password);
		System.out.println(conn);
	}
}

方式五，加載(Class.forName)驅動 也可以省略

??加載(Class.forName)驅動 也可以省略，因為驅動包 postgresql-42.2.20.jar\META-INF\services\java.sql.Driver 文件中有 org.postgresql.Driver，但是建議使用，因為不是所有的驅動包都有這個配置。

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

public class PostgreSQLJDBC {
	public static void main(String[] args) throws Exception {
		String url = "jdbc:postgresql://192.168.58.20:5000/postgres";
		String username = "postgres";
		String password = "postgres";
		//Class.forName("org.postgresql.Driver");
		Connection conn = DriverManager.getConnection(url, username, password);
		System.out.println(conn);
	}
}

方式六，最終且推薦版，將數據庫連接需要的基本信息放到配置文件中，通過讀取配置文件獲取連接

??好處：1. 數據和代碼分離 2. 如果修改配置文件信息，可以避免對程序重新打包

[root@pgtest3 ~]# vi PostgreSQLJDBC.java
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

public class PostgreSQLJDBC {
	public static void main(String[] args) throws Exception {
		// 讀取配置文件信息
		InputStream conf = PostgreSQLJDBC.class.getClassLoader().getResourceAsStream("jdbc.properties");
		Properties pros = new Properties();
		pros.load(conf);
		String url = pros.getProperty("url");
		String username = pros.getProperty("username");
		String password = pros.getProperty("password");
		String driverClass = pros.getProperty("driverclass");
		// 加載驅動
		Class.forName(driverClass);
		// 獲取連接
		Connection conn = DriverManager.getConnection(url, username, password);
		// 定義查詢語句
		String sql = "select inet_server_addr(),pg_is_in_recovery(),current_database(),current_user";
		PreparedStatement ps = conn.prepareStatement(sql);
		// 執行查詢并返回結果集
		ResultSet rs = ps.executeQuery();
		// 處理結果集
		while (rs.next()) {
			System.out.println("inet_server_addr: " + rs.getString(1));
			System.out.println("pg_is_in_recovery: " + rs.getString(2));
			System.out.println("current_database: " + rs.getString(3));
			System.out.println("current_user: " + rs.getString(4));
		}
		// 關閉資源
		rs.close();
		ps.close();
		conn.close();
	}
}

[root@pgtest3 ~]# vi jdbc.properties
url=jdbc:postgresql://192.168.58.20:5000/postgres
username=postgres
password=postgres
driverclass=org.postgresql.Driver

[root@pgtest3 ~]# javac PostgreSQLJDBC.java
[root@pgtest3 ~]# java PostgreSQLJDBC -cp /enmo/soft/postgresql-42.2.20.jar
inet_server_addr: 192.168.58.10
pg_is_in_recovery: f
current_database: postgres
current_user: postgres

PostgreSQL JDBC Driver 連接數據庫可用參數

??除了標準連接參數之外，驅動程序還支持許多附加屬性，這些屬性可用于指定特定于 PostgreSQL 的附加驅動程序行為。 這些屬性可以在連接 URL 或 DriverManager.getConnection 的附加屬性對象參數中指定。
??如果在 URL 和 Properties 對象中都指定了同一個屬性，則忽略 Properties 對象中的值，本小節下方有測試案例證明此說法。
??詳情參考PostgreSQL JDBC Driver 官方文檔： https://jdbc.postgresql.org/documentation/head/index.html

| Property                      | Type    | Default | Description   |
| ----------------------------- | ------- | :-----: | ------------- |
| user                          | String  | null    | The database user on whose behalf the connection is being made. |
| password                      | String  | null    | The database user's password. |
| options                       | String  | null    | Specify 'options' connection initialization parameter. |
| ssl                           | Boolean | false   | Control use of SSL (true value causes SSL to be required) |
| sslfactory                    | String  | null    | Provide a SSLSocketFactory class when using SSL. |
| sslfactoryarg (deprecated)    | String  | null    | Argument forwarded to constructor of SSLSocketFactory class. |
| sslmode                       | String  | prefer  | Controls the preference for opening using an SSL encrypted connection. |
| sslcert                       | String  | null    | The location of the client's SSL certificate |
| sslkey                        | String  | null    | The location of the client's PKCS#8 SSL key |
| sslrootcert                   | String  | null    | The location of the root certificate for authenticating the server. |
| sslhostnameverifier           | String  | null    | The name of a class (for use in [Class.forName(String)](https://docs.oracle.com/javase/6/docs/api/java/lang/Class.html#forName%28java.lang.String%29)) that implements javax.net.ssl.HostnameVerifier and can verify the server hostname. |
| sslpasswordcallback           | String  | null    | The name of a class (for use in [Class.forName(String)](https://docs.oracle.com/javase/6/docs/api/java/lang/Class.html#forName%28java.lang.String%29)) that implements javax.security.auth.callback.CallbackHandler and can handle PasswordCallback for the ssl password. |
| sslpassword                   | String  | null    | The password for the client's ssl key (ignored if sslpasswordcallback is set) |
| sendBufferSize                | Integer | -1      | Socket write buffer size |
| receiveBufferSize             | Integer | -1      | Socket read buffer size  |
| loggerLevel                   | String  | null    | Logger level of the driver using java.util.logging. Allowed values: OFF, DEBUG or TRACE. |
| loggerFile                    | String  | null    | File name output of the Logger, if set, the Logger will use a FileHandler to write to a specified file. If the parameter is not set or the file can't be created the ConsoleHandler will be used instead. |
| allowEncodingChanges          | Boolean | false   | Allow for changes in client_encoding |
| logUnclosedConnections        | Boolean | false   | When connections that are not explicitly closed are garbage collected, log the stacktrace from the opening of the connection to trace the leak source |
| binaryTransferEnable          | String  | ""      | Comma separated list of types to enable binary transfer. Either OID numbers or names |
| binaryTransferDisable         | String  | ""      | Comma separated list of types to disable binary transfer. Either OID numbers or names. Overrides values in the driver default set and values set with binaryTransferEnable. |
| prepareThreshold              | Integer | 5       | Statement prepare threshold. A value of -1 stands for forceBinary |
| preparedStatementCacheQueries | Integer | 256     | Specifies the maximum number of entries in per-connection cache of prepared statements. A value of 0 disables the cache. |
| preparedStatementCacheSizeMiB | Integer | 5       | Specifies the maximum size (in megabytes) of a per-connection prepared statement cache. A value of 0 disables the cache. |
| defaultRowFetchSize           | Integer | 0       | Positive number of rows that should be fetched from the database when more rows are needed for ResultSet by each fetch iteration |
| loginTimeout                  | Integer | 0       | Specify how long to wait for establishment of a database connection.|
| connectTimeout                | Integer | 10      | The timeout value used for socket connect operations. |
| socketTimeout                 | Integer | 0       | The timeout value used for socket read operations. |
| tcpKeepAlive                  | Boolean | false   | Enable or disable TCP keep-alive. |
| ApplicationName               | String  | null    | The application name (require server version >= 9.0) |
| readOnly                      | Boolean | true    | Puts this connection in read-only mode |
| disableColumnSanitiser        | Boolean | false   | Enable optimization that disables column name sanitiser |
| assumeMinServerVersion        | String  | null    | Assume the server is at least that version |
| currentSchema                 | String  | null    | Specify the schema (or several schema separated by commas) to be set in the search-path |
| targetServerType              | String  | any     | Specifies what kind of server to connect, possible values: any, master, slave (deprecated), secondary, preferSlave (deprecated), preferSecondary |
| hostRecheckSeconds            | Integer | 10      | Specifies period (seconds) after which the host status is checked again in case it has changed |
| loadBalanceHosts              | Boolean | false   | If disabled hosts are connected in the given order. If enabled hosts are chosen randomly from the set of suitable candidates |
| socketFactory                 | String  | null    | Specify a socket factory for socket creation |
| socketFactoryArg (deprecated) | String  | null    | Argument forwarded to constructor of SocketFactory class. |
| autosave                      | String  | never   | Specifies what the driver should do if a query fails, possible values: always, never, conservative |
| cleanupSavepoints             | Boolean | false   | In Autosave mode the driver sets a SAVEPOINT for every query. It is possible to exhaust the server shared buffers. Setting this to true will release each SAVEPOINT at the cost of an additional round trip. |
| preferQueryMode               | String  | extended | Specifies which mode is used to execute queries to database, possible values: extended, extendedForPrepared, extendedCacheEverything, simple |
| reWriteBatchedInserts         | Boolean | false   | Enable optimization to rewrite and collapse compatible INSERT statements that are batched. |
| escapeSyntaxCallMode          | String  | select  | Specifies how JDBC escape call syntax is transformed into underlying SQL (CALL/SELECT), for invoking procedures or functions (requires server version >= 11), possible values: select, callIfNoReturn, call |
| maxResultBuffer               | String  | null    | Specifies size of result buffer in bytes, which can't be exceeded during reading result set. Can be specified as particular size (i.e. "100", "200M" "2G") or as percent of max heap memory (i.e. "10p", "20pct", "50percent") |
| gssEncMode                    | String  | allow   | Controls the preference for using GSSAPI encryption for the connection,  values are disable, allow, prefer, and require |

??簡單測試，Properties對象中定義的username=benchmarksql，password=changeme，URL中定義了user=postgres&password=postgres，與數據庫建立連接時使用的是URL中的用戶名和密碼。

[root@pgtest3 ~]# vi jdbc.properties
url=jdbc:postgresql://192.168.58.20:5000/benchmarksql?user=postgres&password=postgres
username=benchmarksql
password=changeme
driverclass=org.postgresql.Driver

[root@pgtest3 ~]# java PostgreSQLJDBC -cp /enmo/soft/postgresql-42.2.20.jar
inet_server_addr: 192.168.58.10
pg_is_in_recovery: f
current_database: benchmarksql
current_user: postgres      # 使用URL中的用戶建立連接

連接的故障轉移和負載均衡

• 支持連接的故障轉移，連接串配置如下，驅動程序將依次嘗試連接到它們中的每一個，直到連接成功。 如果沒有成功，則拋出連接異常。
  jdbc:postgresql://host1:port1,host2:port2/database

# 192.168.58.10是主節點，192.168.58.11/12是只讀從節點
[root@pgtest3 ~]# vi jdbc.properties
url=jdbc:postgresql://192.168.58.10:5432,192.168.58.11:5432,192.168.58.12:5432/benchmarksql
username=postgres
password=postgres
driverclass=org.postgresql.Driver

[root@pgtest3 ~]# java PostgreSQLJDBC -cp /enmo/soft/postgresql-42.2.20.jar
inet_server_addr: 192.168.58.10
pg_is_in_recovery: f
current_database: benchmarksql
current_user: postgres

# 關閉主庫
[root@pgtest1 ~]# patronictl pause
Success: cluster management is paused
[root@pgtest1 ~]# su - postgres
[postgres@pgtest1 ~]$ pg_ctl stop
waiting for server to shut down....... done
server stopped

# 連接故障轉移到其他節點
[root@pgtest3 ~]# java PostgreSQLJDBC -cp /enmo/soft/postgresql-42.2.20.jar
inet_server_addr: 192.168.58.11
pg_is_in_recovery: t
current_database: benchmarksql
current_user: postgres

??在針對每個節點上具有相同數據的 PostgreSQL 高可用性時，故障轉移非常有用。 例如 replication postgres 或 postgres-xc 集群。

• 一個應用程序可以創建兩個連接池。 一個連接池用于寫入，另一個用于讀取。 寫入的連接池限制僅連接到一個主節點：
  jdbc:postgresql://node1,node2,node3/accounting?targetServerType=primary

# 192.168.58.10是主節點，192.168.58.11/12是只讀從節點
[root@pgtest3 ~]# vi jdbc.properties
url=jdbc:postgresql://192.168.58.10:5432,192.168.58.11:5432,192.168.58.12:5432/benchmarksql?targetServerType=primary
username=postgres
password=postgres
driverclass=org.postgresql.Driver

[root@pgtest3 ~]# java PostgreSQLJDBC -cp /enmo/soft/postgresql-42.2.20.jar
inet_server_addr: 192.168.58.10       # 連接主庫
pg_is_in_recovery: f
current_database: benchmarksql
current_user: postgres

# 將 192.168.58.11:5432 放到第一位
[root@pgtest3 ~]# vi jdbc.properties
url=jdbc:postgresql://192.168.58.11:5432,192.168.58.10:5432,192.168.58.12:5432/benchmarksql?targetServerType=primary
username=postgres
password=postgres
driverclass=org.postgresql.Driver

[root@pgtest3 ~]# java PostgreSQLJDBC -cp /enmo/soft/postgresql-42.2.20.jar
inet_server_addr: 192.168.58.10       # 連接還是主庫，說明targetServerType可以判斷數據庫是主還是從
pg_is_in_recovery: f
current_database: benchmarksql
current_user: postgres

用于讀取的連接池負載均衡節點之間的連接，但如果沒有只讀從節點可用，也允許連接到主節點：
jdbc:postgresql://node1,node2,node3/accounting?targetServerType=preferSecondary&loadBalanceHosts=true

# 192.168.58.10是主節點，192.168.58.11/12是只讀從節點
[root@pgtest3 ~]# vi jdbc.properties
url=jdbc:postgresql://192.168.58.10:5432,192.168.58.11:5432,192.168.58.12:5432/benchmarksql?targetServerType=preferSecondary&loadBalanceHosts=true
username=postgres
password=postgres
driverclass=org.postgresql.Driver

[root@pgtest3 ~]# java PostgreSQLJDBC -cp /enmo/soft/postgresql-42.2.20.jar
inet_server_addr: 192.168.58.11   # 連接只讀從節點
pg_is_in_recovery: t
current_database: benchmarksql
current_user: postgres
[root@pgtest3 ~]# java PostgreSQLJDBC -cp /enmo/soft/postgresql-42.2.20.jar
inet_server_addr: 192.168.58.12   # 負載均衡連到另一個從節點
pg_is_in_recovery: t
current_database: benchmarksql
current_user: postgres

# 關閉一個從節點
[root@pgtest2 ~]# systemctl stop patroni.service
[root@pgtest3 ~]# java PostgreSQLJDBC -cp /enmo/soft/postgresql-42.2.20.jar
inet_server_addr: 192.168.58.12   # 多次執行，均只連另一個從庫
pg_is_in_recovery: t
current_database: benchmarksql
current_user: postgres

# 關閉所有從節點，只保留主節點
[root@pgtest3 ~]# systemctl stop patroni.service
[root@pgtest3 ~]# java PostgreSQLJDBC -cp /enmo/soft/postgresql-42.2.20.jar
inet_server_addr: 192.168.58.10   # 連接主庫
pg_is_in_recovery: f
current_database: benchmarksql
current_user: postgres