PolarDB:JDBC

Configure the JDBC driver

To use the JDBC driver in a Java application, add the path to its JAR file to your CLASSPATH. For example, if the JAR file is stored in the /usr/local/polardb/share/java/ directory, run the following command to add its path to the CLASSPATH:

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

Example:

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

To check your JDBC driver version, run the following command:

#java -jar <jar-file-name.jar>

Example:

#java -jar polardb-jdbc18.jar
POLARDB JDBC Driver 42.2.XX.XX.0

Connect to PolarDB

• Example

  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 DEMO
   * <p>
   * Make sure the IP address of the host running this demo is in your cluster's whitelist.
   */
  public class PolarDBJdbcDemo {
    /**
     * Replace the following placeholder values.
     */
    private final String host = "***.o.polardb.rds.aliyuncs.com";
    private final String user = "***";
    private final String password = "***";
    private final String port = "1521";
    private final String database = "db_name";
  
    public void run() throws Exception {
      Connection connect = null;
      Statement statement = null;
      ResultSet resultSet = null;
  
      try {
        Class.forName("com.aliyun.polardb.Driver");
  
        Properties props = new Properties();
        props.put("user", user);
        props.put("password", password);
        String url = "jdbc:polardb://" + host + ":" + port + "/" + database;
        connect = DriverManager.getConnection(url, props);
  
        /**
         * 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 {
        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 {
      PolarDBJdbcDemo demo = new PolarDBJdbcDemo();
      demo.run();
    }
  }

• Load the JDBC driver

  Run the following command in your application to load the JDBC driver:

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

• Connect to the database

  In JDBC, a connection URL represents the database connection. For example:

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

  Parameter	Example	Description
  URL prefix	jdbc:polardb://	The URL prefix for connecting to PolarDB is always jdbc:polardb://.
  Endpoint	pc-***.o.polardb.rds.aliyuncs.com	The endpoint of the PolarDB cluster. For more information, see View or apply for an endpoint.
  Port	1521	The port of the PolarDB cluster. The default is 1521.
  Database	polardb_test	The name of the database to connect to.
  Username	test	The username of the PolarDB cluster.
  Password	Pw123456	The password for the PolarDB cluster username.

• Query data and process results

  To execute a query, create a Statement, PreparedStatement, or CallableStatement object.

  The preceding example uses a Statement object. The following example shows how to use a PreparedStatement object:

  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));
  }

  A CallableStatement is used to call a stored procedure. The following is an example:

  String sql = "{?=call getName (?, ?, ?)}";
  CallableStatement stmt = conn.prepareCall(sql);
  stmt.registerOutParameter(1, java.sql.Types.INTEGER);
  
  //Bind IN parameter first, then bind OUT parameter
  int id = 100;
  stmt.setInt(2, id); // This would set ID as 102
  stmt.registerOutParameter(3, java.sql.Types.VARCHAR);
  stmt.registerOutParameter(4, java.sql.Types.INTEGER);
  
  //Use execute method to run stored procedure.
  stmt.execute();
  
  //Retrieve name with getXXX method
  String name = stmt.getString(3);
  Integer msgId = stmt.getInt(4);
  Integer result = stmt.getInt(1);
  System.out.println("Name with ID:" + id + " is " + name + ", and messageID is " + msgId + ", and return is " + result);

  The getName stored procedure used in the preceding code is defined as follows:

  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;

  Note

  For stored procedures that return a cursor, the cursor type depends on the Java version:

  • For Java 8 or later, use Types.REF_CURSOR.

  • For versions earlier than Java 8, use Types.REF.

• Set the fetch size

  By default, the driver fetches all query results from the database at once. For large result sets, this can consume significant client memory and may cause an Out of Memory (OOM) error. To prevent this, JDBC provides a cursor-based ResultSet to fetch data in batches. To use this feature, you must:

  • Set the FetchSize. The default value of FetchSize is 0, which means all data is fetched at once.

  • Set the connection's autoCommit property to false.

  // make sure autocommit is off
  conn.setAutoCommit(false);
  Statement st = conn.createStatement();
  
  // Set fetchSize to use a cursor
  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 turn off the cursor
  st.setFetchSize(0);
  rs = st.executeQuery("SELECT * FROM mytable");
  while (rs.next())
  {
      System.out.print("many rows were returned.");
  }
  rs.close();
  
  // Close the statement.
  st.close();

Maven integration

If your Java project is built with Maven, run the following command to install the PolarDB JDBC driver package to your local repository:

mvn install:install-file -DgroupId=com.aliyun -DartifactId=<jar-file-name> -Dversion=1.1.2 -Dpackaging=jar -Dfile=/usr/local/polardb/share/java/<jar-file-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 following dependency to the pom.xml file of your Maven project.

<dependency>
    <groupId>com.aliyun</groupId>
    <artifactId><jar-file-name></artifactId>
    <version>1.1.2</version>
</dependency>

Example:

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

Hibernate integration

If your project uses Hibernate, configure the driver class and dialect for PolarDB in your hibernate.cfg.xml file.

<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>

Druid integration

• By default, Druid 1.1.24 and later versions support the PolarDB driver. You do not need to set the driverClassName and dbtype parameters.

• For versions earlier than Druid 1.1.24, you must explicitly set the driverClassName and dbtype parameters:

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

  Note

  Druid versions earlier than 1.1.24 do not have native support for PolarDB. Therefore, you must set the dbtype parameter to postgresql.

If you need to encrypt the database password in the Druid connection pool, see Database password encryption.

Activiti integration

If your application uses the Activiti framework for business process management, the following error may occur when you initialize a PolarDB data source.

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

This error occurs because Activiti's built-in mapping of database product names to database types does not include an entry for PolarDB. To resolve this, create a SpringProcessEngineConfiguration subclass and override the buildProcessEngine method to explicitly specify the database type. The following code shows an example.

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();
    }
}

Place the SpringProcessEngineConfiguration subclass in your project. Then, in the configuration file, set the engine to load the configuration from this class during initialization. The following code shows an example.

<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>

Quartz integration

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

WebSphere integration

MyBatis integration

When you use MyBatis, you may need to configure a databaseIdProvider. The following code shows the default configuration:

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

A databaseIdProvider provides a mapping from a database product name to a specific alias, which is the databaseId. This ensures that a database product name maps to the same alias even if the product name changes across different database versions.

In a MyBatis XML mapping file, you can add the databaseId attribute to an SQL statement. This ensures the statement runs only on the database matching that databaseId. When MyBatis loads the mapping file, it loads only the statements with a matching databaseId and all statements that do not have a databaseId attribute.

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

FAQ

• Q: How do I select a JDBC driver? Can I use an open source community driver?

  A: PolarDB for PostgreSQL (Compatible with Oracle) is based on open-source PostgreSQL, but some of its features require driver-level support. Therefore, we recommend using the official PolarDB JDBC driver, which you can download from the official driver download page.

• Q: Is the PolarDB JDBC driver available in public Maven repositories?

  A: No. The driver is not available in public Maven repositories. You must download the JAR file from the official website and, for Maven projects, manually install it into your local repository.

• Q: How do I check the driver version number?

  A: Run the java -jar <driver-name> command to view the version number.

• Q: Does the connection URL support multiple IP addresses and ports?

  A: Yes, the PolarDB for PostgreSQL (Compatible with Oracle) JDBC driver allows you to specify multiple host-port pairs in the connection URL, as shown in the following example:

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

  Note

  If you configure multiple IP addresses, the driver attempts to connect to them sequentially. If a connection cannot be established with any of the IP addresses, the connection attempt fails. The default connection timeout for each attempt is 10 seconds (connectTimeout). To change the timeout period, you can add the connectTimeout parameter to the connection string.

• Q: How do I select the cursor type?

  A: For JDK versions before Java 1.8, use Types.REF. For Java 1.8 or later, you can use Types.REF_CURSOR.

• Q: Can column names be returned in uppercase by default?

  A: Yes. Add the oracleCase=true parameter to the JDBC connection string to convert all returned column names to uppercase. The following is an example:

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