通过JDBC执行SQL

JDBC包与驱动类

  • JDBC包

    在linux服务器端源代码目录下执行build.sh,获得驱动jar包postgresql.jar,包位置在源代码目录下。从发布包中获取,包名为openGauss-x.x.x-操作系统版本号-64bit-Jdbc.tar.gz。

    驱动包与PostgreSQL保持兼容,其中类名、类结构与PostgreSQL驱动完全一致,曾经运行于PostgreSQL的应用程序可以直接移植到当前系统使用。

  • 驱动类

    在创建数据库连接之前,需要加载数据库驱动类“org.postgresql.Driver”。

    通过JDBC执行SQL - 图1 说明: 由于openGauss在JDBC的使用上与PG的使用方法保持兼容,所以同时在同一进程内使用两个JDBC驱动的时候,可能会类名冲突。

加载驱动

在创建数据库连接之前,需要先加载数据库驱动程序。

加载驱动有两种方法:

  • 在代码中创建连接之前任意位置隐含装载:Class.forName(“org.postgresql.Driver”);
  • 在JVM启动时参数传递:java -Djdbc.drivers=org.postgresql.Driver jdbctest

    通过JDBC执行SQL - 图2 说明: 上述jdbctest为测试用例程序的名称。

连接数据库

在创建数据库连接之后,才能使用它来执行SQL语句操作数据。

函数原型

JDBC提供了三个方法,用于创建数据库连接。

  • DriverManager.getConnection(String url);
  • DriverManager.getConnection(String url, Properties info);
  • DriverManager.getConnection(String url, String user, String password);

参数

表 1 数据库连接参数

参数

描述

url

postgresql.jar数据库连接描述符。格式如下:

  • jdbc:postgresql:database
  • jdbc:postgresql://host/database
  • jdbc:postgresql://host:port/database
  • jdbc:postgresql://host:port/database?param1=value1&param2=value2
  • jdbc:postgresql://host1:port1,host2:port2/database?param1=value1&param2=value2
说明:
  • database为要连接的数据库名称。
  • host为数据库服务器名称或IP地址。

    连接openGauss的机器与openGauss不在同一网段时,host指定的IP地址应为Manager界面上所设的coo.cooListenIp2(应用访问IP)的取值。

    由于安全原因,数据库主节点禁止openGauss内部其他节点无认证接入。如果要在openGauss内部访问数据库主节点,请将JDBC程序部署在数据库主节点所在机器,host使用“127.0.0.1”。否则可能会出现“FATAL: Forbid remote connection with trust method!”错误。

    建议业务系统单独部署在openGauss外部,否则可能会影响数据库运行性能。

    缺省情况下,连接服务器为localhost。

  • port为数据库服务器端口。

    缺省情况下,会尝试连接到5431端口的database。

  • param为参数名称,即数据库连接属性。

    参数可以配置在URL中,以“?”开始配置,以“=”给参数赋值,以“&”作为不同参数的间隔。也可以采用info对象的属性方式进行配置,详细示例会在本节给出。

  • value为参数值,即数据库连接属性值。

info

