PolarDB:JDBC

Connection-level parameter features

The following features are configured using a connection parameter. 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.

Data type parsing

• Date type: Supports 64-bit Date types.

  The kernel supports 64-bit Date types with a data format identical to that of Oracle, which includes hour, minute, and second 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: Supports Interval input in Oracle mode.

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

• Number type: Supports retrieving values of the Number type.

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

• BLOB type: BLOB is processed as Bytea, and CLOB is processed as Text.

  This feature implements the java.sql.Blob and java.sql.Clob interfaces. The kernel has added mappings for BLOB and CLOB, so they can be handled as Bytea and Text at the Java layer. The main class implements methods such as getBytes, setBytes, position, and getBinaryStream.

• Boolean type: Supports converting Boolean types to 1/0.

  For backward compatibility, the setBoolean interface method uses true/false by default. However, you can switch to the Oracle-compatible 1/0 semantics by activating the boolAsInt parameter to meet the requirements of Oracle-compatible database interactions.

  Note

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

  • Versions 42.5.4.0.10 and earlier: The number 1 is treated as True. The number 0 is treated as False. Other numeric values return an error.

  • Versions 42.5.4.0.11 and later: The number 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

• Supports stored procedures without $$ symbols.

  This feature supports omitting the $$ symbol when you create procedures, such as FUNCTION or PROCEDURE, and truncates the / character during syntax parsing.

• Supports colon-prefixed variable names as parameters.

  Supports passing parameters in the :xxx format, where xxx is a variable name that starts with a colon.

• Supports parameter binding in anonymous blocks.

• Supports suppressing PL/SQL warning messages.

  This feature prevents excessive warning information from being stored in a loop, which can cause memory to be exceeded.

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 and process results

When you access the database to execute a query, you must 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 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 conn is an established database connection object
public void testSelectBoolean1() throws Exception {
  // 1. Prepare the attribute array for the struct.
  // The order and types of the 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. Create a Struct object using conn.createStruct
  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

• hibernate.cfg.xml driver class and dialect configuration: 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 Hibernate configuration file.

  Note

  Hibernate must be version 3.6 or later 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 Hibernate's .hbm.xml file. This ensures that Hibernate retains the hour, minute, and second precision when it reads and writes DATE type data in PolarDB. If you use type="date" directly, 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 preserve 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 type 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 this to take effect. You can choose one of the following two 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 started supporting PolarDB in version 1.2.26. Make sure that the version referenced in your project is this version or later. 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 driverClassName and dbType.

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

3. You can use the SQL firewall (WallFilter) to strictly check whether business SQL complies with Oracle syntax specifications and to prevent SQL injection threats. First, configure WallConfig and set setStrictSyntaxCheck(true). You can also configure optional settings, such as disallowing multiple statements, allowing comments, allowing SELECT INTO, or checking for DELETE statements without conditions. Second, use the WallConfig object to configure WallFilter. Finally, add the WallFilter to the proxy filters of your DruidDataSource.

   // 1. Configure WallConfig.
   WallConfig wallConfig = new WallConfig();
   
   // 1.1 Specify whether to perform strict syntax checks. This parameter is required.
   wallConfig.setStrictSyntaxCheck(true);
   
   // 1.2 Configure fine-grained syntax control. Only some options are listed. This parameter is optional.
   wallConfig.setMultiStatementAllow(false);    // Specify whether to allow the execution of multiple statements at a time.
   wallConfig.setCommentAllow(true);            // Allow comments.
   wallConfig.setSelectIntoAllow(true);         // Allow SELECT INTO statements.
   wallConfig.setDeleteWhereNoneCheck(true);    // Check for DELETE statements that do not have a WHERE clause.
   
   // 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 as a data source by following these steps:

1. For the database type, select User-defined.

2. Set the implementation class name to: com.aliyun.polardb2.ds.PGConnectionPoolDataSource.

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

Adapt to the Spring framework

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

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 a PolarDB for PostgreSQL (Compatible with Oracle) cluster through Apache ShardingSphere to implement advanced features, such as data sharding and read/write splitting. The two can be seamlessly integrated because PolarDB is fully compatible with the PostgreSQL protocol, which is natively supported by ShardingSphere.

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, and can I use an open source community driver?

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

Is the PolarDB JDBC driver available in public Maven repositories?

As described on the official website, you must download the JDBC driver JAR package from the website. For Maven projects, you must manually install the JAR package into your local repository. Currently, downloading the JDBC driver package from the official website is the only supported method.

How do I check the version number?

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

Is it supported to configure multiple IP addresses and ports in the URL?

Yes, the JDBC driver for PolarDB for PostgreSQL (Compatible with Oracle) supports configuring multiple IP addresses and ports in the URL. The following is an example:

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

How do I choose a cursor type?

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

Is it supported to return uppercase column names by default?

You can add the parameter oracleCase=true to the JDBC connection string. This parameter converts the returned column names to uppercase by default. The following is an example:

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

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)

• Supports reading and writing functions and stored procedures through the CallableStatement interface, and supports various types of IN, OUT, and INOUT parameters.

• Supports the SQLCODE error code field, which is compatible with the error handling mechanism of the database kernel.

• Supports using Types.STRUCT structs in the Spring framework.

• Optimized the conversion rules for numeric types to Boolean values.

• Enhanced type binding support.

42.5.4.0.10.9 (2025-03-19)

• Supports Oracle-style function parameter binding.

• Fixed a bug where END caused parsing to fail.

42.5.4.0.10.7 (2025-01-06)

• Supports Oracle-compatible comment functionality, including nested comments such as /* /* Comments */.

• Fixed a Misuse of castNonNull issue that was caused using null values when calling the Clob interface in Mybatis.

42.5.4.0.10.6 (2024-12-04)

• Supports the Channel Binding feature for later JDBC versions.

• Upgraded the default value of the escapeSyntaxCallMode parameter to callIfNoReturn to adapt to the parameter binding behavior of Oracle.

• Fixed a bug where attidentity recognition errors could lead to incorrect column type retrieval.

42.5.4.0.10.5 (2024-10-24)

• Optimized the setting of the resetNlsFormat parameter to ensure correct configuration during connection. This optimization also prevents unintended execution records from being left in audit logs.

• Fixed an error in logical replication tests that was caused by the inability to recognize the java.nio.Buffer type interface.

• Fixed an issue where the end of a CASE WHEN...END block in a stored procedure was not parsed correctly.

42.5.4.0.10.4 (2024-09-02)

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

• Supports implicit conversion within the same type category. 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 now: "POLARDB2 Database Compatible with Oracle".

42.5.4.0.10.2 (2024-07-19)

• Fixed an issue in Mybatis where the database could not correctly infer the parameter type when the registered type of the object entity was Timestamp.