Java实现JDBC连接MaxCompute查询数据

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

1.注意事项

• 通过MaxCompute JDBC驱动执行SQL并获取结果，需要执行账号满足以下要求：

  • 是项目空间的成员；

  • 有项目空间的CreateInstance权限；

  • 有目标表的Select和Download权限；

2.引入依赖

通过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>

3.连接MaxCompute

3.1 加载MaxCompute JDBC驱动

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

3.2 通过DriverManager创建Connection　　

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

• accessId：创建项目空间的AccessKey ID。

• accessKey：创建项目空间的AccessKey ID对应的AccessKey Secret。

• url示例： jdbc:odps:<maxcompute_endpoint>?project=<maxcompute_project_name> 

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

3.3 执行查询

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

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

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

4.示例代码

public static void main(String[] args) {
        String url = "jdbc:odps:http://service.cn-beijing.maxcompute.aliyun.com/api?project=project_name";
        String accessId = "username";
        String accessKey = "pwd";
        try {
            Class.forName("com.aliyun.odps.jdbc.OdpsDriver");
            Connection conn = DriverManager.getConnection(url, accessId, accessKey);
            Statement stmt = conn.createStatement();

            // tables
            ResultSet rset = stmt.executeQuery("show tables");
            List<String> tableNames = new ArrayList<>();
            while (rset.next()) {
                tableNames.add(rset.getString(1));
            }
            System.err.println(tableNames);

            // column
            DatabaseMetaData meta = conn.getMetaData();
            ResultSet columns = meta.getColumns(null, null, "table_name", null);
            while (columns.next()) {
                String columnName = columns.getString("COLUMN_NAME");
                String dataType = columns.getString("TYPE_NAME");
                System.out.println("Column Name: " + columnName + ", Data Type: " + dataType);
            }
            
            // 查询数据
            ResultSet rs = stmt.executeQuery("select * from table_name");
            // 获取结果集的元数据　
            ResultSetMetaData metaData = rs.getMetaData();
            // 计算列的数量　
            int columnCount = metaData.getColumnCount();

            // 遍历每一行内容　
            while (rs.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    String columnValue = rs.getString(i);
                    System.out.print(columnValue + " ");
                }
                System.out.println();
            }

            conn.close();
        } catch (Exception e) {

        }
    }

5.遇到的问题

出现报错：com.aliyun.odps.Odps.setCurrentSchema(Ljava/lang/String;)V

原因：odps 依赖冲突

<dependency>
    <groupId>com.aliyun.odps</groupId>
    <artifactId>odps-sdk-core</artifactId>
    <version>0.29.11-oversea-public</version>
</dependency>
修改版本号为：0.45.2-public