PolarDB:JDBC

Connection-level parameter features

The following features are configured using connection parameters. The supported parameters are listed in the table below. All new parameters are effective at the connection level and follow the lifecycle of the Connection object.

Data type parsing

• Date type: Support for the 64-bit Date type.

  The kernel supports the 64-bit Date type. The data format is the same as that in Oracle and includes time information. The driver can handle this Date type as a timestamp. It maps all Date types (Types.DATE or DATEOID) to the timestamp type and processes them accordingly.

• Interval type: Support for interval input in Oracle mode.

  The community PostgreSQL driver does not support interval inputs in formats such as +12 12:03:12.111. Because this is the standard output format in Oracle mode, PolarDB for PostgreSQL (Compatible with Oracle) supports this output format.

• Number type: Support for the GET behavior of the Number type.

  The standard implementation of java.sql does not have a getNumber function. It has only functions such as getInt. If a function parameter is of the Number type, you can use interfaces such as getInt, setInt, and RegisterParam to pass the parameter as an integer.

• BLOB type: BLOBs are processed as Bytea, and Clobs are processed as Text.

  This is an implementation of the java.sql.Blob and java.sql.Clob interfaces. The kernel has added mappings for BLOB and Clob types. At the Java layer, they can be processed as Bytea and Text types. The main class implements methods such as getBytes, setBytes, position, and getBinaryStream.

• Boolean type: Support for converting Boolean types to 1 or 0.

  To ensure backward compatibility, the setBoolean interface method uses true/false by default. However, you can activate the boolAsInt parameter to switch to the Oracle-compatible 1/0 semantics. This adapts to database interaction requirements that are compatible with Oracle.

  Note

  The rules for converting numeric types to Boolean types vary by driver package version:

  • Versions 42.5.4.0.10 and earlier: A number equal to 1 is treated as true. A number equal to 0 is treated as false. Other numeric values return an error.

  • Versions 42.5.4.0.11 and later: A number equal to 0 is treated as false. All other non-zero numeric values are treated as true. This behavior is consistent with the Oracle driver.

PL/SQL adaptation

• Support for stored procedures without $$ symbols.

  This feature supports creating FUNCTION/PROCEDURE objects without the $$ symbols. It also supports truncating the / character during syntax parsing.

• Support for using colon-prefixed variable names as parameters.

  This feature supports passing parameters using the :xxx format, where xxx is a variable name prefixed with a colon.

• Support for binding parameters in anonymous blocks.

• Support for suppressing PL/SQL warning messages.

  This prevents memory limits from being exceeded because of storing too many warning messages in a loop.

Load the JDBC driver

In your application, you can run the following command to load the JDBC driver:

Class.forName("com.aliyun.polardb2.Driver");

Connect to the database

jdbc:polardb://pc-***.o.polardb.rds.aliyuncs.com:1521/polardb_test?user=test&password=Pw123456

jdbc:postgresql://pc-***.o.polardb.rds.aliyuncs.com:1521/postgres?forceDriverType=True

Query data and process results

When you access the database to run a query, you can create a Statement, PreparedStatement, or CallableStatement object.

The following is an example of a PreparedStatement:

PreparedStatement st = conn.prepareStatement("select id, name from foo where id > ?");
st.setInt(1, 10);
resultSet = st.executeQuery();
while (resultSet.next()) {
    System.out.println("id:" + resultSet.getInt(1));
    System.out.println("name:" + resultSet.getString(2));
}

Call functions or stored procedures

You can use the JDBC CallableStatement object to call functions and stored procedures.

CREATE OR REPLACE PROCEDURE test_in_out_procedure (a IN number, b IN OUT number, c OUT number) IS
BEGIN
    b := a + b;
    c := b + 1;
END;

CallableStatement cstmt = connection.prepareCall("{call test_in_out_procedure(?, ?, ?)}");

// IN parameter a
cstmt.setInt(1, 1);

