教程: 使用PreparedStatement访问DLA

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 大家都知道PreparedStatement相比手动拼写SQL有很多好处,比如: 它会自动做敏感字符的转义,防止SQL Injection攻击。 它可以帮助我们动态执行SQL,Prepare一次之后,后续执行只需要替换参数就可以了。

大家都知道PreparedStatement相比手动拼写SQL有很多好处,比如:

  • 它会自动做敏感字符的转义,防止SQL Injection攻击。
  • 它可以帮助我们动态执行SQL,Prepare一次之后,后续执行只需要替换参数就可以了。
  • 它可以帮助以OOP的方式来写SQL相关相关代码,因为我们是通过 PrepareSteatement.setXxx()的方式而不是字符串拼接的方式来设置参数。

等等,PreparedStatement的好处还有很多,更多可以参考这篇《JDBC Statement vs PreparedStatement – SQL Injection Example》, 上面说的很详细。

今天我们Data Lake Analytics也引入了对PreparedStatement的支持, 今天给大家演示一下,如何用 PreparedStatement 来访问 DLA.

准备工作

为了准备演示的环境,大家可能要先熟悉一下怎么在DLA上面创建数据库,创建表,在云栖社区上面通过关键字 DLA 可以搜索到很多文章,比如创建访问RDS的DLA库可以看这篇文章: 教程:使用Data Lake Analytics读/写RDS数据 .

我们在这篇教程里面要演示的表的结构是这样的:

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 ''
)
COMMENT ''

里面的数据是这样的:

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

Java

因为我们兼容MySQL协议,我们使用MySQL的JDBC驱动来访问DLA。值得注意的是 MySQL JDBC驱动支持客户端 PrepareStatement (也是很厉害啊), 要使用服务端的PreparedStatement的功能,需要在JDBC连接串的末尾加上 useServerPrepStmts=true 的参数,如下:

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://1013022312866336-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();
            }
        }
    }
}

执行会发现,控制台会正确打印出结果:

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不愧是世界上最好的语言,写起PreparedStatement起来也是非常的简洁:

<?php
$mysqli = new mysqli("fakee.cn-hangzhou.datalakeanalytics.aliyuncs.com:10000",
                     "democ", "demo", "demo");
$stmt = $mysqli->stmt_init();
// 开始prepare
$stmt->prepare("select * from type_test where id = ?");

$id = 1;
// 绑定参数
$stmt->bind_param("i", $id);
$stmt->execute();
$result = $stmt->get_result();
var_dump($result->fetch_all());
?>

运行结果:

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

CSharp

C#不愧是比Java更好的语言,写起来也是虎虎生风:

        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);
                // 开始prepare
                cmd.Prepare();
                // 绑定参数
                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.");
        }
    }

结果如下:

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.

总结

随着DLA对于各种语言PreparedStatement的支持,大家可以用PreparedStatement替换原先手动拼SQL的代码,让你的代码更OOP,更安全!

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
21天前
|
Java 数据库连接 数据库
JDBC之Statement与PreparedStatement操作数据库对比
JDBC之Statement与PreparedStatement操作数据库对比
19 0
|
7月前
|
SQL 缓存 Java
JDBC中PreparedStatement常用操作实践
JDBC中PreparedStatement常用操作实践
167 1
|
11月前
|
SQL Java 数据库连接
JDBC快速入门(五)--PreparedStatement用法
JDBC快速入门(五)--PreparedStatement用法
PreparedStatement 防止 SQL 注入原理
PreparedStatement 对象可以防止 SQL 注入,而 Statement 对象不能防止 SQL 注入,接下来使用一个案例剖析原理。
PreparedStatement 防止 SQL 注入原理
|
SQL 存储 缓存
JDBC使用PreparedStatement实现CRUD操作
JDBC使用PreparedStatement实现CRUD操作
139 0
|
SQL Java 数据库连接
JDBC学习(二):PreparedStatement初体验
JDBC学习(二):PreparedStatement初体验
JDBC学习(二):PreparedStatement初体验
|
Java 关系型数据库 MySQL
JDBC学习(八):PreparedStatement实现数据的批量插入
JDBC学习(八):PreparedStatement实现数据的批量插入
257 0
|
SQL Java 关系型数据库
PreparedStatement 的用法 | 学习笔记
快速学习 PreparedStatement 的用法。
252 1
|
JSON Java 数据库
简单实现数据库的PreparedStatement查询
查询一个数据库的全部记录,并且将其用java list储存起来
156 0
|
缓存 Oracle 关系型数据库
JDBC PreparedStatement 批量查询 in 的实现 方案
    我们经常会有这种业务需求,根据一个条件集合去查询一张表的数据,比如: select * from all_element t where t.task_id in (List &lt;taskids&gt;);     在java语言中,我们需要用到JDBC来和数据库打交道,那么在JDBC中该如何处理这种需求呢?我们可以有如下几种处理方式 方案一:写一
2245 0