SQL 查询

直接在前端使用数据库查询是卡拉云最强大的功能之一,与数据库连通不光允许你在前端调试、展示数据,同时允许你直接用组件控制要操作的后台数据。

在卡拉云中查询数据库很容易,只需要

  1. 连接数据源
  2. 创建一个 SQL 查询语句

连接数据源

首先你需要连接好一个数据源。不管你的数据库是 MySQL 或者是 PostgreSQL,你都可以非常方便地用卡拉云连接上。关于连接数据源的详细步骤,请参考文档接入数据源

如果你手里没有现成的数据库可以用,或者是想要一个数据库用来测试,请连接

数据源类型请选择 PostgreSQL

Host: db.bit.io
数据库名: bitdotio
用户名: oeddyo_demo_db_connection
密码: 39bLE_tpEVkJkHdDAGFrG3xika3hS
端口: 5432
👍
demo 数据库
在这个测试数据库的表 "oeddyo/demo"."orders" (注意,这个数据库是一个较特殊的数据库,表名中包含引号和点号)中含有本教程中用到的订单数据,包括以下字段
* id - 订单的 id
* address - 订单的地址
* product_image - 产品的图片

在本教程中,我们也用这个数据库做为例子。当然,如果你有自己的数据库可以接入,那么使用自己的数据库更好,测试过后就可以直接使用了。

创建一个查询

连接好数据源之后,创建一个应用。在编辑界面中,我们先拖入一个表格用来展示数据。

接下来,点击编辑界面上方的查询编辑 进入查询编辑器。之后, 点击左侧的新建查询。点击新建查询后,我们就新建了一个名叫 query1 的查询。

👍
demo 数据库
注意,我们建议把查询的名字取好,比如叫 `getAllUserData` 之类更有意义的名字,以方便管理应用。这里简单起见,我们用 query1 作为示例

新建查询

在右侧,我们先选中刚才创建的数据源,这样右方会出现让我们写 SQL 查询的编辑器。你可以点击编辑器上的扩展按钮,这样会出现一个 SQL 编辑窗口,方便操作。

点弹窗

现在在弹窗中就可以编辑 SQL 查询了。

保存和试运行查询

在写完 SQL 查询后,查询还是未保存状态。此时即使你点击或者让其它组件触发这个查询也不会执行成功。

因此你需要先保存查询,同时注意,每次改动查询时,均需要重新保存一次。保存查询很简单,点击“保存”按钮,即可以进入数据源页面。点击

保存查询

到此,我们的 SQL 查询就写好了。如果你点“运行”的话,可以试运行一次,同时让你看到查询的结果。每次运行后,SQL 查询的运行结果会保存在 query.data 这个属性中。如果没有运行过,这个值是不存在的,所以我们可以先运行一下。

最后确认一下,我们添加的查询为:

SELECT id, address, product_image

FROM "oeddyo/demo"."orders"

也即从表中选出 id, address 和图片列。

将 SQL 查询的数据绑定到表格中

回到表格中,我们将表格数据一项内填入 {{query1.data}}。由于在上一步我们点过“运行”,此时 query1 中的 data 属性应该是有值的。表格会加载对应的值,然后展示出来,如下

展示数据

至此,你就可以熟练地用 SQL 查询数据库了。

防 SQL 注入和数据安全

卡拉云会自动将所有的查询转为 Prepared Statement,也称参数化查询。参数化查询是非常标准和重要的防 SQL 注入手段。虽然对于不同的数据库来说,参数化查询的实现不一样,但是绝大多数的数据库实现的参数化查询,是不允许动态查询表名或列名的

也就是说,类似以下查询是无法正常执行

  select * from {{textinput1.value === '学生' ? "students": "teachers"}}

原因是这里试图动态查询表名,当用户输入为 学生 时查询 students 表,而输入为其它值时查询 teachers 表。

同样的,以下查询试图动态查询列名,也是无法执行

  select * from students where id = {{textInput1.value}}

之所以要限制并把所有查询转化为参数化查询,原因是你的用户(比如实习生,或公司的其它同事)可能会尝试构造一些输入,让你的查询去执行未预料到的表或者列。由于参数化查询的重要性,我们默认将所有查询都使用参数化查询来执行。

常见问题

卡拉云中 SQL 查询的限制多少?

每个 SQL 查询可返回的最大数据大小为 10MB,如果你的返回值超限,请检查select 语句中是否有合适的 limit。将所有数据放到前端处理通常会造成不太友好的用户体验,因此多数情况下 10MB 的限制是非常够用的。

卡拉云中的 SQL 语句与标准的 SQL 一样吗?

卡拉云使用标准的 JDBC 来连接各种数据库。如果你的 SQL 在你的 MySQL 上可以执行,那么在卡拉云上几乎一定可以执行。同样的,如果你的 PostgreSQL 在你的 PG 上可以执行,那么在卡拉云中也可以。

在 SQL 中怎样使用数组或 IN 语句查询?

在 MySQL 或 PostgreSQL 中,你都可以使用 IN 语句来查询属于一个数组的内容。

举个例子,如果你要查询你的用户表格,只要满足名字在数组 ["小红", "小白", "小张"] 中就拿出来时,在 MySQL 里可以使用以下 SQL 查询


SELECT * FROM YOUR_TABLE WHERE name IN ('小红', '小白', '小张')

那么在卡拉云中,如果你需要像上面一样,查询包含对应的数组中的值时,则可以用以下查询


SELECT * FROM YOUR_TABLE WHERE name IN ({{['小红', '小白', '小张']}})
👍
SQL 数组查询的区别
注意这里,在 IN 语句中,我们加上了一个花括号,同时在花括号中加上了一个数组。 花括号的作用是为了引用 JavaScript 代码,在花括号内,我们就可以使用普通的 JS 数组,也就是 ['小红', '小白', '小张'] 了

在 SQL 语句中,中怎样使用动态数组进行查询?

如果你的数组是动态值 —— 比如说,你希望让用户可以选择表格中的多行数据,每行数据有一个 name 列,然后你希望查询所有含有用户选择的名字的数据时,你没有办法提前把数组写好,因为你不知道用户会选择哪些数据。因此,我们需要动态构造一个数组,来实现此查询:


SELECT * FROM YOUR_TABLE WHERE name IN ({{ table1.selectedRow.data.map(row => row.name)}})

我们来理解一下这个语句: 在这个语句中,table1.selectedRow.data.map(row => row.name) 即用 JS 代码,将用户在 table1 中选中的所有行拿出来,取其中的 name 列,构造成一个数组。构造出数组后,就可以用 IN 语句直接进行查询了。

特别注意对于表格来说,只有开启了多行选择选项,表格中的属性 table1.selectedRow.data 才会是一个数组,详情请参考表格文档