// IN OUT parameter b
cstmt.setInt(2, 2);
cstmt.registerOutParameter(2, Types.INTEGER);

// OUT parameter c
cstmt.registerOutParameter(3, Types.INTEGER);

cstmt.execute();

int b = cstmt.getInt(2);
int c = cstmt.getInt(3);

CREATE OR REPLACE FUNCTION test_in_out_function (a IN number, b IN OUT number, c OUT number) RETURN number AS
BEGIN
    b := a + b;
    c := b + 1;
    RETURN c + 1;
END;

CREATE OR REPLACE PROCEDURE test_in_out_function_as_procedure_1 (
    a IN number,
    b IN OUT number,
    c OUT number,
    r OUT number
) AS
BEGIN
    r := test_in_out_function(a, b, c);
END;

CallableStatement cstmt = connection.prepareCall("{call test_in_out_function_as_procedure_1(?, ?, ?, ?)}");

cstmt.setInt(1, 1); // a
cstmt.setInt(2, 2); // b
cstmt.registerOutParameter(2, Types.INTEGER);
cstmt.registerOutParameter(3, Types.INTEGER); // c
cstmt.registerOutParameter(4, Types.INTEGER); // r

cstmt.execute();

int b = cstmt.getInt(2);
int c = cstmt.getInt(3);
int r = cstmt.getInt(4);

CREATE OR REPLACE PROCEDURE test_in_out_function_as_procedure_2 (
    a IN number,
    b IN OUT number,
    c OUT number,
    r OUT number
) AS
BEGIN
    SELECT test_in_out_function(a, b, c) INTO r FROM dual;
END;

CallableStatement cstmt = connection.prepareCall("{call test_in_out_function_as_procedure_2(?, ?, ?, ?)}");

cstmt.setInt(1, 1); // a
cstmt.setInt(2, 2); // b
cstmt.registerOutParameter(2, Types.INTEGER);
cstmt.registerOutParameter(3, Types.INTEGER); // c
cstmt.registerOutParameter(4, Types.INTEGER); // r

cstmt.execute();

// Assume that conn is an established database connection object.
public void testSelectBoolean1() throws Exception {
  // 1. Prepare the attribute array for the struct.
  // The order and type of array elements must strictly match the test_object type defined in the database.
  Object[] addressAttributes = new Object[] {
      Integer.valueOf(42),                     // Integer
      new BigDecimal("9999.99"),               // java.math.BigDecimal
      Boolean.TRUE,                            // Boolean
      new Date(),                              // java.util.Date
      new Timestamp(System.currentTimeMillis()), // java.sql.Timestamp
      "This is a test string",                 // String
      new StringBuilder("Mutable string"),     // StringBuilder
      null,                                    // null
  };
  
  // 2. Use conn.createStruct to create a Struct object.
  Struct addressStruct = conn.createStruct("test_object", addressAttributes);
  
   // 3. Prepare and execute a CallableStatement to call the function.
  CallableStatement stmt = conn.prepareCall("{? = call test_object_func(?)}");
  stmt.registerOutParameter(1, Types.VARCHAR);
  stmt.setObject(2, addressStruct);
  stmt.execute();
  
  // 4. Get and print the function's return value.
  System.out.println(stmt.getObject(1).toString());
  
  stmt.close();
}

Adapt to Hibernate

• Driver class and dialect configuration in hibernate.cfg.xml: If your project uses Hibernate to connect to the database, you must configure the driver class and dialect for the PolarDB database in your hibernate.cfg.xml configuration file.

  Note

  Hibernate 3.6 or later is required to support the PostgresPlusDialect dialect.

  <property name="connection.driver_class">com.aliyun.polardb2.Driver</property>
  <property name="connection.url">jdbc:polardb://pc-***.o.polardb.rds.aliyuncs.com:1521/polardb_test</property>
  <property name="dialect">org.hibernate.dialect.PostgresPlusDialect</property>

