阿里云数据源连接(ODPS、DRDS)

一、ODPS

1.准备工作

1）阿里云账号的accessId和accessKey(几乎等同于账号)
2）ODPS连接所需API地址，公网ODPS统一为 http://service.odps.aliyun.com/api
专有云ODPS联系相关负责人员获取API地址。
3）ODPS中project名称(工作空间名称或称为库名)
4）官方提供ODPS的SDK开发包，maven依赖如下：

		<dependency>
			<groupId>com.aliyun.odps</groupId>
			<artifactId>odps-jdbc</artifactId>
			<version>3.0.1</version>
		</dependency>

2.获取连接

根据官方SDK提供的API获取ODPS连接对象。

	public static Odps getConn(String endPoint, String accessId, String accessKey, String project) {
		Account account = new AliyunAccount(accessId, accessKey);
		Odps odps = new Odps(account);
		odps.setEndpoint(endPoint);
		odps.setDefaultProject(project);
		return odps;
	}

3.获取表信息

根据官方SDK提供的API获取ODPS表中英文名称。

	public ArrayList<Map<String, String>> showTables(Odps stmt) {
		ArrayList<Map<String, String>> tablelist = new ArrayList<>();
		Tables tables = stmt.tables();
		for (Table table : tables) {
			Map<String, String> tableInfo = new HashMap<>();
			tableInfo.put("tableName", table.getName());
			tableInfo.put("remarks", table.getComment());
			tablelist.add(tableInfo);
		}
		return tablelist;
	}

3.获取字段信息

根据官方SDK提供的API获取ODPS表字段中英文名称。

	public ArrayList<Map<String, String>> showTableFields(Odps stmt, String tablename) throws OdpsException {
		ArrayList<Map<String, String>> tableFieldlist = new ArrayList<>();
		Table t = stmt.tables().get(tablename);
		t.reload();
		TableSchema schema = t.getSchema();
		List<Column> columns = schema.getColumns();
		for (Column column : columns) {
			Map<String, String> fieldInfo = new HashMap<>();
			fieldInfo.put("columnName", column.getName());
			fieldInfo.put("remarks", column.getComment());
			tableFieldlist.add(fieldInfo);
		}
		return tableFieldlist;
	}

4.获取表数据

ODPS支持类SQL，通过查询语句获取表数据(类似于JDBC方式)。

	public void getTableData(Odps stmt, String tablename) throws OdpsException {
		String sql = "select * from " + tablename + " limit 5;";
		Instance i = SQLTask.run(stmt, sql);
		i.waitForSuccess();
		List<Record> records = SQLTask.getResult(i);
		for (Record r : records) {
			String fielddata1 = r.getString(1);
			String fielddata = r.getString("name");
		}
	}

二、DRDS分布式数据库服务(中间件)

1.简介

基于MYSQL协议进行通信，连接方式同理于连接MYSQL，操作方式本质同MYSQL。

2.准备工作

1）MYSQL驱动，若版本不适更换驱动版本即可，maven依赖如下：

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.25</version>
		</dependency>

2）DRDS连接IP、端口、账号、密码

3.获取连接

参考MYSQL获取数据库连接方式。
JDBC获取数据库连接如下：

	public static Connection getConns(String driver, String url, String username, String password) throws SQLException, ClassNotFoundException {
		Connection conn = null;
		Class.forName(driver);
		conn = DriverManager.getConnection(url, username, password);
		return conn;
	}

3.获取表信息

参考MYSQL获取表方式。
JDBC获取表中英文名名称如下：

	public ArrayList<Map<String, String>> getTablesLists(Connection conn) throws SQLException {
		ArrayList<Map<String, String>> tablelist = new ArrayList<>();
		ResultSet rs = null;
		Statement stmt = conn.createStatement();
		DatabaseMetaData dbmd = conn.getMetaData();
		String[] types = { "TABLE" };
		rs = dbmd.getTables(null, null, "%", types);
		while (rs.next()) {
			Map<String, String> tableInfo = new HashMap<String, String>();
			String tableName = rs.getString("TABLE_NAME");
			tableInfo.put("tableName", tableName);// 表名
			ResultSet rstemp = stmt.executeQuery("SHOW CREATE TABLE `" + tableName + "`");
			if (rstemp != null && rstemp.next()) {
				String create = rstemp.getString(2);
				tableInfo.put("remarks", parse(create));// 注释
			}
			tablelist.add(tableInfo);
		}
		return tablelist;
	}

	public String parse(String all) {
		String comment = null;
		int index = all.indexOf("COMMENT='");
		if (index < 0) {
			return null;
		}
		comment = all.substring(index + 9);
		if (comment.contains("' dbpartition by")) {
			comment = comment.substring(0, comment.indexOf("' dbpartition by"));
		} else {
			comment = comment.substring(0, comment.indexOf("'"));
		}
		try {
			comment = new String(comment.getBytes("utf-8"));
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		return comment;
	}

4.获取字段信息

参考MYSQL获取字段方式。
JDBC获取字段中英文名称如下：

	public ArrayList<Map<String, String>> getColumnsInfos(String tablename, Connection conn) throws SQLException {
		ArrayList<Map<String, String>> fieldList = new ArrayList<>();
		ResultSet rs = null;
		DatabaseMetaData dbmd = conn.getMetaData();
		rs = dbmd.getColumns(null, null, tablename, null);
		while (rs.next()) {
			Map<String, String> fieldInfo = new HashMap<String, String>();
			fieldInfo.put("columnName", rs.getString("COLUMN_NAME"));// 列名
			fieldInfo.put("remarks", rs.getString("REMARKS"));// 注释
			fieldList.add(fieldInfo);
		}
		return fieldList;
	}

5.获取表数据

参考MYSQL获取表数据方式。
JDBC获取表数据如下：

	private void getFieldData(String tablename, Connection conn) throws SQLException {
		String sql = "select * from `" + tablename + "` limit 5";
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			String fielddata1 = rs.getString(1);
			String fielddata = rs.getString("name");
		}
	}