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

You can 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-O 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-O database if your project uses Hibernate to connect to the database.

Note Only Hibernate 3.6 or 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 or later supports the PolarDB-O JDBC driver. You do not need to specify the driver name and dbtype parameters.
  • For Druid 1.1.22 or earlier, set the dbType parameter to postgresql.

The following section provides the descriptions, default configurations, and recommended configurations of Druid parameters:

// 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)
// The maximum 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 connection is deleted. The default value is 7 hours. 
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. The default value indicates that the connection is always valid.
dataSource.setPhyTimeoutMillis(1800000);
// The number of the 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. The default value indicates that the connection is always valid.
dataSource.setPhyMaxUseCount(100);
// The SQL query that is used to validate connections. The default value is null.
dataSource.setValidationQuery("select 1");
// Specifies whether the SQL query is valid before the connection is created. This parameter takes effect only after 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-O 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-O. 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-O cluster URLs are jdbc:polardb://.
Endpoint pc-***.o.polardb.rds.aliyuncs.com The endpoint of the PolarDB-O cluster. For more information about how to query the endpoint, see View endpoints.
Port 1521 The port of the PolarDB-O cluster. Default value: 1521.
Database polardb_test The name of the database to be connected.
Username test The username that is used to connect to the PolarDB-O cluster.
Password Pw123456 The password for the username of the PolarDB-O 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 code:

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;