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

背景信息

Data Lake Analytics(简称DLA)应用中支持调用PreparedStatement接口,与手动编写SQL相比,调用PreparedStatement接口有以下优势:
  • PreparedStatement接口自动做敏感字符的转义,防止SQL Injection攻击。
  • PreparedStatement接口动态执行SQL,Prepare一次之后,后续执行时只需替换参数即可。
  • PreparedStatement可以以OOP(Object Oriented Programming)的方式编写SQL。调用PreparedStatement接口后,将通过PrepareSteatement.setXxx()的方式来设置参数。

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

前提条件

DLA中正式调用PreparedStatement接口之前,您可以参见文档操作步骤,在DLA中创建RDS Schema和表。

本示例在DLA中创建type_test表:
CREATE EXTERNAL TABLE type_test (
    id bigint(20) NULL DEFAULT NULL COMMENT '',
    tinyint_col tinyint(4) NULL DEFAULT NULL COMMENT '',
    int_col int(11) NULL DEFAULT NULL COMMENT '',
    char_col char(10) NULL DEFAULT 'NULL' COMMENT '',
    varchar_col varchar(10) NULL DEFAULT 'NULL' COMMENT '',
    float_col double NULL DEFAULT NULL COMMENT '',
    double_col double NULL DEFAULT NULL COMMENT '',
    decimal_col decimal(20, 4) NULL DEFAULT NULL COMMENT '',
    time_col time(3) NULL DEFAULT 'NULL' COMMENT '',
    datetime_col datetime(6) NULL DEFAULT NULL COMMENT '',
    timestamp_col timestamp(6) NOT NULL COMMENT '',
    string_col varchar(100) NULL DEFAULT 'NULL' COMMENT '',
    date_col date NULL DEFAULT 'NULL' COMMENT '',
    smallint_col smallint(6) NULL DEFAULT NULL COMMENT '',
    mediumint_col int NULL DEFAULT NULL COMMENT '',
    bigint_col bigint(20) NULL DEFAULT NULL COMMENT ''
)
            
以下示例通过MySQL命令行工具连接DLA(也可以通过MySQL客户端或者直接在DMS中查询type_test表数据),然后读取type_test表数据。
> select * from type_test\G;
*************************** 1. row ***************************
           id: 1
  tinyint_col: 2
      int_col: 3
     char_col: hello1
  varchar_col: 5
    float_col: 6.01
   double_col: 7.02
  decimal_col: 8.0300
     time_col: 01:02:01.000
 datetime_col: 1986-10-01 01:02:03.000000
timestamp_col: 2018-11-29 14:04:28.305523
   string_col: hello
     date_col: 2018-09-07
 smallint_col: NULL
mediumint_col: NULL
   bigint_col: 2
*************************** 2. row ***************************
           id: 1111111
  tinyint_col: 127
      int_col: 4
     char_col: hello2
  varchar_col: 5555555555
    float_col: 9996.01
   double_col: 7777777.02
  decimal_col: 888888888.0300
     time_col: 01:02:02.000
 datetime_col: 1986-10-01 01:02:03.000000
timestamp_col: 2018-11-29 14:36:05.486738
   string_col: hello
     date_col: 2018-09-08
 smallint_col: NULL
mediumint_col: NULL
   bigint_col: 1111112
*************************** 3. row ***************************
           id: 3
  tinyint_col: 127
      int_col: 5
     char_col: hello3
  varchar_col: 5555555555
    float_col: 9997.01
   double_col: 7777777.02
  decimal_col: 888888888.0300
     time_col: 01:02:03.000
 datetime_col: 1986-10-01 01:02:03.000000
timestamp_col: 2018-11-20 10:31:40.112000
   string_col: hello
     date_col: 2018-09-09
 smallint_col: 3
mediumint_col: NULL
   bigint_col: 4
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参数即可。

import java.sql.*;
public class DLAPrepStmtMain {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String sql = "select * from type_test where `key` = ?";
        //连接DLA
        try (Connection dlaConn = DriverManager.getConnection(
                "jdbc:mysql://101*******-fake.cn-hangzhou.datalakeanalytics.aliyuncs.com:10000/yourdb?useServerPrepStmts=true",
                "your-username",
                "your-password");
             PreparedStatement stmt = dlaConn.prepareStatement(sql)) {
            stmt.setString(1, "key01");
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                    System.out.print(rs.getString(i + 1) + ", ");
                }
                System.out.println();
            }
        }
    }
}
            
执行上述代码,得到以下结果:
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

<?php
$mysqli = new mysqli("fakee.cn-hangzhou.datalakeanalytics.aliyuncs.com:10000", "your-username", "your-password", "yourdb");
$stmt = $mysqli->stmt_init();
// 开始prepare
$stmt->prepare("select * from type_test where id = ?");
$id = 1;
// 绑定参数
$stmt->bind_param("i", $id);
$stmt->execute();
$result = $stmt->get_result();
var_dump($result->fetch_all());
?>
            
执行上述代码,得到以下结果:
array(1) {
  [0]=>
  array(16) {
    [0]=>
    int(1)
    [1]=>
    int(2)
    [2]=>
    int(3)
    [3]=>
    string(6) "hello1"
    [4]=>
    string(1) "5"
    [5]=>
    float(6.01)
    [6]=>
    float(7.02)
    [7]=>
    float(8.03)
    [8]=>
    string(8) "01:02:01"
    [9]=>
    string(19) "1986-10-01 01:02:03"
    [10]=>
    string(19) "2018-11-29 14:04:28"
    [11]=>
    string(5) "hello"
    [12]=>
    string(10) "2018-09-07"
    [13]=>
    NULL
    [14]=>
    NULL
    [15]=>
    int(2)
  }
}
            

C#

  public static void Main()
        {
            string connStr = "server=your-endpoint.cn-hangzhou.datalakeanalytics.aliyuncs.com;UID=your-username;database=yourdb;port=10000;password=your-password;SslMode=none";
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                Console.WriteLine("Connecting to MySQL...");
                conn.Open();
                string sql = "select * from type_test where id = @var1";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                // 开始prepare
                cmd.Prepare();
                // 绑定参数
                cmd.Parameters.AddWithValue("@var1", 1);
                MySqlDataReader res = cmd.ExecuteReader();
                while (res.Read())
                {
                    for (int i = 0; i < res.FieldCount; i++)
                    {
                        Console.Write(res[i] + ",");
                    }
                }
                while (res.NextResult())
                {
                }
                res.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            conn.Close();
            Console.WriteLine("Done.");
        }
    }
            
执行上述代码,得到以下结果:
 Connecting to MySQL...
 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.