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.
<dependency>
<groupId>com.aliyun.odps</groupId>
<artifactId>odps-jdbc</artifactId>
<version>3.0.1</version>
<classifier>jar-with-dependencies</classifier>
</dependency>
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
- Load the MaxCompute JDBC driver.
Class.forName("com.aliyun.odps.jdbc.OdpsDriver");
- 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
- <maxcompute_endpoint>: the endpoint of MaxCompute in a specific region. For example, the public endpoint
of MaxCompute in the China (Hangzhou) region is
- 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.
- url: The URL must be in the format of
- 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
The following result is returned: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)
- 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)); } } }