数据库连接属性。常用的属性如下:

  • PGDBNAME:String类型。表示数据库名称。(URL中无需配置该参数,自动从URL中解析)
  • PGHOST:String类型。主机IP地址。详细示例见下。
  • PGPORT:Integer类型。主机端口号。详细示例见下。
  • user:String类型。表示创建连接的数据库用户。
  • password:String类型。表示数据库用户的密码。
  • loggerLevel:String类型。目前支持3种级别:OFF、DEBUG、TRACE。设置为OFF关闭日志,设置为DEBUG和TRACE记录的日志信息详细程度不同。
  • loggerFile:String类型。Logger输出的文件名。需要显示指定日志文件名,若未指定目录则生成在客户端运行程序目录。
  • allowEncodingChanges:Boolean类型。设置该参数值为“true”进行字符集类型更改,配合characterEncoding=CHARSET设置字符集,二者使用“&”分隔。
  • currentSchema:String类型。在search-path中指定要设置的schema。
  • hostRecheckSeconds:Integer类型。JDBC尝试连接主机后会保存主机状态:连接成功或连接失败。在hostRecheckSeconds时间内保持可信,超过则状态失效。缺省值是10秒。
  • ssl:Boolean类型。以SSL方式连接。

    ssl=true可支持NonValidatingFactory通道和使用证书的方式:

    1、NonValidatingFactory通道需要配置用户名和密码,同时将SSL设置为true。

    2、配置客户端证书、密钥、根证书,将SSL设置为true。

  • sslmode:String类型。SSL认证方式。取值范围为:require、verify-ca、verify-full。
    • require只尝试SSL连接,如果存在CA文件,则应设置成verify-ca的方式验证。
    • verify-ca只尝试SSL连接,并且验证服务器是否具有由可信任的证书机构签发的证书。
    • verify-full只尝试SSL连接,并且验证服务器是否具有由可信任的证书机构签发的证书,以及验证服务器主机名是否与证书中的一致。
  • sslcert:String类型。提供证书文件的完整路径。客户端和服务端证书的类型为End Entity。
  • sslkey:String类型。提供密钥文件的完整路径。使用时将客户端证书转换为DER格式:
    1. openssl pkcs8 -topk8 -outform DER -in client.key -out client.key.pk8 -nocrypt
  • sslrootcert:String类型。SSL根证书的文件名。根证书的类型为CA。
  • sslpassword:String类型。提供给ConsoleCallbackHandler使用。
  • sslpasswordcallback:String类型。SSL密码提供者的类名。缺省值:org.postgresql.ssl.jdbc4.LibPQFactory.ConsoleCallbackHandler。
  • sslfactory:String类型。提供的值是SSLSocketFactory在建立SSL连接时用的类名。
  • sslfactoryarg:String类型。此值是上面提供的sslfactory类的构造函数的可选参数(不推荐使用)。
  • sslhostnameverifier:String类型。主机名验证程序的类名。接口实现javax.net.ssl.HostnameVerifier,默认使用org.postgresql.ssl.PGjdbcHostnameVerifier。
  • loginTimeout:Integer类型。指建立数据库连接的等待时间。超时时间单位为秒。
  • connectTimeout:Integer类型。用于连接服务器操作的超时值。如果连接到服务器花费的时间超过此值,则连接断开。超时时间单位为秒,值为0时表示已禁用,timeout不发生。
  • socketTimeout:Integer类型。用于socket读取操作的超时值。如果从服务器读取所花费的时间超过此值,则连接关闭。超时时间单位为秒,值为0时表示已禁用,timeout不发生。
  • cancelSignalTimeout:Integer类型。发送取消消息本身可能会阻塞,此属性控制用于取消命令的“connect超时”和“socket超时”。超时时间单位为秒,默认值为10秒。
  • tcpKeepAlive:Boolean类型。启用或禁用TCP保活探测功能。默认为false。
  • logUnclosedConnections:Boolean类型。客户端可能由于未调用Connection对象的close()方法而泄漏Connection对象。最终这些对象将被垃圾回收,并且调用finalize()方法。如果调用者自己忽略了此操作,该方法将关闭Connection。
  • assumeMinServerVersion:String类型。客户端会发送请求进行float精度设置。该参数设置要连接的服务器版本,如assumeMinServerVersion=9.0,可以在建立时减少相关包的发送。
  • ApplicationName:String类型。设置正在使用连接的JDBC驱动的名称。通过在数据库主节点上查询pg_stat_activity表可以看到正在连接的客户端信息,JDBC驱动名称显示在application_name列。缺省值为PostgreSQL JDBC Driver。
  • connectionExtraInfo:Boolean类型。表示驱动是否上报当前驱动的部署路径、进程属主用户到数据库。

    取值范围:true或false,默认值为false。设置connectionExtraInfo为true,JDBC驱动会将当前驱动的部署路径、进程属主用户上报到数据库中,记录在connection_info参数里;同时可以在PG_STAT_ACTIVITY中查询到。

  • autosave:String类型。共有3种:”always”, “never”, “conservative”。如果查询失败,指定驱动程序应该执行的操作。在autosave=always模式下,JDBC驱动程序在每次查询之前设置一个保存点,并在失败时回滚到该保存点。在autosave=never模式(默认)下,无保存点。在autosave=conservative模式下,每次查询都会设置保存点,但是只会在“statement XXX无效”等情况下回滚并重试。
  • protocolVersion:Integer类型。连接协议版本号,目前仅支持3。注意:设置该参数时将采用md5加密方式,需要同步修改数据库的加密方式:gs_guc set -N all -I all -c “password_encryption_type=1” ,重启openGauss生效后需要创建用md5方式加密口令的用户。同时修改pg_hba.conf,将客户端连接方式修改为md5。用新建用户进行登录(不推荐)。
  • prepareThreshold:Integer类型。控制parse语句何时发送。默认值是5。第一次parse一个SQL比较慢,后面再parse就会比较快,因为有缓存了。如果一个会话连续多次执行同一个SQL,在达到prepareThreshold次数以上时,JDBC将不再对这个SQL发送parse命令。
  • preparedStatementCacheQueries:Integer类型。确定每个连接中缓存的查询数,默认情况下是256。若在prepareStatement()调用中使用超过256个不同的查询,则最近最少使用的查询缓存将被丢弃。0表示禁用缓存。
  • preparedStatementCacheSizeMiB:Integer类型。确定每个连接可缓存的最大值(以兆字节为单位),默认情况下是5。若缓存了超过5MB的查询,则最近最少使用的查询缓存将被丢弃。0表示禁用缓存。
  • databaseMetadataCacheFields:Integer类型。默认值是65536。指定每个连接可缓存的最大值。“0”表示禁用缓存。
  • databaseMetadataCacheFieldsMiB:Integer类型。默认值是5。每个连接可缓存的最大值,单位是MB。“0”表示禁用缓存。
  • stringtype:String类型,可选字段为:false, “unspecified”, “varchar”。设置通过setString()方法使用的PreparedStatement参数的类型,如果stringtype设置为VARCHAR(默认值),则这些参数将作为varchar参数发送给服务器。若stringtype设置为unspecified,则参数将作为untyped值发送到服务器,服务器将尝试推断适当的类型。
  • batchMode:Boolean类型。用于确定是否使用batch模式连接。
  • fetchsize:Integer类型。用于设置数据库连接所创建statement的默认fetchsize。
  • reWriteBatchedInserts:Boolean类型。批量导入时,该参数设置为on,可将N条插入语句合并为一条:insert into TABLE_NAME values(values1, …, valuesN), …, (values1, …, valuesN);使用该参数时,需设置batchMode=off。
  • unknownLength:Integer类型,默认为Integer.MAX_VALUE。某些postgresql类型(例如TEXT)没有明确定义的长度,当通过ResultSetMetaData.getColumnDisplaySize和ResultSetMetaData.getPrecision等函数返回关于这些类型的数据时,此参数指定未知长度类型的长度。
  • defaultRowFetchSize:Integer类型。确定一次fetch在ResultSet中读取的行数。限制每次访问数据库时读取的行数可以避免不必要的内存消耗,从而避免OutOfMemoryException。缺省值是0,这意味着ResultSet中将一次获取所有行。没有负数。
  • binaryTransfer:Boolean类型。使用二进制格式发送和接收数据,默认值为“false”。
  • binaryTransferEnable:String类型。启用二进制传输的类型列表,以逗号分隔。OID编号和名称二选一,例如binaryTransferEnable=Integer4_ARRAY,Integer8_ARRAY。

    比如:OID名称为BLOB,编号为88,可以如下配置:

    binaryTransferEnable=BLOB 或 binaryTransferEnable=88

  • binaryTransferDisEnable:String类型。禁用二进制传输的类型列表,以逗号分隔。OID编号和名称二选一。覆盖binaryTransferEnable的设置。
  • blobMode:String类型。用于设置setBinaryStream方法为不同类型的数据赋值,设置为on时表示为blob类型数据赋值,设置为off时表示为bytea类型数据赋值,默认为on。
  • socketFactory:String类型。用于创建与服务器socket连接的类的名称。该类必须实现了接口“javax.net.SocketFactory”,并定义无参或单String参数的构造函数。
  • socketFactoryArg:String类型。此值是上面提供的socketFactory类的构造函数的可选参数,不推荐使用。
  • receiveBufferSize:Integer类型。该值用于设置连接流上的SO_RCVBUF。
  • sendBufferSize:Integer类型。该值用于设置连接流上的SO_SNDBUF。
  • preferQueryMode:String类型。共有4种:”extended”、”extendedForPrepared”、”extendedCacheEverything”和”simple”。用于指定执行查询的模式,simple模式会excute,不parse和bind;extended模式会bind和excute;extendedForPrepared模式为prepared statement扩展使用;extendedCacheEverything模式会缓存每个statement。

