PolarDB:JDBC

Prerequisites

Before you begin, ensure that you have:

• A database account in the PolarDB cluster. See Create a database account

• The host IP address added to the cluster whitelist. See Configure a cluster whitelist

Background

The PolarDB JDBC driver is based on the open-source PostgreSQL JDBC driver and uses the PostgreSQL native network protocol. It supports Java 6 (JDBC 4.0), Java 7 (JDBC 4.1), and Java 8 (JDBC 4.2) via the PostgreSQL 3.0 protocol.

Set up the driver

Add the JDBC JAR package to CLASSPATH. If the driver is in /usr/local/polardb/share/java/, run:

export CLASSPATH=$CLASSPATH:/usr/local/polardb/share/java/<jar-package-name.jar>

Example:

export CLASSPATH=$CLASSPATH:/usr/local/polardb/share/java/polardb-jdbc18.jar

To check the installed version, run:

java -jar polardb-jdbc18.jar

Expected output:

POLARDB JDBC Driver 42.2.XX.XX.0

Connect to PolarDB

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

jdbc:polardb://<endpoint>:<port>/<database>?user=<username>&password=<password>

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

package com.aliyun.polardb;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * PolarDB JDBC quick start.
 * Make sure the host IP running this example is added to your cluster's whitelist.
 */
public class PolarDBJdbcDemo {

    // Replace with your cluster details
    private final String host = "***.o.polardb.rds.aliyuncs.com";
    private final String user = "<username>";
    private final String password = "<password>";
    private final String port = "1521";
    private final String database = "<database-name>";

    public void run() throws Exception {
        Connection connect = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            // Load the PolarDB JDBC driver
            Class.forName("com.aliyun.polardb.Driver");

            Properties props = new Properties();
            props.put("user", user);
            props.put("password", password);

            // Build the connection URL and connect
            String url = "jdbc:polardb://" + host + ":" + port + "/" + database;
            connect = DriverManager.getConnection(url, props);

            // Run a query -- requires a table: create table foo(id int, name varchar(20))
            String sql = "select id, name from foo";
            statement = connect.createStatement();
            resultSet = statement.executeQuery(sql);

            while (resultSet.next()) {
                System.out.println("id:" + resultSet.getInt(1));
                System.out.println("name:" + resultSet.getString(2));
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            // Always close resources in reverse order
            try {
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (connect != null) connect.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw e;
            }
        }
    }

    public static void main(String[] args) throws Exception {
        new PolarDBJdbcDemo().run();
    }
}

Query data

Use Statement, PreparedStatement, or CallableStatement to run queries.

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

String sql = "{?=call getName(?, ?, ?)}";
CallableStatement stmt = conn.prepareCall(sql);
stmt.registerOutParameter(1, java.sql.Types.INTEGER);

int id = 100;
stmt.setInt(2, id);
stmt.registerOutParameter(3, java.sql.Types.VARCHAR);
stmt.registerOutParameter(4, java.sql.Types.INTEGER);

stmt.execute();

String name = stmt.getString(3);
Integer msgId = stmt.getInt(4);
Integer result = stmt.getInt(1);
System.out.println("Name with ID:" + id + " is " + name + ", messageID is " + msgId + ", return is " + result);

CREATE OR REPLACE FUNCTION getName(
    id        In      Integer,
    name      Out     Varchar2,
    result    Out     Integer
  ) Return Integer
Is
  ret     Int;
Begin
  ret := 0;
  name := 'Test';
  result := 1;
  Return(ret);
End;

Fetch large result sets with FetchSize

By default, the driver fetches the entire result set at once. For large queries, this can exhaust client memory and cause an out-of-memory (OOM) error. Use cursor-based batched fetching instead.

Requirements for cursor-based fetching to work:

• FetchSize must be greater than 0

• autoCommit must be set to false

// Disable autoCommit -- required for cursor-based fetching
conn.setAutoCommit(false);
Statement st = conn.createStatement();

// Set FetchSize to enable cursor-based batching (50 rows per batch)
st.setFetchSize(50);
ResultSet rs = st.executeQuery("SELECT * FROM mytable");
while (rs.next()) {
    System.out.print("a row was returned.");
}
rs.close();

// Reset FetchSize to 0 to fetch all rows at once
st.setFetchSize(0);
rs = st.executeQuery("SELECT * FROM mytable");
while (rs.next()) {
    System.out.print("many rows were returned.");
}
rs.close();

st.close();

Maven projects

The PolarDB JDBC driver is not available in public Maven repositories. Download the JAR package from the official website, then install it to your local repository:

mvn install:install-file \
  -DgroupId=com.aliyun \
  -DartifactId=<jar-package-name> \
  -Dversion=1.1.2 \
  -Dpackaging=jar \
  -Dfile=/usr/local/polardb/share/java/<jar-package-name.jar>

