概括
- 本文的操作需要正确部署U8API
- 适合通过编写SQL语句进行数据查询,SQL语句写在xml文件中,U8SQL为接口默认文件随时修改
接口配置数据库信息
- 查询将直接使用web.config的数据库账号密码,建议配置仅有查询权限账号
接口位置
- SQL查询接口,根据sql语句进行查询返回一个结果集json数据
{
"code": 0,
"msg": "OK",
"data": [
{
"MOID": 1000000001,
"iQuantity": 2
}
]
}
- SQL查询接口2,根据sql语句进行查询返回多个结果集json数据
{
"code": 0,
"msg": "OK",
"data": {
"DataTable": [
{
"MOID": 1000000003,
"iQuantity": 5000
}
],
"DataTable1": [
{
"EnumCode": "PE",
"EnumName": "PE"
}
],
"DataTable2": [
{
"iQuantity": 2000,
"cWhCode": "01",
"cInvCode": "01021001"
}
]
}
}
接口参数
- POST请求
- BODY参数如下,表示调用SqlXML/CustomSQL.xml文件,路径为U8API/Person/Get里面的sql语句,参数为paramObj对象
- CustomSQL(接口目录下SqlXML中的文件名,去除后缀!),customSQLPath(自定义sql语句的path路径)
- paramObj(查询语句的参数),此为json值,key为sql语句参数不能以p_开头,当需要外部传入where查询条件时,你应该写T-SQL语句,参考下面高级查询,json的key应该为@where开始的字符串,将进行检查如:@where,@where1,对应的值必须为and开始的不含分号和sql关键字sql条件语句,如: and id = 5。所有传入参数都将进行sql关键字检查,建议先在sql查询器中将sql语句写好!若检查不通过将返回空,此时查询debug日志打印的sql
{
"customSQLFileName": "CustomSQL",
"customSQLPath": "U8API/Person/Get",
"paramObj": {
"@cPsn_Num": "00001",
"@SysCompage": 168
}
}
普通查询示例
- CustomSQL.xml下的sql语句如下
- customSQLPath:U8API/Person/Get
- @cPsn_Num和@SysCompage则为参数
<U8API>
<Person desc="测试示范sql">
<Get>
select p.*,d.cDepName,d.cDepCode ,h.vdescription from hr_hi_person p left join Department d on p.cDept_num =d.cDepCode
left join HR_CT000 h on p.rPersonType = h.ccodeID
where p.cPsn_Num =@cPsn_Num and p.SysCompage=@SysCompage
</Get>
</Person>
</U8API>
- 返回结果
{
"code": 0,
"msg": "OK",
"data": [
{
"cPsn_Num": "00001",
"cPsn_Name": "王铭",
.....其他属性
}
]
}
高级查询
xml转义
- 当sql语句有xml特殊字符需要进行转义
- 比如:select * from hr_hi_person where id > 0;应该将>改写为<
- 推荐使用CDATA转义
<RdRecord11 desc="材料出库">
<Get desc="获取主表列表数据">
<![CDATA[
你的sql语句如:select * from hr_hi_person where id > 0;
]]>
</Get>
</RdRecord11>
T-SQL
<RdRecord11 desc="材料出库">
<Get desc="获取主表列表数据">
<![CDATA[
DECLARE @p_sql nvarchar(4000),@p_where nvarchar(4000),@p_code nvarchar(50);
DECLARE @p_pagesize int,@p_version bigint;
SELECT @p_code = @code,@p_pagesize = @pagesize,@p_where = @where,@p_version = @version;
IF @p_pagesize IS NULL
BEGIN
SET @p_pagesize = 25;
END;
SET @p_sql = N'select top ' + CAST(@p_pagesize AS nvarchar(50)) +' m.*,CONVERT(bigint,s.ufts) version from dbo.RecordOutQ AS m inner join dbo.RdRecord11 AS s on m.id = s.id WHERE 1=1 ' ;
IF ISNULL(@p_code,'') <> ''
BEGIN
SET @p_sql = @p_sql + ' AND m.ccode = ' + char(39) + @p_code + char(39) ;
END
ELSE IF ISNULL(@p_where,'') <> ''
BEGIN
SET @p_sql = @p_sql + @p_where ;
END
ELSE IF @p_version IS NOT NULL
BEGIN
SET @p_sql = @p_sql + ' AND CONVERT(bigint,s.ufts) > ' + CAST(@p_version AS nvarchar(100)) + ' ORDER BY s.ufts ASC';
END;
-- print @p_sql;
EXEC sp_executesql @p_sql;
]]>
</Get>
<GetWithDetail>
SELECT * FROM dbo.RecordOutQ WHERE ccode = @code;
SELECT s.* FROM dbo.RecordOutQ m inner join dbo.RecordOutSQ s on m.id = s.id WHERE m.ccode = @code;
</GetWithDetail>
</RdRecord11>
- 编写T-SQL语句,本地参数以p_开始,传入参数不以p_开头,当参数没传入则为null。
- 请求参数如下,若@where和@version不传入,则替换为null
{
"customSQLFileName": "U8SQL",
"customSQLPath": "U8API/RdRecord11/Get",
"paramObj": {
"@pagesize": 25,
"@code": "0000000002",
"@where": "AND m.ccode like '%0000000002%' OR m.cmaker like '%demo%'",
"@version": "7108589"
}
}
3. 当有多个where需要拼接,建议传@where开始的参数,对应的值会进行一个检查。当然若你确定传入拼接的sql没问题也可以不使用这个规则(必须and开头,不含英文分号和sql关键字)
{
"customSQLFileName": "U8SQL",
"customSQLPath": "U8API/RdRecord11/Get",
"paramObj": {
"@pagesize": 25,
"@code": "0000000002",
"@where": "AND m.ccode like '%0000000002%' OR m.cmaker like '%demo%'",
"@where1": "AND m.ccode like '%0000000002%' OR m.cmaker like '%demo%'",
"@version": "7108589"
}
}
问题记录
给定编码中的字符无效
xml文件编码有误,必须为UTF8,若使用windows的记事本修改,需要左上角另存为编码选UTF8!!!
如何查看执行的sql语句
查看接口目录log文件夹下的当日debug日志
总结
- xml文件需要为标准utf-8文件,意味着当一个xml文件太多sql语句时,可以适当增加个xml文件
- paramObj传入值会进行检查