使用说明 - 云原生大数据计算服务 MaxCompute

本文为您介绍如何下载JDBC和连接MaxCompute，并提供示例代码。

JDBC下载

您可以通过GitHub或Maven库获取MaxCompute各版本的JAR包。推荐您下载包含完整依赖jar-with-dependencies的JAR包。

通过Maven方式使用MaxCompute JDBC的项目对象模型POM（Project Object Model）的示例如下。

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

说明 MaxCompute JDBC驱动是开源代码项目，项目地址为aliyun-odps-jdbc。

MaxCompute欢迎您参与JDBC驱动的开发和改进工作。您可以在该项目的Issues页面反馈问题，或通过Pull requests页面对源代码进行改进。使用Issues及Pull requests时，请您遵循开源项目的模板要求。

连接MaxCompute

加载MaxCompute JDBC驱动。

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

通过DriverManager创建Connection。
```
Connection cnct = DriverManager.getConnection(url, accessId, accessKey);
```
- url：格式为jdbc:odps:<maxcompute_endpoint>?project=<maxcompute_project_name>[&useProjectTimeZone={true|false}]。其中：
  - <maxcompute_endpoint>：MaxCompute服务所在区域的Endpoint。例如，华东1（杭州）区域的外网Endpoint为http://service.cn-hangzhou.maxcompute.aliyun.com/api。Endpoint的配置信息详情请参见Endpoint。
  - <maxcompute_project_name>：MaxCompute项目空间名称。
  - useProjectTimeZone：是否使用MaxCompute项目空间的时区。
  命令示例如下。
```
jdbc:odps:http://service.cn-hangzhou.maxcompute.aliyun.com/api?project=test_project&useProjectTimeZone=true;
```
- accessId：创建项目空间的AccessKey ID。
- accessKey：创建项目空间的AccessKey ID对应的AccessKey Secret。
  
  说明 AccessKey ID和AccessKey Secret的创建和查看，请参见准备阿里云账号。

执行查询。

Statement stmt = cnct.createStatement();
ResultSet rset = stmt.executeQuery("SELECT foo FROM bar");

while (rset.next()) {
    // process the results
}

rset.close();
stmt.close();
cnct.close();

示例代码

删除表、创建表和获取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();
    }
}

返回结果示例如下。

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

更新表

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

批量更新表

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

查询表

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