This topic describes how to use the PolarDB Java Database Connectivity (JDBC) driver to connect a Java application to an ApsaraDB for PolarDB database.

Prerequisites

  • You have created an account for an ApsaraDB for PolarDB cluster. For more information about how to create an account, see Create a database account.
  • You have added the IP address of the host that you want to connect to the ApsaraDB for PolarDB cluster to the whitelist. For more information, see Set IP address whitelists for a cluster.

Background information

JDBC is an application programming interface for the programming language Java, which defines how a client may access a database. ApsaraDB for PolarDB provides the Oracle JDBC driver based on the open-source PostgreSQL JDBC driver. The Oracle JDBC driver uses the PostgreSQL protocols for LAN communications, and it allows Java applications to connect to databases by using standard and database-independent Java code.

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

Download the PolarDB JDBC driver

Download the PolarDB JDBC driver. Alibaba Cloud provides three JDBC versions compatible with Java 6, Java 7, and Java 8. The three JAR packages are named as polardb-jdbc16.jar,polardb-jdbc17.jar, and polardb-jdbc18.jar, respectively. You can select an appropriate JDBC version based on the JDK version used by your application.

Configure the PolarDB JDBC driver

Before you use the PolarDB JDBC driver in a Java application, you must add the path of the JDBC driver package to CLASSPATH. For example, if the path of your 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 package.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 package.jar>

Example:

#java -jar polardb-jdbc18.jar
POLARDB JDBC Driver 42.2.5.2.0

Set up a Java project with Maven

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

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

Example:

<dependency>
    <groupId>com.aliyun</groupId>
    <artifactId>odps-jdbc</artifactId>
    <version>1.1.2</version>
</dependency>

Set up a project for a Hibernate application

If your project uses Hibernate to connect to the database, open the Hibernate configuration file hibernate.cfg.xml and configure the driver class and dialect of the ApsaraDB for PolarDB database.

Note Only Hibernate version 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>

Load the PolarDB 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 usually represented by a URL, for example:

jdbc:polardb://pc-***.o.polardb.rds.aliyuncs.com:1521/polardb_test? user=test&password=Pw123456
Parameter Example Description
URL prefix jdbc:polardb:// Set the prefix of the URL to jdbc:polardb://.
Endpoint pc-***.o.polardb.rds.aliyuncs.com The endpoint of the ApsaraDB for PolarDB cluster. For more information about how to query the endpoint, see View endpoints.
Port 1521 The port of the ApsaraDB for PolarDB cluster. Default value: 1521.
Database polardb_test The name of the database to be connected.
Username test The username for connecting to the ApsaraDB for PolarDB cluster.
Password Pw123456 The password of the username.

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

CallableStatement is used to process a stored procedure, as shown in the following example:

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