This topic describes how to use the PolarDB-O Java Database Connectivity (JDBC) driver to connect Java applications to PolarDB O Edition databases.

Prerequisites

Background information

JDBC is a Java API that is used to connect Java applications to databases. PolarDB O Edition JDBC is developed based on open source PostgreSQL JDBC. PolarDB-O JDBC uses PostgreSQL protocols for communications. PolarDB-O JDBC allows Java applications to connect to databases by using standard and database-independent Java code.

The PolarDB-O JDBC driver uses version 3.0 of the PostgreSQL protocol, and is compatible with Java 6 (JDBC 4.0), Java 7 (JDBC 4.1), and Java 8 (JDBC 4.2).

Download the package of the PolarDB-O JDBC driver

Download the package of the PolarDB-O JDBC driver

Alibaba Cloud provides three versions of the PolarDB-O JDBC driver. If you select the version that is compatible with Java 6, use the polardb-jdbc16.jar JAR file. If you select the version that is compatible with Java 7, use the polardb-jdbc17.jar JAR file. If you select the version that is compatible with Java 8, use the polardb-jdbc18.jar JAR file. You can select a version of the PolarDB-O JDBC driver based on the Java Development Kit (JDK) version that is used by your application.

Configure the PolarDB-O JDBC driver

Before you use the PolarDB-O JDBC driver in your Java application, add the path where the JDBC driver package is stored to CLASSPATH. For example, if your JDBC driver is stored in /usr/local/polardb/share/java/, run the following command to add the JDBC driver path to 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.5.2.0

Set up a Java project by using Maven

If your Java project is set up by using Maven, run the following command to install the PolarDB JDBC driver package in your on-premises 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 the Maven 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

In the hibernate.cfg.xml Hibernate configuration file, configure the driver class and the dialect of the PolarDB database if your project uses Hibernate to connect to the database.

Note Only Hibernate 3.6 and later support PostgresPlusDialect.
<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 pool

  • By default, Druid 1.1.24 and later support the PolarDB JDBC driver. You do not need to specify the driver name and dbtype parameters.
  • For versions earlier than Druid 1.1.24, specify 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 For versions earlier than Druid 1.1.24, the configurations are not adapted to PolarDB. Therefore, you must set dbtype to postgresql.

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

Modify configurations to 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. However, in the built-in mappings, the database version is not mapped to PolarDB in a correct way. To resolve this issue, you can specify the subclass of SpringProcessEngineConfiguration and reload buildProcessEngine to the subclass. You must specify the database type in an explicit way. The following example shows how to specify the database type:

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 subclass of SpringProcessEngineConfiguration in your project and use the subclass in the configuration file to load configurations. Then, initialize the engine. The following example provides details:

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

Load the PolarDB-O JDBC driver

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

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

In JDBC, a database is represented by a URL in most cases, as shown in the following 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 more 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 be connected to.
Account test The username that is used to connect to the PolarDB cluster.
Password Pw123456 The password for the username of the PolarDB cluster.

When 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. The following sample code creates a PreparedStatment 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));
}

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

The following code is used to create the stored procedure getName that is used in the preceding example:

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 use different types of cursors for different Java versions.
  • For Java 8 and later, you must use Types.REF_CURSOR cursors.
  • For versions earlier than Java 8, you must use Types.REF cursors.