1.我在java中发布了一个服务提供给调用,postman中可以请求解析出数据
请求信息如下：
POST /uploadPersonExcel HTTP/1.1
Host: localhost:8017
Content-Type: multipart/form-data; boundary=----WebKitFormBoundary7MA4YWxkTrZu0gW
cache-control: no-cache
Postman-Token: 1c23edfe-1e83-44c6-8f91-e06bccfd4af3
Content-Disposition: form-data; name="file"; filename="C:\Users\elite\Desktop\personinfo.xlsx
------WebKitFormBoundary7MA4YWxkTrZu0gW--
2.Ensemble中请求代码如下：
问题：锁管理里边包含很多WorkQueueMgr的锁，我想批量去移除，比较多，一个一个移除比较慢
解决：查询所有的进程，过滤routine不包含某个routine的进程，进行终止
代码：
ClassMethod BatchTerminalProcessQuery()
{ Set Rset = ##class(%ResultSet).%New("%SYS.ProcessQuery:ListPids")
d Rset.Execute()
While Rset.Next() {
s CurrentLineAndRoutine=""
&sql(SELECT CurrentLineAndRoutine INTO :CurrentLineAndRoutine FROM %SYS.ProcessQuery WHERE Pid = :Rset.GetData(1))
continue:CurrentLineAndRoutine'[".WorkQueueMgr"
w CurrentLineAndRoutine,!
s pid=Rset.GetData(1)
s sc= $System.Process.Terminate(pid)
w sc,!
;w $ZU(4,pid,1)
}
d Rset.Close()
}
csp.log日志是这个
对于第三方接口进行交互的时候，往往需要大量的进行参数合法性校验。以前的方法就是对每个参数进行验证。如下截图：
上图的会存在大量的if else if else..,如果字段很多，那导致一个方法存在大量的验证的代码，那我们考虑是否可以进行统一的验证参数的合法性。
平时建立类的时候我们可以写参数MAXLEN=100，TRUNCATE=1 是否截取等，那找找这些参数的定义地方。如截图：
那我们想要定义自己的参数，该如何定义呢？根据面向对象设计原则之一：
里氏替换原则(Liskov Substitution Principle，LSP)：子类型必须能够替换掉他们的基类型。即，在任何父类可以出现的地方，都可以用子类的实例来赋值给父类型的引用。当一个子类的实例应该能够替换任何其超类的实例时，它们之间才具有是一个 (is-a) 关系
那我们可以自定义数据类型，继承%Library.String，这样子类继续使用父类的参数，还可以自定义自己的参数。此处以字符串为例，其他的数据类型一样的原理。 自定义类型就为String.
`
/// 自定义数据类型实现继承的String
Class Design.DataType.String Extends %Library.String
{
/// 是否为空 1 必填 0 可以为空
Parameter NOTBLANK = 0;
/// 代码值，写取global的表达式? $XECUTE 执行？
Parameter DICCODE;
/// 不为空的错误消息
Parameter MESSAGE;
/// 错误码值错误
Parameter CODEERRMESSAGE;
/// 类型 INT,STRING,FLOAT,NUMBER,DATE,DATETIME
Parameter TYPE = "STRING";
/// 是否时间类型
Parameter ISDATE = 0;
/// 时间格式:yyyy-MM-dd=>3 yyyyMMdd=8 dd/MM/yyyy=1 默认
Parameter DATEFORMAT = 3;
/// 条件取值验证
Parameter CONDITION;
/// 是否需要在当前时间之后，比如预约时间
Parameter ISAFTER;
/// 是否在当前直接之前 比如出生日期
Parameter ISBEFORE;
}
`
2.定义模型类 `
Class Design.DataType.Person Extends (%RegisteredObject, %XML.Adaptor)
{
/// 姓名
Property pname As String(MAXLEN = 100, MESSAGE = "人员姓名不能为空！", NOTBLANK = 1, TRUNCATE = 1);
/// 生日
Property birth As String(DATEFORMAT = 3, ISBEFORE = 1, ISDATE = 1, MESSAGE = "出生日期不能为空！", NOTBLANK = 1, TRUNCATE = 1);
/// 性别
Property sexCode As String(CODEERRMESSAGE = "性别代码错误！", DICCODE = "$O(^CT(""SEX"",0,""Code"",", MAXLEN = 100, MESSAGE = "性别代码不能为空！", NOTBLANK = 1);
/// ...此处省略 民族，国籍，学历等等
/// 工作描述
Property job As Job;
}
`
3.统一验证的方法代码 `
/// 校验对象的工具类
Class Design.DataType.ValidUtil Extends %RegisteredObject
{
/// 判断对象是否有效
/// TODO:嵌套对象可以自己研究研究
ClassMethod IsValid(obj As %ObjectHandle, Output errmsg As %String) As %Status
{
s ClsName=obj.%ClassName(1)
s validFlag=$$$OK
s Name=""
f{
s Name=$o(^oddDEF(ClsName,"a",Name))
q:Name=""
s Val=$Property(obj,Name)
w Name_"="_Val,!
;获取属性对应的参数
;是否为空
s NOTBLANK=$g(^oddDEF(ClsName,"a",Name,"P","NOTBLANK"))
i (NOTBLANK=1)&&(Val=""){
s errmsg= $g(^oddDEF(ClsName,"a",Name,"P","MESSAGE"))
s validFlag=0
q
}
;是否码值校验
s DICCODE=$g(^oddDEF(ClsName,"a",Name,"P","DICCODE"))
i DICCODE'=""{
SET cmd="(out){ s out="_DICCODE_""""""_$$ALPHAUP^SSUTIL4(Val)_""",""""))"_" q 1}"
SET rtn=$XECUTE(cmd,.rowId)
i rowId="" {
s errmsg=$g(^oddDEF(ClsName,"a",Name,"P","CODEERRMESSAGE"))
s validFlag=0
q
}
}
;是否时间格式校验
s ISDATE=$g(^oddDEF(ClsName,"a",Name,"P","ISDATE"))
if ISDATE=1 {
s DATEFORMAT=$g(^oddDEF(ClsName,"a",Name,"P","DATEFORMAT"))
s result=$zdh(Val,DATEFORMAT,"","","","","","-1","时间格式错误","")
if result="时间格式错误"{
s errmsg="字段"_Name_"["_Val_"]"_"时间格式错误"
s validFlag=0
q
}
//时间与当前时间的验证
s ISBEFORE=$g(^oddDEF(ClsName,"a",Name,"P","ISBEFORE"))
if ISBEFORE=1{
if result>+$h {
s errmsg="字段"_Name_"["_Val_"]"_"不能超过当前日期！"
s validFlag=0
q
}
}
s ISAFTER=$g(^oddDEF(ClsName,"a",Name,"P","ISAFTER"))
if ISAFTER=1{
if result<+$h {
s errmsg="字段"_Name_"["_Val_"]"_"不能小于当前日期！"
s validFlag=0
q
}
}
}
}
q validFlag
}
}
`
测试不为空
测试code错误
3.测试时间格式错误
4.测试时间的值先后
[^1]
**参数还需要进行大量的验证，此处只是示例，可能存在错误，欢迎批评纠正**[^1]:
ensemble里边实现分页比较麻烦，毕竟对于sql的书写比较麻烦，单表的查询相对简单，对于多表的关联查询单纯的sql不好查询，我们使用sql进行先查询出主表满足条件的rowId,在根据根据满足条件的rowid进行遍历取值。
我们先取对比一下其他数据库实现的原理。
Mysql的实现原理 总数：SELECT COUNT(*) AS total FROM person WHERE (name LIKE ?) 分页：SELECT id,name,age,email FROM person WHERE (name LIKE ?) LIMIT ?,?
ORACLE的实现原理 rownum 总数：SELECT COUNT() AS total FROM person WHERE (name LIKE ?) 分页：SELECT * FROM ( SELECT TMP., ROWNUM ROW_ID FROM ( SELECT id,name,age,email FROM person WHERE (name LIKE ?) ) TMP WHERE ROWNUM <=?) WHERE ROW_ID > ?
由于cache没有limit关键字，看看有没有和oracle里边rownum一样的原理。Cache的实现原理和oracle类似 %VID 只查询主键id,在遍历取值 总数：select count() FROM Design_Page.Person WHERE birth<'1988-12-1' 分页：SELECT * FROM ( SELECT %VID ROWNUM ,TMP. FROM ( SELECT * FROM Design_Page.Person WHERE birth<'1988-12-1' ) TMP WHERE %VID <=15) WHERE ROWNUM > 5
构建查询的抽象的AbstractQueryWrapper包装类 `
Class Design.Page.V1.AbstractQueryWrapper Extends %RegisteredObject {
/// 构建sql的运算符号
Parameter AND = "AND";
Parameter OR = "OR";
Parameter NOT = "NOT";
Parameter IN = "IN";
Parameter NOTIN = "NOT IN";
Parameter LIKE = "LIKE";
Parameter NOTLIKE = "NOT LIKE";
Parameter EQ = "=";
Parameter NE = "!=";
Parameter GT = ">";
Parameter GE = ">=";
Parameter LT = "<";
Parameter LE = "<=";
Parameter ISNULL = "IS NULL";
Parameter ISNOTNULL = "IS NOT NULL";
Parameter GROUPBY = "GROUP BY";
Parameter HAVING = "HAVING";
Parameter ORDERBY = "ORDER BY";
Parameter EXISTS = "EXISTS";
Parameter NOTEXISTS = "NOT EXISTS";
Parameter BETWEEN = "BETWEEN";
Parameter NOTBETWEEN = "NOT BETWEEN";
Parameter ASC = "ASC";
Parameter DESC = "DESC";
/// 抽象类
Method addCondition(coloumParams As %String) [ Abstract ]
{
}
/// 添加字段之间的条件连接
Method addConditionOperate(operate As %String) [ Abstract ]
{
}
/// 等于的条件
Method eq(column As %String, val As %String)
{
d ..addCondition(" "_column_..#EQ _"'"_val_"' ")
q $this
}
/// 不等于
Method ne(column As %String, val As %String)
{
d ..addCondition(" "_ column_..#NE _"'"_val_"' ")
q $this
}
/// 大于的条件
Method gt(column As %String, val As %String)
{
d ..addCondition( " "_column_..#GT _"'"_val_"' ")
q $this
}
/// 大于等于的条件
Method ge(column As %String, val As %String)
{
d ..addCondition( " "_column_..#GE _"'"_val_"' ")
q $this
}
/// 小于的条件
Method lt(column As %String, val As %String)
{
d ..addCondition(" "_column_..#LT _"'"_val_"' ")
q $this
}
/// 小于等于条件
Method le(column As %String, val As %String)
{
d ..addCondition( " "_column_..#LE _"'"_val_"' ")
q $this
}
/// like 模糊匹配
Method like(column As %String, val As %String)
{
d ..addCondition( " "_column_" "_..#LIKE _" '%"_val_"%' ")
q $this
}
/// not like 模糊匹配
Method notLike(column As %String, val As %String)
{
d ..addCondition( " "_column_" "_..#NOTLIKE _" '%"_val_"%' ")
q $this
}
/// 左匹配 模糊匹配
Method likeLeft(column As %String, val As %String)
{
d ..addCondition( " "_column_" "_..#LIKE _" '"_val_"%' ")
q $this
}
/// 右匹配
Method likeRight(column As %String, val As %String)
{
d ..addCondition( " "_column_" "_..#NOTLIKE _" '%"_val_"' ")
q $this
}
/// between 拼接
Method between(column As %String, startVal As %String, endVal As %String)
{
d ..addCondition( " "_column_" "_..#BETWEEN _" '"_startVal_"' "_..#AND_"'"_endVal_"' ")
q $this
}
/// notBetween 拼接
Method notBetween(column As %String, startVal As %String, endVal As %String)
{
d ..addCondition( " "_column_" "_..#NOTBETWEEN _" '"_startVal_"' "_..#AND_"'"_endVal_"' ")
q $this
}
/// 字段值为空
Method isNull(column As %String)
{
d ..addCondition( " "_column_" "_..#ISNULL _" ")
q $this
}
/// 非空
Method isNotNull(column As %String)
{
d ..addCondition( " "_column_" "_..#ISNOTNULL_" ")
q $this
}
/// in
Method in(column As %String, valueList As %String, Separator As %String = "^")
{
d ..addCondition( " "_column_" "_..#IN_"("_..ConcatListParams(valueList,Separator)_")")
q $this
}
/// not in
Method notIn(column As %String, valueList As %String, Separator As %String = "^")
{
d ..addCondition( " "_column_" "_..#NOTIN_"("_..ConcatListParams(valueList,Separator)_")")
q $this
}
/// in list的参数拼接
Method ConcatListParams(valList As %String, Separator As %String)
{
s paramsLen=$l(valList,Separator)
q:paramsLen=1 "'"_valList_"'"
s paramsList =$lb("")
for i=1:1:paramsLen{
if (i=1) s $LIST(paramsList,1)=$p(valList,Separator,i)
else s $LIST(paramsList,*+1)=$p(valList,Separator,i)
}
q "'"_$LISTTOSTRING(paramsList,"','")_"'"
}
}
`
构建查询的包装类queryWrapper `
/// 包装查询的列和查询条件以及运算符 Class Design.Page.V1.QueryWrapper Extends AbstractQueryWrapper {
/// sql查询的列
Property SelectColoums As %String;
/// 查询的表对应的schema
Property querySchema As %String [ InitialExpression = "SQLUser" ];
/// 查询的表
Property queryTable As %String;
/// 查询条件
Property queryCondition As %String;
/// 字段，值，关系运算符，逻辑运算符构建查询条件
Method addCondition(coloumParams As %String)
{
s ..queryCondition=..queryCondition_" "_coloumParams
}
/// and 连接字段
Method and()
{
s ..queryCondition=..queryCondition_" "_..#AND
q $this
}
Method or()
{
s ..queryCondition="("_..queryCondition_") "_..#OR
q $this
}
}
3. 构建查询总数和过滤条件的sqlBuilder
/// 构建查询的sql语句
Class Design.Page.V1.SqlBuilder Extends %RegisteredObject
{
Property queryWrapper As QueryWrapper;
Method %OnNew(queryWrapper As QueryWrapper) As %Status [ Private, ServerOnly = 1 ]
{
s ..queryWrapper=queryWrapper
Quit $$$OK
}
/// 构建查询的总数据的sql
Method bulidCountSql()
{
q "select count(*) totalcount from "_..queryWrapper.querySchema_"."_..queryWrapper.queryTable_" where"_..queryWrapper.queryCondition
}
/// 构建查询执行查询业务的sql
Method bulidBusiSql()
{
q "select "_..queryWrapper.SelectColoums_" from "_..queryWrapper.querySchema_"."_..queryWrapper.queryTable_" where"_..queryWrapper.queryCondition
}
Method bulidPageSql(stOffset As %Integer, endOffset As %Integer)
{
s businessSql=..bulidBusiSql()
q "SELECT * FROM ( SELECT %VID ROWNUM ,TMP.* FROM ( "_businessSql_") TMP WHERE %VID <= "_endOffset_" ) WHERE ROWNUM > "_stOffset
}
}
4. 构建返回数据列表的IPage
/// 分页插件
Class Design.Page.V1.IPage Extends %RegisteredObject
{
/// 数据列表
Property Data As list Of %RegisteredObject;
/// 查询列表总记录数 0
Property total As %Integer [ InitialExpression = 0 ];
/// 总页数
Property pages As %Integer [ InitialExpression = 0 ];
/// 每页显示条数，默认 10
Property pageSize As %Integer [ InitialExpression = 10 ];
/// 当前页
Property currentPage As %Integer [ InitialExpression = 1 ];
/// 当前计数器
Property currentCount As %Integer [ InitialExpression = 0 ];
/// 单页分页条数限制
Property maxLimit As %Integer;
/// 分页的最后一次循环的ID
Property currentId As %String;
/// /插入数据
Method InternalInsert(obj As %ObjectHandle)
{
q ..Data.Insert(obj)
}
/// 执行往list里边插入对象的操作
Method doInsert(obj As %ObjectHandle) As %Status
{
s currentPage=..currentPage
s pageSize=..pageSize
s currentCount=..currentCount+1
s ..currentCount=currentCount
d:(currentPage=1) ..InternalInsert(obj)
d:((currentCount>((currentPage-1)*pageSize))&&(pageSize>0)&&(currentPage>1)) ..InternalInsert(obj)
;实际的页数大于等于分页的数 退出循环
q ..Data.Count()>=pageSize
}
/// 根据计算起始数和限制查询的条数
Method getOffset(Output stOffset, Output endOffset)
{
;分页数
i ..total # ..pageSize=0 d
.s ..pages= ..total/..pageSize
e s ..pages=$System.SQL.FLOOR(..total/..pageSize) +1
;当前页数
s currentPage = ..currentPage
i currentPage=1{
s stOffset=0
s endOffset=..pageSize
}else{
s stOffset=(currentPage-1)*..pageSize
s endOffset=currentPage*..pageSize
}
q $$$OK
}
/// 获取查询的结果的ID
Method selectPage(queryWrapper As QueryWrapper, Output ok) As %ArrayOfDataTypes
{
s ret = ##Class(%ArrayOfDataTypes).%New()
//拼接sql执行查询总数
s ok=$$$OK
s sqlBuilder=##class(Design.Page.V1.SqlBuilder).%New(queryWrapper)
s countTotalSql=sqlBuilder.bulidCountSql()
d ..exeCountTotalSql(countTotalSql)
q:..total=0 ret
///计算分页
d ..getOffset(.stOffSet,.edOffSet)
///获取分页执行sql
s pageSql=sqlBuilder.bulidPageSql(stOffSet,edOffSet)
///返回结果集的ID
q ..exePageSql(pageSql)
}
/// 执行查询分页sql
Method exePageSql(sql) As %ArrayOfDataTypes
{
s ret = ##Class(%ArrayOfDataTypes).%New()
s rset = ##class(%ResultSet).%New()
d rset.Prepare(sql)
d rset.Execute()
i rset.QueryIsValid() d
.While (rset.Next()) {
.d ret.SetAt(rset.GetData(1),rset.GetData(2))
.}
q ret
}
/// 执行查询总数的sql
Method exeCountTotalSql(sql) As %Status
{
s rset = ##class(%ResultSet).%New()
d rset.Prepare(sql)
s sc= rset.Execute()
i rset.QueryIsValid() d
.While (rset.Next()) {
. s ..total= rset.GetData(1)
.}
q $$$OK
}
}
`
自定义的objlist需要继承IPage `
///定义返回的对象列表
Class Design.Page.ObjList Extends (Design.Page.V1.IPage, %XML.Adaptor)
{
/// 数据列表
Property Data As list Of Object;
}
///单个对象
Class Design.Page.Object Extends (%RegisteredObject, %XML.Adaptor)
{
Property PatientName As %String;
Property PatientNo As %String;
}
`
2.测试代码
`
/// 分页查询
ClassMethod selectPage()
{
s $zt="Err"
//当前页数
s currentPage=1
//每页的大小
s pageSize=10
s objlist=##class(Design.Page.ObjList).%New()
s objlist.currentPage=currentPage
s objlist.pageSize=pageSize
//构建查询的条件
s queryWrapper =##class(Design.Page.QueryWrapper).%New()
s queryWrapper.SelectColoums="ID"
s queryWrapper.querySchema="Design_Page"
s queryWrapper.queryTable="Person"
d queryWrapper.lt("birth",$zdh("2023-12-1",3)).and().like("name","in")
;执行查询查询获取Id
s rset=objlist.selectPage(queryWrapper,.ok)
q:ok'=1 "调用出现异常"
q:objlist.total=0 "未查询到数据！"
q:rset.Count()=0 "未查询到数据！"
s RowId=""
while(rset.GetNext( .RowId)){
continue:'$d(^Design.Page.PersonD(RowId))
s obj=##class(Design.Page.Object).%New()
s obj.PatientName=$lg(^Design.Page.PersonD(RowId),2) ;患者姓名
s obj.PatientNo=$lg(^Design.Page.PersonD(RowId),3) ;病人ID号
d objlist.Data.Insert(obj)
}
w objlist.Data.Count(),!
d objlist.XMLExportToString(.xml)
w xml,!
q
Err
w $ze,!
q $$$OK
}
`
3.查询结果 `
<ObjList>
<total>23</total>
<pages>3</pages>
<pageSize>10</pageSize>
<currentPage>1</currentPage>
<currentCount>0</currentCount>
<Data>
<Object>
<PatientName>Ingrahm,Michelle X.</PatientName>
<PatientNo>436244981</PatientNo>
</Object>
<Object>
<PatientName>Koivu,Clint W.</PatientName>
<PatientNo>473036353</PatientNo>
</Object>
<Object>
<PatientName>Avery,Josephine F.</PatientName>
<PatientNo>815934238</PatientNo>
</Object>
<Object>
<PatientName>Thompson,Clint M.</PatientName>
<PatientNo>970071592</PatientNo>
</Object>
<Object>
<PatientName>Ingersol,Diane S.</PatientName>
<PatientNo>949798228</PatientNo>
</Object>
<Object>
<PatientName>Quince,Sally E.</PatientName>
<PatientNo>643134733</PatientNo>
</Object>
<Object>
<PatientName>Novello,Clint Y.</PatientName>
<PatientNo>612491568</PatientNo>
</Object>
<Object>
<PatientName>Ingrahm,Buzz O.</PatientName>
<PatientNo>72704061</PatientNo>
</Object>
<Object>
<PatientName>Ihringer,Chris M.</PatientName>
<PatientNo>112730429</PatientNo>
</Object>
<Object>
<PatientName>Anderson,Vincent V.</PatientName>
<PatientNo>507161056</PatientNo>
</Object>
</Data>
</ObjList>
`
M里边有
sha256WithRSASign
加密算法么
| Terminating Job 134859 / 'CDYZone.HospDocHealth.BO.SaveSTDData' with Status = 错误 <Ens>ErrGeneral: The JGService setting must be configured in order for this Adapter to work with a JDBC DSN : jdbc:sqlserver://192.168.165.58:1433;Database=qzj_interfacenew;integratedSecurity=false;trustServerCertificate=true;, %QuitTask= |
连接失败.
SQLState: () NativeError: [20009] Message: ，这个怎么错误码值在哪找原因呢.png)
1.在SQL网关通过odbc连接第三方的数据库，第三方的odbc驱动网上只有命令下载的，没有直接的包，这种情况下怎么弄呢 ,jdbc连接的话，jar的类路径怎么填写.png)
*** Recovery started at Wed Sep 28 08:14:28 2022 Current default directory: d:\dhc\devsoftware\ensemble\mgr Log file directory: d:\dhc\devsoftware\ensemble\mgr\ WIJ file spec: d:\dhc\devsoftware\ensemble\mgr\CACHE.WIJRecovering local (d:\dhc\devsoftware\ensemble\mgr\CACHE.WIJ) image journal file...Starting WIJ recovery for 'd:\dhc\devsoftware\ensemble\mgr\CACHE.WIJ'. 0 blocks pending in this WIJ.WIJ pass # is 0.Starting fast WIJ compareFinished comparing 24 blocks in 0 secondsExiting with status 3 (Success)09/28/22-08:14:28:644 (15060) 2 Failed to allocate 8560MB shared memory
//迭代取值
IRISIterator subscriptIter = irisNative.getIRISIterator("^liubo.clazz.personD");
System.out.println("================迭代开始===================");
while (subscriptIter.hasNext()) {
String subscript = subscriptIter.next();
//此处怎么获取此节点的字符串进行解析呢，另外java可以直接访问ensemble的global么
System.out.println(node);
}