• DATE type configuration: For columns of the DATE type in a table, you must adjust the configuration to type="java.util.Date" in the Hibernate .hbm.xml file. This ensures that time precision is retained when Hibernate reads and writes DATE data in PolarDB. If you use type="date", the precision of the DATE type may be lost. The following is a sample configuration:

  <!-- Other configuration information -->
  <hibernate-mapping package="com.aliyun.polardb2.demo">
      <class name="TestTableEntity" table="test_table_name">
          <!-- Other column information -->
          <property name="currentDate" column="curr_date" type="java.util.Date"/> <!-- Specify the java.util.Date type to retain date precision -->
          <!-- Other column information -->
      </class>
  </hibernate-mapping>

• LOB (Large Object) type configuration: Native PostgreSQL does not support LOB types. Therefore, the PostgresPlusDialect dialect maps both CLOB and BLOB columns to oid type columns. This causes strings inserted into these columns to be converted to oid numbers. PolarDB for PostgreSQL (Compatible with Oracle) 2.0 maps the CLOB type to the text type and the BLOB type to the bytea type. You must specify the column type for the mapping to take effect. You can use one of the following configurations.

  • Configuration 1: Java class definition.

    @Lob
    @Column(name = "col_clob")
    @Type(type = "text")
    private String columnClob;
    
    @Lob
    @Column(name = "col_blob")
    @Type(type = "bytea")
    private String columnBlob;

  • Configuration 2: Definition in the Hibernate .hbm.xml file.

    <!-- Other configuration information -->
    <hibernate-mapping package="com.aliyun.polardb2.demo">
        <class name="TestTableEntity" table="test_table_name">
            <!-- Other column information -->
            <property name="columnClob" column="col_clob" type="text"/>
            <property name="columnBlob" column="col_blob" type="bytea"/>
            <!-- Other column information -->
        </class>
    </hibernate-mapping>

Druid connection pool

Druid is a database connection pool that you can use to manage connections between your application and PolarDB for PostgreSQL (Compatible with Oracle). When you use Druid for connections, note the following key configurations to ensure full functionality and stability:

1. Druid has supported PolarDB since version 1.2.26. Make sure that the version referenced in your project is not earlier than this version. The following is a Maven example:

   <dependency>
       <groupId>com.alibaba</groupId>
       <artifactId>druid</artifactId>
       <version>1.2.26</version>
   </dependency>

2. When you initialize the connection pool, you must explicitly set the driver class name (driverClassName) and the database type (dbType).

   DruidDataSource dataSource = new DruidDataSource();
   dataSource.setDriverClassName("com.aliyun.polardb2.Driver");
   dataSource.setDbType("polardb2");

3. Use the SQL firewall (WallFilter) to check whether business SQL statements comply with Oracle syntax specifications and to prevent SQL injection threats.

   // 1. Configure WallConfig.
   WallConfig wallConfig = new WallConfig();
   
   // 1.1 Specifies whether to perform strict syntax checks. This is required.
   wallConfig.setStrictSyntaxCheck(true);
   
   // 1.2 Provides finer-grained syntax control. Only some options are listed. This is optional.
   wallConfig.setMultiStatementAllow(false);    // Specifies whether to allow executing multiple statements at a time.
   wallConfig.setCommentAllow(true);            // Allows comments.
   wallConfig.setSelectIntoAllow(true);         // Allows SELECT INTO.
   wallConfig.setDeleteWhereNoneCheck(true);    // Checks whether DELETE statements have no conditions.
   
   // 2. Use WallConfig to configure WallFilter.
   WallFilter wallFilter = new WallFilter();
   wallFilter.setConfig(wallConfig);
   
   // 3. Use WallFilter to configure the connection pool.
   DruidDataSource dataSource = new DruidDataSource();
   dataSource.getProxyFilters().add(wallFilter);