user

数据库用户。

password

数据库用户的密码。

示例

  • 示例1:此示例将演示如何基于openGauss提供的JDBC接口连接数据库。

    1. //以下代码将获取数据库连接操作封装为一个接口,可通过给定用户名和密码来连接数据库。
    2. public static Connection getConnect(String username, String passwd)
    3. {
    4. //驱动类。
    5. String driver = "org.postgresql.Driver";
    6. //数据库连接描述符。
    7. String sourceURL = "jdbc:postgresql://10.10.0.13:8000/postgres";
    8. Connection conn = null;
    9. try
    10. {
    11. //加载驱动。
    12. Class.forName(driver);
    13. }
    14. catch( Exception e )
    15. {
    16. e.printStackTrace();
    17. return null;
    18. }
    19. try
    20. {
    21. //创建连接。
    22. conn = DriverManager.getConnection(sourceURL, username, passwd);
    23. System.out.println("Connection succeed!");
    24. }
    25. catch(Exception e)
    26. {
    27. e.printStackTrace();
    28. return null;
    29. }
    30. return conn;
    31. };
    32. // 以下代码将使用Properties对象作为参数建立连接
    33. public static Connection getConnectUseProp(String username, String passwd)
    34. {
    35. //驱动类。
    36. String driver = "org.postgresql.Driver";
    37. //数据库连接描述符。
    38. String sourceURL = "jdbc:postgresql://10.10.0.13:8000/postgres?";
    39. Connection conn = null;
    40. Properties info = new Properties();
    41. try
    42. {
    43. //加载驱动。
    44. Class.forName(driver);
    45. }
    46. catch( Exception e )
    47. {
    48. e.printStackTrace();
    49. return null;
    50. }
    51. try
    52. {
    53. info.setProperty("user", username);
    54. info.setProperty("password", passwd);
    55. //创建连接。
    56. conn = DriverManager.getConnection(sourceURL, info);
    57. System.out.println("Connection succeed!");
    58. }
    59. catch(Exception e)
    60. {
    61. e.printStackTrace();
    62. return null;
    63. }
    64. return conn;
    65. };
  • 示例2:此示例将演示如何基于openGauss提供的JDBC接口开发应用程序。

    ``` //DBtest.java //演示基于JDBC开发的主要步骤,会涉及创建数据库、创建表、插入数据等。

    import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Statement; import java.sql.CallableStatement;

    public class DBTest {

    //创建数据库连接。 public static Connection GetConnection(String username, String passwd) {

    1. String driver = "org.postgresql.Driver";
    2. String sourceURL = "jdbc:postgresql://localhost:8000/postgres";
    3. Connection conn = null;
    4. try {
    5. //加载数据库驱动。
    6. Class.forName(driver).newInstance();
    7. } catch (Exception e) {
    8. e.printStackTrace();
    9. return null;
    10. }
    11. try {
    12. //创建数据库连接。
    13. conn = DriverManager.getConnection(sourceURL, username, passwd);
    14. System.out.println("Connection succeed!");
    15. } catch (Exception e) {
    16. e.printStackTrace();
    17. return null;
    18. }
    19. return conn;

    };

    //执行普通SQL语句,创建customer_t1表。 public static void CreateTable(Connection conn) {

    1. Statement stmt = null;
    2. try {
    3. stmt = conn.createStatement();
    4. //执行普通SQL语句。
    5. int rc = stmt
    6. .executeUpdate("CREATE TABLE customer_t1(c_customer_sk INTEGER, c_customer_name VARCHAR(32));");
    7. stmt.close();
    8. } catch (SQLException e) {
    9. if (stmt != null) {
    10. try {
    11. stmt.close();
    12. } catch (SQLException e1) {
    13. e1.printStackTrace();
    14. }
    15. }
    16. e.printStackTrace();
    17. }

    }

    //执行预处理语句,批量插入数据。 public static void BatchInsertData(Connection conn) {

    1. PreparedStatement pst = null;
    2. try {
    3. //生成预处理语句。
    4. pst = conn.prepareStatement("INSERT INTO customer_t1 VALUES (?,?)");
    5. for (int i = 0; i < 3; i++) {
    6. //添加参数。
    7. pst.setInt(1, i);
    8. pst.setString(2, "data " + i);
    9. pst.addBatch();
    10. }
    11. //执行批处理。
    12. pst.executeBatch();
    13. pst.close();
    14. } catch (SQLException e) {
    15. if (pst != null) {
    16. try {
    17. pst.close();
    18. } catch (SQLException e1) {
    19. e1.printStackTrace();
    20. }
    21. }
    22. e.printStackTrace();
    23. }

    }

    //执行预编译语句,更新数据。 public static void ExecPreparedSQL(Connection conn) {

    1. PreparedStatement pstmt = null;
    2. try {
    3. pstmt = conn
    4. .prepareStatement("UPDATE customer_t1 SET c_customer_name = ? WHERE c_customer_sk = 1");
    5. pstmt.setString(1, "new Data");
    6. int rowcount = pstmt.executeUpdate();
    7. pstmt.close();
    8. } catch (SQLException e) {
    9. if (pstmt != null) {
    10. try {
    11. pstmt.close();
    12. } catch (SQLException e1) {
    13. e1.printStackTrace();
    14. }
    15. }
    16. e.printStackTrace();
    17. }

    }

  1. //执行存储过程。
  2. public static void ExecCallableSQL(Connection conn) {
  3. CallableStatement cstmt = null;
  4. try {
  5. cstmt=conn.prepareCall("{? = CALL TESTPROC(?,?,?)}");
  6. cstmt.setInt(2, 50);
  7. cstmt.setInt(1, 20);
  8. cstmt.setInt(3, 90);
  9. cstmt.registerOutParameter(4, Types.INTEGER); //注册out类型的参数,类型为整型。
  10. cstmt.execute();
  11. int out = cstmt.getInt(4); //获取out参数
  12. System.out.println("The CallableStatment TESTPROC returns:"+out);
  13. cstmt.close();
  14. } catch (SQLException e) {
  15. if (cstmt != null) {
  16. try {
  17. cstmt.close();
  18. } catch (SQLException e1) {
  19. e1.printStackTrace();
  20. }
  21. }
  22. e.printStackTrace();
  23. }
  24. }
  25. /**
  26. * 主程序,逐步调用各静态方法。
  27. * @param args
  28. */
  29. public static void main(String[] args) {
  30. //创建数据库连接。
  31. Connection conn = GetConnection("tester", "Password1234");
  32. //创建表。
  33. CreateTable(conn);
  34. //批插数据。
  35. BatchInsertData(conn);
  36. //执行预编译语句,更新数据。
  37. ExecPreparedSQL(conn);
  38. //执行存储过程。
  39. ExecCallableSQL(conn);
  40. //关闭数据库连接。
  41. try {
  42. conn.close();
  43. } catch (SQLException e) {
  44. e.printStackTrace();
  45. }
  46. }
  47. }
  48. ```