全部产品
云市场

通过PreparedStatement访问DLA

更新时间:2019-10-18 10:29:18

本文档将以通过DLA读取RDS中的数据为前提,介绍如何在DLA应用中,通过JavaPhpC#调用PreparedStatement接口处理SQL请求。

背景信息

Data Lake Analytics(简称DLA)应用中支持调用PreparedStatement接口,与手动编写SQL相比,调用PreparedStatement接口有以下优势:

  • PreparedStatement接口会自动做敏感字符的转义,防止SQL Injection攻击。

  • PreparedStatement接口动态执行SQL,Prepare一次之后,后续执行时只需替换参数即可。

  • PreparedStatement可以以OOP的方式编写SQL代码。

    调用PreparedStatement接口后,将通过PrepareSteatement.setXxx()的方式来设置参数,而不是字符串拼接的方式。

关于PreparedStatement接口的更过优势请参见JDBC Statement vs PreparedStatement – SQL Injection Example

前提条件

DLA中正式调用PreparedStatement接口之前,您可以参考文档通过DLA读取RDS中的数据,在DLA中创建RDS映射数据库和外表。

本示例在DLA中创建type_test外表:

  1. CREATE EXTERNAL TABLE `type_test` (
  2. `id` bigint(20) NULL DEFAULT NULL COMMENT '',
  3. `tinyint_col` tinyint(4) NULL DEFAULT NULL COMMENT '',
  4. `int_col` int(11) NULL DEFAULT NULL COMMENT '',
  5. `char_col` char(10) NULL DEFAULT 'NULL' COMMENT '',
  6. `varchar_col` varchar(10) NULL DEFAULT 'NULL' COMMENT '',
  7. `float_col` double NULL DEFAULT NULL COMMENT '',
  8. `double_col` double NULL DEFAULT NULL COMMENT '',
  9. `decimal_col` decimal(20, 4) NULL DEFAULT NULL COMMENT '',
  10. `time_col` time(3) NULL DEFAULT 'NULL' COMMENT '',
  11. `datetime_col` datetime(6) NULL DEFAULT NULL COMMENT '',
  12. `timestamp_col` timestamp(6) NOT NULL COMMENT '',
  13. `string_col` varchar(100) NULL DEFAULT 'NULL' COMMENT '',
  14. `date_col` date NULL DEFAULT 'NULL' COMMENT '',
  15. `smallint_col` smallint(6) NULL DEFAULT NULL COMMENT '',
  16. `mediumint_col` int NULL DEFAULT NULL COMMENT '',
  17. `bigint_col` bigint(20) NULL DEFAULT NULL COMMENT ''
  18. )

以下示例先通过MySQL命令行工具连接DLA(也支持通过MySQL客户端连接DLA或者直接在DMS中查询type_test表数据),然后查询DLA中type_test表数据。

  1. > select * from type_test\G;
  2. *************************** 1. row ***************************
  3. id: 1
  4. tinyint_col: 2
  5. int_col: 3
  6. char_col: hello1
  7. varchar_col: 5
  8. float_col: 6.01
  9. double_col: 7.02
  10. decimal_col: 8.0300
  11. time_col: 01:02:01.000
  12. datetime_col: 1986-10-01 01:02:03.000000
  13. timestamp_col: 2018-11-29 14:04:28.305523
  14. string_col: hello
  15. date_col: 2018-09-07
  16. smallint_col: NULL
  17. mediumint_col: NULL
  18. bigint_col: 2
  19. *************************** 2. row ***************************
  20. id: 1111111
  21. tinyint_col: 127
  22. int_col: 4
  23. char_col: hello2
  24. varchar_col: 5555555555
  25. float_col: 9996.01
  26. double_col: 7777777.02
  27. decimal_col: 888888888.0300
  28. time_col: 01:02:02.000
  29. datetime_col: 1986-10-01 01:02:03.000000
  30. timestamp_col: 2018-11-29 14:36:05.486738
  31. string_col: hello
  32. date_col: 2018-09-08
  33. smallint_col: NULL
  34. mediumint_col: NULL
  35. bigint_col: 1111112
  36. *************************** 3. row ***************************
  37. id: 3
  38. tinyint_col: 127
  39. int_col: 5
  40. char_col: hello3
  41. varchar_col: 5555555555
  42. float_col: 9997.01
  43. double_col: 7777777.02
  44. decimal_col: 888888888.0300
  45. time_col: 01:02:03.000
  46. datetime_col: 1986-10-01 01:02:03.000000
  47. timestamp_col: 2018-11-20 10:31:40.112000
  48. string_col: hello
  49. date_col: 2018-09-09
  50. smallint_col: 3
  51. mediumint_col: NULL
  52. bigint_col: 4
  53. 3 rows in set (0.00 sec)

