本文为您介绍如何下载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;
- <maxcompute_endpoint>:MaxCompute服务所在区域的Endpoint。例如,华东1(杭州)区域的外网Endpoint为
- accessId:创建项目空间的AccessKey ID。
- accessKey:创建项目空间的AccessKey ID对应的AccessKey Secret。
说明 AccessKey ID和AccessKey Secret的创建和查看,请参见准备阿里云账号。
- url:格式为
- 执行查询。
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)); } } }