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.
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 Read and write ApsaraDB RDS MySQL data.
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)
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.