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