This topic describes how to use the 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. The PolarDB O Edition JDBC driver is developed based on the open source PostgreSQL JDBC Driver and uses PostgreSQL protocols for communications. The JDBC driver allows Java applications to connect to databases by using Java code that is standard and independent of the databases.

The JDBC driver uses version 3.0 of the PostgreSQL protocol. JDBC V4.0 is compatible with Java 6. JDBC V4.1 is compatible with Java 7. JDBC V4.2 is compatible with Java 8.

Download the package of the JDBC driver

Download the JDBC driver

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 CLASSPATH:

export CLASSPATH=$CLASSPATH:/usr/local/polardb/share/java/<Name of the JAR file.jar>

The following code provides an 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>

The following code provides an example:

#java -jar polardb-jdbc18.jar
POLARDB JDBC Driver 42.2.5.2.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 = "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();
      }
    }
  • 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. The following code provides 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 of the URL. All URLs of PolarDB databases are prefixed with jdbc:polardb://.
    Endpoint pc-***.o.polardb.rds.aliyuncs.com The endpoint of the PolarDB cluster. For more information about how to check 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 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();

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

The following code provides an 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>

The following code provides an 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.

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

  • By default, Druid 1.1.24 and later versions 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. The following code provides an 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. The following code provides an 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

Quartz is an open source library that is used to schedule jobs. Before you use Quartz to connect to PolarDB, you must set org.quartz.jobStore.driverDelegateClass to org.quartz.impl.jdbcjobstore.PostgreSQLDelegate, as shown in the following example:
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate

Adapt to WebSphere

Before you use WebSphere to connect to PolarDB, you must perform the following steps to configure the JDBC driver as a data source:
  1. Set the database type to Custom.
  2. Set the implementation class to com.aliyun.polardb.ds.PGConnectionPoolDataSource.
  3. Set the path of the class to the path of the JAR file of the JDBC driver.

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.

If you do not want to specify the databaseId property for SQL statements, you do not need to modify the file. To use the databaseId property to identify the SQL statements that can be executed only in PolarDB databases, add the following configuration and set the databaseId property to polardb for the SQL statements in the XML file:
  <property name="POLARDB" value="polardb" />

FAQ

  • Can I use an open source JDBC driver, instead of the JDBC driver of PolarDB O Edition?

    PolarDB O Edition 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 O Edition. The JDBC driver of the PolarDB O Edition 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 O Edition. The following code provides an example:
    jdbc:poalardb://1.2.3.4:5432,2.3.4.5: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. The following code provides an example:
    jdbc:poalardb://1.2.3.4:5432,2.3.4.5:5432/postgres?oracleCase=true