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\G
parameter 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.