Usage notes

Download the MaxCompute JDBC driver

You can download the JAR packages of various MaxCompute JDBC driver versions from GitHub or Maven. We recommend that you download the JAR package whose name includes jar-with-dependencies.

<dependency>
  <groupId>com.aliyun.odps</groupId>
  <artifactId>odps-jdbc</artifactId>
  <version>3.0.1</version>
  <classifier>jar-with-dependencies</classifier>
</dependency>

Connect to MaxCompute

1. Load the MaxCompute JDBC driver.

   Class.forName("com.aliyun.odps.jdbc.OdpsDriver");

2. Use DriverManager to establish a connection to MaxCompute.

   Connection cnct = DriverManager.getConnection(url, accessId, accessKey);

   • url: The URL must be in the format of jdbc:odps:<maxcompute_endpoint>?project=<maxcompute_project_name>. In the URL:
     • <maxcompute_endpoint>: the endpoint of MaxCompute in a specific region. For example, the public endpoint of MaxCompute in the China (Hangzhou) region is http://service.cn-hangzhou.maxcompute.aliyun.com/api. For more information, see Configure endpoints.
     • <maxcompute_project_name>: the name of your MaxCompute project.
     Example:

     jdbc:odps:http://service.cn-hangzhou.maxcompute.aliyun.com/api?project=test_project

   • accessId: the AccessKey ID of the account used to create the project.
   • accessKey: the AccessKey secret of the account used to create the project.
     Note For more information about how to create and view the AccessKey ID and AccessKey secret, see Create an Alibaba Cloud account.
3. Perform a query.

   Statement stmt = cnct.createStatement();
   ResultSet rset = stmt.executeQuery("SELECT foo FROM bar");
   
   while (rset.next()) {
       // process the results
   }
   
   rset.close();
   stmt.close();
   conn.close();

Sample code

• Delete a table, create a table, and obtain metadata

  import java.sql.Connection;
  import java.sql.DatabaseMetaData;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  
  public class Main {
  
      private static final String DRIVER_NAME = "com.aliyun.odps.jdbc.OdpsDriver";
  
      public static void main(String[] args) throws SQLException {
          try {
              Class.forName(DRIVER_NAME);
          } catch (ClassNotFoundException e) {
              e.printStackTrace();
              System.exit(1);
          }
  
          Connection conn = DriverManager.getConnection(
              "jdbc:odps:<maxcompute_endpoint>? project=<maxcompute_project>",
              "aliyun accessId", "aliyun accessKey");
  
  
          // create a table
          Statement stmt = conn.createStatement();
          final String tableName = "jdbc_test";
          stmt.execute("DROP TABLE IF EXISTS " + tableName);
          stmt.execute("CREATE TABLE " + tableName + " (key BIGINT, value STRING)");
  
          // get meta data
          DatabaseMetaData metaData = conn.getMetaData();
          System.out.println("product = " + metaData.getDatabaseProductName());
          System.out.println("jdbc version = "
              + metaData.getDriverMajorVersion() + ", "
              + metaData.getDriverMinorVersion());
          ResultSet tables = metaData.getTables(null, null, tableName, null);
          while (tables.next()) {
              String name = tables.getString("TABLE_NAME");
              System.out.println("inspecting table: " + name);
              ResultSet columns = metaData.getColumns(null, null, name, null);
              while (columns.next()) {
                  System.out.println(
                      columns.getString("COLUMN_NAME") + "\t" +
                          columns.getString("TYPE_NAME") + "(" +
                          columns.getInt("DATA_TYPE") + ")");
              }
              columns.close();
          }
  
          tables.close();
          stmt.close();
          conn.close();
      }
  }

  The following result is returned:

  product = MaxCompute/ODPS
  jdbc version = 3, 0
  inspecting table: jdbc_test
  key    BIGINT(-5)
  value    STRING(12)

• Update a table

  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.SQLException;
  import java.sql.Statement;
  
  public class Main {
  
      private static final String DRIVER_NAME = "com.aliyun.odps.jdbc.OdpsDriver";
  
      public static void main(String[] args) throws SQLException {
          try {
              Class.forName(DRIVER_NAME);
          } catch (ClassNotFoundException e) {
              e.printStackTrace();
              System.exit(1);
          }
  
          Connection conn = DriverManager.getConnection(
              "jdbc:odps:<maxcompute_endpoint>? project=<maxcompute_project>",
              "aliyun accessId", "aliyun accessKey");
  
          Statement stmt = conn.createStatement();
          // The following DML also works
          //String dml = "INSERT INTO jdbc_test SELECT 1, \"foo\"";
          String dml = "INSERT INTO jdbc_test VALUES(1, \"foo\")";
          int ret = stmt.executeUpdate(dml);
  
          assert ret == 1;
  
          stmt.close();
          conn.close();
      }
  }

• Update multiple tables at a time

  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.PreparedStatement;
  import java.sql.SQLException;
  
  public class Main {
  
      private static final String DRIVER_NAME = "com.aliyun.odps.jdbc.OdpsDriver";
  
      public static void main(String[] args) throws SQLException {
          try {
              Class.forName(DRIVER_NAME);
          } catch (ClassNotFoundException e) {
              e.printStackTrace();
              System.exit(1);
          }
  
          Connection conn = DriverManager.getConnection(
              "jdbc:odps:<maxcompute endpoint>? project=<maxcompute project>",
              "aliyun accessId", "aliyun accessKey");
  
          PreparedStatement pstmt = conn.prepareStatement("INSERT INTO jdbc_test VALUES(?, ?)") ;
  
          pstmt.setLong(1, 1L);
          pstmt.setString(2, "foo");
          pstmt.addBatch();
  
          pstmt.setLong(1, 2L);
          pstmt.setString(2, "bar");
          pstmt.addBatch();
  
          int[] ret = pstmt.executeBatch();
  
          assert ret[0] == 1;
          assert ret[1] == 1;
  
          pstmt.close();
          conn.close();
      }
  }

• Query a table

  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  
  public class Main {
  
      private static final String DRIVER_NAME = "com.aliyun.odps.jdbc.OdpsDriver";
  
      public static void main(String[] args) throws SQLException {
          try {
              Class.forName(DRIVER_NAME);
          } catch (ClassNotFoundException e) {
              e.printStackTrace();
              System.exit(1);
          }
  
          Connection conn = DriverManager.getConnection(
              "jdbc:odps:<maxcompute endpoint>? project=<maxcompute project>",
              "aliyun accessId", "aliyun accessKey");
          ResultSet rs;
  
          Statement stmt = conn.createStatement();
          String sql = "SELECT * FROM JDBC_TEST";
          stmt.executeQuery(sql);
  
          ResultSet rset = stmt.getResultSet();
          while (rset.next()) {
              System.out.println(String.valueOf(rset.getInt(1)) + "\t" + rset.getString(2));
          }
      }
  }