JDBC and ODBC Development Specifications

Application Scenarios

The core advantage of a connection pool lies in the reuse of connections, which avoids the network overhead and authentication costs caused by frequent establishment and disconnection of database connections. A connection pool is recommended in the following scenarios:

• High concurrency: Applications need to process a large number of concurrent database requests. Frequent connection creation affects performance.
• Short connections: Each database operation takes a short time, and the overhead of establishing or disconnecting a connection is relatively high.
• Microservice architecture: Multiple service instances share a connection pool, improving resource utilization.

Notes and Constraints

• The session status must be cleared before a connection in the connection pool is returned. Otherwise, the following residual configurations will be used in subsequent operations, resulting in unexpected results:
  • Residual permissions: If the user identity is switched in a session but not reset, subsequent operations will be performed using an incorrect user identity, posing security risks.
  • Residual configurations: Residual GUC parameters may cause exceptions in subsequent query operations (for example, incorrect timeout interval or isolation level).
  • Object conflicts: Residual temporary tables may conflict with objects with the same names in subsequent operations, causing errors.
• The specifications must be followed only when a connection pool is used. No additional processing is required for a single connection (a connection is created for an operation and closed when the operation is complete).

Development Specifications

Before an application returns a connection to the connection pool, process the session status according to the following specifications:

1. Clear GUC parameters and session identities: If GUC parameters are set or the session user identity is switched during the use of a connection, run the following commands to clear the session status before returning the connection:
   1. Reset the session user identity to the initial login user:

      SET SESSION AUTHORIZATION DEFAULT; 

   2. Restore all modified GUC parameters in the current session to their default values:

      RESET ALL;

2. Delete temporary tables: If temporary tables are created during the use of a connection, you must delete all temporary tables before returning the connection. You can run the following command, where temp_table_name indicates the temporary table name:

   DROP TABLE IF EXISTS temp_table_name; 

   Alternatively, you can end the session (temporary tables will be automatically cleared with the session). However, you need to explicitly delete the temporary tables because the connection will not be truly closed when a connection pool is used.
3. (Optional) Verify that the session status has been cleared: Before returning a connection, run the following command to check whether the session status has been reset:

   SHOW session_authorization;

   If the initial login user is returned, the session identity has been successfully reset.

Documentation

• Downloading a driver: Downloading the JDBC or ODBC Driver
• JDBC and ODBC development: Using JDBC or ODBC for DWS Secondary Development
• FAQs: How Do I Do If I Cannot Connect to a DWS Cluster?