Example:

mvn install:install-file \
  -DgroupId=com.aliyun \
  -DartifactId=polardb-jdbc18 \
  -Dversion=1.1.2 \
  -Dpackaging=jar \
  -Dfile=/usr/local/polardb/share/java/polardb-jdbc18.jar

Add the dependency to your pom.xml:

<dependency>
    <groupId>com.aliyun</groupId>
    <artifactId>polardb-jdbc18</artifactId>
    <version>1.1.2</version>
</dependency>

Hibernate

Configure the driver class and dialect in hibernate.cfg.xml:

<property name="connection.driver_class">com.aliyun.polardb.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>

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

Druid connection pools

Druid 1.1.24 and later natively supports the PolarDB driver. No additional configuration is needed.

Druid earlier than 1.1.24 requires explicit driver and database type settings:

dataSource.setDriverClassName("com.aliyun.polardb.Driver");
dataSource.setDbType("postgresql");

For Druid versions earlier than 1.1.24, set db-type to postgresql because native PolarDB support was added in 1.1.24.

To encrypt database passwords in a Druid connection pool, see Database password encryption.

Adapt to Activiti

When initializing a PolarDB data source in Activiti, you may see this error:

couldn't deduct database type from database product name 'POLARDB Database Compatible with Oracle'

Activiti cannot map the PolarDB version string to a known database type. To fix this, create a subclass of SpringProcessEngineConfiguration that overrides buildProcessEngine() and sets the database type explicitly:

package com.aliyun.polardb;

import org.activiti.engine.ProcessEngine;
import org.activiti.spring.SpringProcessEngineConfiguration;

public class PolarDBSpringProcessEngineConfiguration extends SpringProcessEngineConfiguration {

    public PolarDBSpringProcessEngineConfiguration() {
        super();
    }

    @Override
    public ProcessEngine buildProcessEngine() {
        setDatabaseType(DATABASE_TYPE_POSTGRES);
        return super.buildProcessEngine();
    }
}

Then reference this class in your Spring configuration:

<bean id="processEngineConfiguration" class="com.aliyun.polardb.PolarDBSpringProcessEngineConfiguration">
    <property name="dataSource" ref="dataSource"/>
    <property name="transactionManager" ref="transactionManager"/>
    <property name="databaseSchemaUpdate" value="true"/>
    <!-- Other configurations are omitted here. -->
</bean>

Adapt to Quartz

Set org.quartz.jobStore.driverDelegateClass to the PostgreSQL delegate:

org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate

Adapt to WebSphere

Configure the PolarDB JDBC driver as a data source in WebSphere:

1. Set Database type to User-defined.

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

3. Set the class path to the location of the JDBC JAR package.

Adapt to MyBatis

MyBatis uses databaseIdProvider to map a database product name to a logical ID (databaseId). This ensures SQL statements continue to work even after version updates change the product name string.

The default configuration covers SQL Server, DB2, and Oracle:

<databaseIdProvider type="DB_VENDOR">
  <property name="SQL Server" value="sqlserver"/>
  <property name="DB2" value="db2"/>
  <property name="Oracle" value="oracle" />
</databaseIdProvider>

If none of your SQL statements use a databaseId, no changes are needed. To tag SQL statements as PolarDB-specific, add the following property and use polardb as the databaseId in your XML mapping files:

<property name="POLARDB" value="polardb" />

MyBatis loads only the SQL statements whose databaseId matches the current database, plus any statements without a databaseId.

FAQ

Which JDBC driver should I use? Can I use the open-source PostgreSQL driver?

Use the official PolarDB JDBC driver. PolarDB for PostgreSQL (Compatible with Oracle) includes Oracle-compatibility features that require driver-level support not present in the standard community PostgreSQL driver. Download the driver from the official website.

Is the PolarDB JDBC driver in a public Maven repository?

No. Download the JAR package from the official website and install it to your local Maven repository with mvn install:install-file. See Maven projects.

How do I check the driver version?

Run java -jar <driver-jar-name>. For example:

java -jar polardb-jdbc18.jar

Can I configure multiple IP addresses in the connection URL?

Yes. List multiple host:port pairs separated by commas:

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

The driver tries each address in order. If all addresses fail, the connection attempt fails. Each attempt times out after 10 seconds by default. To change the timeout, add connectTimeout=<seconds> to the connection string.

How do I choose the cursor type for stored procedures?

• Java 1.8 and later: use Types.REF_CURSOR

• Earlier than Java 1.8: use Types.REF

Can I return column names in uppercase?

Yes. Add oracleCase=true to the connection string:

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