# datart通过自定义视图查询数据源数据

#### 一、 功能概述

该功能可以实现动态注册数据源，自定义SQL就可以通过统一接口查询数据。减少开发工作量，提高效率。


#### 二、注册数据源

注册数据源如下图。
[![微信图片_20231207143758.png](https://iovhm.com/book/uploads/images/gallery/2023-12/scaled-1680-/5Bh4Fx0bjyhPSf9m-20231207143758.png)](https://iovhm.com/book/uploads/images/gallery/2023-12/5Bh4Fx0bjyhPSf9m-20231207143758.png)

注意：**连接地址，一定要设置utf8编码，不然中文查询不出来**，示例：
jdbc:mysql://121.37.7.167:33314/数据库名?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true



#### 三、添加数据视图
数据视图-新建数据视图-SQL 视图，进入编写要查询数据库的SQL
[![微信图片_20231207144517.png](https://iovhm.com/book/uploads/images/gallery/2023-12/scaled-1680-/ECRgXRCI2H7dcrkH-20231207144517.png)](https://iovhm.com/book/uploads/images/gallery/2023-12/ECRgXRCI2H7dcrkH-20231207144517.png)

1.先选择数据源

2.编写查询SQL

3.执行SQL

4.保存视图

5.将数据视图关掉，重新进入，地址栏可以看到视图id
[![微信图片_20231207153849.png](https://iovhm.com/book/uploads/images/gallery/2023-12/scaled-1680-/tZ7SPb2UE81vryEb-20231207153849.png)](https://iovhm.com/book/uploads/images/gallery/2023-12/tZ7SPb2UE81vryEb-20231207153849.png)




#### 四、调用接口查询数据

##### 1.获取TOKEN

（1）接口地址：[POST]   https://{url}/api/v1/users/login

（2）Body 请求参数:

```json
{
"username": "wangbo", //用户名
"password": "admin@2023!" //密码
}
```

（3）响应头Authorization中获取token
[![微信图片_20231207160706.png](https://iovhm.com/book/uploads/images/gallery/2023-12/scaled-1680-/jMdxmZYfRxRFCKWd-20231207160706.png)](https://iovhm.com/book/uploads/images/gallery/2023-12/jMdxmZYfRxRFCKWd-20231207160706.png)



##### 2.获取视图数据
(1)接口地址: [POST]  https://{url}/api/v1/data-provider/view

(2)Header 请求参数

| 参数名        | 参数值                       | 是否必填 | 参数类型 | 参数说明 |
| ------------- | ---------------------------- | -------- | -------- | -------- |
| Authorization | Bearer eyJhbGciOiJIUzI1xxxxx | 是       | string   | token    |

(3)Body 请求参数

```
{
	"page": 1,//当前页
	"limit": 20,//每页条数
	"viewId": "a961d8dc8af4496aad3dfe71d9668416",//视图id
	"filters": [  //查询条件，接口执行时会拼在sql语句的where部分
		{
			"column": [  //字段名字
				"create_date"
			],
			"sqlOperator": "GT",  //运算符(EQ:等于,NE:不等于,GTE:大于等于,LTE:小于等于,LT:小于,GT:大于)
			"values": [
				{
					"value": "2023-06-08",  //参数值
					"valueType": "DATE"  //参数类型
				}
			]
		}
	]
}
```

(4)响应示例

```
{
	"data": {  //返回数据
		"list": [  返回数据列表，字段由查询数据决定
			{
				"request_time": 154,
				"ip": "127.0.0.1",
				"id": 1667084925162176513,
				"request_method": "POST",
				"create_date": "2023-06-09 16:23:14",
				"operation": "保存",
				"request_uri": "/renren-admin/kong/kongapp/save",
				"status": 0
			}
		],
		"page": 1,   //当前页
		"limit": 20,  //每页面条数
		"total": 56   //总条数
	},
	"errCode": 0,
	"message": null,
	"success": true  //是否成功
}
```





#### 五、自定义视图变量

(1)添加变量
[![微信图片_20231207163845.png](https://iovhm.com/book/uploads/images/gallery/2023-12/scaled-1680-/zjMEckj6hjwQXoo5-20231207163845.png)](https://iovhm.com/book/uploads/images/gallery/2023-12/zjMEckj6hjwQXoo5-20231207163845.png)

（2）变量使用，变量格式：$变量名称$，使用如下:

```sql
select 
    *
from sys_log_operation where create_date between $START_DATE$ and $END_DATE$
```

(3)接口调用时添加参数

```
{
	"page": 1,//当前页
	"limit": 20,//每页条数
	"viewId": "a961d8dc8af4496aad3dfe71d9668416",//视图id
	"params": {  //视图自定义变量
		"START_DATE": [  //变量名称
			"2023-06-12"  //变量值
		],
		"END_DATE": [
			"2023-06-13"
		]
	}
}
```