Executing Sql Statements Using JDBC

JDBC Package and Driver Class

  • JDBC Package

    Run build.sh in the source code directory on Linux OS to obtain the driver JAR package postgresql.jar, which is stored in the source code directory. Obtain the package from the release package named openGauss-x.x.x-OS version number-64bit-Jdbc.tar.gz.

    The driver package is compatible with PostgreSQL. The class name and structure in the driver are the same as those in the PostgreSQL driver. All applications running on PostgreSQL can be smoothly migrated to the current system.

  • Driver Class

    Before establishing a database connection, load the org.postgresql.Driver database driver class.

    NOTE: openGauss is compatible with PostgreSQL in the use of JDBC. Therefore, when two JDBC drivers are used in the same process, class names may conflict.

Loading the Driver

Load the database driver before creating a database connection.

You can load the driver in the following ways:

  • Before creating a connection, implicitly load the driver in the code: Class.forName(“org.postgresql.Driver”)

  • During the JVM startup, transfer the driver as a parameter to the JVM: java -Djdbc.drivers=org.postgresql.Driver jdbctest

    NOTE: jdbctest is the name of a test application.

Connecting to a Database

Function Prototype

JDBC provides the following three database connection methods:

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

Parameter

Table 1 Database connection parameters

Parameter

Description

url

postgresql.jar database connection descriptor. The format is as follows:

  • 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
NOTE:
  • database indicates the name of the database to connect.
  • host indicates the name or IP address of the database server.

    If a machine connected to openGauss is not in the same network segment as openGauss, the IP address specified by host should be the value of coo.cooListenIp2 (application access IP address) set in Manager.

    For security purposes, the primary database node forbids access from other nodes in openGauss without authentication. To access the primary database node from inside openGauss, deploy the JDBC program on the host where the primary database node is located and set host to 127.0.0.1. Otherwise, the error message "FATAL: Forbid remote connection with trust method!" may be displayed.

    It is recommended that the service system be deployed outside openGauss. If it is deployed inside, database performance may be affected.

    By default, the local host is used to connect to the server.

  • port indicates the port number of the database server.

    By default, the database on port 5431 of the local host is connected.

  • param indicates a database connection attribute.

    The parameter can be configured in the URL. The URL starts with a question mark (?), uses an equal sign (=) to assign a value to the parameter, and uses an ampersand (&) to separate parameters. You can also use the attributes of the info object for configuration. For details, see the example below.

  • value indicates the database connection attribute values.

info

