All Products
Search
Document Center

Call PreparedStatement to connect to DLA

Last Updated: Mar 17, 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.