All Products
Search

This Product

    Call PreparedStatement to connect to DLA

    Document Center
    Product Details

    Call PreparedStatement to connect to DLA

    Last Updated: Oct 29, 2019

    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:

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

    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.

    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)

    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.

    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://10130223134566336-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. }

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

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

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

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

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

    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.
    Free Trial Free Trial