PolarDB:JDBC

Download the package of the JDBC driver

Download the JDBC driver (42.2.9.1.6)

Alibaba Cloud provides three versions of the JDBC driver. Select the version based on the Java Development Kit (JDK) version that is used by your application. For Java 6, download the polardb-jdbc16.jar JAR file. For Java 7, download the polardb-jdbc17.jar JAR file. For Java 8, download the polardb-jdbc18.jar JAR file.

Configure the JDBC driver

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

export CLASSPATH=$CLASSPATH:/usr/local/polardb/share/java/<Name of the JAR file.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 <Name of the JAR file.jar>

Example:

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

Connect to PolarDB

• Examples

  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 you cluster's white list.
   */
  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 the application, run the following command to load the JDBC driver:

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

• Connect to a database

  In most cases, a database is represented by a URL in JDBC. 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 of the URL. All the prefixes of the PolarDB cluster URLs are jdbc:polardb://.
  Endpoint	pc-***.o.polardb.rds.aliyuncs.com	The endpoint of the PolarDB cluster. For information about how to view the endpoint, see View or apply for an endpoint.
  Port number	1521	The port of the PolarDB cluster. Default value: 1521.
  Database	polardb_test	The name of the database to which you want to connect.
  Username	test	The username that is used to connect to the PolarDB cluster.
  Password	Pw123456	The password of the username that is used to connect to the PolarDB cluster.

• Run a query and process the query result

  Before you run a query on a database, you must create a Statement, PreparedStatment, or CallableStatement object.

  In the preceding example, a Statement object is created. In the following sample code, a PreparedStatment object is created:

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

  The following code provides an example on how to use CallableStatement to process a stored procedure:

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

  To create the stored procedure getName that is used in the preceding example, run the following command:

  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 you want to use cursors in the stored procedure, you must choose the cursor type based on your Java version.

  • For Java 8 and later, use Types.REF_CURSOR cursors.

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

• Configure FetchSize

  By default, the driver fetches all required data from the database at a time. If the volume of data that you want to query is large, a large amount of memory is consumed. As a result, out-of-memory (OOM) errors can occur. To prevent OOM errors, the JDBC driver provides the cursor-based ResultSet object to help you fetch multiple datasets at a time. To use ResultSet, configure the following parameters:

  • FetchSize: The default value is 0. If you set ResultSet to 0, all data is fetched.

  • autoCommit: Set the value 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();

Set up a Java project by using Maven

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

 mvn install:install-file -DgroupId=com.aliyun -DartifactId=<Name of the JAR file> -Dversion=1.1.2 -Dpackaging=jar -Dfile=/usr/local/polardb/share/java/<Name of the JAR file.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 project:

<dependency>
    <groupId>com.aliyun</groupId>
    <artifactId><Name of the JAR file></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 a database, configure the driver class and dialect of the PolarDB database in the Hibernate configuration file named 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>

Druid connection pools

• By default, Druid 1.1.24 and later support the PolarDB JDBC driver. You do not need to configure the driver name and dbtype parameters.

• For Druid versions earlier than 1.1.24, configure the driver name and dbtype parameters in an explicit way, as shown in the following example:

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

  Note

  Druid versions earlier than 1.1.24 are not adapted to PolarDB. Therefore, you must set dbtype to postgresql.

For more information about how to encrypt database passwords in a Druid connection pool, see Database password encryption.

Adapt to Activiti

If your application uses the Activiti framework for business process management (BPM), the following error message may appear when you initialize PolarDB data sources.

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

The reason is that Activiti provides built-in mappings between database versions and database types. The database version is not correctly mapped to PolarDB in the built-in mappings. To resolve this issue, you can configure the SpringProcessEngineConfiguration child class to reload buildProcessEngine to the child class. When you configure the child class, you must specify the database type in an explicit way. 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();
    }
}

Save the SpringProcessEngineConfiguration child class in your project and use the child class in the configuration file to load configurations. Then, initialize the engine. 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. -->
</bean>

Adapt to Quartz

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

Adapt to WebSphere

Adapt to MyBatis

Before you use MyBatis to connect to PolarDB, you may need to configure the databaseIdProvider file. 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>

databaseIdProvider provides a mapping from a database service to a display name that is specified by the databaseId property. This way, the same database service is mapped to the same display name, even if the name of the database service varies with the version of the database service.

databaseIdProvider is an XML file. In this file, you can specify the databaseId property for an SQL statement. This way, the SQL statement can be executed only in the database specified by the databaseId property. In this case, when MyBatis loads the XML file, only the SQL statements that are mapped to the current database and the SQL statements for which no databaseId is specified are loaded.

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

FAQ

• Can I use an open-source JDBC driver, instead of the JDBC driver of PolarDB for PostgreSQL (Compatible with Oracle)?

  PolarDB for PostgreSQL (Compatible with Oracle) is developed based on the open-source PostgreSQL and provides multiple compatibility features. A driver is required to implement some of these features. We recommend that you use the JDBC driver of PolarDB for PostgreSQL (Compatible with Oracle). The JDBC driver of the PolarDB for PostgreSQL (Compatible with Oracle) can be downloaded from the official website of Alibaba Cloud.

• Can the JDBC driver be used in public Maven repositories?

  The JAR files of the JDBC driver can be downloaded only from the official website of Alibaba Cloud. After you download a JAR file, you need to install the JAR file in your local repository for your projects that are built by using Maven.

• How do I view the version of the JDBC driver?

  You can run the java -jar <Driver name> command to view the version number.

• Can I specify multiple IP addresses and port numbers in a URL?

  Yes, you can specify multiple IP addresses and port numbers in a URL when you configure the JDBC driver of PolarDB for PostgreSQL (Compatible with Oracle). Example:

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

  Note

  If you want to connect your application to a database after you configure multiple IP addresses in the URL, the system attempts to connect to the IP addresses in sequential order until a connection is established. If all IP addresses cannot be connected, the connection fails to be created. The default timeout period for each connection attempt is 10s. To modify the timeout period, you can add and configure the connectTimeout parameter in the URL.

• How do I choose the cursor type?

  If the Java version that you use is earlier than 1.8, use Types.REF. Otherwise, use Types.REF_CURSOR.

• How can the column names returned be automatically converted to uppercase?

  Add oracleCase=true in the URL of the JDBC driver. Then, all column names returned are uppercase. Example:

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