用友U8接口-自定义SQL查询(5)

概括

1. 本文的操作需要正确部署U8API
2. 适合通过编写SQL语句进行数据查询，SQL语句写在xml文件中，U8SQL为接口默认文件随时修改
   

接口配置数据库信息

1. 查询将直接使用web.config的数据库账号密码，建议配置仅有查询权限账号
   

接口位置

1. SQL查询接口，根据sql语句进行查询返回一个结果集json数据

{
	"code": 0,
	"msg": "OK",
	"data": [
		{
			"MOID": 1000000001,
			"iQuantity": 2
		}
	]
}

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"
			}
		]
	}
}

接口参数

1. POST请求
2. BODY参数如下，表示调用SqlXML/CustomSQL.xml文件，路径为U8API/Person/Get里面的sql语句，参数为paramObj对象
3. CustomSQL(接口目录下SqlXML中的文件名，去除后缀！)，customSQLPath(自定义sql语句的path路径)
4. 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
    }
}

普通查询示例

1. CustomSQL.xml下的sql语句如下
2. customSQLPath：U8API/Person/Get
3. @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>  

4. 返回结果

{
	"code": 0,
	"msg": "OK",
	"data": [
		{
			"cPsn_Num": "00001",
			"cPsn_Name": "王铭",
			.....其他属性
		}
	]
}

高级查询

xml转义

1. 当sql语句有xml特殊字符需要进行转义
   
2. 比如：select * from hr_hi_person where id > 0；应该将>改写为<
3. 推荐使用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>

1. 编写T-SQL语句，本地参数以p_开始，传入参数不以p_开头，当参数没传入则为null。
2. 请求参数如下，若@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日志

总结

1. xml文件需要为标准utf-8文件，意味着当一个xml文件太多sql语句时，可以适当增加个xml文件
2. paramObj传入值会进行检查