Database connection attributes. Common attributes are described as follows:

  • PGDBNAME: string type. This parameter specifies the database name. (This parameter does not need to be set in the URL. The system automatically parses the URL to obtain its value.)
  • PGHOST: string type. This parameter specifies the host IP address. For details, see the example below.
  • PGPORT: integer type. This parameter specifies the host port number. For details, see the example below.
  • user: string type. This parameter specifies the database user who creates the connection.
  • password: string type. This parameter specifies the password of the database user.
  • loggerLevel: string type. The following log levels are supported: OFF, DEBUG, and TRACE. The value OFF indicates that the log function is disabled. DEBUG and TRACE logs record information of different levels.
  • loggerFile: string type. This parameter specifies the name of a log file. You can specify a directory for storing logs. If no directory is specified, logs are stored in the directory where the client program is running.
  • allowEncodingChanges: Boolean type. If this parameter is set to true, the character set type can be changed. This parameter is used together with characterEncoding=CHARSET to set the character set. The two parameters are separated by ampersands (&).
  • currentSchema: string type. This parameter specifies the schema to be set in search-path.
  • hostRecheckSeconds: integer type. After JDBC attempts to connect to a host, the host status is saved: connection success or connection failure. This status is trusted within the duration specified by hostRecheckSeconds. After the duration expires, the status becomes invalid. The default value is 10 seconds.
  • ssl: Boolean type. This parameter specifies a connection in SSL mode.

    When this parameter is set to true, the NonValidatingFactory channel and certificate mode are supported.

    For the NonValidatingFactory channel, configure the username and password and set SSL to true.

    In certification mode, configure the client certificate, key, and root certificate, and set SSL to true.

  • sslmode: string type. This parameter specifies the SSL authentication mode. The value can be require, verify-ca, or verify-full.
    • require: The system attempts to set up an SSL connection. If there is a CA file, the system performs verification as if the parameter was set to verify-ca.
    • verify-ca: The system attempts to set up an SSL connection and checks whether the server certificate is issued by a trusted CA.
    • verify-full: The system attempts to set up an SSL connection, checks whether the server certificate is issued by a trusted CA, and checks whether the host name of the server is the same as that in the certificate.
  • sslcert: string type. This parameter specifies the complete path of the certificate file. The type of the client and server certificates is End Entity.
  • sslkey: string type. This parameter specifies the complete path of the key file. You must run the following command to convert the client certificate to the DER format:
    openssl pkcs8 -topk8 -outform DER -in client.key -out client.key.pk8 -nocrypt
  • sslrootcert: string type. This parameter specifies the name of the SSL root certificate. The root certificate type is CA.
  • sslpassword: string type. This parameter is provided for ConsoleCallbackHandler.
  • sslpasswordcallback: string type. This parameter specifies the class name of the SSL password provider. The default value is org.postgresql.ssl.jdbc4.LibPQFactory.ConsoleCallbackHandler.
  • sslfactory: string type. This parameter specifies the class name used by SSLSocketFactory to establish an SSL connection.
  • sslfactoryarg: string type. The value is an optional parameter of the constructor function of the sslfactory class and is not recommended.
  • sslhostnameverifier: string type. This parameter specifies the class name of the host name verifier. The interface must implement javax.net.ssl.HostnameVerifier. The default value is org.postgresql.ssl.PGjdbcHostnameVerifier.
  • loginTimeout: integer type. This parameter specifies the waiting time for establishing the database connection, in seconds.
  • connectTimeout: integer type. This parameter specifies the timeout duration for connecting to a server, in seconds. If the time taken to connect to a server exceeds the value specified, the connection is interrupted. If the value is 0, the timeout mechanism is disabled.
  • socketTimeout: integer type. This parameter specifies the timeout duration for a socket read operation, in seconds. If the time taken to read data from a server exceeds the value specified, the connection is closed. If the value is 0, the timeout mechanism is disabled.
  • cancelSignalTimeout: integer type. Cancel messages may cause a block. This parameter controls connectTimeout and socketTimeout in a cancel message, in seconds. The default value is 10 seconds.
  • tcpKeepAlive: Boolean type. This parameter is used to enable or disable TCP keepalive detection. The default value is false.
  • logUnclosedConnections: Boolean type. The client may leak a connection object because it does not call the connection object's close() method. These objects will be collected as garbage and finalized using the finalize() method. If the caller ignores this operation, this method closes the connection.
  • assumeMinServerVersion: string type. The client sends a request to set a floating point. This parameter specifies the version of the server to connect, for example, assumeMinServerVersion=9.0. This parameter can reduce the number of packets to send during connection setup.
  • ApplicationName: string type. This parameter specifies the name of the JDBC driver that is being connected. You can query the pg_stat_activity table on the primary database node to view information about the client that is being connected. The JDBC driver name is displayed in the application_name column. The default value is PostgreSQL JDBC Driver.
  • connectionExtraInfo: Boolean type. This parameter specifies whether the JDBC driver reports the driver deployment path and process owner to the database.

    The value can be true or false. The default value is false. If connectionExtraInfo is set to true, the JDBC driver reports the driver deployment path and process owner to the database and displays the information in the connection_info parameter. In this case, you can query the information from PG_STAT_ACTIVITY.

  • autosave: string type. The value can be always, never, or conservative. The default value is never. This parameter specifies the action that the driver should perform upon a query failure. If autosave is set to always, the JDBC driver sets a savepoint before each query and rolls back to the savepoint if the query fails. If autosave is set to never, there is no savepoint. If autosave is set to conservative, a savepoint is set for each query. However, the system rolls back and retries only when there is an invalid statement.
  • protocolVersion: integer type. This parameter specifies the connection protocol version. Only version 3 is supported. Note: MD5 encryption is used when this parameter is set. You must use the following command to change the database encryption mode: gs_guc set -N all -I all -c "password_encryption_type=1". After openGauss is restarted, create a user that uses MD5 encryption to encrypt passwords. You must also change the client connection mode to md5 in the pg_hba.conf file. Log in as the new user (not recommended).
  • prepareThreshold: integer type. This parameter specifies the time when the parse statement is sent. The default value is 5. It takes a long time to parse an SQL statement for the first time, but a short time to parse SQL statements later because of cache. If a session runs an SQL statement multiple consecutive times and the number of execution times exceeds the value of prepareThreshold, JDBC does not send the parse command to the SQL statement.
  • preparedStatementCacheQueries: integer type. This parameter specifies the number of queries cached in each connection. The default value is 256. If more than 256 different queries are used in the prepareStatement() call, the least recently used query cache will be discarded. The value 0 indicates that the cache function is disabled.
  • preparedStatementCacheSizeMiB: integer type. This parameter specifies the maximum cache size of each connection, in MB. The default value is 5. If the size of the cached queries exceeds 5 MB, the least recently used query cache will be discarded. The value 0 indicates that the cache function is disabled.
  • databaseMetadataCacheFields: integer type. The default value is 65536. This parameter specifies the maximum cache size of each connection. The value 0 indicates that the cache function is disabled.
  • databaseMetadataCacheFieldsMiB: integer type. The default value is 5. This parameter specifies the maximum cache size of each connection, in MB. The value 0 indicates that the cache function is disabled.
  • stringtype: string type. The value can be false, unspecified, or varchar. The default value is varchar. This parameter specifies the type of the PreparedStatement parameter used by the setString() method. If stringtype is set to varchar, these parameters are sent to the server as varchar parameters. If stringtype is set to unspecified, these parameters are sent to the server as an untyped value, and the server attempts to infer their appropriate type.
  • batchMode: Boolean type. This parameter specifies whether to connect the database in batch mode.
  • fetchsize: integer type. This parameter specifies the default fetchsize for statements in the created connection.
  • reWriteBatchedInserts: Boolean type. During batch import, this parameter, if set to on, can combine N insertion statements into one: insert into TABLE_NAME values(values1, ..., valuesN), ..., (values1, ..., valuesN). To use this parameter, set batchMode to off.
  • unknownLength: integer type. The default value is Integer.MAX_VALUE. This parameter specifies the length of the unknown length type when the data of some postgresql types (such as TEXT) is returned by functions such as ResultSetMetaData.getColumnDisplaySize and ResultSetMetaData.getPrecision.
  • defaultRowFetchSize: integer type. This parameter specifies the number of rows read by fetch in ResultSet at a time. Limiting the number of rows read each time in a database access request can avoid unnecessary memory consumption, thereby avoiding out of memory exception. The default value is 0, indicating that all rows are obtained at a time in ResultSet. There is no negative value.
  • binaryTransfer: Boolean type. This parameter specifies whether data is sent and received in binary format. The default value is false.
  • binaryTransferEnable: string type. This parameter specifies the type for which binary transmission is enabled. Every two types are separated by commas (,). You can select either the OID or name, for example, binaryTransferEnable=Integer4_ARRAY,Integer8_ARRAY.

    For example, if the OID name is BLOB and the OID number is 88, you can configure the OID as follows:

    binaryTransferEnable=BLOB or binaryTransferEnable=88

  • binaryTransferDisEnable: string type. This parameter specifies the type for which binary transmission is disabled. Every two types are separated by commas (,). You can select either the OID or name. The value of this parameter overwrites the value of binaryTransferEnable.
  • blobMode: string type. This parameter sets the setBinaryStream method to assign values to different types of data. The value on indicates that values are assigned to blob data. The value off indicates that values are assigned to bytea data. The default value is on.
  • socketFactory: string type. This parameter specifies the name of the class used to create a socket connection with the server. This class must implement the javax.net.SocketFactory interface and define a constructor with no parameter or a single string parameter.
  • socketFactoryArg: string type. The value is an optional parameter of the constructor function of the socketFactory class and is not recommended.
  • receiveBufferSize: integer type. This parameter is used to set SO_RCVBUF on the connection stream.
  • sendBufferSize: integer type. This parameter is used to set SO_SNDBUF on the connection stream.
  • preferQueryMode: string type. The value can be extended, extendedForPrepared, extendedCacheEverything, or simple. This parameter specifies the query mode. In simple mode, the query is executed without parsing or binding. In extended mode, the query is executed and bound. The extendedForPrepared mode is used for prepared statement extension. In extendedCacheEverything mode, each statement is cached.

