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

Prerequisites

Background information

JDBC is a Java API that is used to connect Java applications to databases. PolarDB-O JDBC is developed based on open source PostgreSQL JDBC. PolarDB-O JDBC uses PostgreSQL protocols for LAN 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 dialect of the PolarDB database if your project uses Hibernate to connect to the database.

Note Only Hibernate 3.6 and later supports 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

When you use the Druid connection pool, 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("polardb");
Note
  • By default, Druid 1.1.22 and later supports the PolarDB JDBC driver. You do not need to specify the driver name and dbtype parameters.
  • For versions earlier than Druid 1.1.22, set the dbType parameter to postgresql.

The following section describes Druid parameters and provides default and recommended configurations:

// The number of initial connections in the connection pool. The default value is 0.
dataSource.setInitialSize(0);
// The maximum number of active connections in the connection pool. The default value is 8.
dataSource.setMaxActive(10);
// The minimum number of idle connections in the connection pool. The default value is 0.
dataSource.setMinIdle(0);
// The interval for reclaiming idle connections. The default value is 60 seconds.
dataSource.setTimeBetweenEvictionRunsMillis();
// The minimum amount of time during which a connection may remain idle in the pool after the connection is used last time. If the specified value is exceeded, the system determines whether the number of the current idle connections is smaller than the allowed minimum number of idle connections. If the number of the current idle connections is smaller than the allowed minimum number of idle connections, the idle connection is retained. Otherwise, the idle connection is deleted. The default value is 30 minutes.
dataSource.setMinEvictableIdleTimeMillis(300000);
// Specifies whether to immediately delete the connection after the connection is deprecated. If it is set to false, the connection is not reclaimed after it is used. The default value is false.
dataSource.setRemoveAbandoned(false);
// The validity period of a connection after the connection is created. If the specified value is exceeded, the connection is not reclaimed. The default value is -1, which specifies that the connection is always valid.
dataSource.setPhyTimeoutMillis(1800000);
// The number of valid requests that are sent by using a connection after the connection is created. If the specified value is exceeded, the connection is not reclaimed. The default value is -1, which specifies that the connection is always valid.
dataSource.setPhyMaxUseCount(100);
// The SQL statement that is used to check whether connections are valid. The default value is null.
dataSource.setValidationQuery("select 1");
// Specifies whether the SQL statement is valid before the connection is created. This parameter takes effect only when you specify the setValidationQuery parameter.
dataSource.setTestOnBorrow(true);

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. To resolve this issue, you can specify the subclass of SpringProcessEngineConfiguration and reload buildProcessEngine to the subclass. You must specify the database types in an explicit way. The following example shows how to specify the database types.

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"/>
      <! -- 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 query the endpoint, see View endpoints.
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 perform 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 example shows 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 shows how 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;