4. To encrypt database passwords in the Druid connection pool, see Encrypt database passwords.

Adapt to WebSphere

When you use WebSphere, you can configure the PolarDB JDBC driver as a data source. The steps are as follows:

1. For Database type, select User-defined.

2. For Implementation class name, enter com.aliyun.polardb2.ds.PGConnectionPoolDataSource.

3. For Class path, select the path where the JDBC JAR package is located.

Adapt to the Spring framework

When you use a new JDBC version (42.5.4.0.11 or later) in the Spring framework, you can directly pass a struct as a stored procedure parameter without extra code modifications. The following example shows how to call the get_user_info stored procedure using the GetUserProcedure method. The parameter c is the composite type com. A corresponding struct object is constructed to pass the composite type parameter.

public class GetUserProcedure extends StoredProcedure {
    private static final String PROCEDURE_NAME = "get_user_info";

    public GetUserProcedure(DataSource dataSource) {
        super(dataSource, PROCEDURE_NAME);
        init();
    }

    private void init() {
        // Declare input parameters.
        declareParameter(new SqlParameter("p_user_id", Types.NUMERIC));
        declareParameter(new SqlParameter("c", Types.STRUCT, "com"));

        compile(); // You must call compile().
    }

    public Map<String, Object> getUserInfo(Integer userId) {
        Map<String, Object> inputs = new HashMap<>();
        inputs.put("p_user_id", userId);
        Calendar cal = Calendar.getInstance();
        cal.set(2023, Calendar.OCTOBER, 1, 12, 30, 45); // Note: The month in Calendar starts from 0.
        cal.set(Calendar.MILLISECOND, 0);

        Rec rec = new Rec();
        rec.t1 = 1;
        rec.t2 = "some text";
        rec.t3 = new Date(cal.getTime().getTime());
        rec.t4 = true;
        rec.t5 = null;
        inputs.put("c", rec);

        return execute(inputs); // Execute the stored procedure.
    }
}

Adapt to Apache ShardingSphere

You can connect to and manage PolarDB for PostgreSQL (Compatible with Oracle) clusters through Apache ShardingSphere to implement advanced features such as data sharding and read/write splitting. PolarDB is fully compatible with the PostgreSQL protocol, and ShardingSphere natively supports this protocol. Therefore, the two can be seamlessly integrated.

dataSources:
  ds0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.aliyun.polardb2.Driver
    jdbcUrl: jdbc:postgresql://pc-***.o.polardb.rds.aliyuncs.com:1521/postgres?forceDriverType=True
    username: ******
    password: ******
    maxPoolSize: 2
    minPoolSize: 2
  ds1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.aliyun.polardb2.Driver
    jdbcUrl: jdbc:postgresql://pc-***.o.polardb.rds.aliyuncs.com:1521/postgres?forceDriverType=True
    username: ******
    password: ******
    maxPoolSize: 2
    minPoolSize: 2

How do I choose a JDBC driver? Can I use an open source community driver?

PolarDB for PostgreSQL (Compatible with Oracle) implements many compatibility features based on open source PostgreSQL. Some features require driver-level cooperation. Therefore, we recommend that you use the PolarDB JDBC driver. You can download the driver from the official website.

Is the PolarDB JDBC driver available in public Maven repositories?

According to the official website, the only supported method to obtain the JDBC driver is to download its JAR package. For Maven projects, you must then manually install this JAR package into your local repository.

How do I view the version number?

You can run the java -jar driver_name command to view the version number.

Can I configure multiple IP addresses and ports in the URL?

PolarDB for PostgreSQL (Compatible with Oracle) JDBC driver lets you configure multiple IP addresses and ports in the URL, as follows:

jdbc:polardb://1.2.XX.XX:5432,2.3.XX.XX:5432/postgres

How do I select a cursor type?

If you use a JDK earlier than Java 1.8, use Types.REF. If you use Java 1.8 or later, you can use Types.REF_CURSOR.

