本文为您介绍如何使用JDBC,包括JDBC的下载、连接MaxCompute和示例代码。

JDBC下载

您可以在开源项目的 release 页面,或者 Maven 获取MaxCompute各版本的Jar包。我们推荐您使用包含了完整依赖的jar-with-dependencies包。

通过Maven方式使用MaxCompute JDBC的项目对象模型POM(Project Object Model)示例片段如下。
<dependency>
  <groupId>com.aliyun.odps</groupId>
  <artifactId>odps-jdbc</artifactId>
  <version>3.0.1</version>
  <classifier>jar-with-dependencies</classifier>
</dependency>
说明 MaxCompute JDBC驱动是开放源代码项目,项目地址为 aliyun-odps-jdbc

MaxCompute非常欢迎您参与JDBC驱动的开发和改进工作。您可以在该项目的Issues页面反馈问题,以及通过Pull Request的方式对源代码进行改进。Issues及PullRequest请遵循开源项目的模板要求。

连接MaxCompute

  1. 加载MaxCompute JDBC驱动。
    Class.forName("com.aliyun.odps.jdbc.OdpsDriver");
  2. 通过DriverManager创建Connection。
    Connection cnct = DriverManager.getConnection(url, accessId, accessKey);
    参数说明如下:
    • url:格式为jdbc:odps:<MaxCompute endpoint>?project=<MaxCompute project name>。 其中:
      • <maxcompute_endpoint>为您MaxCompute服务所在区域的Endpoint。例如,华东1(杭州)区域的外网Endpoint为http://service.cn-hangzhou.maxcompute.aliyun.com/api。更多关于Endpoint的配置信息,请参见配置Endpoint
      • <maxcompute_project_name> 为您的MaxCompute项目空间名称。
      举例如下。
      jdbc:odps:http://service.cn-hangzhou.maxcompute.aliyun.com/api?project=test_project
    • accessId:创建项目空间的AccessKey ID。
    • accessKey:创建项目空间的AccessKey ID对应的AccessKey Secret。
      说明 AccessKey ID和AccessKey Secret的创建和查看,请参见3
  3. 执行查询。
    Statement stmt = cnct.createStatement();
    ResultSet rset = stmt.executeQuery("SELECT foo FROM bar");
    
    while (rset.next()) {
        // process the results
    }
    
    rset.close();
    stmt.close();
    conn.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)
  • 执行INSERT
    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:http://10.101.222.162:8002/odps_dailyrunnew?project=odps_mingyou_test",
                "63wd3dpztlmb5ocdkj94pxmm", "oRd30z7sV4hBX9aYtJgii5qnyhg=");
    
            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();
        }
    }
  • 执行批量INSERT
    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();
        }
    }
  • 执行SELECT
    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));
            }
        }
    }