user

Database user.

password

Password of the database user.

Examples

  • The following illustrates how to connect database based on openGauss JDBC interfaces.

    // The following code encapsulates database connection operations into an interface. The database can then be connected using an authorized username and a password.
    public static Connection getConnect(String username, String passwd)
        {
            // Driver class.
            String driver = "org.postgresql.Driver";
            // Database connection descriptor.
            String sourceURL = "jdbc:postgresql://10.10.0.13:8000/postgres";
            Connection conn = null;
    
            try
            {
                // Load the driver.
                Class.forName(driver);
            }
            catch( Exception e )
            {
                e.printStackTrace();
                return null;
            }
    
            try
            {
                 // Create a connection.
                conn = DriverManager.getConnection(sourceURL, username, passwd);
                System.out.println("Connection succeed!");
            }
            catch(Exception e)
            {
                e.printStackTrace();
                return null;
            }
    
            return conn;
        };
    // The following code uses the Properties object as a parameter to establish a connection.
    public static Connection getConnectUseProp(String username, String passwd)
        {
            // Driver class.
            String driver = "org.postgresql.Driver";
            // Database connection descriptor.
            String sourceURL = "jdbc:postgresql://10.10.0.13:8000/postgres?";
            Connection conn = null;
            Properties info = new Properties();
    
            try
            {
                // Load the driver.
                Class.forName(driver);
            }
            catch( Exception e )
            {
                e.printStackTrace();
                return null;
            }
    
            try
            {
                 info.setProperty("user", username);
                 info.setProperty("password", passwd);
                 // Create a connection.
                 conn = DriverManager.getConnection(sourceURL, info);
                 System.out.println("Connection succeed!");
            }
            catch(Exception e)
            {
                e.printStackTrace();
                return null;
            }
    
            return conn;
        };
    
  • The following illustrates how to develop applications based on openGauss JDBC interfaces.

    //DBtest.java
    // This example illustrates the main processes of JDBC-based development, covering database connection creation, table creation, and data insertion.
    
    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 {
    
      // Create a database connection.
      public static Connection GetConnection(String username, String passwd) {
        String driver = "org.postgresql.Driver";
        String sourceURL = "jdbc:postgresql://localhost:8000/postgres";
        Connection conn = null;
        try {
          // Load the database driver.
          Class.forName(driver).newInstance();
        } catch (Exception e) {
          e.printStackTrace();
          return null;
        }
    
        try {
          // Create a database connection.
          conn = DriverManager.getConnection(sourceURL, username, passwd);
          System.out.println("Connection succeed!");
        } catch (Exception e) {
          e.printStackTrace();
          return null;
        }
    
        return conn;
      };
    
      // Run a common SQL statement to create table customer_t1.
      public static void CreateTable(Connection conn) {
        Statement stmt = null;
        try {
          stmt = conn.createStatement();
    
          // Run a common SQL statement.
          int rc = stmt
              .executeUpdate("CREATE TABLE customer_t1(c_customer_sk INTEGER, c_customer_name VARCHAR(32));");
    
          stmt.close();
        } catch (SQLException e) {
          if (stmt != null) {
            try {
              stmt.close();
            } catch (SQLException e1) {
              e1.printStackTrace();
            }
          }
          e.printStackTrace();
        }
      }
    
      // Run a prepared statement to insert data in batches.
      public static void BatchInsertData(Connection conn) {
        PreparedStatement pst = null;
    
        try {
          // Generate a prepared statement.
          pst = conn.prepareStatement("INSERT INTO customer_t1 VALUES (?,?)");
          for (int i = 0; i < 3; i++) {
            // Add parameters.
            pst.setInt(1, i);
            pst.setString(2, "data " + i);
            pst.addBatch();
          }
          // Perform batch processing.
          pst.executeBatch();
          pst.close();
        } catch (SQLException e) {
          if (pst != null) {
            try {
              pst.close();
            } catch (SQLException e1) {
            e1.printStackTrace();
            }
          }
          e.printStackTrace();
        }
      }
    
      // Run a prepared statement to update data.
      public static void ExecPreparedSQL(Connection conn) {
        PreparedStatement pstmt = null;
        try {
          pstmt = conn
              .prepareStatement("UPDATE customer_t1 SET c_customer_name = ? WHERE c_customer_sk = 1");
          pstmt.setString(1, "new Data");
          int rowcount = pstmt.executeUpdate();
          pstmt.close();
        } catch (SQLException e) {
          if (pstmt != null) {
            try {
              pstmt.close();
            } catch (SQLException e1) {
              e1.printStackTrace();
            }
          }
          e.printStackTrace();
        }
      }
    
    
    // Run a stored procedure.
      public static void ExecCallableSQL(Connection conn) {
        CallableStatement cstmt = null;
        try {
    
          cstmt=conn.prepareCall("{? = CALL TESTPROC(?,?,?)}");
          cstmt.setInt(2, 50); 
          cstmt.setInt(1, 20);
          cstmt.setInt(3, 90);
          cstmt.registerOutParameter(4, Types.INTEGER);  // Register an OUT parameter of the integer type.
          cstmt.execute();
          int out = cstmt.getInt(4);  // Obtain the OUT parameter.
          System.out.println("The CallableStatment TESTPROC returns:"+out);
          cstmt.close();
        } catch (SQLException e) {
          if (cstmt != null) {
            try {
              cstmt.close();
            } catch (SQLException e1) {
              e1.printStackTrace();
            }
          }
          e.printStackTrace();
        }
      }
    
    
      /**
       *Main process. Call static methods one by one.
       * @param args
      */
      public static void main(String[] args) {
        // Create a database connection.
        Connection conn = GetConnection("tester", "Password1234");
    
        // Create a table.
        CreateTable(conn);
    
        // Insert data in batches.
        BatchInsertData(conn);
    
        // Run a prepared statement to update data.
        ExecPreparedSQL(conn);
    
        // Run a stored procedure.
        ExecCallableSQL(conn);
    
        // Close the connection to the database.
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    
      }
    
    }
    
Feedback
编组 3备份
    openGauss 2024-06-24 00:44:47
    cancel