PolarDB:JDBC

Configure the JDBC driver

Before you can use the JDBC driver in a Java application, you must add the path of the JDBC driver package to CLASSPATH. For example, if the JDBC driver is in the /usr/local/polardb/share/java/ path, you can run the following command to add the driver path to CLASSPATH:

export CLASSPATH=$CLASSPATH:/usr/local/polardb/share/java/<jar_package_name.jar>

Example:

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

Run the following command to view the current JDBC version:

#java -jar <jar_package_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>
   * Please make sure the host IP running this demo is in your cluster's whitelist.
   */
  public class PolarDBJdbcDemo {
    /**
     * Replace the following information.
     */
    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

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

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

• Connect to a database

  In JDBC, a database is typically represented by a URL. The following is an example.

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

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

• Query data and process the results

  When you access the database to run 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));
  }

  You can use a CallableStatement object to process stored procedures. 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 messegeID is " + msgId + ", and return is " + result);

  The getName stored procedure used in the preceding code is 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

  If the stored procedure uses a cursor, the cursor type depends on the Java version:

  • For Java 8 and later, use the Types.REF_CURSOR type.

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

• Set FetchSize

  By default, the driver fetches all data from the database at once. For queries that involve a large amount of data, this can consume a large amount of client memory and even cause an out-of-memory (OOM) error. To prevent this issue, JDBC provides a cursor-based ResultSet to fetch datasets in batches. To use this feature:

  • Set FetchSize. By default, FetchSize is 0, which indicates that the driver fetches all data.

  • Set the autoCommit parameter of the connection to false.

  // make sure autocommit is off
  conn.setAutoCommit(false);
  Statement st = conn.createStatement();
  
  // Set fetchSize to use 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 projects

If your Java project is built using 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_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 following dependency to the pom.xml file of the Maven project.

<dependency>
    <groupId>com.aliyun</groupId>
    <artifactId><jar_package_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

If your project uses Hibernate to connect to the database, configure the driver class and dialect for the PolarDB database in your hibernate.cfg.xml configuration 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 connection pools

• Because Druid 1.1.24 and later versions natively support the PolarDB driver, you do not need to set the driver name or dbtype parameters.

• For Druid versions earlier than 1.1.24, you must explicitly set the driver name and dbtype parameters, as follows:

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

  Note

  Versions of Druid earlier than 1.1.24 do not natively support PolarDB. Therefore, you must set db-type to postgresql.

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

Adapt to Activiti

If your application uses the Activiti business process management (BPM) framework, the following error message may appear 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 has built-in mappings between database versions and database types, which prevents it from correctly mapping the PolarDB version information. To resolve this issue, create a SpringProcessEngineConfiguration child class and reload the buildProcessEngine method in the child class. In this solution, you must explicitly specify the database type, as shown in the following 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 child class in your project. In the configuration file, specify that this class is used to load the configuration and initialize the engine, as shown in the following 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>

Adapt to Quartz

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

Adapt to WebSphere

Adapt to MyBatis

When you use MyBatis, you may need to configure databaseIdProvider. The default configuration is as follows:

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

The purpose of databaseIdProvider is to map a database product name to a specific name (the databaseId). This ensures that even if the database product name changes after a version update, it is still mapped to the same name.

In a MyBatis XML mapping file, you can specify the databaseId attribute for an SQL statement. This ensures that the SQL statement is executed only on the database that corresponds to that databaseId. When MyBatis loads the XML mapping file, it loads only the SQL statements whose databaseId matches the current database. SQL statements that do not have a databaseId attribute are always loaded.

  <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 the open source PostgreSQL and includes many features for compatibility. Some of these features require driver-level support. Therefore, we recommend that you use the official PolarDB JDBC driver, which you can download from the official website.

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

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

• Q: How do I view the version number?

  A: Run the java -jar driver_name command to view the version number.

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

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

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

  Note

  After you configure multiple IP addresses, the driver attempts to create a connection using these IP addresses in sequence. If a connection cannot be established with any of the IP addresses, the connection attempt fails. The default timeout period for each connection attempt is 10 s. This value is controlled by the connectTimeout parameter. To modify the timeout period, add this parameter to the connection string.

• Q: How do I select the cursor type?

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

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

  A: Yes, you can. Add the oracleCase=true parameter to the JDBC connection string. This parameter converts the returned column names to uppercase by default. The following is an example:

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