Is it supported to return column names in uppercase by default?

You can add the oracleCase=true parameter to the JDBC connection string, which makes uppercase the default for returned column names. For example:

jdbc:polardb://1.2.XX.XX:5432,2.3.XX.XX:5432/postgres?oracleCase=true

42.5.7.0.13 (2025-12-24)

• Core component upgrade: The JDBC driver version is synchronized to the latest stable version of the community 42.5.x series (42.5.7), which introduces the latest security patches and performance optimizations.

• Connection stability enhancement: A potential connection leak issue in specific connection pool scenarios is fixed. This improves the reliability of resource management in persistent connection environments.

• Third-party ecosystem compatibility optimization: This version ensures that third-party frameworks, such as MyBatis and Hibernate, can accurately identify the driver type. This avoids compatibility exceptions caused by identification drift.

  • The return value of getDatabaseProductName is adjusted to PostgreSQL.

  • The return value of DRIVER_NAME is adjusted to PolarDB-2.0 JDBC Driver.

• Driver conflict avoidance: Support for the jdbc:oracle:thin: connection protocol is removed. This eliminates potential conflicts with the native Oracle driver in projects where multiple drivers coexist and ensures the uniqueness and accuracy of the driver loading logic.

• Oracle migration adaptation enhancement: The retrieval logic of the getTables interface is optimized to support looking up tables by uppercase table names. This feature adapts the Java business code logic migrated from Oracle to PolarDB and reduces the cost of application migration.

42.5.4.0.12 (2025-08-13)

• Supports using structs as parameters.

• Supports using the jdbc:postgresql protocol to connect to clusters.

42.5.4.0.10.11 (2025-07-10)

• This version supports using the CallableStatement interface to read and write functions and stored procedures. It also supports various types of IN, OUT, and INOUT parameters.

• This version supports the SQLCODE error code field and is compatible with the error handling mechanism of the database kernel.

• This version supports the use of the Types.STRUCT struct in the Spring framework.

• The transform rule for converting numeric types to Boolean values is optimized.

• Enhanced type binding support.

42.5.4.0.10.9 (2025-03-19)

• Supports Oracle-style function parameter binding.

• A bug is fixed where END caused parsing to fail.

42.5.4.0.10.7 (2025-01-06)

• This version supports Oracle-compatible comments, such as /* /* Comments */.

• The Misuse of castNonNull issue is fixed. This issue was caused using a null value in Mybatis calls to the Clob interface.

42.5.4.0.10.6 (2024-12-04)

• This version supports the Channel Binding feature of later JDBC versions.

• The default value of the escapeSyntaxCallMode parameter is upgraded to callIfNoReturn to adapt to Oracle's parameter binding behavior.

• A bug is fixed where incorrect attidentity identification could lead to incorrect column type retrieval.

42.5.4.0.10.5 (2024-10-24)

• The setting of the resetNlsFormat parameter is optimized to ensure correct configuration during connection. This also avoids leaving unexpected execution records in audit logs.

• An error in logical replication tests is fixed. The error was caused by the inability to recognize the java.nio.Buffer type interface.

• An issue is fixed where the end of a CASE WHEN...END block in a stored procedure was not correctly parsed.

42.5.4.0.10.4 (2024-09-02)

• An issue with incorrect binding in PL blocks is fixed. This feature is disabled by default because of its performance impact.

• This version supports implicit conversion within the same type group. This allows character types, such as VARCHAR and CHAR, and numeric types, such as NUMERIC, INTEGER, and DOUBLE, to be converted to each other as INOUT parameters.

• The return value of the getDatabaseProductName() function in the driver's metadata is changed to `POLARDB2 Database Compatible with Oracle`.

42.5.4.0.10.2 (2024-07-19)

• An issue in Mybatis is fixed where the database could not correctly infer the parameter type when the object entity was registered as the Timestamp type.