This topic describes how to download the MaxCompute JDBC driver and use the driver to connect to MaxCompute. It also provides sample code.

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.

The following code describes the Project Object Model (POM) file of the MaxCompute JDBC driver downloaded from Maven:
<dependency>
  <groupId>com.aliyun.odps</groupId>
  <artifactId>odps-jdbc</artifactId>
  <version>3.0.1</version>
  <classifier>jar-with-dependencies</classifier>
</dependency>
Note The MaxCompute JDBC driver is an open-source code project that is named aliyun-odps-jdbc.

You are welcome to participate in the development and improvement of the MaxCompute JDBC driver. You can report issues on the Issues tab and improve source code on the Pull requests tab. Your operations on the Issues and Pull requests tabs must follow the template requirements for open-source projects.

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