Call PreparedStatement to connect to DLA - Deep Dive| Alibaba Cloud Documentation Center

Background

Data Lake Analytics (DLA) supports calling the PreparedStatement method. This feature has the following advantages over manually writing SQL statements:

The PreparedStatement method automatically escapes sensitive characters to prevent SQL injection attacks.
The PreparedStatement method dynamically executes SQL statements. If the execution has been implemented once, you only need to replace the parameters for subsequent executions.
The PreparedStatement method allows for object-oriented programming (OOP) of SQL code.

After the PreparedStatement method is called, parameters are set by using PrepareSteatement.setXxx (), rather than string concatenation.

For more information about the PreparedStatement method, see JDBC Statement vs PreparedStatement - SQL Injection Example.

This topic describes how to use Java, PHP, and C# to call the PreparedStatement method for SQL request processing in DLA if you can already use DLA to read data from an RDS database.

Prerequisites

Before calling the PreparedStatement method in DLA, create an RDS-mapping database and external table in DLA by following the instructions provided in Use DLA to read data from an RDS database.

The following example creates an external table named type_test in DLA:

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 ''
)

The following example uses the MySQL CLI tool to connect to DLA, and then queries data of the type_test table in DLA. You can also use the MySQL client to connect to DLA or query data of the type_test table directly in DMS.

> 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)

In select * from type_test \ G;, the \Gparameter enables vertical display of data.

Procedure

Java

DLA is compatible with MySQL, so you can connect to DLA by using the MySQL JDBC driver. After connecting to DLA, use Java to call the PrepareStatement method.

Method: Append useServerPrepStmts=true to the JDBC connection string.

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` = ?" ;
        try (Connection dlaConn = DriverManager.getConnection(
                "jdbc:mysql://10130223134566336-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();
            }
        }
    }
}

After executing the preceding code, you obtain the following response:

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();
// Start preparation
$stmt->prepare("select * from type_test where id = ?") ;
 $id = 1;
// Bind parameters
$stmt->bind_param("i", $id);
$stmt->execute();
 $result = $stmt->get_result();
var_dump($result->fetch_all());
? >

After executing the preceding code, you obtain the following response:

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);
                // Start preparation
                cmd.Prepare();
                // Bind parameters
                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.") ;
        }
    }

After executing the preceding code, you obtain the following response:

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.