select * from type_test\G;\G参数使数据展示纵向显示。

实施步骤

Java

DLA兼容MySQL协议,可以使用MySQL的JDBC驱动来连接DLA。DLA连接成功后,可以通过Java调用PrepareStatement接口。

调用方法:在JDBC连接串的末尾加上useServerPrepStmts=true参数即可。

  1. import java.sql.*;
  2. public class DLAPrepStmtMain {
  3. public static void main(String[] args) throws Exception {
  4. Class.forName("com.mysql.jdbc.Driver");
  5. String sql = "select * from type_test where `key` = ?";
  6. try (Connection dlaConn = DriverManager.getConnection(
  7. "jdbc:mysql://101*******-fake.cn-hangzhou.datalakeanalytics.aliyuncs.com:10000/yourdb?useServerPrepStmts=true",
  8. "your-username",
  9. "your-password");
  10. PreparedStatement stmt = dlaConn.prepareStatement(sql)) {
  11. stmt.setString(1, "key01");
  12. ResultSet rs = stmt.executeQuery();
  13. while (rs.next()) {
  14. for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
  15. System.out.print(rs.getString(i + 1) + ", ");
  16. }
  17. System.out.println();
  18. }
  19. }
  20. }
  21. }

执行上述代码,得到以下结果:

  1. 1 2 3 hello1 5 6.01 7.02 8.03 01:02:01 1986-10-01 01:02:03.0 2018-11-29 14:04:28.305 hello 2018-09-07 null null 2

Php

  1. <?php
  2. $mysqli = new mysqli("fakee.cn-hangzhou.datalakeanalytics.aliyuncs.com:10000", "your-username", "your-password", "yourdb");
  3. $stmt = $mysqli->stmt_init();
  4. // 开始prepare
  5. $stmt->prepare("select * from type_test where id = ?");
  6. $id = 1;
  7. // 绑定参数
  8. $stmt->bind_param("i", $id);
  9. $stmt->execute();
  10. $result = $stmt->get_result();
  11. var_dump($result->fetch_all());
  12. ?>

执行上述代码,得到以下结果:

  1. array(1) {
  2. [0]=>
  3. array(16) {
  4. [0]=>
  5. int(1)
  6. [1]=>
  7. int(2)
  8. [2]=>
  9. int(3)
  10. [3]=>
  11. string(6) "hello1"
  12. [4]=>
  13. string(1) "5"
  14. [5]=>
  15. float(6.01)
  16. [6]=>
  17. float(7.02)
  18. [7]=>
  19. float(8.03)
  20. [8]=>
  21. string(8) "01:02:01"
  22. [9]=>
  23. string(19) "1986-10-01 01:02:03"
  24. [10]=>
  25. string(19) "2018-11-29 14:04:28"
  26. [11]=>
  27. string(5) "hello"
  28. [12]=>
  29. string(10) "2018-09-07"
  30. [13]=>
  31. NULL
  32. [14]=>
  33. NULL
  34. [15]=>
  35. int(2)
  36. }
  37. }

C#

  1. public static void Main()
  2. {
  3. string connStr = "server=your-endpoint.cn-hangzhou.datalakeanalytics.aliyuncs.com;UID=your-username;database=yourdb;port=10000;password=your-password;SslMode=none";
  4. MySqlConnection conn = new MySqlConnection(connStr);
  5. try
  6. {
  7. Console.WriteLine("Connecting to MySQL...");
  8. conn.Open();
  9. string sql = "select * from type_test where id = @var1";
  10. MySqlCommand cmd = new MySqlCommand(sql, conn);
  11. // 开始prepare
  12. cmd.Prepare();
  13. // 绑定参数
  14. cmd.Parameters.AddWithValue("@var1", 1);
  15. MySqlDataReader res = cmd.ExecuteReader();
  16. while (res.Read())
  17. {
  18. for (int i = 0; i < res.FieldCount; i++)
  19. {
  20. Console.Write(res[i] + ",");
  21. }
  22. }
  23. while (res.NextResult())
  24. {
  25. }
  26. res.Close();
  27. }
  28. catch (Exception ex)
  29. {
  30. Console.WriteLine(ex.ToString());
  31. }
  32. conn.Close();
  33. Console.WriteLine("Done.");
  34. }
  35. }

执行上述代码,得到以下结果:

  1. Connecting to MySQL...
  2. 1,2,3,hello1,5,6.01,7.02,8.03,01:02:01,10/01/1986 01:02:03,11/29/2018 14:04:28,hello,09/07/2018 00:00:00,,,2,Done.