This topic describes how to call the PreparedStatement interface by using Java, Hypertext Preprocessor (PHP), and C# in Data Lake Analytics (DLA) to process SQL requests on data that is read from ApsaraDB RDS.

Background information

DLA allows you to call the PreparedStatement interface to compile SQL statements, instead of manually compiling SQL statements. Calling this interface has the following advantages:
  • Automatically escapes sensitive characters to prevent SQL injection attacks.
  • Dynamically executes SQL statements. After an SQL statement is prepared, you need only to replace parameters for subsequent executions.
  • Compiles SQL statements in object-oriented programming (OOP) mode. After this interface is called, you can configure parameters by calling the PrepareSteatement.setXxx() method.

For more information about the advantages of the PreparedStatement interface, see JDBC Statement vs PreparedStatement – SQL Injection Example.

Prerequisites

Before you call the PreparedStatement interface in DLA, make sure that you have created a schema and table of ApsaraDB RDS in DLA. For more information, see Procedure.

In this example, the type_test table is created 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 ''
)
In the following example, the MySQL CLI tool is used to connect to DLA and read data from the type_test table. You can also query data from the table by using the MySQL client or in the Database Management Service (DMS) console.
> 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)
Note The \G parameter in select * from type_test\G; allows data to be displayed vertically.

Java

DLA is compatible with the MySQL protocol. Therefore, you can use the Java Database Connectivity (JDBC) driver of MySQL to connect to DLA. After DLA is connected, you can call the PreparedStatement interface by using Java.

Calling method: Add useServerPrepStmts=true to the end of 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` = ?" ;
        //Connect to 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();
            }
        }
    }
}
After the preceding statements are executed, the following result is returned:
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 to prepare SQL statements.
$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 the preceding statements are executed, the following result is returned:
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 to prepare SQL statements.
                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 the preceding statements are executed, the following result is returned:
 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.